Select all records when no value is supplied

I have a function in php that does retrieval and display work from a MySql database.

It’s like this:

  function thisFunction($id,$country,$year){
    global $conn;
    	$conn = connect();
    	$stmt = $conn->prepare("select * from table where id = :id and countryCode = :country and YEAR(addedDate) = :year and status = 0");
    	$stmt->execute(array(
    		':id'      => $id,
    		':country' => $location,
    		':year'    => $year
    	));
    }

The problem is, sometimes

`$id`

has a value, sometimes it doesn’t. When it does have a value, I’d like to select records with that value, when it doesn’t I’d like to select all.

How do I write the sql in there, or do this thing, so that when there is a value it’ll select only records with that value, and when there isn’t a value, it’ll select all. It’s the part where when no value is selected - then select all where I’m stuck.

I call the function like anyone would. Nothing unique there.

  select * from table where id = 9 -- works fine - displays all records where id = 9
    select * from table where id = no value supplies - should display all value. How do I do this?

Can you please help?

 select * from table where id = * //Does not work

There’s nothing in MySql like id = * or id = NULL. Something like that that’ll get all values when no figure is supplied

php is a pretty powerful language, i’m sure you can arrange it so that when there is no id value to be supplied to the sql, then the sql statement would omit that particluar WHERE condition –

select * from table where countryCode = :country and YEAR(addedDate) = :year and status = 0");

vwalah :slight_smile:

Actually, what I meant was this:

Without changing the following statement:

select * from table where id = :id

How can I return all records when no value is supplied to it, without changing the statement. Sometimes the value is supplied, sometimes not. This happens when a user wants to view records of a particular id or all

Eg:


select * from table where id = 9
// No problems here. This'll return all records where the id is 9

select * from table where id = //no value supplied to the statement.
// Return all the values in the table

why would you not want to change the statement? php has perfectly good “if” capabilities