One column table into an array

Question

I read most of the posts related to my question but none can resolve my simple issue.

I am using PHP and MySQLi to retrieve data from my server.

In my Database I have a one-dimensional table (i.e. one column only) and I want to put its data inside an array.

Until now I was using JSON to hold the data from my server.this is my code:

<?php
include 'ChromePhp.php';
$con=mysqli_connect("197.168.240.100","jsoncloud","nuahyu,hs","json");
// Check connection
if (mysqli_connect_errno())
  {

  echo "Failed to connect to MySQL: " . mysqli_connect_error();
ChromePhp::warn('Failed to connect to MySQL');

  }

$sql="SELECT `street` FROM  `users` ";

$result=mysqli_query($con,$sql);

$rows = array();
while ($row = mysqli_fetch_all($result,MYSQLI_ASSOC)) {
    $rows[] = $row[0];

}
echo json_encode($rows);


// Free result set
mysqli_free_result($result);

mysqli_close($con);
?>

My final goal is to echo the array back so I can use it with Ajax in another JS file

in the other JS file I have the following code:

$.ajax({
  type: 'POST',
  url: 'js/SQLusersstreets.php',
  dataType: 'json',
  success: function(jsonData) {
console.log(jsonData);
  },
  error: function() {
    alert('Error loading SQLusersstreets.php from GoogleMapsCode.js');
  }
});

This is my output when I open the browser's debugger:

I am trying to return a simple array, what am I doing wrong?

For printing out arrays you can use print_r($yourArr). I don`t know if it works with json_encode though — A. Ilazi, May 24 '16 at 18:44
I want to echo the array back so I can use it with Ajax in another JS file — Json, May 24 '16 at 18:50
your data is already stored inside an array, if want to print it use print_r($array), as @A.Ilazi said. anyway your questions seems too ambiguous? — Mohammad, May 24 '16 at 18:51
It would be nice if you write some data output of what you're getting now and what you would like to get. — Richard, May 24 '16 at 19:18
and don't use the while cycle, it's creating an infinite loop — Richard, May 24 '16 at 19:38
Maybe this question can help you [link](http://stackoverflow.com/questions/12693947/jquery-ajax-how-to-send-json-instead-of-querystring) — A. Ilazi, May 24 '16 at 19:58
There's no way how to solve the problem, if you don't understand the basic difference between mysqli_fetch_array and mysqli_fetch_all. — Richard, May 24 '16 at 21:38

score -1 · Answer 1 · answered May 24 '16 at 18:45

Change mysqli for PDO

Assuming a connection is already established, you will need only one line of code:

echo json_encode($pdo->query("SELECT `street` FROM `users`")->fetchAll(PDO::FETCH_COLUMN));

but if you prefer to write a lot of code, then change fetch_all to fetch_assoc

Mohammad · Answer 2 · 2016-05-24T19:10:28.897

-1

try this, if this is what you are asking about:

for($i=0;$i<sizeof($rows);$i++){

     echo $rows[$i]['id']; //for example
}

edited May 24 '16 at 19:10

answered May 24 '16 at 18:56

Mohammad

3,449
6
48
75

Thanks but it's not what I'm looking for. – Json May 24 '16 at 18:59
@Json ok, no problems, but please describe your problem in more details, I didn't get you – Mohammad May 24 '16 at 19:01
In my Database I have a one-dimensional table (i.e. one column only) and I want to put its data inside an array. What do I need to change in my code? I need to replace the "echo json_encode($rows);" with some other statement. – Json May 24 '16 at 19:04
the data are already inside the array `$rows` , if the `print_r()` function not is what you are looking for, please see my updated answer – Mohammad May 24 '16 at 19:09

Richard · Answer 3 · 2016-05-24T21:58:51.583

-1

mysqli_fetch_all is a shortcut for while($row = mysqli_fetch) {}, so you can skip the while cycle.

$sql="SELECT DISTINCT `street` FROM  `users` ";

$result=mysqli_query($con,$sql);

$rows = mysqli_fetch_all($result,MYSQLI_ASSOC);
echo json_encode(array_map(function($i) { return $i['street']; }, $rows));

See the documentation here for more examples http://php.net/manual/en/mysqli-result.fetch-all.php

edited May 24 '16 at 21:58

answered May 24 '16 at 19:09

Richard

1,045
7
11

This one is even worse – Your Common Sense May 24 '16 at 19:14

One column table into an array

3 Answers3