CODEWITHSUNDEEP
phpmysqlwhile-loop
JToland
JToland

Reputation: 3710

Appending MySQL Results Together in a PHP Loop

Using PHP, for-each row in a MySQL query's result set, I need to execute another MySQL query. So far, I've got

$result = mysql_query("SELECT id, state FROM states WHERE region='given_region'")
or die(mysql_error());

while($row = mysql_fetch_row($result))
{
    $state = $row['state'];
    $r = mysql_query("SELECT * FROM locations WHERE state REGEXP '(^|,)".$state."($|,)'");
}

Now I need to append all of the results $r into ONE result set that I can then iterate through with a standard while-loop, however, my very limited PHP skills are leaving me at a loss getting code to do that.

Can anyone provide any insight into how I'd go about appending all the results into one set within the given while loop?

Thanks!

Upvotes: 0

Views: 1110

Answers (2)

NoobishPro
NoobishPro

Reputation: 2549

You could try and use foreach instead of while. I'm sorry but i'm not really getting the question.

If you use a foreach you can generate entire tables with it if you'd like to. let me give you an example:

<?php
Foreach($result as $output) : ?>
<div id="results">
<table id="generated table">
<tr>
<td><?php echo $output->id ?></td><td> <?php echo $output->state ?></td>
</tr>
</table>
<?php endforeach ?>
 </div>

Mind you, if you use foreaches you can just get the ENTIRE table instead of using the query to select a few columns. Then you output the columns data through the foreach (by using the $output->columnname command)

Upvotes: 0

eggyal
eggyal

Reputation: 125875

Learn about SQL joins:

SELECT states.id, states.state, locations.*
FROM   states LEFT JOIN locations USING(state)
WHERE  states.region = 'given_region'

Upvotes: 4

Related Questions