RE: [PHP-DB] 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: [PHP-DB] 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: [PHP-DB] 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: [PHP-DB] 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: [PHP-DB] 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: [PHP-DB] 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