0

I have a problem with getting JSON data from mysql phpmyadmin(Version 4.0) table, I have tried PDO and mysql_connect.

My idea: mysql--> PHP--> echo json

The connection to my server works and the SQL statement works perfectly. I have tested it. But the "json_encode"(and some others) of the JSON itself is not possible. The json array is not builted.

Are there settings in PhpMyAdmin which I have to pay attention to?

PDO:

      $query = $pdo->prepare('SELECT p.*, count(r.rate) AS rates, avg(r.rate) AS average from plugins p left join rate r on p.title = r.title group by p.title');
        $query->execute();
        $row = $query->fetchAll();
        // send the data encoded as JSON
        $json = json_encode($row, JSON_UNESCAPED_UNICODE);
echo $json;
       print_r($row);
        exit;

Result is:

Array ( [0] => Array ( [id] => 153 [0] => 153 [title] =>

Where am I going wrong? I updated my server to PHP 7.0 and now the code doesn't work. Before the update all is working perfectly and there was a long json array(how it should be)

How it should be

{{"id":"1","title":"ExmapleTitle"....},{"id":"2","title":"ExmapleTitle2"....}...}
Improve this question
6
  • 3
    Why do you use both mysqli and pdo? Choose one ;-) Commented Jul 30, 2017 at 16:25
  • Fetching what? What are the expected results and current results? Commented Jul 30, 2017 at 16:25
  • In php7 json_encode only works with utf8, do you have anything in the response that is not? Otherwise, will make it fail Commented Jul 30, 2017 at 16:37
  • Which part of the code isn't working? Is $json_array and/or $data empty? Are you sure your query is correct and is being properly executed? Commented Jul 30, 2017 at 16:43
  • PDO and mysqli scripts are in different files of my web page (I have tested a lot of possibilities). I want to convert my MYSQL phpmyadmin databse to utf8 json. I think there is a problem with making such a json array. The script do nothing. No errors or others Commented Jul 31, 2017 at 4:24

1 Answer 1

Reset to default
2

You must call execute()

    <?php

    $pdo = new PDO('mysql:host=xxxxx;dbname=xxxxx', 'xxxxxx', 'xxxxxx');

    $statement=$pdo->prepare("SELECT p.*, count(r.rate) AS rates, avg(r.rate) AS average from plugins p left join rate r on p.title = r.title group by p.title");

    $statement->execute(); // <<< --- You are missing this
    $data = $statement->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($data); //Echo: data ... voila
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you. I correct it. But i have got a multidimensional array and the encodeing doesn't work: PHP prints out with print_r.... Array ( [0] => Array ( [id] => 153 [0] => 153 [title] => 111 Ink Brushes [1] => 111 Ink Brushes [link] => myphotoshopbrushes.com/brushes/id/3516 [2] => myphotoshopbrushes.com/brushes/id/3516 [data] => Ink111.abr [3] => Ink111.abr [text]
I don't get why you say the encoding does not work. Are you saying that you want the JSON formatted in a particular way?
If you want the JSON formatted a particular way, then loop through the data and format it how you want. Perhaps you should add a sample output how you want the JSON to look.
Depends on how you want the output formatted. If you provide the full print_r output and an example of what you are trying to achieve, I can help you get there. As it stands, there isn't enough information to provide a thorough answer.
@CSSMaster it looks like you're missing the PDO::FETCH_ASSOC argument for the fetchAll() method in your code. Add that, and the JSON should be what you're looking for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.