Basic Question About PHP and MYSQL

How to manipulate resulting data

[RandallK]

This question seems so basic, but darned if I can't find an answer.

I've got a very simple database I've set up as practice for myself. People can vote on their favorite snack. There is a table with vote_id, and snack_choice.

There is a simple form with radio buttons for "Apple" "Banana" "Candy" "I Hate Snacks".

I can get the data from the database with something like SELECT snack_choice, COUNT(*) FROM snackvote;, and by using a 'while' and mysql_fetch_array go through the data and display it where I get something like. Apple 1, Banana 2, Candy 12, and I Hate Snacks 1.

Is there a way to take that 'result' and manipulate the data. Can I set $apple_votes = 1 (Or whatever the result is), so I can then say "15% of the votes are for apple"?

Or do I have to make four separate queries with WHERE and then store the data that way.

Hope that makes sense... it seems so simple, but I can't figure it out.

Thanks in advance.

[IanKelley]

Your question isn't entirely clear, here's what it sounds like you are trying to do...

- You find out, from previously stored data, what percentage of votes each option got.
- Now that you know how many votes each option got you want to store that data in another table?

If so you can do that with one UPDATE statement, i.e. UPDATE SET x=x, y=y, z=z WHERE a=b

Or are you asking if you can somehow query the first table and update the second, after performing some math operations, in the same request?

You could probably do that but it wouldn't make much sense as it wouldn't save you any processor cycles or disk IO requests.

[RandallK]

I am trying to make one query to the database and store the results as PHP variables.

I want to process that one query as above and end up with four PHP variables. $apple_votes, $banana_votes, $candy_votes, $ihatesnacks_votes. Then I can use that information in many different ways.

At this point all I can seem to do is echo it by using a mysql_fetch_array while loop.

Can I do this with the one query, or do I need to make four separate queries using a WHERE clause?

[CyBerAliEn]

Considering the low processing required to run these simple queries, I would argue that it is OK to run four queries; one for each type. Just a quick SQL query to count the desired results. This is a quick/low resource process.

You can then store the results as separate variables and display them and manipulate them.

What I would consider doing is something kind of like:

$voteCounts = array();
$query = "SELECT DISTINCT snack_choice FROM snackvote";
$result = mysql_query($query);
if ($result)
{
  $resultArray = mysql_fetch_array($result);
  if (count($resultArray)>0) { foreach ($resultArray as $row) {
    $currentSnack = $row['snack_choice'];
    $countQuery = "SELECT snack_choice, COUNT(*) FROM snackvote WHERE snack_choice='{$currentSnack}'";
    $countResult = mysql_query($countQuery);
    $count = mysql_result($countResult);
    $voteCounts[$currentSnack] = $count;
    echo "Number of votes for {$currentSnack} is {$count}.";
    /*You can then access the value of the count elsewhere by referencing the voteCounts array, ie: $numAppleVotes = $voteCounts['apple']; */
  } }
}

This code may not work. But the logic in it should give you what you want. Generally, I would make my code as generic as possible so it could be reused again later. In other words, it could be useful to create a class (poll) or a series of functions to aid in processing your poll. In doing this, things like "currentSnack" might become "currentItem", etc. But as you are learning how to use PHP/mySQL, the above is probably more appropriate to learn from.

It is possible there is a complex SQL query that will do what you want, but I am not advanced enough in SQL to ponder this. Another solution (as you hit on), is to simply query the whole table for results. Then run through each result, counting the types of votes through each iteration. Though this works, it would become quite inefficient when votes reach a large threshold (probably well into the thousands+++, which realistically is unlikely in this case).

[RandallK]

Using your logic I came up with the following, which seems to work, but I'm not sure it is the 'proper' way to do it. Anyone have any thoughts?

$query = "SELECT snack_choice, COUNT(snack_choice) FROM snackvote GROUP BY snack_choice";
$result = mysql_query($query);

$snackVotes= array();

while($row = mysql_fetch_array($result)){

$snackVotes[$row['snack_choice']] = $row['COUNT(snack_choice)'];

}

[CyBerAliEn]

Your code is simple. Simple is always nice.

Most importantly... verify that it works as intended. It looks like it does/will. But run some tests to be sure there are no holes in the code or logic (though it seems sound).

Best of luck!

[IanKelley]

Looks good Rand.

For posterity here is a somewhat more efficient version:

$query = "SELECT snack_choice FROM snackvote";
$result = mysql_query($query);
while ($row = mysql_fetch_row($result)){
$snackvotes[$row[0]]++;
}

As you can see there's no reason to ask MySQL to both fetch and count because you can turn the creation of your array into a counter.

Use asort() or arsort() to order the array by choice popularity.

[RandallK]

Thank you very much for the replies!