[RESOLVED] Confused on creating a loop for html tables

[dmacman1962]

Hi everyone,

Nesting loops to create html tables with (incremented) variable names is a task I am weak at.

What I have is a quiz that lists each person in a row then shows the next name in the next row.

I could not figure out how to loop thru this, so I did it all by hand (I know, php would be more efficient, but I could not get it to work).

Here is my page showing what it looks like...

My HTMl/php way of doing it.

I actually did the form by hand also.

My HTML form with N[0] N[1] etc for the names.

The code for the query is. ( I shortened this, it si actually 45 names long!)

//get get the names of the celebs from the Names table
$query="SELECT * FROM QRatingNames";
$result=mysql_query($query);
while ($myrow = mysql_fetch_array($result))
{
$Celeb00 = $myrow['Name0'];
$Celeb01 = $myrow['Name1'];
//etc all the way to 45
}

Heres the query to get the votes ... (Again, I had to do this 45 times!)

//Get Name0
$query0="SELECT COUNT(*) FROM QRatingVotes WHERE Name0='1' AND QRatingID='1'";
$result=mysql_query($query0);
$num=mysql_fetch_row($result);
$Name00 = $num[0];

$query0="SELECT COUNT(*) FROM QRatingVotes WHERE Name0='2' AND QRatingID='1'";
$result=mysql_query($query0);
$num=mysql_fetch_row($result);
$Name01 = $num[0];

$query0="SELECT COUNT(*) FROM QRatingVotes WHERE Name0='3' AND QRatingID='1'";
$result=mysql_query($query0);
$num=mysql_fetch_row($result);
$Name02 = $num[0];

$query0="SELECT COUNT(*) FROM QRatingVotes WHERE Name0='4' AND QRatingID='1'";
$result=mysql_query($query0);
$num=mysql_fetch_row($result);
$Name03 = $num[0];

$query0="SELECT COUNT(*) FROM QRatingVotes WHERE Name0='5' AND QRatingID='1'";
$result=mysql_query($query0);
$num=mysql_fetch_row($result);
$Name04 = $num[0];

$QRatings = ($Name00/$totalcount)*100;
$Total1 = round($QRatings,2);
$NameTotal0 = $Total1 . '%';


//Get Name1

And then to build the table... (45 times, note: I alternated cell colors for ease of reading)

//ROW 0
echo '<tr><p class="fontbold"><td width="150" class="fontbold">'.$Celeb00.'</td></p><td width="100" class="fontred">'.$Name00.'</td><td width="100" class="fontred">'.$Name01.'</td><td width="100" class="fontred">'.$Name02.'</td><td width="100" class="fontred">'.$Name03.'</td><td width="100" class="fontred">'.$Name04.'</td<td width="100" class="fontred">'.$totalcount.'</td><td width="100" class="fontred">'.$NameTotal0.'</td></tr>';

//ROW 1
echo '<tr><td width="150" bgcolor="#CCCCCC" class="fontbold">'.$Celeb01.'</td><td width="100" bgcolor="#CCCCCC" class="fontred">'.$Name10.'</td><td width="100" bgcolor="#CCCCCC" class="fontred">'.$Name11.'</td><td width="100" bgcolor="#CCCCCC" class="fontred">'.$Name12.'</td><td width="100" bgcolor="#CCCCCC" class="fontred">'.$Name13.'</td><td width="100" bgcolor="#CCCCCC" class="fontred">'.$Name14.'</td<td width="100" bgcolor="#CCCCCC" class="fontred">'.$totalcount.'</td><td width="100" bgcolor="#CCCCCC" class="fontred">'.$NameTotal1.'</td></tr>';

I am looking for any suggestions to simplify these with loops. I read thru dozens of posts and could apply what I read, so I pounded thru it the hard way.

Now I am looking to learn the correct way.

Thanks,

Don

[khendar]

