mysql query help!

**steb** · Mar 5, 2002, 20:01

hi all, hope someone can help me..

I have a database including these tables :

games
players

to simplify things right down, lets say a row in "games" looks like this :

id | p1id | p2id | level

and "players" :

id | name | surname

how can I, in 1 query, join these 3 queries :

SELECT * FROM games WHERE p1id OR p2id=1 (select all games that player with id=1 has played in)
SELECT * FROM players where id=games.p1id (select player1's details)
SELECT * FROM players where id=games.p2id (select player2's details)

using an inner join, I managed to get the game data and player 1s details in there, but when it comes to player2, there are duplicate field names..how to get around this problem?

in a nutshell, I need to join 1 row in a table to 2 rows in another..

any help?

thanks!!

**rottenray** · Mar 5, 2002, 20:28

Hello, I sure hope I can get back in and read tehe answers to this post. In MYSQL, I have a PHP and MYSQL database set up for listings on a real estate site, and everything works fine. I can post new entries no sweat adn delete them, However I want to be able to modify the listings I already have. I have created an update listing link that does a loop in PHP_SELF, and it calls the link and doesn't have any errors at all, however the values do not get put into the form, and I am wonderinng if it is possible to have this happen for me. I will post the code. I am also a blind person, so I may not be able to return to get the answer, so if someone has an easy fix for this, please email me ab7jm@cox.net Thank you in advance!
<HTML>
<HEAD><TITLE>Manage Listings</TITLE>
</HEAD>
<BODY>
<?php
if (isset($addlisting)): // If the user wants to add a listing
?>

<BODY BGCOLOR="#ffffcc">
<form action="<?=$PHP_SELF?>" method="post">
<TABLE border="0" cellpadding="1" cellspacing="1">
<TR><TD><CENTER>* Indicates required fields</center></TD></TR>
<TR><TD ALIGN="top">MLS NUMBER:*</TD>
<TD><TEXTAREA NAME="mls" ROWS="1" COLS="10"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">Square Footage:*</TD>
<TD ALIGN="top"><TEXTAREA NAME="sqft" ROWS="1" COLS="5"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">Year Built

/TD>
<TD ALIGN="top"><TEXTAREA NAME="yearblt" ROWS="1" COLS="5"></TEXTAREA></TD></TR>
<TR><TD>Highschool District

/TD>
<TD ALIGN="top"><TEXTAREA NAME="highschool" ROWS="1" COLS="15"></TEXTAREA></TD></TR>
<TR><TD>Middle School District

/TD>
<TD ALIGN="top"><TEXTAREA NAME="jr" ROWS="1" COLS="15"></TEXTAREA></TD></TR>
<TR><TD>Elementary School District

/TD>
<TD ALIGN="top"><TEXTAREA NAME="elementary" ROWS="1" COLS="15"></TEXTAREA></TD></TR>
<TR><TD>Financing Terms, <I>(FHA, Cash, VA

/i></TD>
<TD ALIGN="top"><TEXTAREA NAME="terms" ROWS="2" COLS="40"></TEXTAREA></TD></TR>
<TR><TD>Address With Zip:*</TD>
<TD ALIGN="top"><TEXTAREA NAME="address" ROWS="1" COLS="40"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">Number of Bedrooms <i>(Numbers only):*</i></TD>
<TD ALIGN="top"><TEXTAREA NAME="bdr" ROWS="1" COLS="1"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">Number of Bathrrooms <i>(Numbers only):*</i></TD>
<TD ALIGN="top"><TEXTAREA NAME="ba" ROWS="1" COLS="1"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">Type Of Cooling:*</TD>
<TD ALIGN="top"><INPUT TYPE=RADIO NAME="cooling" VALUE="AirConditioning">Air Conditioning<BR>
<INPUT TYPE=RADIO NAME="cooling" VALUE="Evaporative">Evaporative<BR>
<INPUT TYPE=RADIO NAME="cooling" VALUE="Both">Air and Evap</TD></TR>
<TR><TD ALIGN="top">Have a Pool

/TD>
<TD ALIGN="top"><INPUT TYPE=RADIO NAME="pool" VALUE="Yes">Yes<BR>
<INPUT TYPE=RADIO NAME="pool" VALUE="No">No</TD></TR>
<TR><TD ALIGN="top">Description of Property:*</TD>
<TD ALIGN="top"><TEXTAREA NAME="description" ROWS="4" COLS="40"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">Date Listed, <B>(Must follow this format (YYYY-MM-DD)</B>

/TD>
<TD ALIGN="top"><TEXTAREA NAME="listingdate" ROWS="1" COLS="10"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">List Price <i>(Numbers Only):*</i></TD>
<TD ALIGN="top"><TEXTAREA NAME="price" ROWS="1" COLS="10"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top"><input type="submit" name="submitlisting" value="SUBMIT" /></TD></TR>
</TABLE>
</FORM>

<?php
elseif (isset($changelisting)): // If the user wants to change a listing
?>
<BODY BGCOLOR="#ffffcc">
<form action="<?=$PHP_SELF?>" method="post">
<TABLE border="0" cellpadding="1" cellspacing="1">
<TR><TD><CENTER>* Indicates required fields</center></TD></TR>
<TR><TD ALIGN="top">MLS NUMBER:*</TD>
<TD><TEXTAREA NAME="MLS" VALUE="<?=$MLS?>" ROWS="1" COLS="10"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">Square Footage:*</TD>
<TD ALIGN="top"><TEXTAREA NAME="sqft" VALUE="<?=$sqft?>" ROWS="1" COLS="5"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">Year Built

/TD>
<TD ALIGN="top"><TEXTAREA NAME="yearblt" VALUE="<?=$yearblt?>" ROWS="1" COLS="5"></TEXTAREA></TD></TR>
<TR><TD>Highschool District

/TD>
<TD ALIGN="top"><TEXTAREA NAME="highschool" VALUE="<?=$highschool?>" ROWS="1" COLS="15"></TEXTAREA></TD></TR>
<TR><TD>Middle School District

/TD>
<TD ALIGN="top"><TEXTAREA NAME="jr" VALUE="<?=$jr?>" ROWS="1" COLS="15"></TEXTAREA></TD></TR>
<TR><TD>Elementary School District

/TD>
<TD ALIGN="top"><TEXTAREA NAME="elementary" VALUE="<?=$elementary?>" ROWS="1" COLS="15"></TEXTAREA></TD></TR>
<TR><TD>Financing Terms, <I>(FHA, Cash, VA

/i></TD>
<TD ALIGN="top"><TEXTAREA NAME="terms" VALUE="<?=$terms?>" ROWS="2" COLS="40"></TEXTAREA></TD></TR>
<TR><TD>Address With Zip:*</TD>
<TD ALIGN="top"><TEXTAREA NAME="address" VALUE="<?=$address?>" ROWS="1" COLS="40"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">Number of Bedrooms <i>(Numbers only):*</i></TD>
<TD ALIGN="top"><TEXTAREA NAME="bdr" VALUE="<?=$bdr?>" ROWS="1" COLS="1"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">Number of Bathrrooms <i>(Numbers only):*</i></TD>
<TD ALIGN="top"><TEXTAREA NAME="ba" VALUE="<?=$ba?>" ROWS="1" COLS="1"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">Type Of Cooling:*</TD>
<TD ALIGN="top"><INPUT TYPE=RADIO NAME="cooling" VALUE="AirConditioning">Air Conditioning<BR>
<INPUT TYPE=RADIO NAME="cooling" VALUE="Evaporative">Evaporative<BR>
<INPUT TYPE=RADIO NAME="cooling" VALUE="Both">Air and Evap</TD></TR>
<TR><TD ALIGN="top">Have a Pool

/TD>
<TD ALIGN="top"><INPUT TYPE=RADIO NAME="pool" VALUE="Yes">Yes<BR>
<INPUT TYPE=RADIO NAME="pool" VALUE="No">No</TD></TR>
<TR><TD ALIGN="top">Description of Property:*</TD>
<TD ALIGN="top"><TEXTAREA NAME="description" VALUE="<?=$description?>" ROWS="4" COLS="40"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">Date Listed, <B>(Must follow this format (YYYY-MM-DD)</B>

/TD>
<TD ALIGN="top"><TEXTAREA NAME="listingdate" VALUE="<?=$listingdate?>" ROWS="1" COLS="10"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top">List Price <i>(Numbers Only):*</i></TD>
<TD ALIGN="top"><TEXTAREA NAME="price" VALUE="<?=$price?>" ROWS="1" COLS="10"></TEXTAREA></TD></TR>
<TR><TD ALIGN="top"><input type="submit" name="submitlisting" value="SUBMIT" /></TD></TR>
</TABLE>
</FORM>

<?php
else:
$dbcnx = @mysql_connect

//Select listings database

if ($submitlisting == "SUBMIT") {
$sql = "INSERT INTO Listings SET
MLS='$mls',
sqft='$sqft',
YearBlt='$yearblt',
Highschool='$highschool',
Jr='$jr',
Elementary='$elementary',
Terms='$terms',
Address='$address',
Bdr='$bdr',
Ba='$ba',
Cooling='$cooling',
Pool='$pool',
Description='$description',
ListingDate='$listingdate',
Price='$price'";
if (@mysql_query($sql)) {
echo("<P>Your listing has been added.</P>");
} else {
echo("<P>Error adding submitted listing: " .
mysql_error() . "</P>");
}
}

if (isset($changelisting)) {
if ($mls) {
$sql = "UPDATE Listings SET sqft='$sqft',yearblt='$yearblt',highschool='$highschool',jr='$jr',elementary='$elementary,terms='terms',address='$address',bdr='$bdr',ba='$ba',cooling='$cooling',pool='$pool',description='$description',price='price' WHERE MLS=$mls";
echo("<p>The Listing has been changed.</p>");
} else {
echo("<p>Error changing selected listing</p>");
}
}

// if a listing is removed
//remove it
if (isset($deletelisting)) {
$sql = "DELETE FROM Listings
WHERE MLS=$deletelisting";
if (@mysql_query($sql)) {
echo("<p>The Listing has been removed.</p>");
} else {
echo("<p>Error removing listing</p>");
}
}
echo("<p> Here are all the listings in your database: </p>");
//get all of your listings
$result = @mysql_query("SELECT MLS, sqft, YearBlt, Highschool, Jr, Elementary, Terms, Address, Bdr, Ba, Cooling, Pool, Description, Price FROM Listings");
if (!$result) {
echo("<p>Error Performing request</p>");
exit();
}
while ( $row = mysql_fetch_array($result) ) {
$mls = $row["MLS"];
$sqft = $row["sqft"];
$yearblt = $row["YearBlt"];
$highschool = $row["Highschool"];
$jr = $row["Jr"];
$elementary = $row["Elementary"];
$terms = $row["Terms"];
$address = $row["Address"];
$bdr = $row["Bdr"];
$ba = $row["Ba"];
$cooling = $row["Cooling"];
$pool = $row["Pool"];
$description = $row["Description"];
$price = $row["Price"];
echo("<table border=\"0\" cellpadding=\"3\" cellspacing=\"2\"><tr><td colspan=\"2\" bgcolor=\"#ffffcc\"><B>$mls</b></td></tr><tr><td bgcolor=\"#ffffcc\">Square Footage: $sqft</td><td bgcolor=\"#ffffcc\">Year Built: $yearblt</td></tr><tr><td colspan=\"2\" bgcolor=\"#ffffcc\">Highschool: $highschool</td></tr><tr><td colspan=\"2\" bgcolor=\"#ffffcc\">Jr High: $jr</td></tr><tr><td colspan=\"2\" bgcolor=\"#ffffcc\">Elementary: $elementary</td></tr><tr><td colspan=\"2\" bgcolor=\"#ffffcc\">Terms: $terms</td></tr><tr><td colspan=\"2\" bgcolor=\"#ffffcc\">Address: $address</td></tr><tr><td bgcolor=\"#ffffcc\">Number of Bedrooms: $bdr</td><td bgcolor=\"#ffffcc\">Number of Bathrooms: $ba</td></tr><tr><td bgcolor=\"#ffffcc\">Cooling: $cooling</td><td bgcolor=\"#ffffcc\">Pool: $pool</td></tr><tr><td colspan=\"2\" bgcolor=\"#ffffcc\"><b><u>Property Description</u></b> $description</td></tr><tr><td colspan=\"2\" bgcolor=\"#ffffcc\">Price: $price</td></tr></table> " .
"<a href='$PHP_SELF?deletelisting=$mls'>" .
"Delete this listing</A> " .
"<a href='$PHP_SELF?changelisting=mls&mls=$mls'>" .
"Modify this listing?</a></p>");
}

//Show listing add form so you can add to the database
echo("<p><a href='$PHP_SELF?addlisting=1'>Add a " .
"new listing?</a></p>");
endif;
?>
</BODY>
</HTML>

**CrazyCrane** · Mar 6, 2002, 02:37

steb,

if you need to join the same table twice in a query, you need to qualify the tables. This means explicitly stating which table you take the information from. This is good practice anyway, as it removes any confusion as to where any of your data is coming from. It's common to use aliases to save a bit of typing.

So, you might do something like this (note the exact syntax may differ slightly depending on which database you're using, but the essence is the same):

Code:

SELECT g.level, g.p1id, g.p2id, p1.name AS p1name, p1.surname AS p1surname, p2.name AS p2name, p2.surname AS p2surname
FROM GAMES g
INNER JOIN PLAYERS p1 ON p1.pid = g.p1id
INNER JOIN PLAYERS p2 ON p2.pid = g.p2id
WHERE g.p1id = 1 OR g.p2id = 1

The g, p1 and p2 I've added there are aliases, and it means that the query knows exactly where it's getting the information from.

Hope this helps; if not, post back and I'll try to expand (or someone else more able to explain might post

)

rottenray, can I suggest starting your own thread for your question, rather than posting on an existing one?

The SitePoint Forums have moved.

User Tag List

Thread: mysql query help!

Thread Tools

Display

Hybrid View

mysql query help!

MYSQL Form Difficulty

Bookmarks

Bookmarks

Posting Permissions