How do you create a simple function to query the database? Mine isn't working!

Question

I want to make a simple function that I can call on to query my database. I pass the "where" part of the query to it and in the code below, my $q variable is correct. However, what should I then do to be able to use the data? I'm so confused at how to do something I'm sure is extremely easy. Any help is greatly appreciated!

function getListings($where_clause)
{
 $q = "SELECT * FROM `listings` $where_clause";
 $result = mysql_query($q,$dbh);
 foreach (mysql_fetch_array($result) as $row) {
  $listingdata[] = $row;
 }
 return $listingdata;
}

Answer 1

Do you have a valid db connection? You'll need to create the connection (apparently named $dbh) within the function, pass it in as an argument, or load it into the function's scope as a global in order for $result = mysql_query($q,$dbh); to work.

Answer 2

You need to use while, not foreach, with mysql_fetch_array, or else only the first row will be fetched and you'll be iterating over the columns in that row.

$q = "SELECT * FROM `listings` $where_clause";
$result = mysql_query($q,$dbh);
while (($row = mysql_fetch_array($result)) != false)
{
  $listingdata[] = $row;
}

Answer 3

Always add error handling in your code. That way you'll see what is going wrong.

    $result = mysql_query($q,$dbh);
    if (!$result) {
            trigger_error('Invalid query: ' . mysql_error()." in ".$q);
    }

Answer 4

Your function should be like this:

function getListings($where_clause, $dbh)
{
    $q = "SELECT * FROM `listings` $where_clause";

    $result = mysql_query($q, $dbh);

    $listingdata = array();
    while($row = mysql_fetch_array($result))
        $listingdata[] = $row;

    return $listingdata;
}

Improvements over your function:

1. Adds MySQL link $dbh in function arguments. Passit, otherwise query will not work.
2. Use while loop instead of foreach. Read more here.
3. Initialize listingdata array so that when there is no rows returned, you still get an empty array instead of nothing.