SQL Interface with mySQL from PHP

Thomas Krichel 2005-04-08 LIS651 lecture 3

today • some more SQL • interface with mySQL from PHP

USE • USE database tells mySQL to start working with the database database. • If you have not issued a USE command, you can still address a table table by using database.table, i.e. using the dot to link the two together.

addressing database tables columns • Let there by a database database with a table table and some column column. Then it is addressed as database.table.column. • Parts of this notation can be left out if it is clear what is meant, for example if you have issued USE database before, you can leave out the database part.

INSERT • INSERT inserts values. In its simples form INSERT INTO table VALUES (value1, value2, ..); Example: INSERT INTO products VALUES ('','Neufang Pils',1.23); • Note that in the example, I insert the null string in the first column because it is an auto_increment.

partial INSERT • If you are only giving a part of a record, or if you want to enter them in a different order you will have to give a list of column names. INSERT INTO products (name,id) VALUES ('Neufang Pils','');

SELECT • This is the SQL statement to select rows from a table. Here is the full syntax: SELECT [options] columns [INTO file_details] FROM table [WHERE conditions] [GROUP BY group_type] [HAVING where_definitions] [ORDER BY order_type] [LIMIT limit_criteria ] [ PROCEDURE proc_name(arguments)] [lock_options]

columns to SELECT • You can have a comma-separated list of columns SELECT name, price FROM products; • You can use the star to get all columns SELECT * FROM products;

WHERE condition to SELECT • = means equality WHERE id = 3 • >, <, >=, <= and != also work as expected • IS NULL tests if the value is null • IS NOT NULL • IN allows you to give a set WHERE state IN ("NY","NJ","CT")

SELECT using multiple tables • table1,table2 can be used to join both tables to build a big table that can be searched SELECT orders.id FROM customers, orders WHERE customers.id= 3 • This type of join is a Cartesian product aka a full join For each row of the first table, it adds rows from the second table.

complicated queries • who ordered Bruch Landbock? SELECT customer.id from customers, orders, orders_items, products WHERE customers.id=orders.customer_id AND orders.id=orders_items.order_id AND orders_items.item_id=products_id AND products.name='Bruch Landbock'

left join • Another way to join tables is to join them "on" some column. SELECT customers.name FROM customers LEFT JOIN orders ON customers.id = orders.customerid AND orders.id IS NULL • The joint table is filled with NULL for those costumers who have not placed an order yet. It is also known as a left outer join.

table example • Table A Table B • A1 A2 B1 B2 B3 • 1 4 2 3 4 • 4 5 6 7 3 • 6 3 1 1 4 • Left outer join by A2 and B3 is • A1 A2 B1 B2 B3 • 1 4 2 3 4 • 1 4 1 1 4 • 4 5 • 6 3 6 7 3

aliases • You can use AS to create aliases. If you want to find out which customers live in the same city as another customer select c1.name, c2.name, c1.city FROM customers AS c1, customers AS c2 WHERE c1.city = c2.city AND c1.name != c2.name

ORDER • You can order by a field by saying ORDER BY. • You can add ASC or DESC to achieve ascending or descending order. SELECT name, address FROM customers ORDER BY name ASC

column functions • AVG(column) give average of the column • COUNT(column) gives you a count of non NULL values • COUNT( DISTINCT column) gives a count of distinct values • MIN(column), MAX(column) • STD(column) gives the standard deviation • SUM(column) gives the sum of the items

column functions and grouping • You can use the function on the columns SELECT AVG(amount) FROM orders; • You can group the selection SELECT MIN(amount) FROM orders GROUP BY customerid; • You can use them in conditions with HAVING, such as SELECT customerid FROM orders HAVING AVG(amount) > 10;

LIMIT • This can be used to limit the amount of rows. LIMIT 10 19 • This is useful it web sites where you show a selection of the results. • This ends the discussion of the SELECT command.

UPDATE • The general syntax is UPDATE [LOW_PRIORITY] [IGNORE] table SET column1=expession1, column2=expression2... [WHERE condition] [ORDER BY order_criteria] [LIMIT number]. An example is UPDATE students SET email= 'phpguru@gmail.com' WHERE name='Janice Insinga'; • IGNORE instructs to ignore errors. • LOW_PRIORITY instructs to delay if the server is busy.

DELETE • The general syntax is DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table SET [WHERE condition] [ORDER BY order_criteria] [LIMIT number] • Bad example DELETE FROM customers; • Good example DELETE form customers WHERE customer.name='Thomas Krichel'

