using data from previous loop

[searls03]

This is kindof hard to explain, but what I am trying to do is query one database table getting an ID from it, and then querying another table using that ID, and the second table may have many references with that ID using TB1_id.

 

Table one:

id:1

Name:bob

 

Table two:

id:1

TB1_id:1

name:frank

id:2

TB1_id:1

name:franky

 

so when the query runs, it would display in a nice little table like this

 

TB1 Name TB2name

Bob frank, franky,

 

 

 

 

here is what I have experimented with....

<?php 


$result = mysql_query("SELECT * FROM registrants where event_id='".$_GET['id']."'");
while($row = mysql_fetch_array($result))
 {
$id = $row['id'];
$phone = $row['phone'];
$email = $row['email'];
$name = $row['name'];
$paid = $row['invoiceid'];
?>
<tr>
   <td><?php echo $name;?></td>
   <td><?php echo $phone; ?></td>
   <td><?php echo $email; ?></td>
   <td><?php if($paid ==''){echo 'no';} if($paid !==''){echo $paid; }?></td> <td>
<?php


echo $name;
$result = mysql_query("SELECT * FROM participants where registrant_id='".$id."'");
while($row = mysql_fetch_array($result))
 {
$part = $row['participant'];
?>   <?php echo $part; ?>,



 <?php
echo $part;
 }?></td>
</tr><?php


 }
 ?>
</table>

Sorry it is so (so,so,so,so,so) ugly....hopefully someone can help me make some sense of it.

[Jessica]

Never run queries in loops.

Use a join.

[Psycho]

This might not create the output exactly as you want it, but it should get you started

<?php

function registrantTable($regDataAry)
{
   //Define paid status and make participant list comma separated
   $paid = (!empty($regDataAry['invoiceid'])) ? $regDataAry['invoiceid'] : 'no';
   $participants = implode(', ', $regDataAry['participants']);
   //Create table for the registrant info
   $output  = "<table>\n";
   $output .= "  <tr>\n";
   $output .= "    <td>{$regDataAry['name']}</td>\n";
   $output .= "    <td>{$regDataAry['phone']}</td>\n";
   $output .= "    <td>{$regDataAry['email']}</td>\n";
   $output .= "    <td>{$paid}</td>\n";
   $output .= "  </tr>\n";
   $output  = "  <tr>\n";
   $output .= "    <td colspan='4'>{$participants}</td>\n";
   $output .= "  </tr>\n";
   $output .= "</table>\n";
   return $output;
}

$query = "SELECT r.id, r.name, r.phone, r.email, r.invoiceid
			 p.participant
	  FROM registrants AS r
	  INNER JOIN participants AS p
	    ON p.registrant_id = r.id
	  WHERE event_id='{$_GET['id']}'
	  ORDER BY r.id";
$result = mysql_query($query);

if(!$result)
{
   echo "Query failed";
}
elseif(!mysql_num_rows($result))
{
   echo "There were no matching records.";
}
else
{
   $currentRegID = false; //Flag to track when reg id changes
   $registrantData = array(); //Temp array to hold set of data for a registrant

   while($row = mysql_fetch_assoc($result))
   {
    //Check if registrant ID has changed
    if($currentRegID !== $row['id'])
    {
	    //If not first record display records for previous registrant
	    if(count($registrantData))
	    {
		    echo registrantTable($registrantData);
	    }
	    //Set flag for current registrant
	    $currentRegID = $row['id'];
	    //Set "base" data for the current registrant
	    $registrantData['name'] = $row['name'];
	    $registrantData['phone'] = $row['phone'];
	    $registrantData['email'] = $row['email'];
	    $registrantData['invoiceid'] = $row['invoiceid'];
    }
    //Append particant to current registrant data
    $registrantData['participants'][] = $row['participant'];
   }
   //Display records for last registrant
   echo registrantTable($registrantData);
}

Edited January 4, 2013 by Psycho

[searls03]

thanks!. biggest thing now is that it is only showing participant results, it is not showing any data from the registrant table. here is what I have

 

<?php


