Advanced PHP Using PHP with My SQL C

Advanced PHP: Using PHP with My. SQL C. Daniel Chase The University of Tennessee at Chattanooga

Overview ● ● ● Introduction to PHPMy. Admin to Manage My. SQL Management Introduction to Database Design Creation of Databases Using PHP with My. SQL Useful PHP Functions

PHPMy. Admin ● ● Web-based interface for managing My. SQL Download from phpmyadmin. net Can install in user web space—ours is in system-wide location: http: //localhost/phpmyadmin/ Setup by editing config. inc. php – For auth_type config ● Set controluser & controlpass – Read-only access to mysql/user – Read-only access to mysql/db ● Set user & pass

PHPMy. Admin Configuration ● Setup by editing config. inc. php – For auth_type config ● Set controluser & controlpass Read-only access to mysql/user – Read-only access to mysql/db Set user & pass – ● – For auth_type http Set auth_type to http ● Set controluser & controlpass as above ● Set user & pass to “” ● Do Not Set Apache to require authentication! Trick: With NEW My. SQL Install, Use PHPMy. Admin to create & set passwords in My. SQL BEFORE doing above ● ●

Managing My. SQL with PHPMy. Admin ● ● Adding Users Setting passwords Note: These are Exclusive to My. SQL! Granting Rights – – – Database level access control Table level control Field level control Operation control Remote location limits

Database Design ● ● Database Normalization First Normal form – – – ● Each column contains atomic values Each column has unique name Table has unique Primary Key No two rows identical No repeating groups of data Second Normal Form – – Applies only to multi-column primary keys Move data only dependent on part of primary key

Database Design ● Third Normal Form – ● Remove transitive dependencies—column not directly reliant on primary key, but on another that is Relationships – – – One-to-Many One-to-One Many-to-Many

Creation of Databases ● ● New Database require My. SQL root privileges If using http authentication, admin can create database & users authorized to access, then allow access for user to create actual tables, etc. Grant appropriate Rights to User Suggest admin creating – – Database level admin user with full rights Database level read-only user with SELECT only rights

Using PHP with My. SQL ● Create connection to My. SQL – – ● Select database – ● $conn = mysql_connect(“localhost”, “username”, “password”) or die (“Could not connct to database”); $conn = mysql_pconnect(“localhost”, “username”, “password”) or die (“Could not connct to database”); $db = mysq_select(“database”) or die(“Could not select database”); Send Query – $result = mysql_query(“SELECT * FROM table”) or die(mysql_error());