Using the DATE function.

[champbronc2]

OK I have a table and it has lastlogdate inserted as a VARCHAR in a format looking like such: "October 6, 2008 -3:50 PM"

 

I am trying to do this:

Select * From users where referer='' AND date LIKE %curdate%

 

But I need so that current date is in the format of Full month name, Day number (w/o zero) and year.

 

And it needs to really search in this format assuming October 6, 2008is the date : %October 6, 2008%

 

What should my line of code look like?

[F1Fan]

Check this out:

http://www.w3schools.com/php/func_date_date.asp

[champbronc2]

I have. Still not getting it.

[F1Fan]

Are you trying to do this with PHP or SQL? If PHP, list your code.

[DarkWater]

Don't store MySQL dates as VARCHAR columns.  They need to any one of the MySQL native date column types, like DATE or DATETIME.  They can be easily manipulated with the MySQL DATE_FORMAT function.

[champbronc2]

It is php.

 

Users without referer: <b><?
$checkpemaile = mysql_query("SELECT * FROM users WHERE referer='' AND date LIKE %curdate% AND visits>0");
$pemail_existe = mysql_num_rows($checkpemaile);

[DarkWater]

Read my post.  You definitely don't want a VARCHAR column holding dates.

[F1Fan]

DW's right. You really need to reformat your column type. Also, what you're trying to do is not PHP, it's SQL. I know you're using PHP, but what you need help with is SQL.

[champbronc2]

But then when I do that, the format is in a 0000-00-00 format..

[DarkWater]

But then when I do that, the format is in a 0000-00-00 format..

 

Yeah, because you're not inserting the date properly.  In order to change it to a DATETIME now, you'd need to create a script that parses every single row and updates it to use the right column format.  Also, when inserting new rows, you'd be best off using the NOW() function in MySQL to have it automatically fill in the date and time.

[champbronc2]

Wouldn't it just be easier to have a script search for the current date in that format though? With %...%?

[DarkWater]

Wouldn't it just be easier to have a script search for the current date in that format though? With %...%?

 

No.