MySQL Query Help

jack.mitchell · 23 Mar 2011 at 19:14

Ok, so i'm getting myself muddled with joins, max, distinct and unique, you name it i've tried to get my head round it. Basically i'm trying to get a users max bids on a list of items using the following tables:

Code:

mysql> select * from bids;
+-------+-----------+----------+-----------+------------+
| bidID | listingID | memberID | bidAmount | bidDate    |
+-------+-----------+----------+-----------+------------+
|     1 |         5 |        2 |      8.00 | 1300902139 |
|     2 |         5 |        3 |     10.00 | 1300903458 |
|     3 |         5 |        2 |     11.00 | 1300904226 |
|     5 |         5 |        3 |     12.00 | 1300905712 |
+-------+-----------+----------+-----------+------------+
5 rows in set (0.00 sec)

mysql> select * from runningListings;
+------------------+----------+-----------------------+------------------------------+----------------+-----------------+--------------+-----------------+------------+------------+--------------+---------------+----------------+
| runningListingID | memberID | listingTitle          | listingDescription           | listingPostage | listingQuantity | listingPrice | listingDuration | startDate  | endDate    | currentPrice | reserverPrice | listingRunning |
+------------------+----------+-----------------------+------------------------------+----------------+-----------------+--------------+-----------------+------------+------------+--------------+---------------+----------------+
|                5 |        2 | Ghost Mantis Subadult | Subadult Female Ghost Mantis | 1.95           |               1 |         NULL |            NULL | 1300881051 | 1301485851 |         6.95 |          NULL |              1 |
|                6 |        2 | Congo Green Adult     | Adult Congo Green Mantis.    | 2.50           |               1 |         NULL |            NULL | 1300887508 | 1302097108 |        11.99 |          NULL |              1 |
+------------------+----------+-----------------------+------------------------------+----------------+-----------------+--------------+-----------------+------------+------------+--------------+---------------+----------------+
2 rows in set (0.00 sec)

mysql>

So far I have managed:

Code:

    $query = "SELECT runningListings.listingTitle,
                     runningListings.listingRunning,
                     runningListings.endDate,
                     bids.bidAmount
              FROM runningListings,
                   bids
              WHERE bids.memberID = ".$member->getID();

Which gives me:

Code:

mysql> SELECT runningListings.listingTitle, runningListings.listingRunning, runningListings.endDate, bids.bidAmount FROM runningListings, bids WHERE bids.memberID = 2;
+-----------------------+----------------+------------+-----------+
| listingTitle          | listingRunning | endDate    | bidAmount |
+-----------------------+----------------+------------+-----------+
| Ghost Mantis Subadult |              1 | 1301485851 |      8.00 |
| Congo Green Adult     |              1 | 1302097108 |      8.00 |
| Ghost Mantis Subadult |              1 | 1301485851 |     11.00 |
| Congo Green Adult     |              1 | 1302097108 |     11.00 |
+-----------------------+----------------+------------+-----------+
4 rows in set (0.00 sec)

What I should be getting is:

Code:

| Ghost Mantis Subadult |              1 | 1301485851 |     11.00 |

Could anyone suggest what I might use to achieve this, i've tried so many different things that i've got myself a bit muddled and don't know right from wrong anymore!

TIA, Jack.

SimonCHere · 23 Mar 2011 at 19:29

You aren't joining the tables, although I'm not sure why you've got memberid in the running listings table anyway. All the information is in the bids table. Unless it is for who owns the listing?

Code:

where runningListings.memberid = bids.memberid
and bids.memberID = ".$member->getID();

it's still not what you want as you need the max bid per item per member first.

Code:

select r.listingTitle, r.listingRunning, r.endDate, max(b.bidAmount), b.MemberID
from runningListings r 
inner join bids b 
on r.runningListingID = b.ListingID
Where b.memberid = .$member->getID() 
group by r.listingTitle, r.listingRunning, r.endDate, b.MemberID;

Anthony Kinson · 23 Mar 2011 at 19:31

you probably want to use a LEFT JOIN

Try the following...

Code:

$query = "SELECT *
              FROM runningListings
              LEFT JOIN bids
              ON runningListings.memberID = bids.memberID
              WHERE bids.memberID = ".$member->getID();

This will match all the rows from each table by the memberID, then select the row whose value matches $member->getID().

i've just edited your query structure, you may or may not have to format it properly.

jack.mitchell · 23 Mar 2011 at 20:16

Hi guys,

Thanks I used Simons implementation and it works perfectly.

Does anyone have a link to a good simple tutorial on mysql joins, I really need to brush on my database skills.

Also, just a quick note that memberID in the listings table is indeed who created the listing.

Thanks!

Anthony Kinson · 23 Mar 2011 at 20:52

first one i found... seems to cover the basics http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

SimonCHere · 24 Mar 2011 at 08:56

Glad it worked for you.