[mike_jandreau]

This may be complicated, or entirely impossible, I don't know, but it can't hurt to ask.

I've got information in my MySQL database (submissions from a form), that I need to compare to one individual row. Is this possible?

Further elaboration:
We've run a contest where users have had to vote in 16 different categories (best movie, worst movie, etc), those entries are dumped into my MySQL database, one row per entry.

Each category on the form has it's own entry.

At the end of the contest, I will then add a row to the database for what won each category.

Can I then write a script (and if so, how?) to compare all the data to the "Winner" row, and tell me via output how many of each row matched the winners?

Example of the database is: IP, Name, EMail Address, Worst Movie, Best Ensamble Cast, Best Chick Flick, etc..

So, I would need the output to include the e-mail address, and a total number of how many of their entries matched what I put into the "Winners" column, for every single row in the database (in this instance, just shy of 1500)

Possible, and if so, how? Thanks in advance, for any suggestions, you guys are always extremely helpful (and, if this doesn't make sense to anyone, ask questions, I tend to overthink and overexplain things, to complicate)

[dave111]

Do something like this (example for the winning worst movie)

PHP Code:

$worst_movie = 'White Chicks';

$query = mysql_query("SELECT name, email FROM YOURTABLE WHERE worst_movie LIKE '%".$worst_movie."%'");

$total = mysql_num_rows($query);

echo 'Total Winners: '.$total.'<br><br>';

while($rec = mysql_fetch_array($query)){

    echo $rec['name'] .' - '.$rec['email'];

} 

[mike_jandreau]

Thanks for the help, it's a good start.

It doesn't quite do what I'm looking for though. The code you gave me just gives me the number of times the specific $worst_movie was voted for, and displays the e-mail addresses and names of people who voted for it.

What I need to do, is get that list to compare it to my winner categories.

IE; I'll need to print via output something like this:

Code:

<table width="100%"  border="0" cellpadding="2">
  <tr>
    <td>Name</td>
    <td>E-Mail</td>
    <td>Worst Movie </td>
    <td>Best Movie </td>
    <td>Total Matches </td>
  </tr>
  <tr>
    <td>Winners</td>
    <td>NULL</td>
    <td>House of Wax </td>
    <td>Crash</td>
    <td>N/A</td>
  </tr>
  <tr>
    <td>Me</td>
    <td>my e-mail </td>
    <td>House of Wax </td>
    <td>Walk The Line </td>
    <td>1</td>
  </tr>
</table>

This way I'll have a table set with columns of people's names, e-mail addresses, their votes, and how many of those votes (the very last column) actually matched what I've entered as the "Winners"

I've already got a few pages written that display just the votes (I also exported it to Excel, to count votes), but need to know exactly how many votes each person got that matched what I enter as a winner.

Any other ideas, or suggestions?

[mab]

Firstly, storing a row with the answers into the database won't help. You cannot compare the other rows with that row any easier than just forming a query with the answers in it.

If the 16 categories and the corresponding answers were stored as 16 separate rows using each person's name, I can think of a way to form a query using a GROUP BY and a COUNT that would give results ordered with the person having the most correct answers first... down to the person with the least correct answers. It would be similar to this - http://www.programmingtalk.com/showthread.php?t=29827

However, it seems from the definition you have written that there is one row per person with all their answers in the row. It may be possible to form a complicated query involving sub-queries and counts... that will do this, but I can think of a straight forward way to do this in PHP. Code to follow in a separate post...

[mab]

The following should work or be close (tested except for actual database results) -

PHP Code:

<?php

// your code to connect and select db here...



// put your categories/column names in the database in the following...

$categories = array();

$categories[] = "Worst_Movie";

$categories[] = "Best_Ensamble_Cast";

$categories[] = "Best_Chick_Flick";

// add the remainder of your 16 categories/column names...



// put the corresponding answers to the above categories in the same order

$answers = array();

$answers[] = "abc";

$answers[] = "def";

$answers[] = "ghi";

// add the remainder of your 16 answers...



$result_array = array(); // array to hold names that had a correct answer in any category



foreach($categories as $key => $value){

// the following assume that names are unique...

    $query = "SELECT name FROM your_table WHERE $value LIKE '%$answers[$key]%'";

    $result = mysql_query($query) or die('Query failed: ' . mysql_error());

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

        $result_array[] = $row['name'];

    }

}



