How to convert the mysql result to an array in php?

Question

I am trying to convert the result of mysql to an array.Following is my code. But it return [] this.

Anybody help me to solve this please...

result.php

$prep =$mysqli->prepare("select name,location from token where sen_uid=?");
$prep->bind_param("s",$id);
$prep->execute();
$result= $prep->get_result(); 
$rows= array();
while($r= $result->fetch_array(MYSQL_ASSOC))
{
     $rows[] = $r;
}
$obj= json_encode($rows);

As I want the output of json to be an array

score 1 · Answer 1 · answered Nov 08 '17 at 11:33

I've edited my code like this.Now it is working.

result.php

$prep =$mysqli->prepare("select name,location from token where sen_uid=?");

$prep->bind_param("s",$id);
$prep->execute();
$result= $prep->get_result(); 
$payload= array();
while($r= $result->fetch_array(MYSQL_ASSOC))
{
    $payload[]=array('name' =>$r['name'],
                       'lc' =>$r['location'],

        );
}
$obj= json_encode($payload);

IsThisJavascript · Answer 2 · 2017-11-08T10:18:47.417

Well, let's talk about....
while($r= $result->fetch_array(MYSQL_ASSOC))
What you are doing here is setting $r as an associative array, then setting that full array inside $rows; so if $result did return anything, it would be multiplied inside $rows.
Maybe what you are after is something like this?

$prep =$mysqli->prepare("select name,location from token where sen_uid=?");     
//should the bind_param function be i instead of s? i means integer and s means string
//$prep->bind_param("i",$id);
$prep->bind_param("s",$id);
$prep->execute();
$result= $prep->get_result(); 
$rows= array();
//for an array like [0][0], [0][1] use
//$rows= $result->fetch_array(MYSQLI_NUM);
//for an arary like [0]['name'], [0]['location'] use:
$rows= $result->fetch_array(MYSQLI_ASSOC);
$obj= json_encode($rows);

The execution of the query looks correct. I'd maybe add some error checking to ensure that prepare didn't fail. Have you tried running this query inside MySQL to see if you get results?

Edit; In fact, while is completely pointless in this as $r would only ever be set once. I think this issue sits mainly within a broken prepared query.

if($prep =$mysqli->prepare("select name,location from token where sen_uid=?")){     
    //should the bind_param function be i instead of s? i means integer and s means string
   //$prep->bind_param("i",$id);
    $prep->bind_param("s",$id);
    if(!$prep->execute()){
         print_r("Execute error: ".$mysqli->error);
    }
    $result= $prep->get_result(); 
    $rows= array();
    //for an array like [0][0], [0][1] use
    //$rows= $result->fetch_array(MYSQLI_NUM);
    //for an arary like [0]['name'], [0]['location'] use:
    $rows= $result->fetch_array(MYSQLI_ASSOC);
    $obj= json_encode($rows);
}else{
    print_r("Prepare error: ".$mysqli->error);    
}

For debugging purposes, add error checking like the above snippet

I've tried to write the output to a text file , this returns null. — Neethu M, Nov 08 '17 at 10:24
@NeethuM Have you tried running the code with error checking? please also run the query you are trying to execute in phpmyadmin/mysql cli to see if you are getting a result — IsThisJavascript, Nov 08 '17 at 10:28
Yeah query is working perfectly , but `json_encode` return `null` — Neethu M, Nov 08 '17 at 10:42
Do you have any errors in php? Do you have the `mysqlnd` driver installed as `get_result();` will throw out a php error if that's the case. — IsThisJavascript, Nov 08 '17 at 10:44
Sorry i've double checked my code , I can't find any error in my code.What would be the problem? — Neethu M, Nov 08 '17 at 10:46
Please see this question as there is an error somewhere if you are 100% sure that query works. https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display — IsThisJavascript, Nov 08 '17 at 10:48

score 0 · Answer 3 · answered Nov 08 '17 at 10:32

0

I don't think there is any data related to your query in DB ? if yes then try to change

this

$prep->bind_param("s",$id);

to

$prep->bind_param("i",$id);

answered Nov 08 '17 at 10:32

Amit Rana

121
7

1

It is working. I've given id as `varchar`.So i think instead of using `i` `s` is prefered ,right? – Neethu M Nov 08 '17 at 10:44
If you have issues, you can check the log of server – Vigneshwaran Nov 09 '17 at 09:15

score -1 · Answer 4 · answered Nov 08 '17 at 10:09

-1

you need to use array_push method to push the object(value) into an array.

$prep =$mysqli->prepare("select name,location from token where sen_uid=?");
$prep->bind_param("s",$id);
$prep->execute();
$result= $prep->get_result(); 
$rows= array();
while($r= $result->fetch_array(MYSQL_ASSOC))
{
     array_push($rows,$r);
}
$obj= json_encode($rows);

answered Nov 08 '17 at 10:09

Vigneshwaran

387
1
2
14

This solves nothing. You've really not changed anything as `array_push` works in the same way as `$row[] = $var` – IsThisJavascript Nov 08 '17 at 10:20
what you trying to say? the variable $rows now returns []? Tell me the reason for the downvote. – Vigneshwaran Nov 09 '17 at 09:13
I've already stated the reason for my downvote. `$row[]=$var` == `array_push($row,$var);` – IsThisJavascript Nov 09 '17 at 11:54
First understand the question clear then downvote if it not satisfies. – Vigneshwaran Nov 09 '17 at 13:06
Your answer DOES NOT satisfy the question tho. You do the exact same thing as the OP did – IsThisJavascript Nov 09 '17 at 13:06

How to convert the mysql result to an array in php?

4 Answers4