...

View Full Version : Problem with WHERE statement



steviemac
08-21-2007, 10:51 PM
Hello,
I'm not sure how to code this. I want the total of a column called Seats. My problem is the WHERE statement. It is only giving me the amount of seats in that row. I need to add a AND statement to it so it will total the column and I'm not sure what. I appreciate any help.

Thank You :)



Update <?php echo "" .COURSE_NAME; ?> </h3>
<form name="search" method="post" action="<?=$PHP_SELF?>" onSubmit="return validateCompleteForm(this);">
<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>
<P>Forgot you registration code? <a href="forgotregistration.php">Go here</a></P>
<center><img src="../../images/blkline.jpg"></center>

<?php include '../db_config.php'; ?>
<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set('track_errors', true);
//This is only displayed if they have submitted the form
if ($searching == "yes")

{

echo "<h3 align=\"center\">Results</h3>";





if ($find == "")

{

echo "<p>You forgot to enter a search term</p>";

exit;

}











$find = strtoupper($find);

$find = strip_tags($find);

$find = trim ($find);





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





while($result = mysql_fetch_array($data)) {

if ($result['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\">Seats Requested</td></tr>";
echo "<td class='thevalue'><P align=\"center\">$result[Seats]</p> <input type=\"hidden\" size=\"30\" name=\"Seats\" value=\"$result[Seats]\" /></td></tr>";
echo "<tr><td colspan=\"\"><input type=\"submit\" name=\"submit\" value=\"Update Selected\" /></td></tr>";
echo "<tr><td colspan=\"\"><center><img src=\"../../images/blkline.jpg\"></center></td></tr></table>\n";
echo "</form>\n";

}



else{
echo $result['totalSeats'];
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\">Seats Requested</td></tr>";
echo "<tr><td class='thevalue'><P align=\"center\"><select name=\"Seats\" /><option>$result[Seats]</option><option>1</option><option>2</option></select></p></td></tr>";
echo "<tr><td colspan=\"\"><input type=\"submit\" name=\"submit\" value=\"Update Selected\" /></td></tr>";
echo "<tr><td colspan=\"\"><center><img src=\"../../images/blkline.jpg\"></center></td></tr></table>\n";
echo "</form>\n";

}



}



$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>";

}

?>

nikkiH
08-21-2007, 11:21 PM
Does your version of mysql support "with rollup"?
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

steviemac
08-21-2007, 11:29 PM
I have version 5.0.41

steviemac
08-21-2007, 11:36 PM
I read that article and added WITH ROLLUP and it still only counts the single row.

$data = mysql_query("SELECT *, SUM(Seats) as totalSeats FROM " . USERS_TABLE . " WHERE upper($field) ='$find' GROUP BY registration WITH ROLLUP LIMIT 1") or die('<hr />MySQL Error: ' .mysql_error(). '<hr />');

Fumigator
08-22-2007, 12:00 AM
In a query using GROUP BY you can't select columns that aren't included in the GROUP BY clause. SELECT * FROM tablename GROUP BY column1 makes no sense-- you will not have any control what is returned in any column except column1.

It may also help you if you build your desired query inside MyPHPAdmin or some other adhoc environment to get it working the way you want and then code it into your PHP script. You can separate database mechanics from PHP mechanics which will give you better clarity concerning what you are trying to achieve.

steviemac
08-22-2007, 12:12 AM
I'm really just learning PHP/MYSQL and I just learned about the GROUP BY clause the other day. Everything I have seen or read shows them using a GROUP BY with a column name, thats why I used that.

Inigoesdr
08-22-2007, 12:22 AM
It would probably be easier to select the sum in a separate query.

steviemac
08-22-2007, 12:37 AM
Your probably right but the form is going to an update form. Can I run a seperate query in the same form with one submit button? :confused:

Inigoesdr
08-22-2007, 12:45 AM
Yes, you just can't use the same variable for the return value or it will be overwritten.

steviemac
08-22-2007, 12:46 AM
Thanks I'll work on that.

Fumigator
08-22-2007, 01:27 AM
Everything I have seen or read shows them using a GROUP BY with a column name, thats why I used that.

The proper way to use GROUP BY is:


SELECT column1, count(*), sum(column2) from tablename GROUP BY column1


What GROUP BY does is combines a bunch of rows based on the value of one column. If you GROUP BY registration for example, then it takes every row with the same value in the registration column and globs them together. That's why you only want to do count, sum, and other column functions within that same query.

Example:
id seats
1 4
1 2
1 2
2 1
2 5
3 3
3 1
3 6
3 2


SELECT id, sum(seats) FROM tablename GROUP BY id

Will return:
id sum(seats)
1 8
2 6
3 12

steviemac
08-22-2007, 02:43 AM
I think this is the problem. What I want it to do is total the column (Seats). 1+1+1=3. For example 10 people have signed up for a course and have taken the maximum of 25 seats that are allowed. If they enter their registration number and Seats == 25 than they cannot sign up for any more seats but if Seats == 24 they can have the last seat. :)

Fumigator
08-22-2007, 05:13 AM
If you know the exact course you want to check and you have a course column in your table, then do


SELECT sum(seats)
FROM yourtable
WHERE course = $whateverCourseItIsYouAreChecking


If there is no column indicating course (either by ID or name or whatever) then we probably need to to see your database design to figure out how to phrase the query.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum