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>";
}
?>

am getting the Query Was Empty error. I am not sure what I am doing wrong here. I'm still very new to PHP MYSQL and I appreciate any help.

[Ernie1]

I've fixed some erros, try this:

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' ");
    
    
    while($record = mysql_fetch_array($data)){
        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>$record[Course] to be updated <input type=\"hidden\" size=\"30\" name=\"Course\" value=\"$record[Course]\" /></td></tr>";
            echo "<tr><td class=\"thetag\">Seats Requested</td></tr>";
            echo "<tr><td class='thevalue'>$record[Seats] <input type=\"hidden\" size=\"30\" name=\"Seats\" value=\"$record[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>$record[Course] to be updated <input type=\"hidden\" size=\"30\" name=\"Course\" value=\"$record[Course]\" /></td></tr>";
            echo "<tr><td class=\"thetag\">Seats Requested</td></tr>";
            echo "<tr><td class='thevalue'><select name=\"Seats\" /><option>$record[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>";
    } 

[steviemac]

I am getting the following error.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result

I can see that it is going through the code because it echoed the searched for.

[kleineme]

insert a line after the one with mysql_query in order to get a better error message:

echo "Error: ", mysql_error();

My bet though would be that you are using SUM without a GROUP BY, so try this:

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

[steviemac]

Thank you, I gave the mysql_query an error statement and I got the GROUP BY illegal operation or to that effect. Now everything works fine.
Thank you for your time.

[kleineme]

You're welcome :-)