Lab 5: MySQL and PHP

Lab 5: MySQL & PHP Prepared: TrangNTT Configure MySQL Server Instance Select Start à All Programs à Zend Server Comminity Edition à MySQL Press Modify button if you need to change the location of InnoDB tablespace. Type password for root: Current password: Leave blank New password & confirm: 123456 Use MySQL Command Line Client to investigate the mysql database Change password for root username (if necessary) Select Start à All Programs à Zend Server Comminity Edition à MySQL Server 5.1 à MySQL Command Line Client. Enter password and press Enter. Type: SET PASSWORD = PASSWORD('new_pass'); to change password for root. Investigate mysql database Show and use mysql database Display all tables in the mysql database View column details for user table Create database named sale to run examples from the lecture. Run the example in the lecture slides Create table Products using PHP scripts 1. Create Table 2. <?php 3. $server = 'localhost'; 4. $user = 'phppgm'; 5. $pass = 'mypasswd'; 6. $mydb = 'mydatabase'; 7. $table_name = 'Products'; 8. $connect = mysql_connect($server, $user, $pass); 9. if (!$connect) { 10. die ("Cannot connect to $server using $user"); 11. } else { 12. $SQLcmd = "CREATE TABLE $table_name( ProductID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Product_desc VARCHAR(50), Cost INT, Weight INT, Numb INT)"; 13. mysql_select_db($mydb); 14. if (mysql_query($SQLcmd, $connect)){ 15. print 'Created Table'; 16. print "$table_name in database$mydb"; 17. print "SQLcmd=$SQLcmd"; 18. } else { 19. die ("Table Create Creation Failed SQLcmd=$SQLcmd"); 20. } 21. mysql_close($connect); 22. } 23. ?> The result after creating table successfully: Insert data to Products Display list of products Search products Update product Open and use phpMyAdmin to create a database and tables Open Zend Server Comminity Edition Select Start à All Programs à Zend Server Comminity Edition à Zend Server 4.0 Enter password you setted when installing Open phpMyAdmin Press Login to go to Zend Server page. Click on Open phpMyAdmin Enter username (root) and password (123456) Create tables and relationships for business listing service Create a database named business_service. Create tables and relationships as the following design: Category Administration page Design the following page for Category Adminstration to add a category: Business Registration page Design the following page for Business Registration to add a business. Users can choose multiple categories for the business in the list in the left of the page. Business Listing page Design the following page for Business Listing to display a list of businesses for a specific category. Users can click to the list of categories in the left view the list of corresponding businesses.