PHP/mysql fetch multiple variables in array

Question

I am new to PHP. I wanted to create a new record in another table but just one new variable gets returned. I've tried following:

$user_id = mysql_real_escape_string($_POST['user_id']);
$user_name = mysql_query("SELECT user_name FROM accept WHERE user_id=".$user_id." ");
$row1 = mysql_fetch_array($user_name);
$server = mysql_query("SELECT server FROM accept WHERE user_id=".$user_id." ");
$row2 = mysql_fetch_array($server);
$url = mysql_query("SELECT link FROM accept WHERE user_id=".$user_id."");
$row3 = mysql_fetch_array($url);
$lpoints = mysql_real_escape_string($_POST['lpoints']);

And my result is this. enter image description here

`mysql_fetch_array()` returns an _array_, not a scalar value. You are inserting those arrays directly, which PHP represents as `Array`. You mean to do things like `$row3 = mysql_fetch_array($url); $url = $row3['url'];` _However_, you should get all of these in one query. `SELECT user_name, server, link FROM accept WHERE user_id = $user_id` — Michael Berkowski, Aug 02 '14 at 21:48
Use `var_dump($row1)` for example to see what the return looks like. — Michael Berkowski, Aug 02 '14 at 21:48
Now, all that said, new code should not be written with the `mysql_*()` functions. They are deprecated and will soon be removed from PHP. Instead, you should begin using PDO or MySQLi, with prepared statements. See [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) for practical examples of both. [This excellent PDO tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers) places PDO in context of the `mysql_*()` functions if you're already familiar with those. — Michael Berkowski, Aug 02 '14 at 21:49

EternalHour · Answer 1 · 2014-08-02T21:58:27.507

First of all, combine your queries into one:

$user_id = mysql_real_escape_string($_POST['user_id']);
$user_info = mysql_query("SELECT user_name, server, link FROM accept WHERE user_id=".$user_id." ");
$row = mysql_fetch_array($user_info);
$lpoints = mysql_real_escape_string($_POST['lpoints']);

In order to create a new record, you will need INSERT INTO, to change existing records use UPDATE.

When you're fetching info from the database, it will be an array so you will need to use it accordingly. So essentially, to use the variables it will be like this:

$row['user_name'] or $row['server'] etc..

Also, look into using mysqli instead. You will need to change your connection script and some other syntax but it needs to be done. mysql is deprecated, insecure, and future support is not there so you will need to change it later anyway.

score 0 · Answer 2 · answered Aug 02 '14 at 22:07

You should use pdo or mysqli and here is your code;

  $user_id = &$_POST["user_id"];
    if($user_id){
        $result = mysql_query("select user_name,server,link,lpoints from accept where user_id='".mysql_real_escape_string($user_id)."'");
        /*You should use single quotes for escaping sql injection*/
        if($result){
            $vars = mysql_fetch_array($result);
            if($vars){
                list($username,$server,$link,$lpoints) = $vars;
            }
            else{
                //do something with errors
            }
            mysql_free_result($result);
        }
        else{
            //do something with errors
        }
    }
    else{
        //do something with errors
    }

Suchit kumar · Answer 3 · 2014-08-03T05:26:47.917

0

Try This-

$user_id = mysql_real_escape_string($_POST['user_id']);
$result = mysql_query("SELECT user_name, server, link FROM accept WHERE user_id=".$user_id." ");
$row=mysql_fetch_array($result)
$row1=$row['user_name'];
$row2=$row['server'];
$row3=$row['link'];
$lpoints = mysql_real_escape_string($_POST['lpoints']);

Now you got what you wanted based on your requirement use the data to insert or update.

edited Aug 03 '14 at 05:26

answered Aug 03 '14 at 05:13

Suchit kumar

11,809
3
22
44

PHP/mysql fetch multiple variables in array

3 Answers3