CODEWITHSUNDEEP
phpmysqlsqlwhere-clause
MKJ
MKJ

Reputation: 133

WHERE clause in MySQL for selecting all

I have a script in PHP, which selects data from a Mysql Database using Select & Where clause.

$lrn= "PU2017LN11K";
$stmt = $user_home->runQuery('SELECT * FROM mcq WHERE LRN = :crn ORDER BY LRN,Sr ASC ');
$stmt->bindParam(':crn',$lrn);
$stmt->execute();

Now how to script the code so that it selects from all the LRN, using WHERE LRN = :crn.

I tried:

$lrn= "*";
$stmt = $user_home->runQuery('SELECT * FROM mcq WHERE LRN = :crn ORDER BY LRN,Sr ASC ');
$stmt->bindParam(':crn',$lrn);
$stmt->execute();

But it didn't show any results!

It's easy to skip the WHERE clause if I want to show all results, but the value of $lrn will be defined by the $_GET method.

Upvotes: 0

Views: 54

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

The normal way of handling this would be:

$lrn= "*";
$stmt = $user_home->runQuery('SELECT * FROM mcq WHERE LRN = :crn OR :crn = \'*\' ORDER BY LRN, Sr ASC ');

Alternatively, you could use LIKE:

$lrn = "%";
$stmt = $user_home->runQuery('SELECT * FROM mcq WHERE LRN LIKE :crn ORDER BY LRN, Sr ASC ');

Upvotes: 1

Sentinel
Sentinel

Reputation: 6449

Use this as your where clause:

WHERE LRN = CASE WHEN :crn = '*' THEN LRN else :crn END

That way when the supplied :crn is '*' you will get all records.

Upvotes: 0

Phil - Computer Whisperer
Phil - Computer Whisperer

Reputation: 60

You want all rows, don't use a where clause. Alternatively, if you must use a where (something in your code or thinking/design requires it), then you can also say something like "where 1" (like the way phpMyAdmin does things all the time, for what ever reason).

Upvotes: 0

Related Questions