$count_array = array_count_values($result_array); // get a count for each name

arsort($count_array); // sort the names by count of correct answers in descending order



echo "<pre>";

print_r($count_array);

echo "</pre>";



// your code to fetch the email... and displaying the results ... for the winner(s) based on the names in the $count_array...

?>

[mike_jandreau]

You're my hero.

Send me a bill

[mike_jandreau]

I'm revisiting this, all this time later.
The code, as above, works great, thanks for that.

What I'm trying to do now, is display a message with each part that matches. So if they get a match, they get a "Correct", and if it doesn't match, they get a "Sorry, wrong"

The code, however, is giving me trouble. What I've tried is this:

PHP Code:

<?php

$actionmovie="$BestActionMovie";

if ($actionmovie=="Winner Would Go here")

  echo "<img src=\"..//images/yes.gif\" alt=\"Correct!\"> <strong>Correct!</strong>"; 

else

  echo "<img src=\"../images/no.gif\" alt=\"Wrong!\"> <strong>Sorry, wrong vote!</strong>"; 

?>

What I'm assuming the code would do, but isn't is pull what the person's entry was under each category. ie; $actionmovie="$BestActionMovie", the $BestActionMovie is the variable from the database, where the person's data is stored.

So, if I define what they voted for as $actionmovie, and say if $actionmovie = whatever the winner is, say Correct.

The important part needs to be that $BestActionMovie needs to reflect whatever is stored in the database for the e-mail address specified.

It's not working. I'm thinking that how I've written the code to define what the person's vote was for, isn't working right.

Any suggestions? (Sorry if this sounds confusing, I've been staring at the screen for far too long, and may not be making sense)

Thanks for any advice/help.

[mike_jandreau]

Scratch that, I figured it out.

PHP Code:

<?php

$BestActionMovie=$row['BestActionMovie'];

if ($BestActionMovie=="Winner Would Go here")

  echo "<img src=\"..//images/yes.gif\" alt=\"Correct!\"> <strong>Correct!</strong>"; 

else

  echo "<img src=\"../images/no.gif\" alt=\"Wrong!\"> <strong>Sorry, wrong vote!</strong>"; 

?>

Is what works for me.

Unless someone's got a better suggestion, I consider this all set.

[mike_jandreau]

Originally Posted by mab

The following should work or be close (tested except for actual database results) -

PHP Code:

<?php

// your code to connect and select db here...



// put your categories/column names in the database in the following...

$categories = array();

$categories[] = "Worst_Movie";

$categories[] = "Best_Ensamble_Cast";

$categories[] = "Best_Chick_Flick";

// add the remainder of your 16 categories/column names...



// put the corresponding answers to the above categories in the same order

$answers = array();

$answers[] = "abc";

$answers[] = "def";

$answers[] = "ghi";

// add the remainder of your 16 answers...



$result_array = array(); // array to hold names that had a correct answer in any category



foreach($categories as $key => $value){

// the following assume that names are unique...

    $query = "SELECT name FROM your_table WHERE $value LIKE '%$answers[$key]%'";

    $result = mysql_query($query) or die('Query failed: ' . mysql_error());

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

        $result_array[] = $row['name'];

    }

}



$count_array = array_count_values($result_array); // get a count for each name

arsort($count_array); // sort the names by count of correct answers in descending order



echo "<pre>";

print_r($count_array);

echo "</pre>";



// your code to fetch the email... and displaying the results ... for the winner(s) based on the names in the $count_array...

?>

Trying to expand on this a bit, to make the output only display one email address, which works.

However, it displays it as:

PHP Code:

Array ( [user@domain.com] => 16 ) 

Is there a way I can do the following, based on your example?:
1) Remove the word "Array"?
2) Remove the ([ and] =>

I basically just want to show the e-mail address, and how many matches there were from the array. I've messed around with it for a while, but can't figure out how to modify the output of the array.

[mike_jandreau]

Figured it out thanks to one of the engineers at work.

Resolved.