[SOLVED] Querying multiple tables through one statement.

[Maggan]

mySQL version: MySQL - 4.1.21-standard

Table structure

 

Table #1 (this table is huge, so bare with me.)

Name:lp_members Preset variable in file include MEMBERS_TABLE

Structure:

Field

Type

Null

Default

member_idsmallint(5)No

member_namevarchar(30) No

member_earnedfloat(11,2) No0.00

member_spentfloat(11,2) No0.00

member_adjustmentfloat(11,2) No0.00

member_statusenum('0', '1') No1

member_firstraidint(11) No0

member_lastraidint(11) No0

member_raidcountint(11) No0

member_leveltinyint(2) YesNULL

member_race_idsmallint(3) No0

member_class_idsmallint(3)  No0

member_rank_idsmallint(3)  No0

 

Indexes:

KeynameTypeCardinalityField

PrimaryPRIMARY129member_id

member_nameUNIQUE129member_name

 

Engine: MyISAM

 

Table #2

Name wplayer, Preset variable in file include W_PLAYER_TABLE

 

Structure:

FieldTypeNullDefault

pidint(3)No

pnamevarchar(30)No

Indexes:

KeynameTypeCardinalityField

PrimaryPRIMARY62pid

 

 

Table #3

Name: wrequest Preset variable in file include W_REQUEST_TABLE

 

Structure:

FieldTypeNullDefault

ridint(3)No

pidint(3)No0

iidint(3)No0

Indexes:

KeynameTypeCardinalityField

PrimaryPRIMARY74rid

 

Notes: As you can see, pid reoccurs here too.

 

Engine myISAM again.

 

Now. My dilemma.

 

Using and reading up on joins and various ways of query multiple tables at same time to get an array of rows, I haven't been able to find anything that works for me.

 

The query roughly, that I've experimented is something like:

 

SELECT m.member_name, (m.member_earned-m.member_spent+m.member_adjustment) AS m.member_current, m.member_earned, m.member_spent, m.member_adjustment, m.member_lastraid,
	p.pid, p.pname, w.rid, w.pid, w.iid
	FROM " . MEMBERS_TABLE . " m, " . W_PLAYER_TABLE . " p, " . W_REQUEST_TABLE . " w
	WHERE  w.iid='$iid' AND (m.member_name = p.pname) AND (w.pid=p.pid)
	ORDER BY m.member_current

 

The purpose is to get a array to loop through, with the columns I've selected.

 

Best result I have gotten was

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

 

Looping only through table 3 at first, I can get all the data output I want. By using mysql fetch assoc instead.

But that loses the ability to order output by m.member_current since all data from MEMBERS_TABLE is gotten after the loops has already started.

 

I thought I had it when I finally came up with:

 

$reqres= mysql_query("SELECT m.member_name, m.member_earned, m.member_spent, m.member_adjustment
(m.member_earned-m.member_spent+m.member_adjustment) AS m.member_current
m.member_lastraid, p.pid
FROM " . MEMBERS_TABLE . " m, " . W_REQUEST_TABLE . " w
RIGHT JOIN " . W_PLAYER_TABLE . " p
ON m.member_name = p.pname
WHERE (w.iid = '$iid') AND (w.pid = p.pid)");


	while ($row = mysql_fetch_array($reqres))
	{ (insert PHP formatting of output here)
}		

 

but again, I get the Warning.

 

Oh and for reassurance sake, $iid variable is working throughout rest of script.

 

I've been trying my best, debugging this to bits. What I really want to know is, can someone come up with a query to actually do what I want this to do:

 

Have a preset $iid that I want to look up who requested p.pid=w.pid and then loop through the member data of said player by looking up p.pname and looping through p.pname's records by setting it = to m.member_name. Then to finish it all off order this by a value I set in sql m.member_current.

 

 

[bubblegum.anarchy]

Change this:

 

$reqres= mysql_query("SELECT m.member_name, m.member_earned, m.member_spent, m.member_adjustment
(m.member_earned-m.member_spent+m.member_adjustment) AS m.member_current
m.member_lastraid, p.pid
FROM " . MEMBERS_TABLE . " m, " . W_REQUEST_TABLE . " w
RIGHT JOIN " . W_PLAYER_TABLE . " p
ON m.member_name = p.pname
WHERE (w.iid = '$iid') AND (w.pid = p.pid)");

 

To this:

 

$reqres= mysql_query($query = "SELECT m.member_name, m.member_earned, m.member_spent, m.member_adjustment
(m.member_earned-m.member_spent+m.member_adjustment) AS m.member_current
m.member_lastraid, p.pid
FROM " . MEMBERS_TABLE . " m, " . W_REQUEST_TABLE . " w
RIGHT JOIN " . W_PLAYER_TABLE . " p
ON m.member_name = p.pname
WHERE (w.iid = '$iid') AND (w.pid = p.pid)") or trigger_error(mysql_error()."<PRE>".$query."</PRE>", E_USER_ERROR);

 

to see what the error is.

[Maggan]

Thank you!

 

This was a nifty little line which was incredibly helpful to debug sql. Wow.

 

All fixed now!

 

$reqres= mysql_query($query = "SELECT m.member_name, m.member_earned, m.member_spent, m.member_adjustment, (member_earned-member_spent+member_adjustment) AS member_current, m.member_lastraid, p.pid
	FROM " . MEMBERS_TABLE . " m, " . W_REQUEST_TABLE . " w
	RIGHT JOIN " . W_PLAYER_TABLE . " p
	ON m.member_name = p.pname
	WHERE (w.iid = '$iid') AND (w.pid = p.pid)
	ORDER BY member_current DESC")

 

taking out table prefixes for (member_earned-member_spent+member_adjustment) AS member_current,  did the trick