Slide 4.e: A PHP example

A PHP Example

The SELECT statement is used to select data from a database. The next example performs the following tasks according to the selected option:

Drop

Drop the tables Enrollments and Courses if requested.

Create

Create and populate the tables Enrollments and Courses if requested. The SQL commands are given below. Note that MySQL does not perform any check to make sure that course actually exists in Courses.

 CREATE TABLE  Courses (
   number char( 10 )  PRIMARY KEY,
   name   varchar( 64 ) );
 INSERT INTO Courses VALUES ('457','E-Commerce'), ('532','Programming Languages');

 CREATE TABLE  Enrollments (
   name    varchar( 64 ),
   course  char( 10 ),
   PRIMARY KEY ( name, course ),
   FOREIGN KEY ( course ) REFERENCES  Courses ( number ) );
 INSERT INTO  Enrollments  VALUES ('Bart', '532'), ('Bart, '457');
 INSERT INTO  Enrollments
   SELECT  'Ben', number  FROM  Courses  WHERE  name = 'Programming Languages';

Select

Perform the following tasks:

Use mysql_query( ) function to execute the SQL SELECT command:

 SELECT  E.name student, C.name course  FROM   Enrollments E, Courses C 
   where  E.course like '%$course%' and E.course = C.number;

Store the data returned by the mysql_query( ) function in the $result variable.
Use the mysql_fetch_array( ) function to return the first row from the recordset as an array.
Each subsequent call to mysql_fetch_array( ) returns the next row in the recordset.
The while loop loops through all the records in the recordset. To print the value of each row, we use the PHP $row variable ($row['student'] and $row['course']).