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 4 of 4
  1. #1
    New Coder
    Join Date
    Apr 2010
    Posts
    14
    Thanks
    4
    Thanked 0 Times in 0 Posts

    MySql less than in php not working

    My mysql less than query is not working in my php.
    My sql queries ought to filter classes that are full from the classes that are open.

    To show open classes:
    WHERE 'class_registrants' < class_capacity
    This query does show all the classes that aren't filled to max capacity in mysqladmin, but when i use this query with php it displays classes that are full as well.

    Closed classes look like this:
    WHERE 'class_registrants' >= class_capacity
    Again, this works in mysqladmin, but it shows nothing with my php query.

    index.php
    Code:
    Session 3 Class: <br />
    <select name="class_selection" size="1">
        <option name="class_selection" value="closed">Choose Class</option>
        <option>:: -- -- ::</option>
    <?php
    $con = mysql_connect("SERVER","USERNAME","PASSWORD");
    if (!$con)
      {
      die();
      echo "<option>Error accessing database.</option></select>";
      }
    
    mysql_select_db("DATABASE", $con);
    
    $classes_open = mysql_query("SELECT * FROM session3_classes
    WHERE 'class_registrants' < class_capacity")
    or die("<option>error in SQL query:</option></select><br /> " . mysql_error());
    
    while($row_open = mysql_fetch_array($classes_open))
      {
      echo "<option name=\"class_selection\" value=\"" . $row_open['id'] . "\">";
      echo $row_open['class_name'] . " :: " . $row_open['class_day'] . "s " . $row_open['class_time_pst'] . " PST :: " . $row_open['class_registrants'] . ":" . $row_open['class_capacity'] ;
      echo "</option> \n";
      }
      
      echo "<option>:: -- -- ::</option>";
    
    $classes_closed = mysql_query("SELECT * FROM session3_classes
    WHERE 'class_registrants' >= class_capacity")
    or die("<option>error in SQL query:</option></select><br /> " . mysql_error());
    
    while($row_closed = mysql_fetch_array($classes_closed))
      {
      echo "  <option name=\"class_selection\" style=\"color:#777777\" value=\"closed\" disabled=\"disabled\">";
      echo "FULL :: " . $row_closed['class_name'] . " :: " . $row_closed['class_day'] . "s " . $row_closed['class_time_pst'] . "PST";
      echo "</option> \n";
      }
    
    mysql_close($con);
    
    ?>
    
    </select>
    My table session3_classes:
    1 :: Fund of Writing :: 4 :: 0 :: Monday
    2 :: Fund of Writing :: 4 :: 4 :: Tuesday

    Please forgive my novice programming. I'm really trying here. I'm unaware of conventional dev practices, just figuring out how to make stuff work functionally as effectively and as efficiently I can.
    I appreciate any of your kindness to help me get this working correctly

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Here is your query, it is all we need to see (minus the PHP code until we know the problem isn't with the query):
    Code:
    SELECT 
      * 
    FROM 
      session3_classes
    WHERE 
      'class_registrants' < class_capacity
    Looking at that it is very easy to see the error. Remove the quotes around class_registrants. With the quotes around it you are referring to a string, without the quotes it is referring to your column called class_registrants.

  • Users who have thanked guelphdad for this post:

    RottenRobbie (12-01-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,641 Times in 4,603 Posts
    You have probably seen MySQL code that put field and/or table names in *BACK TICKS*. Back ticks are *NOT* apostrophes. They usually are on the same keyboard key as the tilde ~.

    So look carefully and you can see these are *NOT* apostrophes:
    Code:
    SELECT 
      * 
    FROM 
      session3_classes
    WHERE 
      `class_registrants` < class_capacity
    And that will work. Back ticks, however, are *only* needed when you have field or table names that match reserved MySQL keywords or have non-name characters in them (e.g., spaces or dashes). Names that start with a letter an contain only letters, digits, and underlines are okay without the back ticks (again, providing they don't match some keyword).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    RottenRobbie (12-01-2011)

  • #4
    New Coder
    Join Date
    Apr 2010
    Posts
    14
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I understand. It works grand now. Thank you!


  •  

    Posting Permissions

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