[RESOLVED] error-query is empty--- its not HONEST!!

[noobyphpchick]

Surprise! i'm stuck again:p
i'm trying to search a table which contains a column date, i would like to display which videos are on loan between certain dates, i get the error ... Error: Query was empty.
i have 2 input boxes containing sdate and edate.
then i assign them to the variable and then the query then print it out

$sDate = $_POST['sDate'];
$eDate = $_POST['eDate'];

$result = mysql_query ("SELECT c.Firstname, c.Lastname, v.Title from Customer as c inner join Loan as l on (c.customerId = l.customerId) inner join Video as v on (v.videoId = l.videoId) where l.date >= sDate and l.date <= eDate");

if (!mysql_query($result,$con))
  {
  die('Error: ' . mysql_error());
  }

echo "<table border='1'>
<tr>
<th>FirstName</th>
<th>lastName</th>
<th>title</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['firstName'] . "</td>";
  echo "<td>" . $row['lastName'] . "</td>";
  echo "<td>" . $row['title'] . "</td>";

  echo "</tr>";
  }
echo "</table>";

its not printing the result and its telling me. Error: Query was empty.
although if i get rid of the die('Error: ' . mysql_error()); bit i get the error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ........ on line 91

this is driving me maaaaad lol
appreciate if anyone can shed some light for me :o

[laserlight]

One possible problem is that "where l.date >= sDate and l.date <= eDate" should be "where l.date >= $sDate and l.date <= $eDate". Of course, you have to ensure that $sDate and $eDate are in the correct format to begin with since they are from incoming variables.

[noobyphpchick]

they are in date format 0000-00-00
i have been running the query in mysqlcc and i get a result with

SELECT c.Firstname, c.Lastname, v.Title from Customer as c inner join Loan as l on (c.customerId = l.customerId) inner join Video as v on (v.videoId = l.videoId) where l.date >= 0000-00-00;

but not with this-

SELECT c.Firstname, c.Lastname, v.Title from Customer as c inner join Loan as l on (c.customerId = l.customerId) inner join Video as v on (v.videoId = l.videoId) where l.date >= 0000-00-00 and l.date <= 3000-12-31;

can't think of what else to try now, other than changing the database to have 2 columns of eDate and sDate rather than just date. :eek: 😕

[NogDog]

The literal date values need to be quoted: they are strings, not numbers. Also, you left out the "$" on the PHP variables names used in your original query. It should look something more like:

"SELECT c.Firstname, c.Lastname, v.Title from Customer as c 
inner join Loan as l on (c.customerId = l.customerId) 
inner join Video as v on (v.videoId = l.videoId) 
where l.date >= '$sDate' and l.date <= '$eDate'"

For debugging purposes, you might want to do something like the following:

$sql = "SELECT c.Firstname, c.Lastname, v.Title from Customer as c 
inner join Loan as l on (c.customerId = l.customerId) 
inner join Video as v on (v.videoId = l.videoId) 
where l.date >= '$sDate' and l.date <= '$eDate'";
$result = mysql_query($sql) or die("Query failed: [$sql]: " . mysql_error());

Eventually you'll want to get rid of the "or die()" and do some more robust error-handling that won't output database details to the user if it fails.

[noobyphpchick]

Thankyou PHP + mysql GODS🙂
after a bit of fiddling and that fab error code nogdog it works

now on to the next challenge😉- see you in a few hours i imagine :o lol

[bradgrafelman]

Note that the problem in your original code was that $result contained the resource (or FALSE) returned by [man]mysql_query/man, but then in your if() statement you again execute [man]mysql_query/man using $result as the first parameter.