CODEWITHSUNDEEP
phpmysql
Mr_Thomas
Mr_Thomas

Reputation: 869

PHP page to get results of MySQL query

I haven't a clue on how to write PHP but one of the sites I oversee uses it. I'd like to have a pie chart that automatically updates based on current data. I know how to write the HTML that will draw the chart but don't know how to get my MySQL statement to interact with the data. Here's the piece of code I want to run:

SELECT
pc.shortname Name,
Count(qa.factor) Count

FROM
stable_host pc,
stable_area pq,
stable_area_front qa

WHERE
pc.id = pq.host
AND pq.id = qa.area
AND ((pq.name='Signal1') OR (pq.name='Signal5'))

GROUP BY pc.shortname

ORDER BY pc.shortname

When I run this script, it gives me the following output:

Category1   62
Category2   53
Category3   35
Category4   38

My question is this: How do I get the output so it just shows the numbers, comma delimited? (Example: "62,53,35,38")

Upvotes: 1

Views: 168

Answers (2)

Martin Lyne
Martin Lyne

Reputation: 3065

You could use:

<?php 
$dsn = '';
$username = '';
$password = '';
$query = "Paste your query here";

try {
  $pdo = new PDO($dsn, $username, $password);
  $pdo->prepare($query);
} catch(PDOException $e) {
    die("Could not connect to the database\n");
}
    $results = $pdo->fetchAll();
if(count($results) > 0) {
    $array = array(); 
    foreach($queryResult as $row) {
      $array[] = $row['Count'];
    }
    $commaDelimitedString = implode(',',$array);
} // No need for end PHP tag, they cause problems.

This: - connects to database (or tries to) - runs your query - makes an empty array - loops over your results - takes the "Count" column and adds it to the end of the array - compresses the array into a comma-delimited string.

Look here (http://wezfurlong.org/blog/2004/may/first-steps-with-pdo/ and http://www.electrictoolbox.com/php-pdo-bound-placeholders/) for some guidance on the usage of PDO and what you'll need to connect to the database (don't post those details, obviously)

It may look daunting, but just go one step at a time. If you have more questions just ask.

Upvotes: 0

wesside
wesside

Reputation: 5740

   SELECT GROUP_CONCAT(count(qa.factor) SEPARATOR ',') count

I believe this is what you're asking.

Upvotes: 2

Related Questions