MySQL

1. MySQL PHP Web Technology

2. Logging in to Command Line • Start -> Programs -> AppServ -> MySQL Command Line Client • Enter Password, then you’ll be working as root.

3. Browsing Databases • Some useful commands to begin with • status; • show databases; - show all databases • use <database_name>; • show tables; • desc <table_name> • create database <database_name> • Create a new database CREATE DATABASE webtech

4. Create a User • It is a good idea to create a username to manage tables in new created database • Proper privileges can be granted to a particular user so that only a user who has right access can manage the table GRANT <previledge> [(col1, col2, … colN)] ON database.[table] TO user@host IDENTIFIED BY 'passwd'; GRANT select ON webtech.tct_phone TO tct@localhost IDENTIFIED BY ‘tct';

5. MySQL Privilege Scope

6. Create a Table CREATE TABLE <table_name> ( column_name1 <col_type> ….., ............ ) CREATE TABLE tct_phone ( STD_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, STD_FNAME VARCHAR( 64 ) NOT NULL , STD_LNAME VARCHAR( 64 ) NOT NULL , STD_PHONE VARCHAR( 12 ) NOT NULL );

7. SELECT Statements • Select all records (rows) from a table • Select some columns of all records from a table SELECT * FROM <table_name>; SELECT * FROM tct_phone; SELECT col1, col2,….colx FROM <table_name>; SELECT std_id, std_fname, std_lname FROM tct_phone;

8. SELECT Statements (cont.) • Select some records from a table • WHERE clause could be any boolean expression • ORDER BY clause (either asc or desc) SELECT * FROM <table_name> WHERE <condition>; SELECT * FROM tct_phone WHERE std_id > 20; SELECT * FROM tct_phone WHERE std_id > 20 and std_fname like ‘sor%’; SELECT * FROM tct_phone WHERE std_id > 20 and std_fname like ‘sor%’ ORDER BY std_fname desc;

9. Connecting to Database Using PHP mysql_connect(HOSTNAME, USER, PASSWD); $hostname = “localhost”; $dbUser = “tct”; $dbPass = “tct”; $conn = mysql_connect($hostname, $db_user, $password) or die(“Cannot open connection”);

10. Selecting a Database mysql_select_db(DATABASE, CONNECTION); mysql_select_db(“webtech”, $conn ) or die ("Cannot open database");

11. Making Query • Making query to opened database • Checking the number of fields from the query • Checking the number of records we get mysql_query($query); mysql_num_fields($result) mysql_affected_rows()

12. How to Retrieve the Records • There are a number of ways to get them: $name_row = mysql_fetch_row($result) while ($name_row = mysql_fetch_row($result)) print("$name_row[0] $name_row[1] $name_row[2] <BR>\n"); $row = mysql_fetch_object($result) while ($row = mysql_fetch_object($result)) print("$row->std_id -> $row->std_fname $row->std_lname<BR>\n"); $row = mysql_fetch_array($result) while ($row = mysql_fetch_array($result)) print($row[std_id '].$row[std_fname '].$row[std_lname'] <BR>\n");

13. Example Code $conn = mysql_connect(‘localhost’, ‘tct’, ‘tct’) or die("Cannot open connection"); mysql_select_db(“webtech”, $conn ) or die ("Cannot open database"); mysql_db_query("tct_phone","SET NAMES utf8");//Use UTF8 for Thai font $query = "select * from tct_phone"; $result = mysql_query($query); $num_fields = mysql_num_fields($result); echo "<TABLE border=1>"; echo "<TR>"; for($i=0; $i < $num_fields; $i++) echo "<TH>".mysql_field_name($result, $i)."</TH>"; echo "</TR>"; while ($name_row = mysql_fetch_row($result)) { echo "<TR>"; for($i=0; $i < $num_fields; $i++) print("<TD>$name_row[$i] </TD>"); echo "</TR>"; } echo "</table>";