...

View Full Version : Query Was Empty error



steviemac
08-16-2007, 05:55 AM
I am still very new to php mysql and I have run into a problem.
I am trying to do a search of a db by a unique registration number. In this table are seats that can be selected to attend a course. I want the results to echo Sum(Seats) = 25 than they can not select any more seats and if the Sum(Seats) <= 24 they can add a seat.

This is my code

<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set('track_errors', true);

if ($searching =="yes")
{
echo "<h3 align=\"center\">Results</h3>";


if ($find == "")
{
echo "<p>You forgot to enter a search term</p>";
exit;
}


mysql_connect("localhost", "xxxx", "xxxx") or die(mysql_error());
mysql_select_db("xxxx") or die(mysql_error());


$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);


$data = mysql_query("SELECT registration, SUM(Seats) as totalSeats FROM ".USERS_TABLE." WHERE upper($field) ='$find' ");
$result = mysql_query($data) or die('<hr />MySQL Error: ' .mysql_error(). '<hr />'); {


while($record = mysql_fetch_array($results))
if ($record['totalSeats'] == 25){
echo "<form action=\"updateck\" \" name=\"update\" method =\"POST\" >";
echo "<table align=\"center\" width=\"630\" cellspacing=\"1\" cellpadding=\"3\" border=\"0\" class=\"tblwhi\"><tr>";
echo "<tr><td class=\"thetag\" colspan=\"3\"><B>$result[Course] to be updated <input type=\"hidden\" size=\"30\" name=\"Course\" value=\"$result[Course]\" /></td></tr>";
echo "<tr><td class=\"thetag\">Seats Requested</td></tr>";
echo "<tr><td class='thevalue'>$result[Seats] <input type=\"hidden\" size=\"30\" name=\"Seats\" value=\"$result[Seats]\" /></td></tr>";

}
else if ($record['totalSeats'] <= 24){
echo "<form action=\"updateck\" \" name=\"update\" method =\"POST\" >";
echo "<table align=\"center\" width=\"630\" cellspacing=\"1\" cellpadding=\"3\" border=\"0\" class=\"tblwhi\"><tr>";
echo "<tr><td class=\"thetag\" colspan=\"3\"><B>$result[Course] to be updated <input type=\"hidden\" size=\"30\" name=\"Course\" value=\"$result[Course]\" /></td></tr>";
echo "<tr><td class=\"thetag\">Seats Requested</td></tr>";
echo "<tr><td class='thevalue'><select name=\"Seats\" /><option>$result[Seats]</option><option>1</option><option>2</option><option>3</option></select></td></tr>";

}
}

$anymatches=mysql_num_rows($data);
if ($anymatches == 0)
{
echo "<P>Sorry, but we can not find an entry to match your query<br><br></P>";
}


echo "<P><b>Searched For:</b> " .$find;
echo "</p>";
}
?>


I am getting the Query Was Empty error. I am not sure what I am doing wrong here. I appreciate any help.

matak
08-16-2007, 06:20 AM
i'm no mysql wizz, but form examples i saw, you don't need to escape USERS_TABLE couse it's not variable, it's plain name?

this should be correct way to query


$data = mysql_query("SELECT registration, SUM(Seats) as totalSeats FROM USERS_TABLE WHERE ".upper($field)." ='$find' ");

I added ". where upper begins too...

CFMaBiSmAd
08-16-2007, 06:43 AM
Since I don't see any output containing "Query Was Empty" I'll assume you mean the echo "<P>Sorry, but we can not find an entry to match your query<br><br></P>"; output?

That would indicate that mysql_num_rows($data) was equal to zero. Backtracking to where $data gets set, is the following query -

$data = mysql_query("SELECT registration, SUM(Seats) as totalSeats FROM ".USERS_TABLE." WHERE upper($field) ='$find' ");There is no error checking and error reporting on that query, which is odd because all the other mysql function calls in the posted code does have error checking.

Once you add some error checking to that mysql_query(), php/mysql will point you in the correct direction to find what is wrong (I suspect that the sum() function needs a GROUP BY clause.)

steviemac
08-16-2007, 01:37 PM
I'm getting the SQL error MySQL Error: Query was empty. It is not even finishing the code

I thought this was the error checking
error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set('track_errors', true);

$result = mysql_query($data) or die('<hr />MySQL Error: ' .mysql_error(). '<hr />');

I tried GROUP BY


$data = mysql_query("SELECT registration, SUM(Seats) as totalSeats FROM ".USERS_TABLE." WHERE upper($field) ='$find' GROUP BY Sum(Seats) ");

Still the same error. I'm still at a loss.

CFMaBiSmAd
08-16-2007, 02:27 PM
Upon further review, the following code has no meaning -

$data = mysql_query(... your query string that may or may not be working ...);
$result = mysql_query($data) ...$data is either a FALSE if the query failed or a result resource if the query was successful (even if there are zero rows in the result set.) In either case, you don't put that into a mysql_query() on the next line. You must form a valid SQL query string for any mysql_query().

error_reporting(), like its' name implies, sets the php error reporting level, for php code errors - http://php.net/error_reporting The error being discussed is a mysql query error and has nothing to do with php code errors.

Posting actual error messages is necessary for anyone here to see what you are describing. "MySQL Error: Query was empty" is not the same as "I am getting the 'Query Was Empty error'". Programming languages are very literal and very unforgiving. Changing wording around when describing what is occurring can result in looking in the wrong place for the cause.

Since you had mysql error reporting logic on the other mysql function calls, I assumed that when I distinctly pointed out that one of the mysql function calls did not have any, that you would get that you should add the same logic you had on the other mysql function calls to the one that was missing it. Once your add the or die(...) logic on to the end of the first mysql_query() function call, you will probably receive information as to why it is failing.

You will also need to form a query string for the second mysql_query() function call and now that I have examined the code closer, I don't see why you have the second query anyway.

steviemac
08-16-2007, 03:10 PM
I gave the mysql_query an error code. I gave me the GROUP BY error. I did some more reading on the GROUP BY function and I figured it out. Thank you for your time.

Inigoesdr
08-16-2007, 09:05 PM
But did you fix the code?

$result = mysql_query("SELECT registration, SUM(Seats) as totalSeats FROM ".USERS_TABLE." WHERE upper($field) ='$find' ") or die('<hr />MySQL Error: ' .mysql_error(). '<hr />');



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum