Appending MySQL Results Together in a PHP Loop

Question

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!

eggyal · Accepted Answer · 2012-10-14 00:30:18Z

4

Learn about SQL joins:

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

edited Oct 14, 2012 at 0:30

answered Oct 14, 2012 at 0:25

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JToland Over a year ago

I do struggle with more advanced Joins, so I might be wrong, but I don't think I can use a Join in my case. I oversimplified my example too much above. In the query in the while loop, I'm actually matching a single value, state, to a possible value within a comma separated list. So the 'state' column is actually a list of states and I want to match for all rows where $state is in that list. I've modified my example above.

eggyal Over a year ago

@JToland: I'd strongly suggest that you normalise your schema. But if that's not possible, you could replace USING(state) above with ON FIND_IN_SET(states.state,locations.state)

JToland Over a year ago

I know normalising the data would certainly be the best way to go about this long term, but unfortunately that's not something I can easily change (or at least not quickly enough to suit my current need). I tried the above query with the ON FIND_IN_SET and that's working beautifully! It seems to give me exactly what I need and is much easier than how I was trying to do it before, thanks a lot @eggyal!

NoobishPro · Accepted Answer · 2012-10-14 00:30:23Z

0

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)

answered Oct 14, 2012 at 0:30

NoobishPro

2,5591 gold badge14 silver badges25 bronze badges

Collectives™ on Stack Overflow

Appending MySQL Results Together in a PHP Loop

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related