Retrieving Data From Database in PHP

[VitaminWater]

I am making a script in which a person is able to post things to an online weblog. The user submits a form and its added to the database. I am using this code to retrieve data from my database, and put it on my main page.


mysql_connect("freshsql.com:","","");
mysql_select_db("");
$query ="SELECT type, poster, during, time";
$query.=" FROM ws ORDER BY time DESC LIMIT 20";
$result=mysql_query($query);
while (list($type,$poster,$during,$time) =
mysql_fetch_row($result)) {


However, instead of 'LIMIT 20' i would like the limit to be only posts that were made on the current day. How do i accomplish this?
If i use the date() function can i do something like


<?php
$today = date("l, F j, Y");
$query.=" FROM ws ORDER BY time DESC LIMIT $today";

Thank you.

[Twey]

Rather than a limit, use a WHERE clause: ... FROM ws WHERE SUBDATE(NOW(), time) < INTERVAL 1 DAY ORDER BY time DESC

[VitaminWater]

Rather than a limit, use a WHERE clause: ... FROM ws WHERE SUBDATE(NOW(), time) < INTERVAL 1 DAY ORDER BY time DESC

Thank you very much for this response.
I dont exactly understand the SUBDATE(NOW(), time) <INTERVAL 1 DAY.

When my user submits the form it also adds a date into the database, November 22, 2008, this is put into the timeRef field. Do i need to change this code to something like this?


<?php
$today = date("l, F j, Y");
?>
...FROM ws WHERE timeRef='$today' ORDER BY time DESC

I would test this myself but for the time being my host is down and I'm unable to update anything.
THanks very much for the help

[Twey]

No. The code is fine as it is; all date calculation is done within the SQL. WHERE SUBDATE(NOW(), time) < INTERVAL 1 DAY means 'where the value of the column time subtracted from the current date/time yields less than one day'. In other words, 'where the value of time is somewhere within the past 24 hours'.

[VitaminWater]

$result = mysql_query("SELECT type, poster, during, time, timeRef FROM ws
WHERE SUBDATE(NOW(), time) < INTERVAL 1 DAY ORDER BY time DESC") or die(mysql_error());

I have the above code however i am getting an error when trying to view it, 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 'ORDER BY time DESC' at line 2

[Twey]

Oh, here's a more elegant solution anyway: ... WHERE TO_DAYS(NOW()) - TO_DAYS(time) < 1.

[VitaminWater]

I dont think i am able to use this code in the way i added the data into my database.
When i implemented the code above, i get no results.


<?php
mysql_connect("");
mysql_select_db("");

$type = addslashes($HTTP_POST_VARS["type"]);
$poster =addslashes($HTTP_POST_VARS["poster"]);
$during = addslashes($HTTP_POST_VARS["during"]);
$time =$HTTP_POST_VARS["hiddenField"];
$timeRef =$HTTP_POST_VARS["hiddenField2"];
$preTime =$HTTP_POST_VARS["hiddenField3"];



$query ="INSERT INTO ws (type,during,poster,time,timeRef,preTime)";
$query.="VALUES ('$type','$during','$poster','$time','$timeRef','$preTime')";
$result=mysql_query($query);
if ($result) echo "<b>Successfully Posted!</b>";
else echo "<b>ERROR: unable to post.</b>";
?>
<p><a href="index.php">Click Here to View</a></p>

And when calling for the information on the main page it is sorted by $time which is in the form of,


$today = date("l, F j, Y. g:i A");

[Twey]

What is the type of the time column? It should be DATETIME.

[VitaminWater]

The time column is nothing special, its just time, varchar(100) and thats it. What special qualities do i have to give it to make it DATETIME?
THank you.

[Twey]

Dates and times do not go in strings for the same reasons that integers do not go in strings. Use string types for string data, integer types for integer data, and date/time types for date/time data. MySQL provides four different types for date/time data: DATE, TIME, DATETIME, and TIMESTAMP. You will need to alter the field and do it properly. DATETIME is the type you want.