Re: [PHP] Re: loading a db table into a php array from mysql

[Rick Widmer]

At 02:57 PM 12/31/02 -0500, David T-G wrote:

...and then Tularis said...
%
% Usually,
% using mysql to handle your tables is *way* faster than letting php
% handle it.

Yes, do as much as you can in the database. The people who wrote it spent a lot of time trying to optimize it.

%
% In your case, you could just do a complex join I think. That would give

Ahhh...  A new term.  Back to the books for me!

Here is a query from one of my projects...

$R1 = $DB->Query( "SELECT ScheduleID, CourseName, DepartmentName, " .
" TimeStart, TimeStop, BuildingName, " .
" BuildingNumber, RoomName, RoomNumber, " .
" a.GradeLevel AS MinGrade, " .
" b.GradeLevel AS MaxGrade " .
"FROM Schedule " .
"LEFT JOIN Courses USING( CourseID ) " .
"LEFT JOIN Departments " .
" ON Schedule.DepartmentID = Departments.DepartmentID " .
"LEFT JOIN Periods " .
" ON Schedule.PeriodID = Periods.PeriodID " .
"LEFT JOIN Rooms " .
" ON Schedule.RoomID = Rooms.RoomID " .
"LEFT JOIN Buildings USING( BuildingID ) " .
"LEFT JOIN Levels a " .
" ON a.LevelID = MinLevel " .
"LEFT JOIN Levels b " .
" ON b.LevelID = MaxLevel " .
"WHERE YearID = '$CurrYearID' " .
" AND TeacherID = '{$_SESSION[ 'CurrentUserID' ]}' " .
"ORDER BY TimeStart " );

This combines data from seven different tables, and pulls two different values out of one of the tables (Levels) to create a single result set. In many cases you can collect all the data you need in a single query.

Every field in the Schedule table is a key to another table that has to be looked up and translated to something people will understand. Note the two different ways of connecting tables with LEFT JOIN. USING( fieldname ) connects the table being joined, with the table listed right before it, using the same fieldname in both. The ON syntax allows you to use differently named fields, and/or a different table.

Courses has two fields (MinLevel, MaxLevel) that contain the highest and lowest grade that is allowed to take the course. Look closely at how the table aliases (a and b) are used to join two different fields to the same table. (Levels)

The table structures are listed below.

Rick




CREATE TABLE Buildings (
BuildingID bigint(20) NOT NULL auto_increment,
BuildingName varchar(20) default NULL,
BuildingNumber varchar(20) default NULL,
NumRooms int(11) default NULL,
FirstYear bigint(20) default NULL,
LastYear bigint(20) default NULL,
PRIMARY KEY (BuildingID),
KEY BuildingName (BuildingName)
) TYPE=MyISAM;


CREATE TABLE Courses (
CourseID bigint(20) NOT NULL auto_increment,
DepartmentID bigint(20) NOT NULL default '0',
CurriculumID bigint(20) NOT NULL default '0',
CourseName varchar(20) default NULL,
MinLevel bigint(20) NOT NULL default '0',
MaxLevel bigint(20) NOT NULL default '0',
BeginYear bigint(20) default NULL,
EndYear bigint(20) default NULL,
PRIMARY KEY (CourseID),
KEY DepartmentID (DepartmentID),
KEY CurriculumID (CurriculumID)
) TYPE=MyISAM;


CREATE TABLE Departments (
DepartmentID bigint(20) NOT NULL auto_increment,
DepartmentHead bigint(20) default NULL,
DepartmentName varchar(30) default NULL,
MinGrade int(11) default NULL,
MaxGrade int(11) default NULL,
DepartmentOrder decimal(4,2) default NULL,
PRIMARY KEY (DepartmentID)
) TYPE=MyISAM;


CREATE TABLE Levels (
LevelId bigint(20) NOT NULL auto_increment,
DepartmentID bigint(20) NOT NULL default '0',
LevelName varchar(30) default NULL,
GradeLevel int(11) default NULL,
PRIMARY KEY (LevelId)
) TYPE=MyISAM;


CREATE TABLE Periods (
PeriodID bigint(20) NOT NULL auto_increment,
PeriodGroupID bigint(20) default NULL,
TimeStart time default NULL,
TimeStop time default NULL,
Days set('Sun','Mon','Tue','Wed','Thu','Fri','Sat') default NULL,
RollReqd char(2) default NULL,
PRIMARY KEY (PeriodID),
KEY DepartmentID (PeriodGroupID)
) TYPE=MyISAM;


CREATE TABLE Rooms (
RoomID bigint(20) NOT NULL auto_increment,
BuildingID bigint(20) NOT NULL default '0',
RoomName varchar(20) default NULL,
RoomNumber varchar(20) default NULL,
PRIMARY KEY (RoomID),
KEY BuildingID (BuildingID)
) TYPE=MyISAM;


CREATE TABLE Schedule (
ScheduleID bigint(20) NOT NULL auto_increment,
YearID bigint(20) NOT NULL default '0',
DepartmentID bigint(20) NOT NULL default '0',
PeriodID bigint(20) NOT NULL default '0',
RoomID bigint(20) NOT NULL default '0',
CourseID bigint(20) NOT NULL default '0',
TeacherID bigint(20) NOT NULL default '0',
PRIMARY KEY (ScheduleID),
KEY YearID (YearID),
KEY DepartmentID (DepartmentID),
KEY PeriodID (PeriodID),
KEY RoomID (RoomID)
) TYPE=MyISAM;



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php