mySQL PHP function reference • affected_rows -- Get number of affected rows in previous MySQL operation • change_user -- Change logged in user of the active connection • client_encoding -- Returns the name of the character set • close -- Close MySQL connection • connect -- Open a connection to a MySQL Server • create_db -- Create a MySQL database • data_seek -- Move internal result pointer • db_name -- Get result data • db_query -- Send a MySQL query • drop_db -- Drop (delete) a MySQL database

mySQL PHP function reference • errno -- Returns the numerical value of the error message from previous MySQL operation • error -- Returns the text of the error message from previous MySQL operation • escape_string -- Escapes a string for use in a mysql_query • fetch_array -- Fetch a result row as an associative array, a numeric array, or both • fetch_assoc -- Fetch a result row as an associative array • fetch_field -- Get column information from a result and return as an object • fetch_lengths -- Get the length of each output in a result • fetch_object -- Fetch a result row as an object • fetch_row -- Get a result row as an enumerated array

mySQL PHP function reference • field_flags -- Get the flags associated with the specified field in a result • field_len -- Returns the length of the specified field • field_name -- Get the name of the specified field in a result • field_seek -- Set result pointer to a specified field offset • field_table -- Get name of the table the specified field is in • field_type -- Get the type of the specified field in a result • free_result -- Free result memory • get_client_info -- Get MySQL client info • get_host_info -- Get MySQL host info • get_proto_info -- Get MySQL protocol info

mySQL PHP function reference • get_server_info -- Get MySQL server info • info -- Get information about the most recent query • insert_id -- Get the ID generated from the previous INSERT operation • list_dbs -- List databases available on a MySQL server • list_fields -- List MySQL table fields • list_processes -- List MySQL processes • list_tables -- List tables in a MySQL database • num_fields -- Get number of fields in result • num_rows -- Get number of rows in result • pconnect -- Open a persistent connection to a MySQL server

mysql_connect() • This is used to establish a connection to the mySQL server. It is of the form mysql_connect('host', 'user', 'password'); • Example $link= mysql_connect('localhost','boozer', 'heineken'); • The function returns a variable of type “resource”. If there is a mistake, it returns false.

mysql_error() • This shows the error from the last mySQL command. $error=mysql_error(); if($error) { print "mySQL error: $error<br/>"; } • The value returned from that function is a simple string. • It is a good idea to check out error messages.

mysql_select_db() • This command has the syntax mysql_select_db('database') where database is the name of a database. • This tells mySQL that you now want to use the database database. mysql_select_db('beer_shop'); • It has the same effect as issuing USE beer_shop; within mySQL.

mysql_query() • mysql_query(query) send the query query to the connection identified by link. Usually link in left blank and the query goes to the most recently opened connection. $query="SELECT * FROM beer_shop.customers"; $result=mysql_query($query); • Note that the query itself does not require a terminating semi-colon.

result of mysql_query() • For SELECT, SHOW, DESCRIBE or EXPLAIN mySQL queries, mysql_query() return a resource that can be further examined with mysql_fetch_array(). • For UPDATE, DELETE, DROP and others, mysql_query() returns a Boolean value.

