Query Optimization

[rdennisak]

I have built a taxi scheduling system but I'm a bit rusty on my queries so they are a little clunky.

 

I think a JOIN query could/should be used here.

 

These queries are inside a FOR loop that iterates 7 times (a weekly calendar), which is inside a WHILE loop (displays multiple vehicles)

 

The first query gets the driver_id for a vehicle from the Schedule table

The second query gets the driver's name from the Drivers table using the driver_id from the first query.

 

$sql1 = "SELECT driver_id, sched_id FROM schedule WHERE car_id = '$car_id' and sched_date = '$weekDate';

 

$sql2 = "SELECT first_name, last_name FROM drivers WHERE driver_id = '$driver_id'";

 

What woud a JOIN query look like?

 

Here is my info:

MySQL Version 5.0.32-Debian

 

 

CREATE TABLE `schedule` (

  `sched_id` int(6) unsigned NOT NULL auto_increment,

  `sched_date` date NOT NULL,

  `sched_time` varchar(10) collate utf8_unicode_ci NOT NULL,

  `car_id` varchar(4) collate utf8_unicode_ci NOT NULL,

  `driver_id` varchar(4) collate utf8_unicode_ci NOT NULL,

    PRIMARY KEY  (`sched_id`),

  KEY `sched_date` (`sched_date`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9885 ;

 

CREATE TABLE `drivers` (

  `driver_id` int(4) unsigned NOT NULL auto_increment,

  `first_name` varchar(30) collate utf8_unicode_ci NOT NULL,

  `last_name` varchar(30) collate utf8_unicode_ci NOT NULL,

  `nickname` varchar(30) collate utf8_unicode_ci NOT NULL,

  `street` varchar(50) collate utf8_unicode_ci NOT NULL,

  `city` varchar(50) collate utf8_unicode_ci NOT NULL,

  `state` varchar(20) collate utf8_unicode_ci NOT NULL,

  `zip` varchar(11) collate utf8_unicode_ci NOT NULL,

  `phone_1` varchar(20) collate utf8_unicode_ci NOT NULL,

  `phone_2` varchar(20) collate utf8_unicode_ci NOT NULL,

  `chauffer_number` varchar(20) collate utf8_unicode_ci NOT NULL,

  `chauf_exp_date` date NOT NULL,

  `adl_number` varchar(20) collate utf8_unicode_ci NOT NULL,

  `adl_exp_date` date NOT NULL,

  `birthdate` date NOT NULL,

  `e_contact` varchar(100) collate utf8_unicode_ci NOT NULL,

  `e_phone` varchar(40) collate utf8_unicode_ci NOT NULL,

  UNIQUE KEY `driver_id` (`driver_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=163 ;

 

[fenway]

Try this:

SELECT 
s.driver_id, s.sched_id, d.first_name, d.last_name
FROM schedule AS s
INNER JOIN drivers AS d USING ( driver_id )
WHERE s.car_id = '$car_id' and s.sched_date = '$weekDate';

[rdennisak]

Thanks for your help. I tried the code and it stops with this mysql_error:

 

Unknown column 'driver_id' in 'from clause'

 

 

Never mind - my mistake.  Let me try it again.

[Mchl]

'driver_id' column should have the same type in both tables

It might not be what's causing the error, but it should certainly help avoid other errors.

[fenway]

Good catch... id as varchar....!!