date problem

[gonny]

Hi all!

I have:

$date = $row['date']; //result from above query ex. 122300433

Now I want to perform that into another query:
Ex.

$query = mysql_query("Select id, date FROM table WHERE date='$date'");

The example show where is the same date and time exatly.

The problem is, how I can do this where is not the same time but same day,moth,year.

[rob7591]

If your result is a Unix timestamp (number of seconds since Jan 1, 1970), you can use some simple math. To find the number of days since 1970, you can use this:

ceil($date / (24 * 60 * 60))
(you're dividing the number of seconds since 1970 by 60[seconds per minute] x 60[minutes per hour] x 24[hours per day], get it?)

So if you put that into your mysql query, you would need:

WHERE CEIL(date / (24 * 60 * 60)) = CEIL($date / (24 * 60 * 60))

Should work, that is if you're using Unix time stamp (although the example you provided is some time in 1973, so I don't know if that's what you're using)

[gonny]

I dont know what result show.

But first I convert date in day format:
$date = date('d m Y', $date); //13 04 2009

Now need to convert date field, but how?

$query = mysql_query("Select id, date, date_FORMAT('date', '%d %m %Y') AS newdate FROM table WHERE newdate='$date'");

This show mysql error "Error Number: 1054 Error: Unknown column 'newdate' in 'where clause' "

[LifeinAsia]

If there is no 'newdate' field in your table, "WHERE newdate='$date'" will throw an error. Try something like:
WHERE newdate=date_FORMAT('date', '%d %m %Y').

[gonny]

newdate not exist as field but I rename "date" field runnig "date_FORMAT('date', '%d %m %Y') AS newdate".

I have retrive a date from another query and now I want to run in other query but having same day-month-year.

Sorry for my english but I dont know if have explain fine what I want to do.

[LifeinAsia]

newdate not exist as field but I rename "date" field runnig "date_FORMAT('date', '%d %m %Y') AS newdate".

I know what you are trying to do, but not all databases allow this. Some do not create the alias (renaming) until after all the records have been selected. So if you try to reference the alias before that point, the DB does not recognize the alias (since it hasn't been assigned yet).

[gonny]

exist another way?

[LifeinAsia]

Did you try my last suggestion?

[gonny]

WHERE date=date_FORMAT('date', '%d %m %Y') AND id='2'");THIS IS IMPOSSIBLE where date is same date.

[LifeinAsia]

First of all, you need to come up with different variable and field names. You should NEVER use "date" (or any other reserved word) for variable names or field names. You're just asking for trouble and makes things very confusing. Like now.

Chances are that you can't do much about the table field name in this case, but at least change the variable name. Use something like:
$checkdate = date('d m Y', $date);

Then the WHERE clause would be something like:
WHERE checkdate=date_FORMAT('date', '%d %m %Y') AND id='2'

(Note- I am not a PHP programmer, so you may need to tweak it a bit.)

[gonny]

Lol
There is bit of difference to $checkdate and checkdate.

If use "WHERE checkdate=date_FORMAT('date', '%d %m %Y')" in this case the "checkdate" is a field of table_name .
And if use:
$checkdate
This represents something in this case a date retrived from preview query.

[LifeinAsia]

I told you I don't know PHP. Then use
WHERE $checkdate=date_FORMAT('date', '%d %m %Y')
if $checkdate is the correct format to use.

[gonny]

no isn't. Must be a table_name field first like this:
WHERE data_field=$datacheck

[LifeinAsia]

Then try it that way.

Don't bother trying to teach me PHP- I have no interest in learning (right now anyway). Focus on fixing your own problem.