mysql_fetch_array() • mysql_fetch_array(resource) return an array that is the result row for the resource resource representing the most recent, or NULL if it the last result is reached. Its results in an array that contains the columns requested both by number and by column name: while($columns=mysql_fetch_array($result)) { print 'name: '.$columns['name']; print 'first column: $columns[0]; }

utility function from php.net function mysql_fetch_all($query) { $r=@mysql_query($query); if($err=mysql_error()) { return $err;} if(mysql_num_rows($r)) { while($row=mysql_fetch_array($r)) {$result[]=$row; } return $result;}} // usage if(is_array($rows=mysql_fetch_all($query)) { // do something } else { if (! is_null($rows)) { die("Query failed!");} }

mysql_data_seek(); • mysql_data_seek(result, point) sets the array that is returned by mysql_fetch_array to a number number. while($row=mysql_fetch_array($result)) { print 'first column: '.$row[0]; } mysql_data_seek($result,0); // otherwise the second loop would not work while($row=mysql_fetch_array($result)) { print 'first column: '.$row[0]; }

mysql_real_escape_string() • mysql_real_escape_string(string) returns a string escaped for the using in mySQL. $name="John O'Guiness"; $s_name=mysql_real_escape_string($name); print $s_name; // prints: John O\'Guiness • Note that this function makes a call to mySQL, therefore a connection must be established before the function can be used. • This function guards against SQL injections.

mysql_close() • This command connection. When it is invoked without an argument, it closes the current connection. • This is the happiest command there is, because it means that we have finished. • Unfortunately it is not used very often because the mySQL connection is closed automatically when the script finishes running.

mySQL PHP function reference • ping -- Ping a server connection or reconnect if there is no connection • query -- Send a MySQL query • real_escape_string -- Escapes special characters in a string for use in a SQL statement • result -- Get result data • select_db -- Select a MySQL database • stat -- Get current system status • tablename -- Get table name of field • thread_id -- Return the current thread ID • unbuffered_query -- Send an SQL query to MySQL, without fetching and buffering the result rows

extra: sha1() • This is a function that calculates a combination of 40 characters from a string. • The result of sha1() can not be translated back into the original string. • This makes it a good way to store password. • $s_password=sha1($password);

login.php & create_account.php • Both require a database that has three fields • id which is an auto_increment int acting as a handle • username is the username of the account. it must be unique and this is enforced by mySQL • password is a varchar(41) because the sha1 of the password is stored. This is 40 chars long.

login.php function show_form($message) { print "<div><h1>$message</h1><h2>Login</h2> <form action=\"$_SERVER[PHP_SELF]\" method=\"post\"> <div><input type=\"hidden\" name=\"submitted\" value=\"1\" /></div><p>Username <input type=\"text\" name=\"username\" maxlength=\"15\" value=\"$_POST[username]\" /></p><p>Password <input type=\"password\" name=\"pass\" value=\"$_POST[pass]\"/></p><p><input type=\"submit\" value=\"Login\" /> Not yet a member? <a href=\"create_account.php\">Create an account</a>! </p></form></div>"; }

function process_form() { • $username=trim($_POST['username']); • $pass=trim($_POST['pass']); • $sha_pass=sha1($pass); • $db=mysql_connect('localhost','krichel','laempel'); • $query="SELECT * FROM beer_shop.users WHERE • username='$username' AND password = '$sha_pass'"; • $result=mysql_query($query); • $error=mysql_error(); • if($error) { return "Sorry: $query gives an error<br/> • $error"; } • $affected=mysql_affected_rows(); • if(! ($affected)) {return "Invalid username or password";} • }

login.php (end) if($_POST['submitted']) { $error=process_form(); if($error) { show_form($error); } else { $user=$_POST['username']; print "<h1>Welcome to $user</h1>"; } } else { show_form(''); }

create_account.php function show_form($message) { print "<div><h1>$message</h1><h2>Create Account</h2><p>Please complete the form below to create your account. </p> <form action=\"$_SERVER[PHP_SELF] \" method=\"post\"><div><input type=\"hidden\" name=\"submitted\" value=\"1\" /></div> It must be more than 5 characters and cannot be your username.</p><p> <input type=\"submit\" value=\"Create Account\" /> </p></form></div>";

create_account.php <h3>Password</h3><p> Password <input type=\"password\" name=\"pass1\" value=\"$_POST[pass1]\"/>Confirm Password <input type=\"password\" name=\"pass2\" value=\"$_POST[pass2]\"/> </p><p>The password you enter will be used to access your account. It must be more than 5 characters and cannot be your username.</p> <p><input type=\"submit\" value=\"Create Account\" /></p></form></div>"; }

create_account.php function process_form() { $username=trim($_POST['username']); $pass1=trim($_POST['pass1']); $pass2=trim($_POST['pass2']); if(strlen($username)<6) { return "Username is too short."; } if(! ($pass1 == $pass2)) { return "Passwords do not match."; } $pass=$pass1; if($pass == $username) { return "Your username can not be your password.";

create_account.php if(strlen($pass)<6) {return "Password is too short.";} $sha_pass=sha1($pass); $db=mysql_connect('localhost','krichel','laempel'); $query="INSERT INTO beer_shop.users VALUES ('','$username','$sha_pass')"; $result=mysql_query($query); $error=mysql_error(); if($error == "Duplicate entry '$username' for key 2") { return "Sorry: Username $username is already taken, choose another."; } else {print "<h1>Thank you for registering with us!</h1>";} } 1

create_account.php (end) if($_POST['submitted']) { $error=process_form(); if($error) { show_form($error); } } else { show_form(''); }

Thank you for your attention! http://openlib.org/home/krichel

SQL Interface with mySQL from PHP