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