So you are storing the votes for one celebrity in one row of the database ? eg (i've changed the field names so its a little clearer)

celeb_id|celeb_name  |fav_count|vg_count|g_count|fp_count|nh_count
1       |Adam Sandler|3        |1       |1      |0       |1
etc

[dmacman1962]

Exactly!

[khendar]

Ok....

echo '<table ... >';
echo '[INSERT COLUMN HEADINGS HERE]';

$query = "select * from QRatingVotes";
$result = mysql_query($query);

while($celeb = mysql_fetch_array($result))
{
$fav_count = $celeb['fav_count'];
$vg_count = $celeb['vg_count'];
$g_count = $celeb['g_count'];
$fp_count = $celeb['fp_count'];
$nh_count = $celeb['nh_count'];
$total_count = $fav_count + $vg_count + $g_count + $fp_count + $nh_count;
$qRating = ...;

echo '<tr>';
echo '<td>'.$celeb['celeb_name'].'</td>';
echo '<td>'.$fav_count.'</td>';
echo '<td>'.$vg_count.'</td>';
echo '<td>'.$g_count.'</td>';
echo '<td>'.$nh_count.'</td>';
echo '<td>'.$total_count.'</td>';
echo '<td>'.$qRating.'%</td>';
echo '</tr>';
}

echo  '</table>';

That should handle the display - Obviously insert your formatting stuff and field names..

[dmacman1962]

This is getting me closer, but I am stil having a few issues.

Here is the page showing your changes...

Your changes in my file...

I am grey on a few things. How are you getting the names? I have an array (sorry not a loop) to get the names from the names table

$query="SELECT * FROM QRatingNames";

The $Celeb name is from the table, and formatted like this...

ID |QRatingID | Name0 | Name1 | all the way to Name44|
3 | 1 | Adam Sandler | Arnold S | all the way to 44|

Here is the code.

//get get the names of the celebs from the Names table
$query="SELECT * FROM QRatingNames";
$result=mysql_query($query);
while ($myrow = mysql_fetch_array($result))
{
$Celeb00 = $myrow['Name0'];
$Celeb01 = $myrow['Name1'];
$Celeb02 = $myrow['Name2'];
$Celeb03 = $myrow['Name3'];
$Celeb04 = $myrow['Name4'];
$Celeb05 = $myrow['Name5'];
$Celeb06 = $myrow['Name6'];
$Celeb07 = $myrow['Name7'];
$Celeb08 = $myrow['Name8'];
$Celeb09 = $myrow['Name9'];
$Celeb10 = $myrow['Name10'];
$Celeb11 = $myrow['Name11'];
$Celeb12 = $myrow['Name12'];
$Celeb13 = $myrow['Name13'];
$Celeb14 = $myrow['Name14'];
$Celeb15 = $myrow['Name15'];
$Celeb16 = $myrow['Name16'];
$Celeb17 = $myrow['Name17'];
$Celeb18 = $myrow['Name18'];
$Celeb19 = $myrow['Name19'];
$Celeb20 = $myrow['Name20'];
$Celeb21 = $myrow['Name21'];
$Celeb22 = $myrow['Name22'];
$Celeb23 = $myrow['Name23'];
$Celeb24 = $myrow['Name24'];
$Celeb25 = $myrow['Name25'];
$Celeb26 = $myrow['Name26'];
$Celeb27 = $myrow['Name27'];
$Celeb28 = $myrow['Name28'];
$Celeb29 = $myrow['Name29'];
$Celeb30 = $myrow['Name30'];
$Celeb31 = $myrow['Name31'];
$Celeb32 = $myrow['Name32'];
$Celeb33 = $myrow['Name33'];
$Celeb34 = $myrow['Name34'];
$Celeb35 = $myrow['Name35'];
$Celeb36 = $myrow['Name36'];
$Celeb37 = $myrow['Name37'];
$Celeb38 = $myrow['Name38'];
$Celeb39 = $myrow['Name39'];
$Celeb40 = $myrow['Name40'];
$Celeb41 = $myrow['Name41'];
$Celeb42 = $myrow['Name42'];
$Celeb43 = $myrow['Name43'];
$Celeb44 = $myrow['Name44'];
}


echo '<table ... >';
echo '<table width="800" border=1 ><tr><td width="100" class="fontbold">Name</td><td width="100" class="fontbold">One of my favorites!</td><td width="100" class="fontbold">Very Good personality</td><td width="100" class="fontbold">Good personality</td><td width="100" class="fontbold">Fair - Poor personality</td><td width="100" class="fontbold">I have never heard of this person</td><td width="100" class="fontbold">Total Familar Score</td><td width="100" class="fontbold">QRating</td></tr>';


$query = "select * from QRatingVotes";
$result = mysql_query($query);

while($celeb = mysql_fetch_array($result))
{
$fav_count = $celeb['fav_count'];
$vg_count = $celeb['vg_count'];
$g_count = $celeb['g_count'];
$fp_count = $celeb['fp_count'];
$nh_count = $celeb['nh_count'];
$total_count = $fav_count + $vg_count + $g_count + $fp_count;
//$total_count = $fav_count + $vg_count + $g_count + $fp_count + $nh_count;

(Never heard of them does not go to total counted votes, so I deleted it)

$qRating = $fav_count/$total_count;

echo '<tr>';
echo '<td>'.$celeb['celeb_name'].'</td>';
echo '<td>'.$fav_count.'</td>';
echo '<td>'.$vg_count.'</td>';
echo '<td>'.$g_count.'</td>';
echo '<td>'.$nh_count.'</td>';
echo '<td>'.$total_count.'</td>';
echo '<td>'.$qRating.'%</td>';
echo '</tr>';
}

echo  '</table>'; 

I think with your help, I can get this working.

I REALLY appreciate your help, since they now told me this need to be up and running by Wedensday.

Thanks,

Don

[samudasu]

Run the code just like khendar posted. It does exactly what you want. It loops through the query results row by row and prints them out to the screen. There's no need to run a query and assign a unique variable for each and every person.

UPDATE:
Ok, now i see where your confused. You're actually using two tables to retrieve data instead of one. That changes things. You're going to need to do a sql join. If you don't know, it means write a query that selects info from, in this case, 2 different tables. Here's some reading info.

http://sqlcourse2.com/joins.html

[khendar]

Why do you have two tables ? This is unneccessary.

[dmacman1962]

Sorry people, I am trying to understand what you are suggesting and what I did.

Also, I think I understand the suggestions and need to normalize this all.

We are going to have many different polls, and want to do this the correct way. If I follow this all, I think here's what I need to do.

I think I really need 4 tables.

| ID | Name|

|QRatingID | ID|

|QRatingID | VotesID |

| VotesID | Rating |

In this scenario, I would join the 2 ID's (in the first two tables).

Then the QRatingID's in the 2nd and 3rd tables.

Then the VotesID in tables 3 and 4.

That way I have 1 table of names (the 1st table).

and JOIN tables 2-4 to tally the votes and keep them seperate for each poll (QRating).

Is this the correct (Normalized) way?

Would it be more efficient, to have ID for all 4 tables?

Thanks,

Don

[dmacman1962]

After going thru the suggestions and thinking about how I am going to use this, I am not sure I explained this correctly.

We will have multiple poll systems running and they will have some redundant names. (IE: the teacher poll and the students may have some of the same names, but I need to track the votes seperately). And I may need to have have other polls that will have some of the same names for teachers, also. That is why I don't think the solutions suggested would be correct. My mistake.

Thanks,

Don

[dmacman1962]

Well, I took your suggestions (except I have another table that stores the names ) and I can now get the results. Except, it does not give me a total of all the votes for that celeb. It gives me the individual vote, line buy line.

Here is my code...

echo '<table><tr>
<td width="150">Names</td>
<td width="100">Favorite</td>
<td width="100">Very Good</td>
<td width="100">Good</td>
<td width="100">Fair</td>
<td width="100">Never Heard</td>
<td width="100">Total Count</td>
<td width="100">QRating</td></tr>';

$query="SELECT * FROM QRatingVotes2, QRatingNames WHERE QRatingVotes2.NamesID = QRatingNames.ID ";
$result=mysql_query($query);
while($celeb = mysql_fetch_array($result)) {
$celeb_ID = $celeb['QRatingID'];
$fav_count = $celeb['fav_count'];
$vg_count = $celeb['vg_count'];
$g_count = $celeb['g_count'];
$fp_count = $celeb['fp_count'];
$nh_count = $celeb['nh_count'];
$total_count = $fav_count + $vg_count + $g_count + $fp_count;
$qRating = round((($fav_count/$total_count)*100),2); 

echo '<tr>';
echo '<td>'.$celeb_ID.'</td>';
echo '<td>'.$fav_count.'</td>';
echo '<td>'.$vg_count.'</td>';
echo '<td>'.$g_count.'</td>';
echo '<td>'.$fp_count.'</td>';
echo '<td>'.$nh_count.'</td>';
echo '<td>'.$total_count.'</td>';
echo '<td>'.$qRating.'%</td>';
echo '</tr>';
}

echo  '</table>'; 

mysql_close(); 

And here is the output...

Name Favorite Very Good Good Fair Never Heard Total Count QRating
Adam Sandler 1 2 3 4 5 10 10%
Adam Sandler 5 4 3 2 1 14 35.71%

As you can see, I get Adam Sandler listed individually, I want tp get the total of all votes then it will display the results, name by name. This gives me vote, by vote.

Am I clear on the results or still unclear?

Thanks,

Don

[dmacman1962]

Hi all,

I resolved this, here is the final code that works, in case it can help anybody else.

echo '<table><tr>
<td width="150">Name</td>
<td width="100">Favorite</td>
<td width="100">Very Good</td>
<td width="100">Good</td>
<td width="100">Fair</td>
<td width="100">Never Heard</td>
<td width="100">Total Count</td>
<td width="100">QRating</td></tr>';

$query= "SELECT QRatingNames.Name, QRatingNames.ID, SUM(fav_count) as total_fav_count, SUM(vg_count) as total_vg_count, SUM(g_count) as total_g_count, SUM(fp_count) as total_fp_count, SUM(nh_count) as total_nh_count FROM QRatingVotes2, QRatingNames WHERE QRatingVotes2.NamesID=QRatingNames.ID group by QRatingNames.ID";
    $result=mysql_query($query);    

        while($celeb = mysql_fetch_array($result)) {
            $name = $celeb['Name'];
            $fav_count = $celeb['total_fav_count'];
            $vg_count = $celeb['total_vg_count'];
            $g_count = $celeb['total_g_count'];
            $fp_count = $celeb['total_fp_count'];
            $nh_count = $celeb['total_nh_count'];
            $total_count = $fav_count + $vg_count + $g_count + $fp_count;
            $qRating = round((($fav_count/$total_count)*100),2);

        echo '<tr>';
        echo '<td>'.$name.'</td>';
        echo '<td>'.$fav_count.'</td>';
        echo '<td>'.$vg_count.'</td>';
        echo '<td>'.$g_count.'</td>';
        echo '<td>'.$fp_count.'</td>';
        echo '<td>'.$nh_count.'</td>';
        echo '<td>'.$total_count.'</td>';
        echo '<td>'.$qRating.'%</td>';
        echo '</tr>';
            }
echo  '</table>';

mysql_close();

Thanks to everyone who helped,

Don