...

View Full Version : MySql less than in php not working



RottenRobbie
11-30-2011, 06:37 AM
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

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 :)

guelphdad
11-30-2011, 05:29 PM
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):


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.

Old Pedant
12-01-2011, 05:33 AM
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:


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).

RottenRobbie
12-01-2011, 04:31 PM
I understand. It works grand now. Thank you!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum