Help with mysql query please ...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pepe_lepew1962
Forum Commoner
Posts: 44
Joined: Thu Nov 20, 2008 10:29 am

Help with mysql query please ...

Post by pepe_lepew1962 »

Hi:

I am very new at this and totally confused as to what query I need. Here is my problem. We have bus trips all over the place and allow our drivers to put a request on routes as they become available. When the driver logs in, a session with his/her id is created. Now, display all the routes that the driver has not requested on. On the example below, when "PB" logs in and the session is created, display trips 1234, 1236, 1237 and 1238. I have a command button that is next to each trip that the driver would click and load a new screen with trip details. My problems is how to limit trips that the driver has NOT put a request on.

$D_Name = $_SESSION['sesdrivername01'];

tblTRIPS:
Trip_Num Trip_From Trip_To
1234AB Dallas Miami
1235AB Ft Worth Montreal
1236AB Houston Phoenix
1237AB Seattle Tacoma
1238AB Portland Vegas
//
//
tblDRIVER:
Driver_Load Driver
1235AB PB
1235AB AJ
1237AB PB


$frmTrip101 = "SELECT DISTINCT Trip_Num,Trip_From,Trip_To,Driver_Load FROM tblTRIPS, tblDRIVER where tblTRIPS.Trip_Num != tblDRIVER.Driver_Load";
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Help with mysql query please ...

Post by andyhoneycutt »

I would create a separate table "tblREQUESTS" as follows:

Code: Select all

Driver | Trip_Num
-------+---------
PB     | 1234AB
PB     | 1235AB
Then modify your query to exclude these items:

Code: Select all

$frmTrip101 = "
  SELECT DISTINCT Trip_Num, Trip_From, Trip_To, Driver_Load 
  FROM tblTRIPS, tblDRIVER 
  WHERE tblTRIPS.Trip_Num NOT IN (SELECT Trip_Num FROM tblREQUESTS WHERE Driver = tblDRIVER.Driver)
"; 
Not sure off the top of my head if this is going to work exactly as intended, but I would shoot for something like this.

Hope this helps,
Andy
Post Reply