While Loop Database Issue

[leequalls]

The code below is only outputing the data from the first insert in the database can anyone please help. Note that in the $pa['id'] does change but the $name $email $location stay the same on each line.

 

The column names in proposal_answers is q1 q2 q3 its basicly 'q' and the matching id from proposal_questions.

 

echo"<hr width='90%'><p><table width='90%'><tr><th>Name</th><th>Email</th><th>Location</th><th>Status</th></tr>";         

$pqresult = mysql_query("SELECT * FROM proposal_questions");
$paresult = mysql_query("SELECT * FROM proposal_answers");
while($pa = mysql_fetch_assoc($paresult)) {
     $state = NULL;
     while($pq = mysql_fetch_assoc($pqresult)) {
           $q = 'q'.$pq['id'];
           if(stristr($pq['question'], 'Email') != FALSE) { $email = $pa[$q]; }
           if(stristr($pq['question'], 'Name') != FALSE) { $name = $pa[$q]; }
           if(stristr($pq['question'], 'City') != FALSE) { $location = $pa[$q]; }
           if(stristr($pq['question'], 'State') != FALSE and !$state) { $location .= " ".$pa[$q]; $state = 'true'; }
           if(stristr($pq['question'], 'Country') != FALSE) { $location .= " ".$pa[$q]; }
                                                                          }

    $table = "<tr><td><a href='?id=".$pa['id']."'>".$name."</a></td><td>".$email."</td><td>".trim($location)."</td><td>".$pa['status']."</td></tr>";
    if($pa['status'] == "pending") { $pending .= $table; }
      elseif($pa['status'] == "approved") { $approved .= $table; }
        elseif($pa['status'] == "denied") { $denied .= $table; }
                                                                     }


echo $pending."".$approved."".$denied."</table>";         
}

 

The Output from the above code:

 

[font=monospace][size=1]<table width='90%'>[/size][/font][font=monospace][size=1]<tr>[/size][/font][font=monospace][size=1]<th>[/size][/font][color=#000000][font=monospace][size=1]Name[/size][/font][/color][font=monospace][size=1]</th>[/size][/font][font=monospace][size=1]<th>[/size][/font][color=#000000][font=monospace][size=1]Email[/size][/font][/color][font=monospace][size=1]</th>[/size][/font][font=monospace][size=1]<th>[/size][/font][color=#000000][font=monospace][size=1]Location[/size][/font][/color][font=monospace][size=1]</th>[/size][/font][font=monospace][size=1]<th>[/size][/font][color=#000000][font=monospace][size=1]Status[/size][/font][/color][font=monospace][size=1]</th>[/size][/font][font=monospace][size=1]</tr>[/size][/font]
[font=monospace][size=1]<tr>[/size][/font][font=monospace][size=1]<td>[/size][/font][font=monospace][size=1]<a href='[url="https://party934.com/en/djpanel/admin/proposal.php?id=6"]?id=6[/url]'>[/size][/font][color=#000000][font=monospace][size=1]Scott[/size][/font][/color][font=monospace][size=1]</a>[/size][/font][font=monospace][size=1]</td>[/size][/font][font=monospace][size=1]<td>[/size][/font][color=#000000][font=monospace][size=1][email protected][/size][/font][/color][font=monospace][size=1]</td>[/size][/font][font=monospace][size=1]<td>[/size][/font][color=#000000][font=monospace][size=1]Channahon IL US[/size][/font][/color][font=monospace][size=1]</td>[/size][/font][font=monospace][size=1]<td>[/size][/font][color=#000000][font=monospace][size=1]pending[/size][/font][/color][font=monospace][size=1]</td>[/size][/font][font=monospace][size=1]</tr>[/size][/font]
[font=monospace][size=1]<tr>[/size][/font][font=monospace][size=1]<td>[/size][/font][font=monospace][size=1]<a href='[url="https://party934.com/en/djpanel/admin/proposal.php?id=4"]?id=4[/url]'>[/size][/font][color=#000000][font=monospace][size=1]Scott[/size][/font][/color][font=monospace][size=1]</a>[/size][/font][font=monospace][size=1]</td>[/size][/font][font=monospace][size=1]<td>[/size][/font][color=#000000][font=monospace][size=1][email protected][/size][/font][/color][font=monospace][size=1]</td>[/size][/font][font=monospace][size=1]<td>[/size][/font][color=#000000][font=monospace][size=1]Channahon IL US[/size][/font][/color][font=monospace][size=1]</td>[/size][/font][font=monospace][size=1]<td>[/size][/font][color=#000000][font=monospace][size=1]pending[/size][/font][/color][font=monospace][size=1]</td>[/size][/font][font=monospace][size=1]</tr>[/size][/font][font=monospace][size=1]</table>[/size][/font]

[Beeeeney]

Fantastic mate!

 

So what's your question?

[kicken]

You're loop for

while($pq = mysql_fetch_assoc($pqresult))

 

is only going to run through the results on the first iteration of the outer loop. For each additional iteration that inner loop will be skipped because mysql_fetch_assoc will return false (all the rows got consumed already in the first iteration). For that code to work as you're expecting, you'd have to repeat the select query on each loop iteration, which is a terrible thing to do and will cause major performance problems.

 

The better way to handle this would be to use a JOIN on your tables and only run a single query that gives you all the data you need. If you post your table structures someone can probably help you come up with the proper query.