Of course, you can avoid that problem if you use a join, I guess it all comes down to code complexity vs SQL complexity. -- Josh -----Original Message----- From: Ignatius Reilly [mailto:ignatius.reilly@free.fr] Sent: Wednesday, November 06, 2002 1:38 PM To: Josh Johnson; '1LT John W. Holmes'; 'Graeme McLaren'; php-db@lists.php.net Subject: Re: mysql_fetch_array() question John is right. In fact one routinely calls two different $result(s) when coding nested loops: while( $details = mysql_fetch_array( $result ) ) { while ( $Email = mysql_fetch_array( $result2 ) ) .. } } when executing the next instance of your loop: while( $details = mysql_fetch_array( $result ) and $Email = mysql_fetch_array( $result2 ) ), each mysql_fetch_array will push both pointers (on your two result resources) by one position. if your result sets do not have the same number of rows, the shorter one will choke before the longer one is finished. therefore you will finish the loop not having called all results from the "long" resource. My 0.02 Belgian francs Ignatius ____________________________________________ ----- Original Message ----- From: "Josh Johnson" <mailing_lists@allusiveimage.com> To: "'1LT John W. Holmes'" <holmes072000@charter.net>; "'Graeme McLaren'" <mickel@ntlworld.com>; <php-db@lists.php.net> Sent: Wednesday, November 06, 2002 7:24 PM Subject: RE: mysql_fetch_array() question > :) see why I love mailing lists! :) > > It must have been their logic then, I respectfully retract everything I > said, sorry Graeme! Why do I have to work, I could spend my days > researching this stuff before I post! > > Thanks John! > -- Josh > > -----Original Message----- > From: 1LT John W. Holmes [mailto:holmes072000@charter.net] > Sent: Wednesday, November 06, 2002 1:21 PM > To: Josh Johnson; 'Graeme McLaren'; php-db@lists.php.net > Subject: Re: mysql_fetch_array() question > > That's not true. You can assign them to different variables and it works > fine. > > $r1 = mysql_query("select * from main limit 1") or die(mysql_error()); > $r2 = mysql_query("select * from apft limit 1") or die(mysql_error()); > > $row1 = mysql_fetch_array($r1); > $row2 = mysql_fetch_array($r2); > > print_r($row1); > echo "<br><hr>"; > print_r($row2); > > ---John Holmes... > > ----- Original Message ----- > From: "Josh Johnson" <mailing_lists@allusiveimage.com> > To: "'Graeme McLaren'" <mickel@ntlworld.com>; <php-db@lists.php.net> > Sent: Wednesday, November 06, 2002 1:02 PM > Subject: RE: mysql_fetch_array() question > > > > In my experience, (I haven't checked the docs just yet to figure out > > exactly why), you can only work with one result id at a time. Every > time > > you call mysql_query, it resets the result id (I think it frees the > > result id automatically so you can send another query before getting > the > > new result id), so even if you store it in two separate variables, it > > will only refrence the last id (which sounds just like what happened > in > > your initial post). I think this is limited to a given connection, but > > I'm not sure. > > > > One of the, I guess you could say, "shortcomings" of the mysql > interface > > in php is that you can only execute one query at a time, and I think > > this odd result id overwrighting that happens is due to that fact (I > > might be wrong, but I think you can execute multiple queries at a time > > with perl's DBI module). > > > > I hope that makes more sense :) > > > > -- Josh > > > > -----Original Message----- > > From: Graeme McLaren [mailto:mickel@ntlworld.com] > > Sent: Wednesday, November 06, 2002 12:17 PM > > To: php-db@lists.php.net > > Subject: Re: mysql_fetch_array() question > > > > Josh, Thank you for reply. Thank you to everyone else who replied to > my > > email also. I solved the problem shortly after posting my question, > > I've > > now got a massive SQL query which does the trick. > > > > I was interested when you replied and part of it read: "Just > > remember that you can only work with one mysql result per connection > at > > a time". Can you explain a little more about that please? I don't > > think I > > fully understand what you mean. > > > > Cheers, > > > > Graeme :) > > > > ----- Original Message ----- > > From: "Josh Johnson" <mailing_lists@allusiveimage.com> > > To: <php-db@lists.php.net> > > Sent: Wednesday, November 06, 2002 12:29 PM > > Subject: RE: mysql_fetch_array() question > > > > > > > I concur with Jason, but if restructuring is out of the question, > just > > > rearrange your queries like this: > > > > > > $query = "SELECT Name, Address FROM users"; > > > $result = mysql_query($query); > > > while($details = mysql_fetch_array($result)){ > > > echo "Name: $details[Name]"; > > > echo "Address: $details[Address]"; > > > } > > > $query2 = "SELECT EmailAddress From Members"; > > > $result2 = mysql_query($query2); > > > while($details = mysql_fetch_array($result2)){ > > > echo "Email: $Email[EmailAddress]"; > > > } > > > > > > The results won't come out at the same time, but you could use some > > > logic to combine the results into an array by a common factor. Just > > > remember that you can only work with one mysql result per connection > > at > > > a time. You *may* (untested!) be able to accomplish what you want to > > do > > > with two separate connections, but again, this is seriously > overkill. > > :) > > > > > > I'd definitely recommend restructuring your talbes as Jason > suggested. > > > -- Josh > > > > > > > > > -----Original Message----- > > > From: Jason Wong [mailto:phplist@gremlins.com.hk] > > > Sent: Wednesday, November 06, 2002 5:24 AM > > > To: php-db@lists.php.net > > > Subject: Re: mysql_fetch_array() question > > > > > > On Tuesday 05 November 2002 05:47, Graeme McLaren wrote: > > > > Hi, Anyone know how I can use two mysql_fetch_array() functions > > > similar > > > > to the code below? I've tried a few different ways but I keep > > getting > > > > Resource ID #4. I need to do this to retrieve an email address > from > > > one > > > > table and retrieve details from another. > > > > > > > > Cheers for any tips - I'm stumped with this one, > > > > > > > > Graeme :) > > > > > > > > $query = "SELECT Name, Address FROM users"; > > > > $query2 = "SELECT EmailAddress From Members"; > > > > > > > > $result = mysql_query($query); > > > > $result2 = mysql_query($query2); > > > > > > > > while($details = mysql_fetch_array($result) and $Email = > > > > mysql_fetch_array($result2)) > > > > { > > > > echo "Name: $details[Name]"; > > > > echo "Address: $details[Address]"; > > > > echo "Email: $Email[EmailAddress]"; > > > > } > > > > > > Unless I've missed something you're going about this the wrong way. > > For > > > what > > > you want to do, you should (in general) be able to accomplish it > using > > > just a > > > single query. > > > > > > What fields do the tables 'users' and 'Members' contain? There > should > > be > > > a > > > field there (like eg. userid) which links the two together. If there > > > isn't > > > one then you should seriously restructure your tables so that there > is > > > one. > > > > > > -- > > > Jason Wong -> Gremlins Associates -> www.gremlins.com.hk > > > Open Source Software Systems Integrators > > > * Web Design & Hosting * Internet & Intranet Applications > Development > > * > > > > > > > > > /* > > > Cats are smarter than dogs. You can't make eight cats pull a sled > > > through > > > the snow. > > > */ > > > > > > > > > -- > > > PHP Database Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > > > > -- > > > PHP Database Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
