how to return mysql data from a php function

16,207

Solution 1

Well I see now what are you trying to do.

First of all, your idea of having such function is absolutely brilliant. Only few people ever come to it.
But implementation seems not that good.

You are trying to create both mysql helper function and specialized function to get particular user data at once. Let me show you how can you make former one and then let's see if you will need latter one.

Every php developer need a function like yours, but general purpose one - to get a single value from query, without typing tons of repetitive code of sanitizing parameters and and getting data and error control and stuff.
Here is an example of such function, using placeholders to pass data into query. These placeholders of printf family, so, you have to use %s to represent a string and %d to represent int

function dbgetvar(){
  $args  = func_get_args();
  $query = array_shift($args);
  foreach ($args as $key => $val) {
    $args[$key] = mysql_real_escape_string($val);
  }
  $query = str_replace("%s","'%s'",$query); 
  $query = vsprintf($query, $args);

  $res = mysql_query($query);
  if (!$res) {
    trigger_error("dbgetarr: ".mysql_error()." in ".$query);
    return FALSE;
  } else {
    $row = mysql_fetch_row($res);
    if (!$row) return NULL;
    return $row[0];
  }
}

Having this function in your config file, you can get your user info this way

$name = dbgetvar("SELECT name FROM users WHERE id=%d",$_GET['id']);

and many other things like this

$name = dbgetvar("SELECT id FROM users WHERE name=%s AND surname = %s",
                 $_GET['name'],
                 $_GET['surname']);

I doubt you will need specialized function for userinfo anymore. However, it can be done too, based on this function

Solution 2

use

function mysql_fetch_all($res) {
   while($row=mysql_fetch_array($res)) {
       $return[] = $row;
   }
   return $return;
}

and

$data = mysql_fetch_all($result);

Solution 3

Well you are only fetching one row of data since $data=mysql_fetch_rows($result) only gives the first row of the data set. If you want to return all the data then you'll need to populate an array with the data using a while loop.

$data_arr= array();
while($data=mysql_fetch_assoc($result)){
    array_push($data_arr, $row);
}

Then you can just return $data_arr from the function.

Share:
16,207

Related videos on Youtube

Brooke.
Author by

Brooke.

Hello, Hi, I am a woman of many talents, designer, developer, photographer. Most of all I'm about people, and getting to know the heart of people, what they are passionate about.

Updated on June 04, 2022

Comments

  • Brooke.
    Brooke. almost 2 years

    I have a php function that I'm hoping will return all data. The problem right now is it will return the first row called then hang out and I'm not sure why.

        function get_user_info($id,$field='') 
    {
        //connect to database
        include_once("../config.php");
        $qry= "SELECT `$field` FROM `".$user_table."`  WHERE `id` ='$id'";
        //die($qry);
        $result=mysql_query($qry);
        if($result) {
            if(mysql_num_rows($result)>0) {
                //query successful 
                $data=mysql_fetch_row($result);
                $user_info= $data[0];
            }
            else{
                $user_info ="invalid field";
            }
    
        }
        else{
            $user_info="invalid data";
            }
        return $user_info; 
    }
    

    If I run a function like echo (get_user_info($user_id,'username')." ".get_user_info($user_id,'username')); It returns username invalid data For the life of me I can't figure out why it won't get the data assocated with each row of get_user_data

    EDIT: It seems to be dying at $result=mysql_query($qry); which returns Incorrect table name ''

    EDIT2: Looks like the problem was that I was only including the config file once and using a variable for my table name which was getting unset when I tried to call it a second time.

    EDIT 3: Here is the final function

    //connect to database
    if(file_exists('..config.php')){
        include_once("../config.php");
    }
    function get_user_info($id,$field) 
    {
    
        //get users table
        global $user_table_name;
    
        $qry= "SELECT `$field` FROM `$user_table`  WHERE `id` ='$id'";
        $result=mysql_query($qry) or die(mysql_error());
        if($result) {
            if(mysql_num_rows($result)>0) {
                //query successful 
                $data=mysql_fetch_row($result);
                $user_info= $data[0];
            }
            else{
                $user_info =$qry;
            }
    
        }
        else{
            $user_info="invalid data";
            }
        return $user_info; 
    }
    
  • Brooke.
    Brooke. almost 13 years
    Awesome, I guess my idea of the specialized function was simply to avoid SQL queries inside my view pages to keep things cleaner.
  • Your Common Sense
    Your Common Sense almost 13 years
    @Bandon that's wrong idea of avoiding SQL queries in view pages. your specialized function still belongs to the business logic and shouldn't be in the view too. Only data to display should be passed to the view, and nothing else.

Related