Skip to content
Advertisement

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)

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.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement