PHP - Export MySQL tables to JSON file

Question

I am trying to export my MySQL tables from my database to a JSON file, so I can list them in an array.

I can create files with this code no problem:

        $sql=mysql_query("select * from food_breakfast");

    while($row=mysql_fetch_assoc($sql))
    {
    $ID=$row['ID'];
    $Consumption=$row['Consumption'];
    $Subline=$row['Subline'];
    $Price=$row['Price'];
    $visible=$row['visible'];

    $posts[] = array('ID'=> $ID, 'Consumption'=> $Consumption, 'Subline'=> $Subline, 'Price'=> $Price, 'visible'=> $visible);
    }
    $response['posts'] = $posts;

    $fp = fopen('results.json', 'w');
    fwrite($fp, json_encode($response));
    fclose($fp);

Now this reads a table and draws it's info from the fields inside it.

I would like to know if it is possible to make a JSON file with the names of the tables, so one level higher in the hierarchy.

I have part of the code:

    $showtablequery = "
    SHOW TABLES
    FROM
        [database]
    LIKE
    '%food_%'
    ";

$sql=mysql_query($showtablequery);

    while($row=mysql_fetch_array($sql))
     {
       $tablename = $row[0];

     $posts[] = array('tablename'=> $tablename);
      }
     $response['posts'] = $posts;

But now i am stuck in the last line where is says: $ID=$row['ID']; This relates to the info inside the Table and I do not know what to put here.

Also as you can see, I need to filter the Tables to only list the tables starting with food_ and drinks_

Any help is greatly appreciated:-)

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. — Mr. Polywhirl
– Mr. Polywhirl, Commented Jan 8, 2013 at 2:32

hek2mgl · Accepted Answer · 2013-01-08 02:48:57Z

2

There is no 'table id' in MySQL and therefore the result set from SHOW TABLES has no index id. The only index in the resultset is named 'Tables_in_DATABASENAME'.

Also you should use the mysqli library as the good old mysql library is depreacted. Having prepared an example:

<?php

$mysqli = new mysqli(
    'yourserver',
    'yourusername',
    'yourpassword',
    'yourdatabasename'
);

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") "
         . $mysqli->connect_error;
}


$result = $mysqli->query('SHOW TABLES FROM `yourdatabasename` LIKE \'%food_%\'');
if(!$result) {
    die('Database error: ' . $mysqli->error);
}

$posts = array();
// use fetch_array instead of fetch_assoc as the column
while($row = $result->fetch_array()) {
    $tablename = $row[0];
    $posts []= array (
        'tablename' => $tablename
    );
}

var_dump($posts);

edited Jan 8, 2013 at 2:48

answered Jan 8, 2013 at 1:41

hek2mgl

159k31 gold badges263 silver badges279 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

jwknz Over a year ago

ok, so I got that and it generates the file, but does not populate the array - I have updated the code to get the formatting right, please see the second section.

jwknz Over a year ago

No not yet, sorry for the delay -> is the secret, the pw to the database and the test the name of the database?

jwknz Over a year ago

so how does it know what the user and secret are??

jwknz Over a year ago

yeah I thought so, what i mean is, do I replace them, or do I define them before hand, so that the code knows where to look?? I use mysql_connect("localhost", "username", "password"); to define them

hek2mgl Over a year ago

just replace them in new mysqli( ...)

|

Collectives™ on Stack Overflow

PHP - Export MySQL tables to JSON file

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related