PHP if statement in SQL query

Question

So I am trying to involve an if statement in my SQL query in my PHP code. Basically, I want to say "if $var is not 0, add the WHERE clause, but if $var is 0, don't add it".

Code below, doesn't seem to be working. Help is greatly appreciated!

$var = $_GET["var"];

$query = sprintf("SELECT * FROM table ".if($var != 0) { echo "WHERE var = '%s'," }."
  if($var != 0) { mysql_real_escape_string($var) });

$result = mysql_query($query);

Update

I ultimately decided that since I will be using multiple conditions for multiple variables, it was best to do the following:

$var = $_GET["var"];
$string ='';
if ($var != 0) {
  $string = "WHERE var =". $var . " ";
}

$query = sprintf("SELECT * FROM table ". $string.");

$result = mysql_query($query);

Now I can use as many variables as I want and add additional clauses to the SQL statement.

Thanks for the help!!

Luchian Grigore · Accepted Answer · 2012-01-29 22:25:57Z

4

You can't have an if expression inside string concatentation. Also, you're missing the trailing ".

$query = sprintf("SELECT * FROM table ". $var != 0 ? "WHERE var = '%s'" : "");

Also, mysql_real_escape_string() doesn't take the variable by reference, so the modified string is returned:

if($var != 0) { $var = mysql_real_escape_string($var) });

answered Jan 29, 2012 at 22:25

Luchian Grigore

260k68 gold badges465 silver badges634 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alec Gorge · Accepted Answer · 2012-01-29 22:24:53Z

2

Try this:

$query = sprintf("SELECT * FROM table%s", $var != 0 ? " WHERE var = '" . mysql_real_escape_string($var) . "'" : "");

answered Jan 29, 2012 at 22:24

Alec Gorge

17.5k10 gold badges63 silver badges71 bronze badges

Comments

jmkeyes · Accepted Answer · 2012-01-29 22:35:19Z

2

Don't use PHP for this; use the database.

$db = new PDO($dsn, $username, $password, $database);
$stmt = $db->prepare('SELECT * FROM table WHERE (:select <> 0 AND column = :value)');
$stmt->execute(array(':select' => $var, ':value' => $var));

answered Jan 29, 2012 at 22:35

jmkeyes

3,77119 silver badges20 bronze badges

2 Comments

Mohammad Naji Over a year ago

It's a completely MySQL way. Would you please comment an example result that it might produce? I want to learn this query. Thank you

jmkeyes Over a year ago

That depends on the data you have: that query tells the database to only collect rows where both column is exactly equal to $value and a user specified parameter is not equal to zero. The point is that database is more than just a giant hash table for storing data; they're also capable of filtering and operating on massive amounts of data very efficiently -- much more so than PHP is.

Toto · Accepted Answer · 2012-02-02 16:42:39Z

0

$var = $_GET["var"];
$string ='';
if ($var != 0) {
  $string = "WHERE var =". $var . " ";
}

$query = sprintf("SELECT * FROM table ". $string.");

$result = mysql_query($query);

edited Feb 2, 2012 at 16:42

Toto

91.7k63 gold badges97 silver badges135 bronze badges

answered Feb 2, 2012 at 1:11

mjrobichaud

1151 gold badge2 silver badges9 bronze badges

Collectives™ on Stack Overflow

PHP if statement in SQL query

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related