SELECT Question

[dweb77]

What is the wildcard or expression for a SELECT statement to get all records in a particular column as shown below.

 

I want all records which have column state='CA' and population greater than 10k and any entry in city. I am processing this from a form where the user can select a particular city or choose 'All' cities. I know how to process the form fields with strings and using GET, but don't know the character to use in the SELECT statement for if they choose 'All'. An example of the final expression with '????' where I am uncertain of what to have the 'All' selection translate to:

 

SELECT location_id FROM locations WHERE $state='CA' AND $city = ???? AND $population > 10000

 

Thank you in advance for any help.

 

 

[Wildbug]

You don't need a "$city = 'xx'" if they choose "ALL."  Just don't include it.

[soycharliente]

I would just put an if statement and write two queries and depending on what they selected it would decide which query to run.

[wsantos]

first you dont put $ inside the query statement.you append them...

if($city=='ALL')
 $qry="SELECT location_id FROM locations WHERE state='" . $statevariable . "' AND population > " . $populationvariable;
else
 $qry="SELECT location_id FROM locations WHERE state='" . $statevariable . "' AND city= '" . $cityvariable . "' AND population > " . $populationvariable;

[soycharliente]

That is completely wrong. You CAN put $ inside a double quoted strings. It acts as a macro in a sense and simply does text replacement.

 

However, you CANNOT use $ inside of single quoted strings. If you try to put $ inside a single quoted string, it will read the $ as a character.

 

GOOD:

<?php
$table = "users";
$query = "SELECT * FROM $table LIMIT 10";
?>

 

BAD:

<?php
$table = "users";
$query = 'SELECT * FROM $table LIMIT 10';
?>

 

<?php
$table = "users";
echo "$table";  // output: users
echo '$table';  // output: $table
?>

[wsantos]

ok i stand corrected...

[dweb77]

Thank you for the responses...

 

If I were to use the response regarding the if statement, am I just to have several of these as there are four different criteria on the form (I guess I should have included this in the original question). Each could have a general - All - selection or could have a specfic selection.

 

Or is there a better way?

 

Thanks.

 

 

[soycharliente]

Use wsantos approach.

<?php
if ($city == "All") {
$query = "SELECT location_id FROM locations WHERE state='$state' AND population>10000";
} else {
$query = "SELECT location_id FROM locations WHERE state='$state' AND city='$city' AND population>10000";
}
?>

[Wildbug]

Another option.  You could also use the ternary operator + sprintf to choose between situations while generating a query in one place.

 

<?php

$query = sprintf('SELECT location_id FROM locations WHERE state="%s"%sAND $population > 10000',
$_POST['state'],
$_POST['city'] == 'ALL' ? '' : " AND city='{$_POST['city']}' "
);

?>

(Error checking omitted for brevity; you should check user submitted data and escape it.)