CODEWITHSUNDEEP
phpmysqlsqljsonpdo
gmaestro
gmaestro

Reputation: 339

Fetch column name and rows data from mysql and create json

Here I have an database mysql:

enter image description here

I need to make a json file in this format:

{"cols":[{"label":"ID","type":"number"},{"label":"Naziv","type":"string"},{"label":"Povrsina","type":"number"},{"label":"Lokacija","type":"string"},{"label":"Arkod ID","type":"number"},{"label":"Osnov koriscenja","type":"string"},{"label":"Kultura","type":"string"},{"label":"Naziv","type":"string"},{"label":"Navodnjavanje","type":"string"}],"rows":[{"c":[{"v":1},{"v":"Vinograd"},{"v":230457},{"v":"dole"},{"v":57875},{"v":"zakup zemlje"},{"v":"vocnjak"},{"v":"Vinograd"},{"v":"DA"}]},{"c":[{"v":2},{"v":"Njiva 3"},{"v":33445},{"v":"selo"},{"v":4564},{"v":"vlasnistvo"},{"v":"njiva"},{"v":"Njiva 3"},{"v":"da"}]},{"c":[{"v":3},{"v":"Vocnjak N"},{"v":230457},{"v":"iznad brdo"},{"v":57875},{"v":"zakup zemlje"},{"v":"vocnjak"},{"v":"Vocnjak N"},{"v":"da"}]},{"c":[{"v":4},{"v":"Njiva Dek"},{"v":33445},{"v":"selo"},{"v":4564},{"v":"vlasnistvo"},{"v":"njiva"},{"v":"Njiva Dek"},{"v":"da"}]}]}

I need this format becouse I use data for google visualisation api.

Now I do that to work with this code:

 try {
      $conn = new PDO("mysql:host=localhost;dbname=$dbname", $username, $password);
      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

      $result = $conn->query('SELECT * FROM zemljiste');

      $rows = array();
      $table = array();
      $table['cols'] = array(

        array('label' => 'ID', 'type' => 'number'),
        array('label' => 'Naziv', 'type' => 'string'),
        array('label' => 'Povrsina', 'type' => 'number'),
        array('label' => 'Lokacija', 'type' => 'string'),
        array('label' => 'Arkod ID', 'type' => 'number'),   
        array('label' => 'Osnov koriscenja', 'type' => 'string'),
        array('label' => 'Kultura', 'type' => 'string'),
        array('label' => 'Naziv', 'type' => 'string'),
        array('label' => 'Navodnjavanje', 'type' => 'string')
    );
        foreach($result as $r) {

          $temp = array();

          $temp[] = array('v' => (int) $r['ID']); 
          $temp[] = array('v' => (string) $r['naziv']);
          $ha = (int)$r['ha'];
          $ar = (int)$r['ar'];
          $m2 = (int)$r['m2'];
          $povrsina = $ha*10000+$ar*100+$m2;
          $temp[] = array('v' => (int) $povrsina); 
          $temp[] = array('v' => (string) $r['lokacija']);
          $temp[] = array('v' => (int) $r['arkod']);
          $temp[] = array('v' => (string) $r['osnov']);
          $temp[] = array('v' => (string) $r['kat_kul']);
          $temp[] = array('v' => (string) $r['naziv']);
          $temp[] = array('v' => (string) $r['navodnjavanje']);


          $rows[] = array('c' => $temp);
        }

    $table['rows'] = $rows;

    // convert data into JSON format
    $jsonTable = json_encode($table);
    //echo $jsonTable;
    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }
    echo $jsonTable;

But is it possible to auto fetch column names then fetch rows data and then create JSON in format I put above?

Why I need this? Becouse I have many table and for each table I need to create manualy create php file for that...

So I need some solution and with that to get auto column names, rows data and create JSON.

Upvotes: 1

Views: 1704

Answers (1)

meda
meda

Reputation: 45490

Please read up on PDOStatement.fetchAll method. You could also use fetch or fetchAll to build your arrays.

You can pass fetch styles as a parameter.

for example PDO::FETCH_NAMED:

$result = $query->fetchAll(PDO::FETCH_NAMED);

Will create associative array using the columns names, which will save you a lot of code.

Upvotes: 3

Related Questions