Looping through 2 query results

Suppose I have results from 2 queries each to a different table:

$result1 = (mysql_query($query1);

and

$result2 = (mysql_query($query2);

And I want to loop through both of them in order to put some values
from each in a table.

With one, I would do:

while ($row1 = mysql_fetch_array ($result1)) { etc.

etc.

Suppose I want the next cell to be:

//associated with $result2

How do I construct the "while" conditional to loop through both
queries????

The results are linked by a common column (person_id).

Thanks,

--Kenoili

--Kenoli

Re: Looping through 2 query results

Kenoli wrote:

> Suppose I have results from 2 queries each to a different table:
>
> $result1 =3D (mysql_query($query1);
>
> and
>
> $result2 =3D (mysql_query($query2);
>
> And I want to loop through both of them in order to put some values
> from each in a table.
>
> With one, I would do:
>
> while ($row1 =3D mysql_fetch_array ($result1)) { etc.
>
> etc.
>
> Suppose I want the next cell to be:
>
> //associated with $result2
>
> How do I construct the "while" conditional to loop through both
> queries????
>
> The results are linked by a common column (person_id).

Nesting while-loops is pretty simple. In 99% of the cases someone asks =

this, it's far simpler and efficient to write a query that holds the =

desired results of the 2 though. Could you tell us more about the tables=
=

and the exact query?
-- =

Rik Wasmus

Re: Looping through 2 query results

On 7 Feb, 21:58, "Kenoli" wrote:
> Suppose I have results from 2 queries each to a different table:
>
> $result1 = (mysql_query($query1);
>
> and
>
> $result2 = (mysql_query($query2);
>
> And I want to loop through both of them in order to put some values
> from each in a table.
>
> With one, I would do:
>
> while ($row1 = mysql_fetch_array ($result1)) { etc.
>
> etc.
>
> Suppose I want the next cell to be:
>
> //associated with $result2
>
> How do I construct the "while" conditional to loop through both
> queries????
>
> The results are linked by a common column (person_id).
>
> Thanks,
>
> --Kenoili
>
> --Kenoli

If the results are linked by a common column then query using a JOIN
between the tables thus:

SELECT *
FROM table1
JOIN table2 USING(person_id)
WHERE .....

Then all your results are in single rows based on person_id

Re: Looping through 2 query results

On Feb 8, 2:00 am, "Captain Paralytic" wrote:
>
> If the results are linked by a common column then query using a JOIN
> between the tables thus:
>
> SELECT *
> FROM table1
> JOIN table2 USING(person_id)
> WHERE .....
>
> Then all your results are in single rows based on person_id

This sounds exactly what I would like to do but haven't been able to
get to work. Here is the query based on your format applied to my
tables with the php code:

$query = "SELECT * FROM tbl_person JOIN tbl_contact_info USING
(person_id) WHERE person_id = '5' OR person_id = '6';";

$result = mysql_query ($query);

if (!$result) {echo "

You screwed up!";} else {echo "

>You succeeded!

";}

The result is: "You screwed up!"

What am I not getting????

Thanks,

--Kenoli

Re: Looping through 2 query results

Kenoli wrote:

> On Feb 8, 2:00 am, "Captain Paralytic" wrote:=

>>
>> If the results are linked by a common column then query using a JOIN
>> between the tables thus:
>>
>> SELECT *
>> FROM table1
>> JOIN table2 USING(person_id)
>> WHERE .....
>>
>> Then all your results are in single rows based on person_id
>
> This sounds exactly what I would like to do but haven't been able to
> get to work. Here is the query based on your format applied to my
> tables with the php code:
>
> $query =3D "SELECT * FROM tbl_person JOIN tbl_contact_info USING
> (person_id) WHERE person_id =3D '5' OR person_id =3D '6';";
>
> $result =3D mysql_query ($query);
>
> if (!$result) {echo "

You screwed up!";} else {echo "
/
>> You succeeded!

";}

Please echo mysql_error(), it's much more informative then 'You screwed =
=

up!' ;P


I always like to:
1. Name every desired field explicitly.
2. Use explicit joins.

So that would mean:

SELECT
a.`field1`, a.`field2`, b.`field3`
FROM `tbl_person` a
JOIN `tbl_contact_info` b
ON a.`person_id` =3D b.`person_id`
WHERE `person_id` IN (5,6)

-- =

Rik Wasmus

Re: Looping through 2 query results

Kenoli wrote:
> On Feb 8, 2:00 am, "Captain Paralytic" wrote:
>>
>> If the results are linked by a common column then query using a JOIN
>> between the tables thus:
>>
>> SELECT *
>> FROM table1
>> JOIN table2 USING(person_id)
>> WHERE .....
>>
>> Then all your results are in single rows based on person_id
>
> This sounds exactly what I would like to do but haven't been able to
> get to work. Here is the query based on your format applied to my
> tables with the php code:
>
> $query = "SELECT * FROM tbl_person JOIN tbl_contact_info USING
> (person_id) WHERE person_id = '5' OR person_id = '6';";
>
> $result = mysql_query ($query);
>
> if (!$result) {echo "

You screwed up!";} else {echo
> "

>> You succeeded!

";}
>
> The result is: "You screwed up!"
>
> What am I not getting????
>
> Thanks,
>
> --Kenoli

Is person_id a string or a number?

Re: Looping through 2 query results

On Feb 8, 12:57 pm, "Paul Lautman"
wrote:

> Is person_id a string or a number?

It's a number.

Re: Looping through 2 query results

On 9 Feb, 05:19, "Kenoli" wrote:
> On Feb 8, 12:57 pm, "Paul Lautman"
> wrote:
>
> > Is person_id a string or a number?
>
> It's a number.

Then the reason that your query is failing is most likely that you
have put quotes arund the 5 and 6, making them into strings and are
trying to compare them with person_id which is a number.

Re: Looping through 2 query results

Captain Paralytic wrote:
> On 9 Feb, 05:19, "Kenoli" wrote:
>> On Feb 8, 12:57 pm, "Paul Lautman"
>> wrote:
>>
>>> Is person_id a string or a number?
>> It's a number.
>
> Then the reason that your query is failing is most likely that you
> have put quotes arund the 5 and 6, making them into strings and are
> trying to compare them with person_id which is a number.

The single quotes around the values has no ill effect, there is something else
that is wrong, and Kenoli should output mysql_error() to see what went wrong.

--

//Aho

Re: Looping through 2 query results

On 9 Feb, 10:54, "J.O. Aho" wrote:
> Captain Paralytic wrote:
> > On 9 Feb, 05:19, "Kenoli" wrote:
> >> On Feb 8, 12:57 pm, "Paul Lautman"
> >> wrote:
>
> >>> Is person_id a string or a number?
> >> It's a number.
>
> > Then the reason that your query is failing is most likely that you
> > have put quotes arund the 5 and 6, making them into strings and are
> > trying to compare them with person_id which is a number.
>
> The single quotes around the values has no ill effect, there is something else
> that is wrong, and Kenoli should output mysql_error() to see what went wrong.
>
> --
>
> //Aho

good point

Re: Looping through 2 query results

On Feb 9, 2:54 am, "J.O. Aho" wrote:
> Captain Paralytic wrote:
> > On 9 Feb, 05:19, "Kenoli" wrote:
> >> On Feb 8, 12:57 pm, "Paul Lautman"
> >> wrote:
>
> >>> Is person_id a string or a number?
> >> It's a number.
>
> > Then the reason that your query is failing is most likely that you
> > have put quotes arund the 5 and 6, making them into strings and are
> > trying to compare them with person_id which is a number.
>
> The single quotes around the values has no ill effect, there is something else
> that is wrong, and Kenoli should output mysql_error() to see what went wrong.
>
> --
>
> //Aho

I get the error:

MySQL Error: Column 'person_id' in where clause is ambiguous

--Kenoli

Re: Looping through 2 query results

Kenoli wrote:

> I get the error:
> MySQL Error: Column 'person_id' in where clause is ambiguous

This happens sometimes when you have tables with the same column names, you
have to specify which tables person_id you are going to use

In your case it would be tbl_person.person_id or tbl_contact_info.person_id

IMHO it's always good to specify a columns table when using joins, you get a
lot less of these errors that way and you easilly know from which table a
column is when you look at the query.


$query = "SELECT * FROM tbl_person JOIN tbl_contact_info USING
(person_id) WHERE person_id = '5' OR person_id = '6';";

would become:

$query = "SELECT * FROM tbl_person JOIN tbl_contact_info ON
(tbl_person.person_id=tbl_contact_info.person_id) WHERE tbl_person.person_id
= '5' OR tbl_person.person_id = '6'";

Yes, it will be a bit longer to type, but will work better.

--

//Aho

Re: Looping through 2 query results

On Feb 9, 7:03 am, "J.O. Aho" wrote:
> Kenoli wrote:
> > I get the error:
> > MySQL Error: Column 'person_id' in where clause is ambiguous
>
> This happens sometimes when you have tables with the same column names, you
> have to specify which tables person_id you are going to use
>
> In your case it would be tbl_person.person_id or tbl_contact_info.person_id
>
> IMHO it's always good to specify a columns table when using joins, you get a
> lot less of these errors that way and you easilly know from which table a
> column is when you look at the query.
>
> $query = "SELECT * FROM tbl_person JOIN tbl_contact_info USING
> (person_id) WHERE person_id = '5' OR person_id = '6';";
>
> would become:
>
> $query = "SELECT * FROM tbl_person JOIN tbl_contact_info ON
> (tbl_person.person_id=tbl_contact_info.person_id) WHERE tbl_person.person_id
> = '5' OR tbl_person.person_id = '6'";
>
> Yes, it will be a bit longer to type, but will work better.
>
> --
>
> //Aho

Great! It works I'm curious that it works to point it only at the
person_id in one table, since the match actually applies to both
tables. Would it do the same thing if I pointed it at the other
table?

At any rate, it's a great shortcut.

Does anyone know a good online tutorial or other resource on joins.
The MySQL manual might as well be written in Greek for all I've been
able to make of it. I don't even see the join described here in the
manual.

For some reason, the concept in general has been hard for me to get,
though this example is straightforward and intuitive and exactly what
I was looking for.

I also tried out UNION, with no sucess, though I don't think it was
what I wanted anyway.

--Kenoli

Thanks,

Re: Looping through 2 query results

On 10 Feb, 04:11, "Kenoli" wrote:
> On Feb 9, 7:03 am, "J.O. Aho" wrote:
>
>
>
>
>
> > Kenoli wrote:
> > > I get the error:
> > > MySQL Error: Column 'person_id' in where clause is ambiguous
>
> > This happens sometimes when you have tables with the same column names, you
> > have to specify which tables person_id you are going to use
>
> > In your case it would be tbl_person.person_id or tbl_contact_info.person_id
>
> > IMHO it's always good to specify a columns table when using joins, you get a
> > lot less of these errors that way and you easilly know from which table a
> > column is when you look at the query.
>
> > $query = "SELECT * FROM tbl_person JOIN tbl_contact_info USING
> > (person_id) WHERE person_id = '5' OR person_id = '6';";
>
> > would become:
>
> > $query = "SELECT * FROM tbl_person JOIN tbl_contact_info ON
> > (tbl_person.person_id=tbl_contact_info.person_id) WHERE tbl_person.person_id
> > = '5' OR tbl_person.person_id = '6'";
>
> > Yes, it will be a bit longer to type, but will work better.
>
> > --
>
> > //Aho
>
> Great! It works I'm curious that it works to point it only at the
> person_id in one table, since the match actually applies to both
> tables. Would it do the same thing if I pointed it at the other
> table?
>
> At any rate, it's a great shortcut.
>
> Does anyone know a good online tutorial or other resource on joins.
> The MySQL manual might as well be written in Greek for all I've been
> able to make of it. I don't even see the join described here in the
> manual.
>
> For some reason, the concept in general has been hard for me to get,
> though this example is straightforward and intuitive and exactly what
> I was looking for.
>
> I also tried out UNION, with no sucess, though I don't think it was
> what I wanted anyway.
>
> --Kenoli
>
> Thanks,- Hide quoted text -
>
> - Show quoted text -

Your match conditions say
"tbl_person.person_id=tbl_contact_info.person_id" or the shortcut of
USING(person_id).
This condition says that the values of person_id in both tables must
be the same. So it doesnt' matter which one you "point to", as the
match condition will make it "point to" both of them.