Trying to use only one mysql_query...

[jafa4500]

Here’s what I’ve got:

[php]$result_ne = mysql_query(“SELECT * FROM clients WHERE market=“North East” ORDER BY market, id”);
echo “

North East

”;
while ($row_ne = mysql_fetch_array($result_ne)) {
echo $row_ne[“client”] . " - " . $row_ne[“phone”] . "

";
}

$result_mw = mysql_query(“SELECT * FROM clients WHERE market=“Mid West” ORDER BY market, id”);
echo “

Mid West

”;
while ($row_mw = mysql_fetch_array($result_mw)) {
echo $row_mw[“client”] . " - " . $row_mw[“phone”] . "

";
}
[/php]

In reality I really have many more ‘markets’ - but I don’t want to do a mysql_query for every one. What I want to do is have one mysql_query that gets all the data.

[php]$result = mysql_query(“SELECT * FROM clients ORDER BY market, id”);[/php]

Now - here’s my problem: How do I separate the data into arrays per market?

Please help! This has been driving me nuts!

[RaythXC]

That one single query is fine, all you have to do then is use if statements to sort out the different markets. You wouldn’t even have to ORDER BY market to do so

[jafa4500]

Thanks! It works! Crap - I was definitely overthinking this… or just thinking wrong. This is what I did:

[php]
$result = mysql_query(“SELECT * FROM clients ORDER BY market, id”);

echo “

North East

”;
while ($row = mysql_fetch_array($result)) {
if ($row[“market”] == “North East”)
echo $row[“id”] . " - " . $row[“client”] . "

";
}

mysql_data_seek($result, 0);

echo “

Mid West

”;
while ($row = mysql_fetch_array($result)) {
if ($row[“market”] == “Mid West”)
echo $row[“id”] . " - " . $row[“client”] . "

";
}
[/php]

I had to use the line mysql_data_seek($result, 0); between the while statements to start the while loop from the top of the array again.

If anyone has any better or more efficient ways to do this, I’d love to know.

[smokeys_13]

Try the following:

[php]$markets = array();
while(false !== ($row = mysql_fetch_array($result)))
{
if(!isset($markets[$row[‘market’]]))
{
$markets[$row[‘market’]] = array();
}
$markets[$row[‘market’]][] = $row[“id”] . " - " . $row[“client”];
}

foreach($markets as $name => $info)
{
echo ‘

’.$name.’

’;
echo implode(’
’,$info);
}[/php]

[jafa4500]

Very Nice, Sir!

That’s exactly what I was looking for. It’s short. It’s efficient. And it’s a great way to break apart the array into sections.

I don’t know exactly what it means, or how it’s doing it. But I’m definitely going to figure it out from your example.

[smokeys_13]

Glad to have helped!

If you can’t figure out what it’s doing or how it works, and you would like an explanation or a commented version of that code just let me know.