combining two queries, but leave them separate (mySQL)

[frizzle]

Hi,

I know this might sound strange but i think(/hope)
it's quite simple:

I'm running 2 queries in a mysql DB, first one returns 20 results.

Now how can i echo results from the second query in the 1st query's
result,
like:

do{

echo 'foo'.$second_result['thingy'];

}while($first_result = $first_query);

I hope this makes sense...

Greetings frizzle

[frizzle]

* please, anyone ? *

[NC]

frizzle wrote:

I know this might sound strange but i think(/hope)
it's quite simple:

I'm running 2 queries in a mysql DB, first one returns
20 results.

Now how can i echo results from the second query in the
1st query's result

What exactly are you trying to do? You can output all
results of the second query next to each result of the
first query, or you can pair records returned by the
first query with those returned by the second query.
Which are you trying to accomplish? Or is it something
else?

Also, you can attempt to combine the two queries into
one using JOIN or UNION.

Cheers,
NC

[frizzle]

I need to pair the queries, but i want to leave them separate (as i
said). I've told my problem earlier on
http://groups-beta.google.com/group/...141a9a168bbcd1

But they told me to redesign my tables. I know that this can be solved
differently, without redesigning my tables and combining the queries...

I hope this clears things out a little bit more...

Thanks in advance...

[NC]

frizzle wrote:

I need to pair the queries, but i want to leave them separate (as i
said). I've told my problem earlier on
http://groups-beta.google.com/group/...thread/thread/ 6cf997d613e2a595/ce141a9a168bbcd1#ce141a9a168bbcd1

But they told me to redesign my tables.
It wasn't "they". It was I. :)
I know that this can be solved differently, without redesigning my tables and combining the queries...

It can, but performance is going to suck. What you are looking for is
this:

$result1 = mysql_query('SELECT id, name, buddies_id FROM employees');
while ($record1 = mysql_fetch_assoc($result1)) {
echo $record1['name'], "'s buddies are: \r\n";
$result2 = mysql_query('SELECT id, name FROM employees WHERE id IN ('
..
str_replace('-', ',', $record1['buddies_id']) . ')');
while ($record2 = mysql_fetch_assoc($result2)) {
echo $record2['name'], "\r\n";
}
}

What you don't seem to realize is that a query takes a certain time to
prepare for execution. Assuming that time is 0.1 seconds (a not
particularly scientific rule of thumb), by asking MySQL to do 21
queries where one would suffice, you are asking for 2 extra seconds
of preparation time, even before MuSQL actually executes any of the
queries. If you redesigned your tables, the seemingly complicated
query would be resolved in way under one second.

Cheers,
NC

[frizzle]

lol it was you, could've checked... ;)

Anyway, i understand the method you supplied this time, and i know
it's going to take a lot of time. That's why a want to avoid this
situation.

My current situation is sort of like this:
SELECT id, name, function, buddies FROM employees LIMIT 20

do{

$output .= $first_query['name'];
$output .= " - ".$first_query['function']." - ";
$output .= "<br>&nbsp&nbsp&nbsp";
$output .= "buddies:"
$output .= ; //place for buddies names

}while(employees are returned);

returned buddies form 1st query are put in an unique, sorted array, and
then the second query is runned:
SELECT id, name FORM emplyees WHERE id IN($array)

Then i need to mix up the second query's result mixed with $output..>

Hope this clears up the situation a little bit. If not, please ask,
because i'd really like this to work. :)

Thanks anyway for taking the effort again to have a look at my
problems..

Greetings!

[NC]

frizzle wrote:

Anyway, i understand the method you supplied this time, and i know
it's going to take a lot of time. That's why a want to avoid this
situation.

My current situation is sort of like this:
SELECT id, name, function, buddies FROM employees LIMIT 20

do{

$output .= $first_query['name'];
$output .= " - ".$first_query['function']." - ";
$output .= "<br>&nbsp&nbsp&nbsp";
$output .= "buddies:"
$output .= ; //place for buddies names

}while(employees are returned);

returned buddies form 1st query are put in an unique, sorted array,
and then the second query is runned:
SELECT id, name FORM emplyees WHERE id IN($array)

OK, this should solve your problem then:

$output = array();
$ids = array();
$result1 = mysql_query('SELECT id, name, buddies_id FROM employees');
while ($record1 = mysql_fetch_assoc($result1)) {
$i = $record1['id'];
$buddies = explode('-', $record1['buddies_id']);
foreach ($buddies as $buddy) {
if (!in_array($buddy, $ids)) {
$ids[] = $buddy;
}
}
$output[$i] = array('name' => $record1['name'],
'buddies' => $buddies);
}
$buddies = array();
$result2 = mysql_query('SELECT id, name FROM employees WHERE id IN (' .

implode(',', $ids) . ')');
while ($record2 = mysql_fetch_assoc($result2)) {
$i = $record2['id'];
$buddies[$i] = $record2['name'];
}
foreach ($output as $employee) {
echo $emloyee['name'], "'s buddies are:\r\n";
foreach ($employee['buddies'] as $i) {
echo $buddies[$i], "\r\n";
}
}

Cheers,
NC

[frizzle]

Wow, i fixed it! (Together with you of course :) )

Anyway, i didn't use your answer, but you gave me an idea to come to a
solution that even i was capable of understanding:

Now i'm running a str_replace for each buddy_id, and it works great! :)
Thanks for helping me out, i'm really glad i got this working!!!
:)

(Do you think str_replace is a heavy function to perform..?)