Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Mar 2007
    Location
    New York State
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Problem with WHERE statement

    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

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

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Does your version of mysql support "with rollup"?
    http://dev.mysql.com/doc/refman/5.0/...modifiers.html

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    New Coder
    Join Date
    Mar 2007
    Location
    New York State
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have version 5.0.41

  • #4
    New Coder
    Join Date
    Mar 2007
    Location
    New York State
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I read that article and added WITH ROLLUP and it still only counts the single row.
    Code:
    $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 />');

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #6
    New Coder
    Join Date
    Mar 2007
    Location
    New York State
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  • #7
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    It would probably be easier to select the sum in a separate query.

  • #8
    New Coder
    Join Date
    Mar 2007
    Location
    New York State
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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?

  • #9
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Yes, you just can't use the same variable for the return value or it will be overwritten.

  • Users who have thanked Inigoesdr for this post:

    steviemac (08-21-2007)

  • #10
    New Coder
    Join Date
    Mar 2007
    Location
    New York State
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks I'll work on that.

  • #11
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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:
    Code:
    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
    Code:
    SELECT id, sum(seats) FROM tablename GROUP BY id
    Will return:
    id sum(seats)
    1 8
    2 6
    3 12

  • #12
    New Coder
    Join Date
    Mar 2007
    Location
    New York State
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  • #13
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    If you know the exact course you want to check and you have a course column in your table, then do
    Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •