php - Fetch column name and rows data from mysql and create json -
here have database mysql:
i need make json file in 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 format becouse use data google visualisation api.
now work code:
try { $conn = new pdo("mysql:host=localhost;dbname=$dbname", $username, $password); $conn->setattribute(pdo::attr_errmode, pdo::errmode_exception); $result = $conn->query('select * 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 $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 json format $jsontable = json_encode($table); //echo $jsontable; } catch(pdoexception $e) { echo 'error: ' . $e->getmessage(); } echo $jsontable;
but possible auto fetch column names fetch rows data , create json in format put above?
why need this? becouse have many table , each table need create manualy create php file that...
so need solution , auto column names, rows data , create json.
please read on pdostatement.fetchall
method. use fetch
or fetchall
build arrays.
you can pass fetch styles parameter.
for example pdo::fetch_named
:
$result = $query->fetchall(pdo::fetch_named);
will create associative array using columns names, save lot of code.
Comments
Post a Comment