Geeklog Forums

I'm working on developing a plugin for my School District's web site that I will post here once it's done. I'm calling it the Trips Plugin, and amoung other things, it will:

• Allow users to request a bus or other vehicle from our transportation department
• Send email to the requestor, Transportation Director, Principal at the school where the students will be picked up
• Allow transportation director to "offer" trip to a specifc driver from a pool of drivers
• Allow driver to accept or decline trip
• Archive all information for detailed reporting
• etc, etc, etc

On the main trips screen, I want to display to the user any trips that relate to them. These trips could have been submitted (requested) by them, or if the user is a principal, they should see trips that will be loading at their school - whether they requested it or not. Similarily, if the trip is to be billed to a special school district account, then if the user is associated with that special account, they should see those trips as well.

I'm using 4 tables to store data:

MAIN TRIPS TABLE
+-------------------------------------------------------------------+
| id | tr_uid | bldg | program | other fields |
+----------------------------------------------|--------------------+
| 1 | 4 | 2 | 1 | other info |
+-------------------------------------------------------------------+


BUILDING TABLE
+-------------------------------------------- -+
| id | name | contact |
+----------------------------------------------+
| 2 | High School | 6 |
+----------------------------------------------+


PROGRAM TABLE
+-------------------------------------------- -+
| id | name | contact |
+----------------------------------------------+
| 1 | Special Ed | 4 |
+----------------------------------------------+


CONTACTS TABLE
+------------------------------------------------------------------------------------+
| id | gl_uid | name | email |
+------------------------------------------------------------------------------------+
| 4 | 8 | Tom Thumb | thumb@nowhere.net |
+------------------------------------------------------------------------------------+
| 6 | 9 | Bob Davis | bdavis@nowhere.net |
+------------------------------------------------------------------------------------+


So, for the 1 trip in the trips table, Geeklog UID 4 should be able to see the trip as the requestor, but Tom Thumb should be able to see it as the contact person for the Special Ed department, and Bob Davis should also be able to see it as the contact person for the high school.

My question is in how to contruct my select statment. I have the geeklog uid number recorded in the contacts table so I know that I can compare that against $_USER['uid'], but I first have to use the building or program table as a "lookup" before I can get at the stored gl_uid number.

I've read through the MySQL manual on Select, Join, and Union syntax, and I have my suspicions that I need to use a join to accomplish this. However, I've been unsuccesful in getting a select statement to work yet.

I know that this forum really isn't meant to answer MySQL questions, but I'm hoping that it relates to a plugin that I'm developing, somebody might be willing to give an example on how to write the query statement.

I thank you for any help you can offer.

---

~Brian

Well the first thing I would ask is what is the contacts table for? Why not use the GL uid and just index into the gl_user table for the name and email address? That would simplify things somewhat.

- Andy

You can try this:
select contact from building_table left join trips_table on building_table.id=trips_table.bldg

Same stmt using a table alias (a and b)
select contact from building_table a left join trips_table b on a.id=b.bldg

Once you have the contact (uid) you can then just do another select for the user detail.

You may look at not using a separate contacts table and maybe just use a group or if not needed - just access the users table. It's best not to duplicate the name and email incase the user updates their profile.

---

Geeklog components by PortalParts -- www.portalparts.com

I would actually prefere to do it that way. Unfortunately, the Transportation director is planning on keeping non-site members inside the contacts table. (She does trips for some of the private schools in the area, so those principals are not members of our district, and thus not of the web site).

---

~Brian

If you're using MySQL > 4.1 with subqueries, maybe something like this [following Blaine's example]:

SELECT name, email FROM contacts WHERE id=(SELECT contact FROM building_table a LEFT JOIN trips_table b ON a.id=b.bldg LIMIT 1) LIMIT 1

- Andy

Thank you both for your insight and suggestions. I decided that it would be best to not have a separate contacts table, and was able to convince the transportation director of this fact as well. So, after a small redesign, I have it working *nearly* the way I need it too. I hope you'll entertain 1 follow-up question though...

Each request is written to a 'trips' table. The transportation director then offers the trip to one of our drivers. This transaction gets written to a 'history' table with a unique id, and the trip id from the 'trips' table. The driver can then accept or decline the trip, which also gets written to the 'history' table.

When a driver logs into the database, they are shown any trips that have been offered to them:



The challenge is that if the driver has already declined a trip, there will be a record showing 'Offered' and another record showing 'Declined'. I don't want them to see these trips any longer though.

+------------------------------+
| id | tid | driver | status |
+------------------------------+
| 1 | 2 | 11 | Offered |
+------------------------------+
| 2 | 2 | 11 | Declined |
+------------------------------+
| 3 | 3 | 11 | Offered |
+------------------------------+
| 4 | 3 | 11 | Accepted |
+------------------------------+

So, in this example, when the driver logs in, they should only be able to view trip id 3. Although they were offered trip id 2, since they declined it, it should no longer be available to them.

Is there some way that I can modify my select query to where it would not return trip id 2?

Thanks!

---

~Brian

Why insert a new record in the table when it is declined? Why not just update the status - isn't that the purpose of the field?

- Andy

Perhaps the word 'status' in the history table is misleading...

I was asked specifically to create a database where a running history would be kept. The drivers are all paranoid that they are not all getting treated fairly, so writing out each offer, acceptance, declination, or expiration will allow the drivers to see when each trip was offered, to whom, and in how much time they had to accept or decline before the trip was offered to somebody else.

---

~Brian

Ah, OK. I think you're trying to use one table for two different concepts. What about creating an 'offer' table which lists the driver, trip, and status of the offer [for real this time]? Then you can use that table to get the list of trips a given driver hasn't declined and use that to select your history.

- Andy

Andy -

I implemented your suggestion and that took care of it!

Thank you so much for your help! I had been stuck for 4 days on that latter problem, so I REALLY appreciate your input.

~Brian

---

~Brian