Slide 4.15: PHP MySQL select

PHP MySQL Select

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 and Populate

Create and populate the tables Enrollments and Courses if requested. The SQL commands are given below:

 CREATE TABLE  Courses (
   number CHAR( 10 )  PRIMARY KEY,
   name   VARCHAR( 16 ) ) ENGINE=InnoDB;
 INSERT INTO Courses VALUES ('457','E-Commerce'), ('351','File Processing');

 CREATE TABLE  Enrollments (
   name    VARCHAR( 32 ),
   course  CHAR( 10 ),
   PRIMARY KEY ( name, course ),
   FOREIGN KEY ( course ) REFERENCES  Courses ( number ) ) ENGINE=InnoDB;
 INSERT INTO  Enrollments  VALUES ('Bart', '351'), ('Bart', '457');
 INSERT INTO  Enrollments
   SELECT  'Ben', number  FROM  Courses  WHERE  name = 'E-Commerce';

Select

Perform the following tasks:

Use 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 query function in the $result variable.
Use the fetch_assoc( ) function to fetch a result row as an associative array.
Each subsequent call to fetch_assoc( ) returns the next row in the result rows.
The while loop loops through all the records in the result rows. To print the value of each row, we use the PHP $row variable ($row['student'] and $row['course']).