Select "all" from WHERE statement

PHP
1

I have a SELECT query that takes a WHERE statement whose argument is a variable. What do I set the variable to when I want the query to select all records? I’ve tried ‘*’, but without success.

Jim

2

Something that evaluates to TRUE, like:

select * from fubar where 1>0;

3

Hi,

Just remove the WHERE statement or do you still want to keep it?

4

set the variable to 1… so if your query is something like this:


$query = "SELECT * FROM table WHERE $condition";

do this if you want to select all the records:


$condition = 1;
$query = "SELECT * FROM table WHERE $condition";
5

Thanks for the suggestions, but I haven’t had any luck with them.

What I have is more like this:
$query = "SELECT * FROM table WHERE Color=‘$color’

$color comes from a form:
<select name=“color”>
<option value=“All” selected>All</option>
<option value=“red”>Red</option>
<option value="green>Green</option>
…

When the selection is All, I want the query to return all records. Any more thoughts?

Jim

6

IF ($color == ‘ALL") {
$query = “SELECT * FROM table”;
} else {
$query = "SELECT * FROM table WHERE Color=’$color’";
}

7

Yes, of course!!

Thanks!!

8

I’d go for:

$query = “SELECT * FROM table WHERE Color LIKE ‘$color’”;

and

<option value=“%” selected>All</option>

The percent sign being a wildcard for MySql

hasta later,
kamm…

9

You can combine lite’s answer into a singel statement

$query = "SELECT * FROM table" . ($color == 'all' ? " WHERE Color LIKE '$color'" : '' );
10

Amit: Certainly using the ternary conditional operator makes the statement more compact, but really I don’t see the need for a conditional statement here at all.

Just my 2 cents worth… :slight_smile:

hasta later,
kamm…

11

kamm, one reason is the optimizer

what is the difference between

SELECT * FROM table

and

SELECT * FROM table WHERE Color LIKE ‘%%%’

as far as the resuilts are concerned, they are the same

but do they perform the same?

are you sure?

12

That’s a good point which I hadn’t considered.

I guess without the WHERE it’s faster, but no, I don’t know for sure…

Tnx for the input :slight_smile:

hasta later,
kamm…

13

Kamm, the previous statement was working fine. My posting was to give another point of view.

Besides it was also towards encouraging to write compact code. The earlier you get this habit, the better you are later on in the programming.

14