Page 1 of 1

[SOLVED] One size fits all SQL function

Posted: Sun Jul 18, 2004 11:49 am
by evilmonkey
Hello. I'm developing a one size fits all mysql function to write one line instead of 4 when I eed to putt something from a database. I want this function to execute the query that is passed to it, and return the result in the fom of an associative array. However, I ran into a problem when I'm expecting more than one row to be returned. Here is my code:

Code: Select all

function sql_pull ($query) {
	//performs a query on a database, and returns the results 	
$result = mysql_query($query, $db);
	if (mysql_num_rows($result)==1){
		$row = mysql_fetch_assoc($result);
		return $row; } //returns the one row it found
	else if (mysql_num_rows($result)==0) {
		return NULL; } //return a null value meaning no results returned
	else {
		//what do I do here? There is more than one result...
	}
}

sql_pull("SELECT * FROM table WHERE blah=blah");
//pretty slick, huh?
Thanks for the help! :D

Posted: Sun Jul 18, 2004 11:51 am
by Joe
If I am thinking right I would say:

Code: Select all

function sql_pull ($query) {
   //performs a query on a database, and returns the results in a loop
   $result = mysql_query($query, $db);
   if (mysql_num_rows($result)==1){
      $row = mysql_fetch_assoc($result);
      return $row; } //returns the one row it found
   else if (mysql_num_rows($result)==0) {
      return NULL; } //return a null value meaning no results returned
   else {
      while (true)
      { 
       $row = mysql_fetch_assoc($result);
       if ($row == false) break;
       echo $row['name']; 
      }
   }
}

sql_pull("SELECT * FROM table WHERE blah=blah");
//pretty slick, huh?

Posted: Sun Jul 18, 2004 11:53 am
by kettle_drum
I usually use something along the lines of:

Code: Select all

function select(){
      $result = mysql_query($query);								#runs the query
      $data = array();
      if(mysql_num_rows($result)>1){											#checks to see if there is more than 1 row returned
         for($x=0;$x<mysql_num_rows($result);$x++){
            $data[] = mysql_fetch_assoc($result);
         }
      }else{
         $data = mysql_fetch_assoc($result);									#places single result into array
      }
      return $data;	
}

Posted: Sun Jul 18, 2004 11:54 am
by evilmonkey
The problem with that is, I don't want it to echo anything, I want it to return a value to the calling script. I had an idea to do it like this:

Code: Select all

while ($row=mysql_fetch_assoc($result)) {
return $row; }
But that would stop the script after the first run...

Posted: Sun Jul 18, 2004 11:55 am
by evilmonkey
Kettle_drum, how would I access $data[] from the calling script? data[0]['column1'], data[1]['column1']?

Posted: Sun Jul 18, 2004 11:59 am
by d3ad1ysp0rk
Yup.
You can then loop through it with:

Code: Select all

for($i=0,$s=sizeof($data);$i<$s;$i++){
echo $data[$i]['column1'] . " blah blah, tex formatting... " . $data[$i]['column2'];
}
I believe.

Posted: Sun Jul 18, 2004 11:59 am
by kettle_drum
It returns all the results as an array. If theres only 1 result then the format is $array['fieldname'] and if there is a number or rows you use $array[0]['fieldname'], $array[1]['fieldname']; etc.

Posted: Sun Jul 18, 2004 12:04 pm
by evilmonkey
Thank for the help everyone, I used kettle_drum's code. :)