how to return mysql data from a php function

Question

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; 
}

echo the query, see what he run , and if you have error show them, echo mysql_errno($link) . ": " . mysql_error($link) . "\n"; — Haim Evgi
– Haim Evgi, Commented Apr 4, 2011 at 7:12
you shouldn't include anything inside of function. And your solution most likely is vulnerable to SQL injection — Your Common Sense
– Your Common Sense, Commented Apr 4, 2011 at 8:17

xkeshav · Accepted Answer · 2011-04-04 07:12:28Z

3

use

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

and

$data = mysql_fetch_all($result);

answered Apr 4, 2011 at 7:12

xkeshav

54.2k47 gold badges183 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

xkeshav Over a year ago

@Bandon if u want only selected field then optimize Query(SELECT username,user_id FROM ) not the fetch metho

Lawrence Cherone Over a year ago

why make a new function when he can return the result set with return $data

Brooke. Over a year ago

that's what I'm trying to do. Just do it in a generic function so I can request just the rows I need.

Your Common Sense · Accepted Answer · 2011-04-04 09:16:00Z

2

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

answered Apr 4, 2011 at 9:16

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

2 Comments

Brooke. Over a year ago

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 Over a year ago

@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.

Steve · Accepted Answer · 2011-04-04 07:15:35Z

1

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.

answered Apr 4, 2011 at 7:15

Steve

1,1057 silver badges20 bronze badges

Comments

Brooke. · Accepted Answer · 2011-04-04 08:33:27Z

0

I admit I made a mistake when posting this question. I didn't post a variable that was used inside the functions. This variable was what was causing the issue. That along with including the connection inside the function Edit 3 of the question contains the final function in working form.

answered Apr 4, 2011 at 8:33

Brooke.

3,76113 gold badges53 silver badges83 bronze badges

Comments

sid · Accepted Answer · 2013-01-28 14:21:05Z

0

Hi I also came across to a very similar situation. I have made a custom function to return the value from the table. You can also enhance it by returning true false if the field exists or not.

function getuserinfo($table, $matchid, $uid, $field)
{
    $sql=mysql_query("select $field from $table where $matchid=$uid");
    $row=mysql_fetch_array($sql);
    $userinfo=$row[0];
    return $userinfo;
}

You can use the above function like this:-

<?php echo getuserinfo('usertable', 'id', $userid, 'fullname' );
echo getuserinfo('usertable', 'id', $userid, 'lastname' );
?>

Feel free to comment.

answered Jan 28, 2013 at 14:21

sid

1

Collectives™ on Stack Overflow

how to return mysql data from a php function

5 Answers 5

3 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related