CODEWITHSUNDEEP
phpmysqlsql
Norman
Norman

Reputation: 6365

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

Upvotes: 1

Views: 352

Answers (4)

ajtrichards
ajtrichards

Reputation: 30565

You could use something like this:

function thisFunction($id,$country,$year){

    global $conn;

    $sql_query = "select * from table where status = 0";

    $where_data = array();

    if(isset($id) && $id != '*'){ // Only add this if ID is set to something other than *
        $where_data[':id'] = $id;
        $sql_query .= " AND id = :id";
    }

    if(isset($location)){
        $where_data[':country'] = $location;
        $sql_query .= " AND countryCode = :country";
    }

    if(isset($year)){
        $where_data[':year'] = $year;
        $sql_query .= " AND YEAR(addedDate) = :year"; 
    }

    $conn = connect();
    $stmt = $conn->prepare($sql_query);
    $stmt->execute($where_data);

}

Upvotes: 1

Aaron Long
Aaron Long

Reputation: 93

you could potentially select them if the column is not null. 

select * from table where id is not null

Upvotes: 0

Kenny
Kenny

Reputation: 5410

Just remove the id part if it's empty:

function thisFunction($id,$country,$year){
    global $conn;

    $conn = connect();

    if (!isset($id) || empty($id))
    {
        $stmt = $conn->prepare("select * from table where countryCode = :country and YEAR(addedDate) = :year and status = 0");
        $stmt->execute(array(      
            ':country' => $location,
            ':year'    => $year
        ));
    }
    else
    {
        $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
        ));
    }
}

Upvotes: 2

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

select * from table where id = id;

Sample fiddle

Upvotes: 1

Related Questions