PHP page to get results of MySQL query

Question

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")

wesside · Accepted Answer · 2012-11-02 20:23:33Z

2

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

I believe this is what you're asking.

answered Nov 2, 2012 at 20:23

wesside

5,7605 gold badges32 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mr_Thomas Over a year ago

I get error #1111, Invalid Use of group function when I use this code. If I leave the 'count' part of it out, it doesn't give me the error -- but it also doesn't give me what I'm looking for.

Martin Lyne · Accepted Answer · 2012-11-02 20:55:45Z

0

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.

edited Nov 2, 2012 at 20:55

answered Nov 2, 2012 at 20:21

Martin Lyne

3,0952 gold badges24 silver badges29 bronze badges

5 Comments

Mr_Thomas Over a year ago

I don't understand. Where does my SQL statement fit in to this? Where would my table names/field names go? Would this be the whole PHP code? I see other pages that start with <? and end with ?>. Would your statement above just go between those?

Martin Lyne Over a year ago

I'll expand (sorry thought you already had some of this written up)

Mr_Thomas Over a year ago

I looked at the examples you gave for PDO but can't get past the database. Your example above just keeps saying Can't connect to the database no matter what I do. My DSN looks like this: 'mysql:host=MyHost;dbname=MyDBName'; What am I doing wrong?

Martin Lyne Over a year ago

That looks perfect to me.. perhaps try other way around, see the first example here php.net/manual/en/pdo.construct.php

Mr_Thomas Over a year ago

Still not working. Either I completely break it or it can't connect to the database. I am stumped, which is not hard to do in this situation.

Collectives™ on Stack Overflow

PHP page to get results of MySQL query

2 Answers 2

1 Comment

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Related