[RESOLVED] Auction system Query help

Derokorian

So I'm building a system to make it easier to manage the auction for this event for a customer. I currently have 3 tables:

CREATE TABLE `dtco_auction_items` (
 `id_item` int(11) NOT NULL auto_increment,
 `donor_id` int(11) NOT NULL,
 `item_name` varchar(50) NOT NULL,
 `item_value` float NOT NULL,
 `item_live` tinyint(1) NOT NULL,
 `item_desc` text NOT NULL,
 `item_rest` text NOT NULL,
 `item_del` tinyint(1) NOT NULL,
 `item_del_by` date NOT NULL,
 `item_gc` tinyint(1) NOT NULL,
 `item_db` tinyint(1) NOT NULL,
 `date_added` date NOT NULL,
 PRIMARY KEY  (`id_item`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8

CREATE TABLE `dtco_auction_listing` (
 `id_listing` int(11) NOT NULL auto_increment,
 `name` varchar(50) NOT NULL,
 `desc` text NOT NULL,
 `type` enum('sports','vacation','activity','other') NOT NULL,
 PRIMARY KEY  (`id_listing`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

CREATE TABLE `dtco_item_listing` (
 `id_item` int(11) NOT NULL,
 `id_listing` int(11) NOT NULL,
 KEY `id_item` (`id_item`,`id_listing`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I have the following query for retrieving all items that are not currently in a listing:

SELECT di . *
FROM dtco_auction_items AS di
LEFT JOIN dtco_item_listing AS il ON di.id_item = il.id_item
LEFT JOIN dtco_auction_listing AS al ON il.id_listing = al.id_listing
WHERE il.id_item IS NULL

This works exactly as desired. I have another query to retrieve all the listings and their combined value:

SELECT SUM( di.item_value ) AS Total, al . *
FROM dtco_auction_listing AS al
LEFT JOIN dtco_item_listing AS il ON il.id_listing = al.id_listing
LEFT JOIN dtco_auction_items AS di ON di.id_item = il.id_item
GROUP BY il.id_listing

What I'm looking for is a way to change the second query, such that I can build my page so that when someone clicks a little + button next to the listing, they can then see the individual items. Maybe this needs to be done with a second query run for each item but that doesn't sound right. Basically I want the output to look like this:

+-----------------------------------------------------------------+
| Listing Name | Value |  Type  | Description (shortened) | Items |
+--------------+-------+--------+-------------------------+-------+
| Test         | 500   | Sports | Steelers Paraphanalia...| 3  [+]|
+--------------+-------+--------+-------------------------+-------+
| Test 2       | 750   |Activity| Safaria for 2           | 4  [+]|
+-----------------------------------------------------------------+

then when you click the plus I'd like to show the contained items for it as such:

+-----------------------------------------------------------------+
| Listing Name | Value |  Type  | Description (shortened) | Items |
+--------------+-------+--------+-------------------------+-------+
| Test         | 500   | Sports | Steelers Paraphanalia...| 3  [-]|
|   -> Throw Blanket - 35       |                         |       |
|   -> Autographed Jersy - 165  |                         |       |
|   -> 4 tickets to a game - 300|                         |       |
+--------------+-------+--------+-------------------------+-------+
| Test 2       | 750   |Activity| Safaria for 2           | 4  [+]|
+-----------------------------------------------------------------+

I hope that makes sense. I'm just not sure how to go about building this output like this. I appreciate any input you might have. Sorry for the ASCII art lol

TIA

johanafm

Derokorian;10986606 wrote:

CREATE TABLE `dtco_item_listing` (
 `id_item` int(11) NOT NULL,
 `id_listing` int(11) NOT NULL,
 KEY `id_item` (`id_item`,`id_listing`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

You are probably aware of it, but this table only makes sense if one item can belong to several different listings. I'm not saying it shouldn't be this way, just that it could be simpler IF an item can only belong to one listing

Derokorian;10986606 wrote:
I have the following query for retrieving all items that are not currently in a listing:

I have another query to retrieve all the listings and their combined value:

These two queries aren't at all the same. One actually retrieves items, while the other retrieves listings, without their items. If you solve this, either by ajax, or by dumping everything in the page from the start, you should in my opinion still treat listed and unlisted items the same way.

I.e. if you use ajax, you retrieve all listings that aren't empty, and assuming you have any items not in a listing, you create one final fake listing "Unlisted" or whatever you'd call it. Then, when the user clicks to expand, you use ajax to retrieve all items in the expanded listing or all unlisted items.

If you retrieve everything from the start, you neededn't separate the two queries. You just order items by their listing name, display them in block elements with display: none; and switch visibility to block whenever a listing is expanded.

Derokorian;10986606 wrote:
SELECT SUM( di.item_value ) AS Total, al . *
FROM dtco_auction_listing AS al
LEFT JOIN dtco_item_listing AS il ON il.id_listing = al.id_listing
LEFT JOIN dtco_auction_items AS di ON di.id_item = il.id_item
GROUP BY il.id_listing

Fix your query as explained here

Derokorian

johanafm;10986647 wrote:
You are probably aware of it, but this table only makes sense if one item can belong to several different listings. I'm not saying it shouldn't be this way, just that it could be simpler IF an item can only belong to one listing

I did it this way, simply because many of the items will not be included in a listing, but I understand your point. I could just put listing_id in the item table, just like I have my donor_id in there. However there will ALWAYS be a donor_id present - there may not be a listing_id present and I prefer to reduce wasted space by not having a bunch of NULL or empty cells.

johanafm;10986647 wrote:
Fix your query as explained here

This query seems to get me exactly what I'm looking for. I'm not entirely sure its valid in other DBMS, it does not use the invalid GROUP BY as I had before. Thanks for the link, was a GREAT explanation! However it does work perfectly:

SELECT di.id_item, di.item_name, di.item_value, al.id_listing, al.name, al.desc, al.type,
	(SELECT SUM( t1.item_value )
		FROM dtco_auction_items t1
		WHERE t1.id_item = di.id_item
	) AS Total,
	(SELECT COUNT( t2.id_item )
		FROM dtco_item_listing t2
		WHERE t2.id_listing = al.id_listing
	) AS Count
FROM dtco_auction_items AS di
	INNER JOIN dtco_item_listing AS il ON di.id_item = il.id_item
	INNER JOIN dtco_auction_listing AS al ON il.id_listing = al.id_listing
ORDER BY al.id_listing ASC

So now, I must figure out how to create the javascript to show/hide the items for each listing. This is my code to create the output, maybe you can advise?

// Get auction listings, with combined values and associated items.
$sql = "SELECT di.id_item, di.item_name, di.item_value, al.id_listing, al.name, al.desc, al.type,
				(SELECT SUM( t1.item_value )
					FROM dtco_auction_items t1
					WHERE t1.id_item = di.id_item
				) AS Total,
				(SELECT COUNT( t2.id_item )
					FROM dtco_item_listing t2
					WHERE t2.id_listing = al.id_listing
				) AS Count
			FROM dtco_auction_items AS di
				INNER JOIN dtco_item_listing AS il ON di.id_item = il.id_item
				INNER JOIN dtco_auction_listing AS al ON il.id_listing = al.id_listing
			ORDER BY al.id_listing ASC ";

if( !($result = $db->query($sql)) ) trigger_error('#'. __LINE__ .' - Query Error.<br />('.$db->errno.') '.$db->error.'<br />Query:'.$sql,E_USER_ERROR);
if( !$result->num_rows > 0 ) trigger_error('#'. __LINE__ .' - Query returned no results. Please create a listing.',E_USER_NOTICE);
$content .= '
	<table>
		<tr>
			<th>Listing Name</th>
			<th>Value</th>
			<th>Type</th>
			<th>Description (shortened)</th>
			<th>Items</th>
		</tr>
';
$prev_listing = 0;
while( $row = $result->fetch_assoc() ) {
	if( $prev_listing != $row['id_listing'] ) {
		$content .= '
		<tr>
			<td>'. $row['name'] .'</td>
			<td>'. $row['Total'] .'</td>
			<td>'. ucfirst($row['type']) .'</td>
			<td>'. (strlen($row['desc'])>150?substr($row['total'],0,145).'...':$row['desc']) .'</td>
			<td>'. $row['Count'] .' <a href="javascript:void()" onClick="someFunctionToShowItems();">[+]</a></td>
		</tr>
		';
	}
	$prev_listing = $row['id_listing'];
	$content .= '
		<tr class="collapse">
			<td>'. $row['item_name'] .'</td>
			<td>'. $row['item_value'] .'</td>
			<td></td>
			<td></td>
			<td></td>
		</tr>
	';
}
$content .= '</table>';

$additional_html_header = '<style type="text/css">tr.collapse { visibility: collapse; }</style>';

Unfortunately something about javascript just hasn't clicked with me yet, so I have no idea how to make this work as desired.

Derokorian

Ok i was wrong my query isn't working. The total is the same as the item_value.

Good news tho, I got the show/hide functionality working:

	<script type="text/javascript">
		function showHide(input) {
			var items = input + '_items';
			link = document.getElementById(input);
			link = link.innerHTML;
			if( link == '[+]' ) {
				document.getElementById(items).style.display = 'table-row';
				var temp = document.getElementById(input);
				temp.innerHTML = '[-]';
			} else {
				document.getElementById(items).style.display = 'none';
				var temp = document.getElementById(input);
				temp.innerHTML = '[+]';
			}
		}
	</script>
...
			<td>3 <a id="listing1" href="javascript:void()" onClick="showHide('listing1');">[+]</a></td>
		</tr>
		<tr id="listing1_items" style="display:none;">

Just need to get the total to be correct.

Derokorian

NVM got it working.

SELECT di.id_item, di.item_name, di.item_value, al.id_listing, al.name, al.desc, al.type,
	(SELECT SUM( t1.item_value )
		FROM dtco_auction_items t1
		WHERE t1.id_item IN (
			SELECT id_item 
				FROM dtco_item_listing t2 
				WHERE t2.id_listing = al.id_listing
		)
	) AS Total,
	(SELECT COUNT( t3.id_item )
		FROM dtco_item_listing t3
		WHERE t3.id_listing = al.id_listing
	) AS Count
FROM dtco_auction_items AS di
	INNER JOIN dtco_item_listing AS il ON di.id_item = il.id_item
	INNER JOIN dtco_auction_listing AS al ON il.id_listing = al.id_listing
ORDER BY al.id_listing ASC