SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

    Code:
      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
    Code:
    `$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.

    Code:
      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?

    Code:
     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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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 --
    Code:
    select * from table where countryCode = :country and YEAR(addedDate) = :year and status = 0");
    vwalah
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, what I meant was this:

    Without changing the following statement:
    Code:
    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:
    Code:
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    why would you not want to change the statement? php has perfectly good "if" capabilities
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •