[SOLVED] Problem with query

[steviemac]

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 WasEmpty error.  I am not sure what I am doing wrong here. I'm still very new to PHP MYSQL and I appreciate any help.

[trq]

Where do you define $field used in your query?

[steviemac]

In the form they input their registration number in.

 

<form name="search" method="post" action="<?=$PHP_SELF?>">
<P align="center"><b>Enter Course Registration Number:</b> <input type="text" name="find" id="find" /> 
<input type="hidden" NAME="field" VALUE="registration">



<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</P> 
</form>

[Sesquipedalian]

Line 27:

 

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

 

Line 30:

 

while($record = mysql_fetch_array($results))

 

I think maybe if on line 30 you remove the "s" from "$results" making it "$result" to correspond the two lines rather than them not matching it should probably work.

 

Unless I missed something.

[steviemac]

I saw that and fixed it.  I am still having the problem.

[Sesquipedalian]

Maybe on line 26:

 

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

 

try making sure that the variables are noticed.. by doing like this:

 

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

 

I guessed that $field is a variable as well. Try to make sure it's like this everywhere.

 

or er.. nevermind said its solved