trying to show everything except for records created "today"

[RyanSF07]

Hi Guys,

 

I'm trying to display the content added to the database from the beginning of time until "yesterday" (or the day before). This is what I have so far and it returns a general error.

 

$sql = "SELECT video.id, title, description_text, category_text, level_text, user_name, 
DATE_FORMAT(date, '%M %D, %Y') as date FROM video, 
registered_users WHERE video.user_id = registered_users.id, 
(TO_DAYS(NOW()) - TO_DAYS(DATE_FORMAT(created,'%M %D %Y'))) < 2, ORDER BY id DESC";

 

Is there a better way to do this?

 

Thanks much for your help!

Ryan

[kenrbnsn]

What's the error?

 

Ken

[RyanSF07]

Thanks Ken,

 

The error is:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/5640/domains/eslvideo.com/html/ESL_WebTech_7/allquizzes.php on line 17

 

note: everything works perfectly until I add this condition:

(TO_DAYS(NOW()) - TO_DAYS(DATE_FORMAT(created,'%M %D %Y'))) < 2,

 

..which triggers the error.

 

I'm trying to form a condition that limits the data displayed to everything EXCEPT for what has been inserted "today."

Ryan

[redarrow]

$sql =SELECT video.id, title, description_text, category_text, level_text, user_name, FROM video
WHERE video.user_id = registered_users.id and DATE_SUB(CURDATE(),INTERVAL - 2 DAY) <= date ORDER BY id DESC;

[RyanSF07]

Thanks Redarrow,

 

Unfortunately, the same error message was triggered.

 

I'm new at php.. is this part problematic: "DATE_FORMAT(date, '%M %D, %Y') as date" ?

 

what is generating the error?

 

Ryan

[RyanSF07]

Here are the attributes of the date row in the video table.

 

Is my error that something is simply missnamed?

Thanks again for your help!

 

date  timestamp  ON UPDATE CURRENT_TIMESTAMP  No  CURRENT_TIMESTAMP

 

 

Ryan

[RyanSF07]

Still trying to solve this...

All ideas appreciated.

 

Thanks,

Ryan

[per1os]

You need to put an OR DIE after the mysql_query($sql) OR DIE(mysql_error()); and report the sql error.

[RyanSF07]

Here is the error that is generated:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM video WHERE video.user_id = registered_users.id and DATE_SUB(CURDATE(),INTE' at line 1

 

I tried changing CURDATE to CURRENT_TIMESTAMP, but that just seemed to slow everything way down -- the page never loaded.

 

Thanks for you help,

Ryan

 

 

[RyanSF07]

Any other ideas?

 

[Lytheum]

$sql =SELECT video.id, title, description_text, category_text, level_text, user_name, FROM video

WHERE video.user_id = registered_users.id and DATE_SUB(CURDATE(),INTERVAL - 2 DAY) <= date ORDER BY id DESC;

 

Remove the comma that I colored.  Not sure if this would cause an error though

[RyanSF07]

Thanks. I tried removing the comma but still get the error.

 

I'm sure it has everything to do with the "time" conditional as without that bit the page loads fine.

 

still searching...

 

Thanks for your suggestion!

 

Ryan

[Lytheum]

Do you have two separate tables from which you are trying to cross-reference?

 

'video' and 'registered_users'?

 

That's the impression I got from the first post,  but in the latter ones you stopped using the 'registered_users' table. Maybe I'm wrong.

 

[RyanSF07]

Thanks Lytheum,

 

That helped. I think I'm almost there... but still getting an error.

 

With this:

$sql = "SELECT video.id, title, description_text, category_text, level_text, user_name, 
FROM video, registered_users WHERE video.user_id = registered_users.id and DATE_SUB(CURDATE(),INTERVAL - 1 DAY) <= date ORDER BY id DESC";

 

I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM video, registered_users WHERE video.user_id = registered_users.id and DATE_' at line 2

 

And, if I leave the date part in, as in:

$sql = "SELECT video.id, title, description_text, category_text, level_text, user_name, 
DATE_FORMAT(date, '%M %D, %Y') as date FROM video, 
registered_users WHERE video.user_id = registered_users.id and DATE_SUB(CURDATE(),INTERVAL - 1 DAY) <= date ORDER BY id DESC";

 

then the page loads without any errors, yet nothing is selected/displayed from the database. ... it loads without error, properly formatted and everything, however no content is displayed  ???

 

Thanks again for all your help!

Ryan

[Lytheum]

Maybe it would be smarter to make two separate sql statements. One for the registered_users table and another for the video table. Then you could generate a statement comparing the data from both. What is the structure of both of your tables, in this format:

 

->Database

-->Table

--->Column

--->Column2

--->Etc..

 

?