Lecture6 display data by okello erick

Lecture6 display data by okello erick

• 1.
  MySQL and PHP
• 2.
  Road Map  MySQLFunctions  Connect to The Database  Query the database  Display table data  Select individual records 2
• 3.
  MySQL Functions mysql_connect mysql_select_db mysql_query mysql_fetch_array mysql_fetch_assoc mysql_num_rows mysql_close Mysql_error 3
• 4.
  mysql_connect You can connectto a MySQL database by using the mysql_connect function. A typical mysql_connect statement may look like the following: $db = mysql_connect(“localhost”, “root”, “password”); 4
• 5.
  mysql_select_db The mysql_connect functionreturns a database link identifier $db and its used as an argument to the other MySQL functions. Selecting the database is a separate step after you are connected to a MySQL server; to do it, you use the mysql_select_db function. mysql_select_db(“mydb”, $db); 5
• 6.
  mysql_close When you arefinished using MySQL in a script, you close the connection and free up its resources by using mysql_close, like this: mysql_close($db); 6
• 7.
  mysql_query The function topass a SQL statement to MySQL is mysql_query. It takes two arguments the query itself and an optional link identifier. The following code executes a CREATE TABLE SQL statement on the MySQL database for $db: $sql = “CREATE TABLE mytable (col1 INT, col2 VARCHAR(10))”; mysql_query($sql, $db); 7
• 8.
  mysql_fetch_array PHP provides aconvenient way to work with more than one item from a selected row of data at a time. By using mysql_fetch_array, you can create An array from the query result that contains one element for each column in the query. 8
• 9.
  mysql_fetch_array $sql = “SELECTcol1, col2 FROM mytable”; $res = mysql_query($sql, $conn); while ($row = mysql_fetch_array($res)) { echo “col1 =“.$row[“col1”]; echo “col2 =“.$row[“col2”].“<br>”; } Each row of data is fetched in turn, and in each pass of the loop, the entire row of data is available in the array structure 9
• 10.
  mysql_fetch_assoc mysql_fetch_assoc Which also returnsthe row as an associative array. mysql_num_rows The function mysql_num_rows returns the number of rows found by the query, and you can use this value to create a loop with mysql_result to examine every row in the result. 10
• 11.
  mysql_num_rows $sql = “SELECTcol1, col2 FROM mytable”; $res = mysql_query($sql, $db); for ($i=0; $i < mysql_num_rows($res); $i++) { echo “col1 = “ . mysql_result($res, $i, 0); echo “, col2 = “ . mysql_result($res, $i, 1) . “<br>”; } With the query used in this example, because the column positions of col1 and col2 are known, you can use mysql_result with a numeric argument to specify each one in turn. 11
• 12.
  Fetching Full Rowsof Data You can build a very powerful loop structure by using mysql_fetch_array $sql = “SELECT col1, col2 FROM mytable”; $res = mysql_query($sql, $conn); while ($row = mysql_fetch_array($res)) { echo “col1 = “ . $row[“col1”]; echo “, col2 = “ . $row[“col2”] . “<br>”; } Each row of data is fetched in turn, and in each pass of the loop, the entire row of data is available in the array structure 12
• 13.
  SQL Errors When thereis an error in a SQL statement, it is not reported right away. You should check the return value from mysql_query to determine whether there was a problem it is NULL if the query has failed for any reason. 13
• 14.
  SQL Errors The followingexample tries to perform an invalid SQL statement (the table name is missing from the DELETE command): $sql = “DELETE FROM”; $res = mysql_query($sql, $db); if (!$res) { echo “There was an SQL error”; exit; } 14
• 15.
  SQL Errors If youwant to find out why a call to mysql_query failed, you must use the mysql_error and mysql_errno functions to retrieve the underlying MySQL warning text and error code number. if (!$res) { echo “Error “ . mysql_errno() . “ in SQL “; echo “<PRE>$sql</PRE>”; echo mysql_error(); exit; } 15
• 16.
  Connecting to Database $username= "your_name"; $password = "your_password"; $hostname = "localhost"; //connection to the server $dbhandle = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL"); echo "Connected to MySQL"; 16
• 17.
  Connecting to Database //selecta database to work with $selected = mysql_select_db(“pet", $dbhandle) or die("Could not select examples"); //execute the SQL query and return records $result = mysql_query("SELECT * FROM cars"); 17
• 18.
  Select data ina table //fetch tha data from the table while ($row = mysql_fetch_array($result)) { echo "ID:".$row['id‘]." Name:". $row['model‘]."Year: ". //display the results $row['year‘]."<br>"; } //close the connection mysql_close($dbhandle); 18
• 19.
  Try out <?php $dbhost ='localhost'; $dbuser = 'root'; $dbpass = ''; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ){die('Could not connect: ' . mysql_error());} $sql = 'SELECT * FROM pet'; mysql_select_db(‘webdb'); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die('Could not get data: ' . mysql_error()); } 19
• 20.
  Try out if(! $retval) { die('Could not get data: ' . mysql_error()); } while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) { echo "Tutorial ID :{$row['tutorial_id']} <br> ". "Title: {$row['tutorial_title']} <br> ". "Author: {$row['tutorial_author']} <br> ". "Submission Date : {$row['submission_date']} <br> ". "--------------------------------<br>"; } echo "Fetched data successfullyn"; mysql_close($conn); ?> 20