I am trying to upgrade some google charts i use for my own use from mysql to mysqli procedural way, and i have trouble in re-creating the correct array for the google chart to understand. I think i am very close by i am loosing something. Any assistance would be welcome.
This is my old code:
mysql_select_db($database_localhost, $localhost); $query_Recordset1 = "select count(*) as count,visible from packs where type=1 and serial=1 group by visible"; $Recordset1 = mysql_query($query_Recordset1, $localhost) or die(mysql_error()); $row_Recordset1 = mysql_fetch_assoc($Recordset1); $totalRows_Recordset1 = mysql_num_rows($Recordset1); $data[0] = array('visible','count'); for ($i=1; $i<($totalRows_Recordset1+1); $i++) { $data[$i] = array(mysql_result($Recordset1, $i-1,'visible'), (int) mysql_result($Recordset1, $i-1, 'count')); }
which results to:
[["visible","count"],["0",266],["1",1466],["2",1],["3",59]]
But when i upgrade (or try to upgrade the code to mysqli):
$query_Recordset1 = "select count(*) as count,visible from packs where type=1 and serial=1 group by visible"; $Recordset1 = mysqli_query($connection,$query_Recordset1) or die(mysqli_error($mysqli)); $row_Recordset1 = mysqli_fetch_assoc($Recordset1); $totalRows_Recordset1 = mysqli_num_rows($Recordset1); # set heading $data[0] = array('visible','count'); for ($i=1; $i<=($totalRows_Recordset1+1); $i++) { mysqli_data_seek($Recordset1, $i-1); $data[$i] = array((string) mysqli_fetch_array($Recordset1)['visible'],(int) mysqli_fetch_array($Recordset1)['count']); }
it results to:
[["visible","count"],["0",1466],["1",1],["2",59],["3",0],["",0]]
which obviously does not match the results i want since one column for some reason is offset by one (see value 266 is not fetched at all)
Advertisement
Answer
Not sure why your code is structured the way it is, you could simplify it a lot by using a standard loop….
$query_Recordset1 = "select count(*) as count,visible from packs where type=1 and serial=1 group by visible"; $Recordset1 = mysqli_query($connection,$query_Recordset1) or die(mysqli_error($mysqli)); # set heading $data = [['visible','count']]; while ($row_Recordset1 = mysqli_fetch_assoc($Recordset1) ) { $data[] =[$row_Recordset1['visible'],(int) $row_Recordset1['count']]; }
In your code, the line…
$data[$i] = array((string) mysqli_fetch_array($Recordset1)['visible'], (int) mysqli_fetch_array($Recordset1)['count']);
retrieves 2 rows of data, one for the visible
part and one for the count
part which is why the data is offset.