Extracting Mysql Maths Using Php

[Supa Comix 0]

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.

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

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

[dserban 0]

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_daysafter which you would need to use:$diff = $row["diff_in_days"];in the subsequent PHP code.

Edited September 7, 2007 by dserban (see edit history)

[TavoxPeru 0]

$diff = $row["TO_DAYS('CURDATE()') - TO_DAYS('$date')"];
} linenums:0'>$sql = mysql_query("SELECT TO_DAYS('CURDATE()') - TO_DAYS('$date')");while ($row = mysql_fetch_array($sql)){ $diff = $row["TO_DAYS('CURDATE()') - TO_DAYS('$date')"];}

I view that your problem is with your sql query, change your $sql variable to:

$sql = mysql_query("SELECT TO_DAYS(CURDATE())-TO_DAYS('$date')"); // or$sql1 = mysql_query("SELECT TO_DAYS(CURDATE())-TO_DAYS('$date') as diff");

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.

$row = mysql_fetch_array($sql);$diff = $row["SELECT TO_DAYS(CURDATE())-TO_DAYS('$date')"]; // or$row1 = mysql_fetch_array($sql1);$diff1 = $row["diff"];

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:

$sql = mysql_query("SELECT DATEDIFF( CURDATE( ) , '$date') as diff");

Best regards,
Edited September 17, 2007 by TavoxPeru (see edit history)