Query() Function for PHP

query() function is a PHP function will accept a query as its argument and return the data in different formats based on the result of the query.

For more information on this function, go to my PHP query () Function page.

The Old Method

I used to have four different functions in a small class for MySQL operations -

query()
$sql->query("SELECT * FROM users WHERE user_id='1'");
This will return the first row of the result of the given query.

array (
 'user_id' => 1,
 'user_name' => 'Binny',
 'user_url' => 'http://www.bin-co.com/',
 'user_status' => '1'
)

getOne()
$sql->getOne("SELECT user_name FROM users WHERE user_id='1'");
This function will return the first item of the first row of the given row.

Binny

getAll()

There are two different results for this function...

$sql->getAll("SELECT user_name FROM users WHERE user_status='1'");
This type will return all the items in the result as an numerical array.

array (
 [0] => 'Binny',
 [1] => 'Another Guy',
 [2] => 'Yet Another Guy'
)

$sql->getAll("SELECT user_id,user_name FROM users WHERE user_status='1'");
This type will return all the items in the result as an associative array - the first item(user_id) will be the index for the array.

array (
 [1] => 'Binny',
 [3] => 'Another Guy',
 [7] => 'Yet Another Guy'
)

New Method

All these functions were really helpful - but then I got this bright idea to combine all these functions into one single function. And that is what I have done.

Right now, this function only supports MySQL - I use this most - but it can be easily edited to support others.

function query($query) {
 if(!$query) return "";

 $result = mysql_query($query) or die("MySQL Error : " . mysql_error() . "\n<br />In Query <code>$query</code>");

 if(mysql_num_rows($result) == 1) { //If there is just one result, return it.
  $arr = mysql_fetch_assoc($result);
  if(count($arr) == 1) { //If there is just one result...
   $item = array_values($arr);
   return stripslashes($item[0]); // Creates the effect of 'getOne()'
  } else {
   foreach($arr as $key => $value) $arr[$key] = stripslashes($value);
   return $arr; // Makes the 'query()' effect
  }

 } else {
  $arr = array();
  $primary_key = false;

  while ($all = mysql_fetch_row($result)) {
   if(count($all) == 1) array_push($arr,$all[0]);
   elseif(count($all) == 2) {
    if(!$primary_key) { //Happens only one time
     $meta = mysql_fetch_field($result,0); //If the first element is a primary key, make
     $primary_key = $meta->primary_key;  // the result an associative array.
    }
    //Make it an Associative Array if there is a primary_key in the given data.
    if($primary_key) $arr[$all[0]] = $all[1];
    else break;
   }
   else break;
  }
  if($arr) {
   //Do a stripslashes() on each element
   foreach($arr as $key => $value) $arr[$key] = stripslashes($value);
   return $arr; // For 'getAll()'

  } else { //If nothing matches...
   mysql_data_seek($result,0); //Reset the Query.
   return $result; // This results in 'getSqlHandle()' effect
  }
 }
}

Problems

This function is too new for me to notice any bugs. I was hoping that maybe you could find some. One problem is there - readability. If some is reading the code you wrote using this function, they will have a hard time understanding what is happening. For example...

$name = query("SELECT name FROM people WHERE id='2'");
print "Hello, " . $name;

/* ... later in the program ... */

$all_idiots = query("SELECT id,name FROM people WHERE is_idiot='1'");
foreach($all_idiots as $id=>$idiot) {
 /* ... whatever ... */
}

/* ... even later ... */

$sql_result = query("SELECT * FROM people WHERE status='1'");
while($row = mysql_query($sql_result)) {
 /* ... whatever ... */
}

This will really confuse the reader. He will have no idea what the function does - of course you could put some comments on the function - but he would still have to wade through all the included files to find the file with the function to read the comment.

Filed Under...

• web
• development
• php
• mysql
• functions