SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Jul 2001
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql query help!

    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!!

  2. #2
    SitePoint Member
    Join Date
    Feb 2002
    Location
    Tucson
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MYSQL Form Difficulty

    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>

  3. #3
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Nick Wilson [ - email - ]


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •