PHP & MySQL

1. PHP & MySQL Beginning Get all source files used here in “sfiles.zip” (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

2. A All Commands of MySQL (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

3. My SQL Commands • Connecting to the database • · mysql –h localhost –u username –p • Security and users • · grant select, insert, update, delete, • on dbname.tablename.* to username identified by ‘password’; • · revoke insert on dbname.tablename.* from username; • · show grants for username; • Creating databases • · show databases; • · create database dbname; • · use dbname; • Data types • · tinyint, smallint, mediumint, int, integer, bigint, float, • double, real, decimal, numeric • · date, datetime, time, timestamp • · char, varchar, text, blob, tinyblob, tinytext, mediumblob, • mediumtext, longblub, longtext • · bit, bool, enum, set (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

4. Contd. • Working with tables • · show tables; • · show columns from table_name; • · create table table_name ( • column_name datatype, …, • primary key (column_name)); • · describe table_name; • Working with files • · load data local infile ‘c:/mydata/mysql_stuff/filename.txt’ into • table table_name; • · < ‘input.sql’; • · < ‘input.sql’ > ‘output.txt’; (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

5. Contd. • Select statements • · select … from … where … order by … • · select * from table_name; • · select * from table_name where column_name=’value’; • · select column1, column2 from table_name; • · select distinct column1 from table_name; • · select * from table_name order by column2; • · select * from table_name order by column2 desc; • Insert statements • · insert into table_name (column1, column2) values (value1, • value2); • · insert into table_name set column1=value1, column2=value2; • Update statement • · update table_name set column1=value1; (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

6. End of commands • Delete statement • · delete from table_name where column1=value1; • · delete * from table_name; • · drop table table_name; • · drop database db_name; • Questions? • http://www.mysql.com/documentation (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

7. B Theories & Installation (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

8. The need for dynamic content • The Web is no longer static; it's dynamic. As the information content of the Web grows, so does the need to make Web sites more dynamic. Think of an e-shop that has 1,000 products. The owner has to create 1,000 Web pages (one for each product), and whenever anything changes, the owner has to change all those pages. Ouch!!! Wouldn't it be easier to have only one page that created and served the content on the fly from the information about the products stored in a database, depending on the client request? • Nowadays sites have to change constantly and provide up-to-date news, information, stock prices, and customized pages. PHP and SQL are two ways to make your site dynamic. • PHPPHP is a robust, server-side, open source scripting language that is extremely flexible and actually fun to learn. PHP is also cross platform, which means your PHP scripts will run on Unix, Linux, or an NT server. • MySQLSQL is the standard query language for interacting with databases. MySQL is an open source, SQL database server that is more or less free and extremely fast. MySQL is also cross platform (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

9. Installing Apache server routines • Installing Apache is relatively simple. First download the Apache archive, apache_x.x.xx.tar.gz (the latest I downloaded was apache_1.3.14.tar.gz) from the Apache site and save it in /tmp/src directory. Go to that directory: • # cd /tmp/src/ • # gunzip -dc apache_x.x.xx.tar.gz | tar xv • # ./configure --prefix=/usr/local/apache --enable-module=so # make # make install • This will install Apache in the directory /usr/local/apache. If you want to install Apache to a different directory, replace /usr/local/apache with your directory in the prefix. • To test your install, start up your Apache HTTP server by running: • # /usr/local/apache/bin/apachectl start • You should see a message like "httpd started". Open your Web browser and type "http://localhost/" in the location bar (replace localhost with your ServerName if you set it differently). You should see a nice welcome page. (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

10. Installing MySQL • Download the source from the MySQL site and save it in /tmp/src • # cd /tmp/src/ • # gunzip -dc mysql-x.xx.xx.tar.gz | tar xv • # cd mysql-x.xx.xx • # ./configure --prefix=/usr/local/mysql • # make • # make install • MySQL is installed. Now you need to create the grant tables: • # scripts/mysql_install_db Then start the MySQL server: • # /usr/local/bin/safe_mysqld & (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

11. Contd. • And test your installation by typing: • mysql -uroot -p At the password prompt, just press Enter. You should see something like: Welcome to MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version 3.22.34 Type 'help' for help. mysql> • If you see this, you have MySQL running properly. If you don't, try installing MySQL again. Type status to see the MySQL server status. Type quit to exit the prompt. (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

12. Installing PHP • Download and save the source from the PHP site to /tmp/src: • # cd /tmp/src/ • # gunzip -dc php-x.x.xx.tar.gz | tar xv • # cd php-x.x.xx • # ./configure --with-mysql=/usr/local/mysql --with-apxs=/usr/local/apache/bin/apxs • # make # make install • Copy the ini file to the proper directory: • # cp php.ini-dist /usr/local/lib/php.ini Open httpd.conf in your text editor (probably located in /usr/local/apache/conf directory), and find a section that looks like the following: • # And for PHP 4.x, use: # #AddType application/x-httpd-php .php #AddType application/x-httpd-php-source .phps Just remove those #s before the AddType line so that it looks like: • # And for PHP 4.x, use: # AddType application/x-httpd-php .php .phtml AddType application/x-httpd-php-source .phps (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

13. Contd. • Save your file and restart apache: • # /usr/local/apache/bin/apachectl stop # /usr/local/apache/bin/apachectl start (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

14. C First PHP & MySQL (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

15. Test whether you have PHP installed properly • Type the following code in a text editor and save it as test.php in a directory accessible by your Web server: <HTML> <?php phpinfo(); ?> </HTML> (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

16. Your first database • mysqladmin -uroot create learndb Type: • mysql You should see something like: Welcome to MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version 3.22.34 Type 'help' for help. (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

17. Contd.. > CONNECT learndb • CREATE TABLE personnel ( id int NOT NULL AUTO_INCREMENT, firstname varchar(25), lastname varchar(20), nick varchar(12), email varchar(35), salary int, PRIMARY KEY (id), UNIQUE id (id) ); > INSERT INTO personnel VALUES ('1','John','Lever','John', 'john@everywhere.net','75000'); • INSERT INTO personnel VALUES ('2','Camilla','Anderson','Rose', 'rose@flower.com','66000'); (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

18. Screen (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

19. Using the right database • mysql> use dbname; • Alternatively mysql dbname –h hostname –u username –p Mysql> use learndb; Database has been changed. (You will see) (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

20. Looking at databases • mysql> show tables; • mysql> show databases; • mysql> describe users; (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

21. Practices • Viewing individual rows : SELECT * FROM personnel WHERE id=‘1’; All: SELECT * FROM personnel ; Deleting rows: DELETE FROM personnel WHERE id=‘2’; (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

22. 2 command-examples • Insert: INSERT INTO personnel (firstname, lastname, nick, email, salary) VALUES (‘Abdur',‘Rahmant',‘Abid',‘abid@yahoo.com',‘6000'); • Update: UPDATE personnel SET firstname=‘Abdul',lastname=‘Wahab',nick=‘Mohammad',email=‘mohammad@yahoo.com', salary=‘8000' WHERE id=“2"; (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

23. D Dissection of MySQL  PHP (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

24. Opening and closing databases $link=mysql_connect("server","username","password"); • Next, the mysql_select_db command can be used to select a database on the current server: mysql_select_db("db_name",$link); • Last but not least, the mysql_close command closes an open database connection: mysql_close($link); (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

25. Submitting queries $result=mysql_query("SELECT * FROM CONTACTS",$link); • This sends a query (in the SQL language) to the database server specified by the link identifier ($link in the example). You can omit the identifier to use the last connection you made, which is sufficient unless you are maintaining more than one connection to different servers. A pointer to the results of the query is returned and stored in $result as seen in the example above. • An alternate command selects a database and makes a query: • mysql_db_query("SELECT * FROM CONTACTS","db_name",$link); Using this command eliminates the need for the mysql_select_db command, and is useful if you are working with a number of databases at once. As with the previous command, the link identifier ($link in the example) is optional. (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

26. Retrieving data from queries > First, you can retrieve a row of data into a simple array: $array=mysql_fetch_row($result); echo "the fields are: $array[0] and $array[1]"; > An alternate method uses associative arrays: $array=mysql_fetch_array($result); echo "Name: " . $array["contact_name"]; > A third command uses an object instead of an array: $object=mysql_fetch_object($result); echo "Name: " . $object->contact_name; (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

27. More on retrieving data • Another useful command allows you to determine the number of rows in a query's result: $rows=mysql_num_rows($result); • You can use this information to create a loop to display the results or to simply use a while loop without knowing the exact number of rows. • After you have finished using a query result you can use the following command to free the memory used by the result: mysql_free_result($result); (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

28. Creating and deleting databases $success=mysql_create_db("db_name",$link); > This attempts to create a database called db_name on the server connection identified by the $link variable. As before, the link identifier is optional. This returns a boolean value that indicates whether the operation was a success. > You can also delete an existing database using the mysql_drop_db command: $success=mysql_drop_db("db_name",$link); (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

29. Creating and deleting tables SQL command: CREATE TABLE Contacts (PRIMARY KEY contact_name VARCHAR(200), address VARCHAR(200), phone VARCHAR(20),num_sales INT(10)) In PHP (creating): <?php $query = "CREATE TABLE Contacts ("; $query .= "PRIMARY KEY contact_name VARCHAR(200),"; $query .= "address VARCHAR(200), phone VARCHAR(20),"; $query .= " num_sales INT(10))"; $result=mysql_query($query); if ($result) echo "Success!"; ?> deleting: $result=mysql_query("DROP TABLE Contacts"); (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

30. Retrieve data with ‘select’ queries > Basic: SELECT * FROM Contacts SELECT contact_name, phone FROM Contacts > ‘WHERE’ clause: • SELECT * FROM Contacts WHERE contact_name = 'John Smith' • SELECT * FROM Contacts WHERE contact_name LIKE '%Smith%' • SELECT * FROM Contacts WHERE num_sales > 10 • SELECT * FROM Contacts WHERE num_sales > 5 and num_sales < 10 (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

31. The ORDER BY clause • SELECT * FROM Contacts ORDER BY contact_name ( by default, ASC) • SELECT * FROM Contacts ORDER BY contact_name DESC • SELECT * FROM Contacts WHERE num_sales > 10 ORDER BY contact_name (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

32. Using a simple SELECT query SQL: • SELECT contact_name, phone FROM Contacts ORDER BY contact_name In PHP: <TABLE> <?php $query="SELECT contact_name,phone FROM Contacts ORDER BY contact_name"; $result=mysql_query($query); while(list($name,$phone)=mysql_fetch_row($result)) { echo "<TR> <TD> $name </TD> <TD> $phone </TD> </TR>"; } ?> </TABLE> (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

33. Using SELECT to count records SELECT COUNT(*) FROM Contacts <?php $result=mysql_query("SELECT COUNT(*) FROM Contacts"); $count=mysql_result($result,0); echo "$count records in database."; ?> (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

34. INSERT queries INSERT INTO Contacts (contact_name, phone) VALUES ('Fred Smith','801-555-1245') $query = "INSERT INTO Contacts (contact_name, phone, address)"; $query .= " VALUES('$name', '$phone', '$address')"; $result = mysql_query($query); if ($result) echo "Successfully added record."; (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

35. DELETE queries DELETE FROM Contacts $result = mysql_query("DELETE FROM Contacts "); $count = mysql_affected_rows($result); echo "$count records were deleted."; (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

36. UPDATE queries • UPDATE Contacts SET phone='801-555-2345' WHERE contact_name='John Smith‘ • UPDATE Contacts SET num_sales = num_sales + 1 WHERE contact_name='John Smith' (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

37. E First case Study (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

38. Case Study • A popular feature of many Web sites is a running list of news items called a weblog. This often appears as a list of announcements on a company home page, or even by itself in sites dedicated to news or journals. While you can create this type of page manually in HTML, using a database makes it easy to add entries to the log, and PHP can be used to display the most recent entries. • The following pages will guide you through the process of creating the PHP and HTML files used in this application. To try it out yourself, you will need access to a Web server that supports PHP 3.0 or later, and a user name and password for a MySQL database server. If you have a different database server, you can easily adapt the PHP code for use in another database format. • In the PHP scripts on the following pages, be sure to replace the generic server_name, user, password, and db_name entries with the correct information for your server. (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

39. Creating the database table <html> <head><title>Administration</title> </head> <body> <H1>Create Weblog Table</H1> <?php $link = mysql_connect("server_name","user","password") or die("Unable to connect to database"); mysql_select_db("db_name") or die("Unable to select database"); $query="CREATE TABLE weblog (item TEXT, aname VARCHAR(100)"; itemdate TIMESTAMP, primary key(itemdate))"; $result=mysql_query($query); if ($result) echo "Successfully created table!<br>"; else echo "Error creating table!<br>"; ?> </body> </html> createlog.php (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

40. Creating the Add Item form <html> <head><title>Administration</title> </head> <body> <H1>Add a Weblog Item</H1> <form action="addlog.php" method="post"> <b>Administrator Name:</b> <input type="text" name="admin" value=""> <br> <b> News Text:</b> <br> <textarea rows="5" cols="70" name="logtext"></textarea> <br> <input type="submit" name="addlog" value="Submit Item"> </form> </body> </html> addlog.html (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

41. Snapshot of addlog.html (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

42. Creating the Add Item code (addlog.php) (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

43. Creating the News Item Display (thelog.php) (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

44. Snapshot ofthelog.php (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

45. F Second example [Do Yourself] (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

46. Where's my view? • viewdb.php:  viewdb.php (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

47. Adding new records  detain.html (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

48. Content of detain.php (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

49. Putting it together(input.php) (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA

50. Viewing individual rows  view.php (c) Manzur Ashraf----Short Course,2004 ---- KFUPM, KSA