[RESOLVED] Select record by timestamp

[TrielD]

I hope someone can help me. I have a table that I created that has a timestamp field in it called "start". I am trying to select all the records that have a timestamp equal to a date a user enters.

Here is what I have but the query is coming up empty even tho the date being displayed when it says "no ads meet the date you entered" actually does meet dates in the MySQL table. :mad:

// Format incoming date to correct format
$year = $_GET['yr'];
$month = $_GET['mth'];
$day = $_GET['day'];
$hour = 00;
$min = 00;
$sec = 00;

// use mktime for changing to UNIX time
$dspdate = mktime(0, 0, 0, $month, $day, $year);
$displaydate = date("Y-m-d H:i:s", $dspdate);

$query = mysql_query("SELECT * FROM classifieds WHERE starts = $displaydate"); 

$result = mysql_query($query); 

	if (!$result) { 
		echo("<p>No Ads meet the date you entered.</p>");
		echo("<p>The date entered was: $displaydate</p>");
	}

The date showing on my screen is: The date entered was: 2007-05-03 00:00:00

The date in the record is: 2007-05-03 00:00:00

so I don't understand why the query is coming up empty.

Thanks!

[NogDog]

You need to put single quotes around $displaydate in the SQL, as it is a string, not a numeric value. I'd suggest using this pattern, at least while debugging, to catch any other query syntax errors:

$sql = "SELECT * FROM classifieds WHERE starts = '$displaydate'";
$result= mysql_query($sql) or die("Query failed ($sql): " . mysql_error());
if(mysql_num_rows($result))
{
   // display results
}
else
{
   // no matches found
}

PS: I just noticed that you do two consecutive calls to mysql_query(), the second time using the result of the first call as the argument to the second. That ain't never gonna work. 😉

[TrielD]

Thanks for your quick reply.

I did try with the single quotes before posting here and it didn't work. But since as you said I was doing two consecutive calls I said well let's see! LOL

It still does not work and I am wondering why the heck it prints out the messages:

"No Ads meet the date you entered.

The date entered was: 2007-03-05 00:00:00"

before it even prints out the headings! The headings are in code before the loop for the query.

<table> 
<tr> 
<td><strong>Title</strong></td> 
<td><strong>Description</strong></td> 
</tr> 

// ?php

// Format incoming date to correct format
$year = $_GET['yr'];
$month = $_GET['mth'];
$day = $_GET['day'];
$hour = 00;
$min = 00;
$sec = 00;

// use mktime for changing to UNIX time
$dspdate = mktime(0, 0, 0, $month, $day, $year);
$displaydate = date("Y-m-d H:i:s", $dspdate);

$sql = "SELECT * FROM classifieds WHERE starts = '$displaydate'"; 
$result= mysql_query($sql) or die("Query failed ($sql): " . mysql_error()); 

/* Retrieves the rows from the query result set and puts them into a HTML table row */ 
if(mysql_num_rows($result)) 
{ 
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
     	echo("<tr><td>" . $row['title'] . "</td>"); 
	 	echo "<td>" . $row['description'] . "</td>";
	 	echo "<td><a href=\"ad.php?id=".$row[id]."\">View</a></td></tr>"; 
	}  // end while
} 
else 
{ 
   // no matches found 
            echo("<p>No Ads meet the date you entered.</p>"); 
            echo("<p>The date entered was: $displaydate</p>"); 
} // end if

/* Closes the table */ 

P.S. The errors will not show up on my server anyway, they are turned off. :queasy:

I also know I should only be selecting the id, title and description since that is all I am using.

[NogDog]

To turn errors on, add this to the start of your script:

<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
// ... rest of script ...
?>

I think the reason the error message is getting displayed before the table headers is just because it's resulting in broken HTML: the error <p> elements are output before a closing </table> tag is output.

[TrielD]

Well that doesn't help for this server, at least I have been told that. I put it in anyway just for grins. I got nothing from it.

But I still don't understand why this is not working. I did check in MySQL Browser with the statement to make sure that I was formatting my statement right and it did come up with the data (of course I don't use a variable).

SELECT * FROM classifieds WHERE starts = '2007-05-03 00:00:00'

(Also I took out the <p> and just made it a <br> at the end of the line and I am still getting the messages before my headings! I made it all straight php thinking that too may matter. :eek: The reason I was concerned about this was because in my paging it prints out the page navigation before my headings and data. Not that big of a deal just was wondering why.)

<form action="ad.php" method="post" name="form1" id="form1">			
<?php

/* Starts the table and creates headings */ 
echo("<table><tr>"); 
echo("<td><strong>Title</strong></td>");
echo("<td><strong>Description</strong></td></tr> ");

// Format incoming date to correct format
$year = $_GET['yr'];
$month = $_GET['mth'];
$day = $_GET['day'];
$hour = 00;
$min = 00;
$sec = 00;

// use mktime for changing to UNIX time
$dspdate = mktime(0, 0, 0, $month, $day, $year);
$displaydate = date("Y-m-d H:i:s", $dspdate);

$sql = "SELECT * FROM classifieds WHERE starts = '$displaydate'"; 
$result= mysql_query($sql) or die("Query failed ($sql): " . mysql_error()); 

/* Retrieves the rows from the query result set and puts them into a HTML table row */ 
if(mysql_num_rows($result)) 
{ 
	//while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
     	echo("<tr><td>" . $row['title'] . "</td>"); 
	 	echo "<td>" . $row['description'] . "</td>";
	 	echo "<td><a href=\"ad.php?id=".$row[id]."\">View</a></td></tr>"; 
	//}  // end while
} 
else 
{ 
   // no matches found 
            echo("No Ads meet the date you entered.<br>"); 
            echo("The date entered was: $displaydate<br>"); 
} // end if

/* Closes the table */ 
echo("</table> </form>");
?>


		</div>
	</div>
<div id="Num"><img src="Images/line.jpg" alt="line" width="779" height="4"></div>
<div id="footer">
<div class="footerTXT" id="footertxt"><span class="style3">Copyright 2007 &copy; <a href="#" class="footerTXT">e-classifieds.com</a>. All rights reserved.</span></div>
</div>
</div>

<?php 

/* Closes Connection to the MySQL server */ 
mysql_close($connection); 
?> 

</body>
</html>

[TrielD]

OMG Never mind! Thank you!

I just hit my back button instead of hitting my link to select by date. It works thanks so much! 🙂

🆒