How to fetch all rows of sql query from MySQL database into array in php

Question

I am writing an application to build a chart using the google chart API. The data is dynamic, and resides in a MySQL database.

The issue being faced is that the array that contains the datasource to be displayed only contains the first row returned by the query. Even after looping.

This is the code:

     include 'connect.php';
     $sql="SELECT result,COUNT(result) value
     FROM test
     WHERE result LIKE 'GY%'
     GROUP BY result";
     $result=mysqli_query($link,$sql);
     $myurl[]="['Option','Value']";
     while($row=mysqli_fetch_assoc($result))
    {
    $result=$row['result'];
    $value=$row['value'];
    $Title="My Results"; 
    $myurl[]="['".$result."',".$value."]";
    }

   <script type="text/javascript">
  google.load('visualization', '1', {'packages':['corechart']});
  google.setOnLoadCallback(drawChart);
  function drawChart() {
   var data = new google.visualization.arrayToDataTable([
   <?php 

 echo (implode(",",$myurl));?>
  ]);
 var options={
 title:'<?php echo($Title);?> '

  };

Only the first row being returned is used in the chart,therefore it shows that value as 100%. How can i ensure ALL the row values are used?

Answer 1

If you want an associative array, use this:

$query = "SELECT column FROM `table` WHERE id='" . $id . "'"; / Whatever your query is
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);

You can then call $row and the column name from the row with a numbered id that is $id by using $row['columnname']; I'm also pretty sure that you can use print_r($row['columnname']); without including the WHERE id='" . $id . "'"; in the query and instead just having $query = "SELECT column FROMtable; and it will print out all of the data (every row) in that column.

Also, if you're just looking to draw data to a chart, using a google API could over complicate things, as you could just output the data to an HTML table with this code:

<?php
   $query = "SELECT FROM `table`";
   $result = mysql_query($query);

   echo '<table class="table">';
   echo '<tr class="table">';
   echo '<th class="table">Column</th>';
   echo '</tr>';

   while($row = mysql_fetch_assoc($result)
   {
       echo '<tr class="table">';
       echo '<th class="table">' . $row['column'] . '</th>';
       echo '</tr>';
   }

   echo '</table>';
?>

Add any other things you need using echo in php then the html code. You can also put buttons in the table, and pretty much anything else. This site should give any more info you would need on HTML tables: http://www.w3schools.com/html/html_tables.asp

Here's another little example I made of an HTML form echoed in php. It has buttons, etc.

$query = "SELECT * FROM `users` WHERE status='accepted'";
$result = mysql_query($query) or die(mysql_error()); 

echo '<table class="table">';
echo '<h2>Accounts</h2>';
echo '<tr class="table">';
echo '<th class="table">Name</th>';
echo '<th class="table">Email</th>';
echo '<th class="table">Username</th>';
echo '<th class="table">Date Created</th>';
echo '<th class="table">Decline</th>';
echo '<th class="table">Accept</th>';
echo '<th class="table">Give Admin</th>';
echo '</tr>';

while($row = mysql_fetch_assoc($result))
{
    echo '<tr class="table">';
    echo '<th class="table">' . $row['name'] . '</th>';
    echo '<th class="table">' . $row['email'] . '</th>';
    echo '<th class="table">' . $row['username'] . '</th>';
    echo '<th class="table">' . $row['trn_date'] . '</th>'; 
    echo '<form method="post">';
    echo '<th class="table"><input type="submit" name="decline_' .$row["id"]. '" value="decline" ></input></th>';
    echo '<th class="table"><input type="submit" name="accept_' .$row["id"]. '" value="accept" ></input></th>';
    echo '<th class="table"><input type="submit" name="giveadmin_' .$row["id"]. '" value="accept/give admin" ></input></th>';
    echo '</form>';
    echo '</tr>';
}

echo '</table>';

Hope that helped:)

Answer 2

Modify the query like this

$sql="SELECT result value
     FROM test
     WHERE result LIKE 'GY%'
     GROUP BY result";
$result=mysqli_query($link,$sql);

And the number of results you can access like below.

mysqli_num_rows($result)