-1

I'm trying to SELECT all rows as specified in the query below, but I think the issue is related to mysql_fetch_row, because in MYSQL Workbench the MYSQL query (yes I know I should be using PDO) shows all desired rows.

$result = mysql_query("SELECT zebra_id FROM zebra WHERE user_id = '$id' AND pref = '1'")
or die(mysql_error());
$array =  mysql_fetch_row($result);
echo json_encode($array);

JSON Output (only showing 1 row):

["986"]

I tried changing to mysql_fetch_array, but that doesn't output all rows in the JSON encoded $array.

michelle
  • 623
  • 2
  • 6
  • 22

2 Answers2

3

mysql_fetch_row() only fetchs one row as the name implies. So your error is obvious.

mysql_fetch_array() won't work unless you iterrate through the entire result set*. You just don't call it once and expect to get the entire result set in an array.

$result = mysql_query("SELECT zebra_id FROM zebra WHERE user_id = '$id' AND pref = '1'")
or die(mysql_error());
$array = array();
while($row = mysql_fetch_assoc($result)) {
    $array[] = $row;
}
echo json_encode($array);

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Community
  • 1
  • 1
John Conde
  • 217,595
  • 99
  • 455
  • 496
1

You have to do this in a loop. See this:

$result = mysql_query("SELECT zebra_id FROM zebra WHERE user_id = '$id' AND pref = '1'")
or die(mysql_error());
$array = array();
while($row =  mysql_fetch_row($result)){
    $array[] = $row;
}
echo json_encode($array);

And also I have to say that, do not use mysql_* functions anymore. These are deprecated officially. You have to use mysqli_* or PDO instead.

Ahmad
  • 5,551
  • 8
  • 41
  • 57