JSON encode mySQL results clarification

Question

I am triyng to encode mySQL to JSON. I have tried this popular link JSON encode MySQL results but the answer didn't work for me. I'll appritiate your help.

Here is my code:

<html>
<body>

<?php
    header('Content-Type: text/html; charset=utf-8');
    $con=mysqli_connect("localhost","root","root","Theory");
    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    $result = mysqli_query($con,"SELECT `questions`.`questionID` AS question,
                                        `questions`.`questionText` AS questionText,
                                        `questions`.`categoryID` AS categoryID,
                                        `answers`.`answerID` AS answerID,
                                        `answers`.`answerText` AS answerText,
                                        `answers`.`isTrue` AS isTrue
                                 FROM `questions`,`answers`
                                 WHERE `questions`.`questionID`=`answers`.`questionID`");

    if (!$result)
    {
        die('Error: ' . mysqli_error($con));
    }

   $rows = array();
   while($r = mysql_fetch_assoc($result)) {
   $rows[] = $r;
   }
   print json_encode($rows);

    mysqli_close($con);
    ?>

</body>
</head>

I am getting :"[]" Sorry if I am doing something stupid, I am new to php.

You're mixing and matching `mysql` and `mysqli`. Is `mysql_fetch_assoc()` a typo? — Jason McCreary, Jul 16 '13 at 15:27
@JasonMcCreary It is not a typo, it is ignorance. Please explain what did I do wrong. — Luda, Jul 16 '13 at 15:34
You should not be using `mysql_fetch_assoc` on a `mysqli` connection. — tadman, Jul 16 '13 at 15:35
@tadman what should I use in this case and why mysql_fetch_assoc is not appropriate? — Luda, Jul 16 '13 at 15:36
`mysqli` and `mysql`, despite their name similarities, are two totally different libraries and have nothing in common internally. Always check [the documentation](http://php.net/manual/en/book.mysqli.php) for the correct methods to use. — tadman, Jul 16 '13 at 15:40

score 3 · Answer 1 · edited Feb 15 '14 at 23:37

3

Maybe try:

$rows = array();
$i = 0;
while($r = mysqli_fetch_assoc($result)) {
    $rows[$i] = $r;
    $i++;
}
print json_encode($rows);

Or maybe:

$rows = array();
while($r = mysqli_fetch_assoc($result)) {
    array_push($rows, $r);
}
print json_encode($rows);

edited Feb 15 '14 at 23:37

Mr. Polywhirl

42,981
12
84
132

answered Jul 16 '13 at 15:35

Banning

2,279
2
16
20

1

+1 for you but it would be more better if you brief the problem – M Khalid Junaid Jul 16 '13 at 15:42
@dianuj Thank, I guess I read right over her using mysql_fetch_assoc and not mysqli_fetch_assoc... whoops! – Banning Jul 16 '13 at 15:44

score 2 · Accepted Answer · answered Jul 16 '13 at 15:36

2

You're mixing and matching the mysql and mysqli libraries.

If you are using mysqli (which the rest of your code is), you need to use the mysqli functions. In this case mysqli_fetch_assoc():

while($r = mysqli_fetch_assoc($result)) {
  $rows[] = $r;
}

Note: As an aside, I'd encourage you to use the mysqli Object Oriented style.

answered Jul 16 '13 at 15:36

Jason McCreary

71,546
23
135
174

1

`mysql` is the *original* and is now deprecated (PHP 5.5). `mysqli` is the newer, *improved* extension. – Jason McCreary Jul 16 '13 at 15:40
1

`mysql*` are depreciated in php newer version so try to avoid these functions instead you can use `PDO` – M Khalid Junaid Jul 16 '13 at 15:41
Just for completeness, [PDO](http://www.php.net/manual/en/intro.pdo.php) is another database extension that supports more databases platforms than just MySQL. Simply put, you could consider it a *generic* database extension that provides greater flexibility. – Jason McCreary Jul 16 '13 at 15:45

score 0 · Answer 3 · answered Jul 16 '13 at 15:38

This would probably be a lot more clear if you used the object-oriented interface to mysqli:

$mysqli = new mysqli("localhost", "root", "root", "Theory");

$result = $mysqli->query("...");

while ($row = $result->fetch_object())
{
  ...
}

$result->close();

It's much harder to confuse methods when you're calling them directly on $result.

JSON encode mySQL results clarification

3 Answers3