close ad
Databridge V2 with MySQLi support IS Now Available!
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

Filter data in select to omit records related to logged in user.

Thread began 3/01/2011 12:57 pm by troyd | Last modified 3/21/2011 10:45 am by troyd | 2049 views | 16 replies

troyd

Jason,

I have tested this thing pretty much every way I can and even waited for my client to try and break it. And it's still working for this particular setup. I can include other parts here if needed but I want to at least post back with the JOIN I am using. It has a couple of additions but rather than me trying to strip it down to bare minimum and risk a typo, here it is. I'll include an explanation from my point of view in case it's read by someone else learning this stuff like me. It still uses a slightly modified version of your subquery in the last suggestion you gave. (thanks)

Of course, feel free to correct me if anything is wrong because I know that even if it is working, that doesn't mean it is correct. And the last thing I want to do is mislead someone trying to learn joins.

Again, this is used to populate a dropdown select in a form used to reserve tables at an event. I needed the event dates to show up in the dropdown list only if the following were met.
a). The event had to be in the future. b). At least 5 tables still available. c). The logged in user had not already reserved that date.

JOIN

SELECT B.booth_id, date_format(show_date, '%M %e, %Y') as show_date, R.UserID, COALESCE(sum(reserved), 0) AS total, capacity - COALESCE(sum(reserved), 0) AS available
FROM tblbooths AS B LEFT JOIN tblreserved AS R ON R.booth_id=B.booth_id
WHERE B.booth_id NOT IN (SELECT booth_id FROM tblreserved WHERE UserID = ParamUserID)AND show_date >= CURDATE()
GROUP BY show_date
HAVING available > 5



Variable

ParamUserID
Int
-1
$_SESSION['UserID']




  1. Using LEFT JOIN so that all records from the table on the left, "tblbooths" are returned even if the table on the right does not have a record that matches.

  2. The "B" and "R" used is shorthand. So B.booth_id is from the FROM tblbooths AS B.

  3. COALESCE(sum(reserved), 0) AS total = This takes the total (sum) of the column 'reserved' and returns it as a new name "total". But if the column is NULL, returns a "0" for that record. Not sure it's required to do it that way, but it works.

  4. capacity - COALESCE(sum(reserved), 0) AS available = This one first looks at the 'capacity' column, then SUBTRACTS the SUM of 'reserved' and returns that number as "available".

  5. show_date >= CURDATE() returns all dates if they are from today forward. So even if an event date was not sold out, but is a past date, it disappears from the reservation form's select.

  6. The WHERE is then making sure that a subquery is compared to the first SELECT and that no show dates that were containing the logged in user were included in the query. This was the trickiest part of all.

  7. GROUP BY show_date = This is a consolidation or "aggregate function". Basically, as I understand it, the show_date column is grouped at it's top level so that rather than showing the same event date several times in the list, it groups each date.

  8. HAVING available > 5 = This one is looking for events that have more than 5 tables available so as to prevent overselling. And it works. But I'm a little unsure if this fits the standard because I have read that the HAVING column must be included in the GROUP BY. Maybe that was a misunderstanding on my part but I read that after I had this already working.



Thanks,
TroyD

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...