function registrantTable($regDataAry)
{
       //Define paid status and make participant list comma separated
       $paid = (!empty($regDataAry['invoiceid'])) ? $regDataAry['invoiceid'] : 'no';
       $participants = implode(', ', $regDataAry['participants']);
       //Create table for the registrant info
       $output  = "<table>\n";
       $output .= "  <tr>\n";
       $output .= "    <td>{$regDataAry['name']}</td>\n";
       $output .= "    <td>{$regDataAry['phone']}</td>\n";
       $output .= "    <td>{$regDataAry['email']}</td>\n";
       $output .= "    <td>{$paid}</td>\n";
       $output .= "  </tr>\n";
       $output  = "  <tr>\n";
       $output .= "    <td colspan='4'>{$participants}</td>\n";
       $output .= "  </tr>\n";
       $output .= "</table>\n";
       return $output;
}


$query = "SELECT r.id, r.name, r.phone, r.email, r.invoiceid,
                                p.participant
                 FROM registrants AS r
                 INNER JOIN participants AS p
                       ON p.registrant_id = r.id
                 WHERE event_id='{$_GET['id']}'
                 ORDER BY r.id";
$result = mysql_query($query);


if(!$result)
{
       echo "Query failed";
}
elseif(!mysql_num_rows($result))
{
       echo "There were no matching records.";
}
else
{
       $currentRegID = false; //Flag to track when reg id changes
       $registrantData = array(); //Temp array to hold set of data for a registrant

       while($row = mysql_fetch_assoc($result))
       {
               //Check if registrant ID has changed
               if($currentRegID !== $row['id'])
               {
                       //If not first record display records for previous registrant
                       if(count($registrantData))
                       {
                               echo registrantTable($registrantData);
                       }
                       //Set flag for current registrant
                       $currentRegID = $row['id'];
                       //Set "base" data for the current registrant
                       $registrantData['name'] = $row['name'];
                       $registrantData['phone'] = $row['phone'];
                       $registrantData['email'] = $row['email'];
                       $registrantData['invoiceid'] = $row['invoiceid'];
               }
               //Append particant to current registrant data
               $registrantData['participants'][] = $row['participant'];
       }
       //Display records for last registrant
       echo registrantTable($registrantData);
}?>

 

basically what you gave me. but how do I display both table results? I have never worked with inner join before

[searls03]

it also loops through data for each registrant. so if it has two registrants, it shows the first registrant's participants with the second....it builds up.....1st displays 1st. 2nd displays 1st and 2nd, etc. 5, 4, 3, 2, 1,Bob,||||||||||||||5, 4, 3, 2, 1, Bob, me, You

similar to that kindof

[Barand]

From your original post, this would give the output you want

 

SELECT t1.name, GROUP_CONCAT(t2.name SEPARATOR ', ') as names
FROM table_1 t1
INNER JOIN table_2 t2 ON t1.id = t2.TB1_id
GROUP BY t1.name

 

You can build on that.

Edited January 4, 2013 by Barand

[Psycho]

I don't see why it would be showing only the participant data and not the registrant data. But, I'm not going to create a database to test it. Learn to debug code.

 

But, I do see why it is appending the participants for each registrant. On the condition that tests for a a different $currentRegID the code needs to reset the $registrantData to remove the previous data. So, right before the line that sets the $currentRegID flan in the if condition you could put:

$registrantData = array();

 

 

Having said that however, Barand's query would be much more efficient. Once you see the results from that you should be able to see how to implement the output.

[searls03]

Ok, I am still having an issue, but I think I figured out why the registrants are not displaying, its because the output is not displaying

 

 

$output = " <tr>\n";

$output .= " <td>{$regDataAry['name']}</td>\n";

$output .= " <td>{$regDataAry['phone']}</td>\n";

$output .= " <td>{$regDataAry['email']}</td>\n";

$output .= " <td>{$paid}</td>\n";

$output .= " </tr>\n";

$output = " <tr>\n";

$output .= " <td colspan='4'>{$participants}</td>\n";

$output .= " </tr>\n";

return $output;

 

this is what the view source returns:

<tr> <td colspan='4'>5, 4, 3, 2, 1</td> </tr> <tr> <td colspan='4'>Bob, Aaron</td> </tr> <tr> <td colspan='4'>me, You, Aaron</td> </tr>

 

 

meaning only the participant output is displaying....can anyone help me out?

Edited January 7, 2013 by searls03

[searls03]

got it, one little "." was missing.