phpmysqliprocedural

convert mysql to mysqli procedural way for google charts

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]]

database results

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)

Solution

  • 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.