Right, this is a really simple thing and it has me completely stumped. I'm working on this mini maths function and for some reason i cannot seem to do some simple math process using mysql. This is the code: (php btw), now assume that $date is actually a defined mysql date variable already successfully extracted.

[codebox]$sql = mysql_query("SELECT TO_DAYS('CURDATE()') - TO_DAYS('$date')");
while ($row = mysql_fetch_array($sql)){
$diff = $row["TO_DAYS('CURDATE()') - TO_DAYS('$date')"];
}
[/codebox]

Can anyone spot what im doing wrong becuase im just thrown by it.

Aha, I think I have the answer to that - you need to use a so-called "column alias".
The SQL ANSI standard allows you to do things like:
SELECT table.column AS alias FROM ...
Aliases are mostly optional, but not in your particular case. Here you absolutely do need an alias because you are referencing the result of a formula. Therefore your piece of SQL would look like:
SELECT current_date_to_days - some_other_date_to_days AS diff_in_days
after which you would need to use:
$diff = $row["diff_in_days"];
in the subsequent PHP code.

This post has been edited by dserban: Sep 7 2007, 07:18 AM

I view that your problem is with your sql query, change your $sql variable to:
But now, why do you want this and then loop through your recordset???, the result of your query will return only one row with one column, so, instead simply use the mysql_fetch_array directly without the while.
Another way to get the difference between two dates is by using the DATEDIFF MySql Function. This function returns the number of days between start and end date, notice that the TO_DAYS function returns a daynumber (the number of days since year 0).

So, i think that you can also use the following to get the same result:
Best regards,

This post has been edited by TavoxPeru: Sep 17 2007, 05:35 PM