needing data from several different tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

needing data from several different tables

Post by malcolmboston »

ok its hard to explain but basically im trying not to use joins and use seperate queries (this code is for someone else, someone who doesnt understand JOIN syntax)

im wondering if theres anyway around the following problem im having, please read comments.

Code: Select all

// we need to get the username from one table
$query_1 = "SELECT customer_id, username FROM members";
$result_1 = mysql_query($query_1) or die (mysql_error());
// get initial array
$array_usernames_1 = mysql_fetch_array($result_1, MYSQL_ASSOC);
// PRINT SOME OUTPUT TO THE BROWSER HERE
// LIKELY - <td>$array_usernames_1['username']</td>
// we now need to find some information from another table
// the field with a common relationship is customer_id
$query_2 = "SELECT customer_id, paypal_acc FROM paypal WHERE customer_id = '$array_usernames_1[customer_id]'";
$result_2 = mysql_query($query_2) or die (mysql_error());
// create another array with this value
$array_paypal_1 = mysql_fetch_array($result_2) or die (mysql_error());
// MORE PRINTING TO THE BROWSER

// now here i need to fetch the rest of the resultset using mysql_fetch_assoc
// however i am unsure on how to do this in this 'instance'
any ideas?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

lemme get this straight. The second query will likely return multiple rows? If so, you could store all the rows into an array, do your special processing on the first row, then do whatever you want on remaining rows.. basically, you can just slap in the fetch_assoc call on $result_2 like you would normally..
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

feyd wrote:lemme get this straight. The second query will likely return multiple rows?
I thought it was the first query_1 that selects all the members that had multiple rows? The second query_2 looks like it should only return one paypal_acc per member? ( Yeah there are no doubt people with multiple PayPal accounts. But last time I checked Paypal was discouraging such activity.)
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

yeah thats right

im having problems trying to get the rest of the members

normally you would use mysql_fetch_assoc for this but i have done this and it just keeps repeating the first row
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Use a JOIN. They're not hard.
oyse
Forum Newbie
Posts: 6
Joined: Wed Jul 07, 2004 3:37 am

Post by oyse »

I am not certain that I understand the problem. Do you want the pay pal information for all members or just one of them?

If you just want the information for the first member, can't you use the solution purposed by feyd? Only you read the complete resultset from the member table into an array before the second query is executed?

If you want the information for all members you could read all the members into an array like above and then query the paypal table for each one. The code would be something like this:

Code: Select all

//get all the members with a query
//read all members into array $allMembers. $allMembers is an array of arrays 

foreach( $allMembers as $member ){
$query =  "SELECT customer_id, paypal_acc FROM paypal WHERE customer_id = member[customer_id]'";
//query database
//get the information from the resultset
}
This will of course be very inefficent if you have a large member table. So you should use JOIN instead if that is an option.

oyse
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

malcolmboston wrote:normally you would use mysql_fetch_assoc for this but i have done this and it just keeps repeating the first row
That really sounds like a bug. Closest thing I could find on PHP bugbase was: http://bugs.php.net/bug.php?id=22498
I couldn't find anything in the change log though.
Post Reply