Jump to content
xisto Community
Supa Comix

Extracting Mysql Maths Using Php

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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 by dserban (see edit history)

Share this post


Link to post
Share on other sites

    $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 by TavoxPeru (see edit history)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.