Problem with From to Query data between two dates

miller_levi

I'm having a problem getting a query of data between two dates to work.

I'm pretty sure my query structure is good (based on running it with static dates against the DB in PHPMyAdmin and getting the expect result), but when I run it as part of the script below I get no results. I'm concatenating the date values from three fields in a form (and the output of that form prints on screen correctly in the first echo statement). Any ideas what I'm doing wrong?

<body>

<?php
$username="name";
$password="password";
$database="Database";

$searchterm=$_POST["CardNum"];

$startdate = $_POST['startyear'] . '-' . $_POST['startmonth'] . '-' .
$_POST['startday'];

$enddate = $_POST['endyear'] . '-' . $_POST['endmonth'] . '-' .
$_POST['endday'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM table WHERE datecolumn BETWEEN ".$startdate." AND ".$enddate." ";

$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();

echo '<p>All cards purchased between ';
echo $startdate;
echo ' and ';
echo $enddate ;
echo '</p>';

?>
<table>
<tr>
<th><font face="Arial, Helvetica, sans-serif">Card Number</font></th>
<th><font face="Arial, Helvetica, sans-serif">Amount</font></th>
<th><font face="Arial, Helvetica, sans-serif">Date Purchased</font></th>
<th><font face="Arial, Helvetica, sans-serif">Vendor Redeemed</font></th>
<th><font face="Arial, Helvetica, sans-serif">Date Redeemed</font></th>
<th><font face="Arial, Helvetica, sans-serif">Check Number</font></th>
<th><font face="Arial, Helvetica, sans-serif">Date Paid</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {

$f1=mysql_result($result,$i,"CardNum");
$f2=mysql_result($result,$i,"Amount");
$f3=mysql_result($result,$i,"DatePurchased");
$f4=mysql_result($result,$i,"VendorRedeem");
$f5=mysql_result($result,$i,"DateRedeemed");
$f6=mysql_result($result,$i,"CheckNum");
$f7=mysql_result($result,$i,"DatePaid");
}
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f6; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td>
</tr>

<?php
$i++;
?>

</body>
</html>

johanafm

Indent your code properly so you see what belongs in each code block.

<?php
$i=0;
# This loop will run indefinitely if $num > 0
while ($i < $num)
{
	$f1=mysql_result($result,$i,"CardNum");
	$f2=mysql_result($result,$i,"Amount");
	$f3=mysql_result($result,$i,"DatePurchased");
	$f4=mysql_result($result,$i,"VendorRedeem");
	$f5=mysql_result($result,$i,"DateRedeemed");
	$f6=mysql_result($result,$i,"CheckNum");
	$f7=mysql_result($result,$i,"DatePaid");
}
?>

<?php
$i++;
?>

Since this isn't the problem you are describing, I'm assuming your code doesn't look like this code does.

Also, using mysql_result is slower than using one of the mysql_fetch_* functions.

miller_levi

I'll be sure to check out the mysql_fetch_* functions.

I'm sure it's my ignorance preventing me from see it, but what is wrong with the code snippet you quoted? My code looks exactly like what i posted and I used the same code for other (not date related) queries and it seems to work fine.

bradgrafelman

miller.levi;10986847 wrote:
I'm sure it's my ignorance preventing me from see it, but what is wrong with the code snippet you quoted?

For one, the values of the variables inside the while() loop are never used before they are overwritten by the next iteration of the loop. In other words, you might as well just throw away all results except the last one since that's all you'd ever be able to use.

For another, you never increment $i inside the loop, so $i will always be 0 for every iteration. In other words, the while() loop will either run 0 times or an infinite number of times.

miller.levi;10986847 wrote:
My code looks exactly like what i posted and I used the same code for other (not date related) queries and it seems to work fine.

I highly doubt that due to the errors above.