Adding an If condition to modify a SQL query

@lightnbJul 11.2006

I’m trying to add an if condition, so that when the form field ‘min_transmision’ is filled in, a where statement is added to the end.

I’m trying:

[code=php]if(isset($_POST[‘color_submit’])) { $color_number_input = $_POST[‘color_number_input’]; $query_string = “SELECT color_name, color_number, transmission_value_percent, correction, manufacturer FROM roscolux_color”;

if(isset($_POST[‘min_transmission’])) { $query_string = “$query_string WHERE `transmission_value_percent` >= $_POST[‘min_transmission’]”; }

$sql = mysql_query(“$query_string”); echo(mysql_error());[/code]

But get:

[CODE]Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/.sites/22/site13/web/color_information_enchanced.php on line 50[/CODE]

to post a comment

PHP

13 Comments(s) _↴

@mwmwnmwJul 11.2006 — #For starters....

$query_string = "$query_string WHERE transmission_value_percent >= $_POST['min_transmission']";

Has to be changed to:

$query_string = $query_string." Where transmission_value_percent >= $_POST['min_transmission']";

No way for me to assess the validity of the variables you have put in the string, but this should go a long way toward correcting the problem. As you had it you were literally putting the letters $query_string into your query rather than adding on to the variable $query_string. Be sure to add the space after you open the quotes before the Where.

@lightnbauthorJul 11.2006 — #Thanks. ? I've made the change, but am still getting a parse error.

new code block is:

[code=php]if(isset($_POST['color_submit']))
 {
 $color_number_input = $_POST['color_number_input'];
 $query_string = "SELECT color_name, color_number, transmission_value_percent, correction, manufacturer FROM roscolux_color";
 
 
  if(isset($_POST['min_transmission']))
   {
   $query_string = $query_string." WHERE transmission_value_percent >= $_POST['min_transmission']";
   }
 
  $sql = mysql_query("$query_string");
  echo(mysql_error());[/code]

@mwmwnmwJul 11.2006 — #Ok, now you're down to a problem w/ a variable. Was the error message the same? I've never had to call a post variable directly in a query before, so I can't say for sure that your quotes are correct there. Just thinking about how php compiles, you may want to declare the post variable first outside of the query and then reference that variable within the query.

$mint = $_POST['min_transmission'];

$query_string = $query_string." Where 'transmission_value_percent >= '$mint'";

Just a thought.... but I haven't slept in about 23 hours so who knows what my thoughts are worth.

@mwmwnmwJul 11.2006 — #Ok, just getting ready to head off to bed and this thread ran through my mind again. You're doing the same thing with the $_POST variable that you did with the $query_string variable earlier. I would still reccomend declaring the post variable before the query simply because it gives you the opportunity to verify that your $_POST variable is indeed a number or to check for any special conditions. If you are really set on including $_POST variables directly in your query you will have to use something along the lines of...

$query_string = "$query_string WHERE transmission_value_percent >= ". $_POST['min_transmission'];

That should work as well.

@lightnbauthorJul 11.2006 — #I've changed min_transmission to a declared variable, and the parse error changed to:

[CODE]Parse error: parse error, unexpected T_IF in /home/.sites/22/site13/web/color_information_enchanced.php on line 49[/CODE]

If it helps, the whole chunk is:

[code=php]
 <?PHP
 
 if(isset($_POST['color_submit']))
 {
 $color_number_input = $_POST['color_number_input'];
 $query_string = "SELECT color_name, color_number, transmission_value_percent, correction, manufacturer FROM roscolux_color";
 
  $min_transmission = $_POST['min_transmission'] 
 
  if(isset($_POST['min_transmission']))
   {
   $query_string = $query_string." WHERE transmission_value_percent >= $min_transmission";
   }
 
  $sql = mysql_query("$query_string");
  echo(mysql_error());
 
  echo '<table border="0"><tr><td align="center" width="150"><b>Manufacturer</b></td><td align="center" width="150"><b>Color Number</b></td><td align="center" width="150"><b>Color Name</b></td><td align="center" width="150"><b>Transmission</b></td><td align="center" width="150"><b>Correction</b></td></tr>';
 
  while ($row = mysql_fetch_assoc($sql))
   {
   echo '<tr><td align="Center">';
   echo $row["manufacturer"];
   echo '</td><td align="Center">';
   echo $row["color_number"];
   echo '</td><td align="Center">';
   echo $row["color_name"];
   echo '</td><td align="Center">';
   echo $row["transmission_value_percent"];
   echo ' %</td><td align="Center">';
   echo $row["correction"];
   echo '</td></tr>';
   }
 
  echo '</table>';
 
  }
 ?>
 [/code]

@mwmwnmwJul 11.2006 — #You are getting the unexpected T_IF error because

$min_transmission = $_POST['min_transmission']

needs a semicolon after it.

Whenever you get an error message that points to the last line of your document it's usually either a missed simicolon or a bracket issue. Also...

$query_string = $query_string." WHERE transmission_value_percent >= $min_transmission";

$min_transmission should require single quotes as it is a php variable, while transmission_value_percent should not need quotes as it appears to be a column name. Try something like...

$query_string = $query_string." WHERE transmission_value_percent >= '$min_transmission'";

To simplify... Think of the single quotes in your query as a way of designating something that is foreign to your database. transmission_value_percent is a column name, which means your database is very familiar with the term so no special treatment is required. With any variables, whether it's $query_string, $_POST[], or $min_transmission you have to use the single quotes as a means of resolving the value of that variable prior to sending the query to your db. Sending $min_transmission would mean absolutely nothing to your database. Using '$min_transmission' tells the php engine to send the value of $min_transmission as part of the query, which is what you are really trying to do here.

@lightnbauthorJul 12.2006 — #Thank you, that fixed the issue and it seems to be working ?

It is doing something strange with the numbers though... If I enter '80' as my query, I get results that are 80,92,96,88,85, but I also get 9.

Nine is less than 80, so it shouldn't show up. Is there a fix for this?

@mwmwnmwJul 12.2006 — #This sounds like an issue with your table structure, rather than with the query. It looks as if the SQL engine is viewing these numbers as text strings, where 80 would be less than 9. Be sure that the columns that hold integers are declared as integers, or doubles, or floats or whatever they are and not as varchar or text.

@lightnbauthorJul 12.2006 — #The transmission_value_percent column (in question) contains both the numbers I'm trying to calculate/compare, as well as the words 'Not Provided' for items that the manufacturer did not specify a transmission value for.

The reason for putting 'Not Provided' is so that the import into the database (using a CSV file) goes smoothly, since leaving the field blank causes an invalid line count.

Is there a setting where numbers are treated as numbers and text is ignored?

@mwmwnmwJul 12.2006 — #So this column is set to varchar? If so, I would recommend changing the default from "not provided" to something like 101 and changing the column type to integer. I'm still a little confused, though... if your csv is simply blank in those columns then you are using logic somewhere to convert the blank to "not provided", right? If so, then set it to 101, that way it won't show up in any queries like the one you are using above, and you can still write a query to return only those without a transmission percentage ie

select * from table where transmission_percent > 100

In theory that would only return those items that were "not provided" since everything that had an actual percent would be < = 100.

The problem is that you really can't mix numbers and text in SQL because the SQL engine has no way of recasting, as you could in java, or recognizing on the fly as the PHP engine does. When it pulls data from a table it handles it strictly based upon the column type. All of the column types that hold characters... char, varchar, text, blob, etc. will sort and compare based up the common character set. They are strings. There is no way to cast those values back into integers until they hit your php engine.

The right thing to do would be to work out the problems with the database. If "not provided" is either part of your logic or, even easier, simply the default you put on the column to handle null values, then change that to an integer, change the column type to int, and you're set.

A band-aid here... and I almost feel guilty for throwing this out because you really should fix the db structure rather than look for a workaround... but if you just want a quick fix use the php engine, which will cast on the fly, to filter your results before you handle them.....

while ($row = mysql_fetch_assoc($sql))
 {
 if($row["transmission_value_percent"] &gt;= $mint)
 {
 echo '&lt;tr&gt;&lt;td align="Center"&gt;';
 echo $row["manufacturer"];
 echo '&lt;/td&gt;&lt;td align="Center"&gt;';
 echo $row["color_number"];
 echo '&lt;/td&gt;&lt;td align="Center"&gt;';
 echo $row["color_name"];
 echo '&lt;/td&gt;&lt;td align="Center"&gt;';
 echo $row["transmission_value_percent"];
 echo ' %&lt;/td&gt;&lt;td align="Center"&gt;';
 echo $row["correction"];
 echo '&lt;/td&gt;&lt;/tr&gt;';}
 }

It basically just negates all the work you've done to get that last condition added to the query and uses the php engine to handle that part of the sort. It's very bad form and shouldn't be used over time, but it would work as a band aid until you figure out how to address the column type issue.

@aussie_girlJul 12.2006 — #Can I also add that you really should sort of the forms first then the SQL..I would use something like this..

[code=php]
 if(isset($_POST['color_submit']))
 {
 $color_number_input = $_POST['color_number_input'];
 }
 else{
 $color_number_input = 0; // whichever the data type is
 }
 if(isset($_POST['min_transmission']))
 {
 $query_string =  $_POST['min_transmission'];
 }
 else{
 $query_string = 0;// or  whatever minimum is...
 }
 
  $query = "SELECT color_name, color_number, transmission_value_percent, correction, manufacturer FROM roscolux_color WHERE transmission_value_percent >= '$query_string'";
  $sql = mysql_query($query_string); 
 [/code]

If you get parse errors about T_STRINGS try puting curly braces around your post variables.

<i>
 </i> $color_number_input = {$_POST['color_number_input']};
 $query_string =  {$_POST['min_transmission']};

@lightnbauthorJul 13.2006 — #I'm still a little confused, though... if your csv is simply blank in those columns then you are using logic somewhere to convert the blank to "not provided", right?[/QUOTE]

The CSV was initially blank in those columns, when I first tried to import it using phpMyAdmin. But since Excel doesn't create the correct number of commas if two fileds are blank in a row, while exporting a CSV file, I kept getting an 'invalid field count on line x' error on import.

The solution was to put something in the excel spreadsheet in those blank columns, so the structure of the table would be preserved correctly on the CSV export.

I changed the column in mySQL to int, and SQL automatically changed all of my "not provided" values to '0'. I guess as long at the value doen't get in the way of the 'real values' I should be fine.

I can make them all '98104' or somthing, and then add "WHERE transmission_color_percent <> 98104" to the end of my querys.

Can I also add that you really should sort of the forms first then the SQL[/QUOTE]

I'm not really sure what that does. It looks like it sets variables to zero if the user hasn't submited them?

@mwmwnmwJul 13.2006 — #zero would work, as would any number greater than 100. Just so long as you set it to a number that is not a possible percentage it's easy enough to filter them out in the query with < >. Whatevery you decide you should be able to go in and set the default in your database to that number so that all future entries get assigned the same value.

Also in #PHP _↴

Problem of Enter in PHP The Main part of my site, with code included...Regular Expression

Success!

Help @lightnb spread the word by sharing this article on Twitter...

Tweet This

Adding an If condition to modify a SQL query

13 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Adding an If condition to modify a SQL query

13 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

13 Comments(s) _↴

Also in #PHP _↴