PHP with MySQL
- 1. Unit - 4 PHPWITH MYSQL
- 2. Connecting to MySQL •Syntax: $dbc = mysqli_connect (hostname, username, password, db_name); • If a connection pblm occurred, mysqli_connect_error( ); • The encoding schema: mysqli_set_charset($dbc, 'utf8’); mysqli_query($dbc, 'SET NAMES utf8');
- 3. • Example: $dbc =@mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ‘. mysqli_connect_error( )); • DB connection can be done separately: $dbc = mysqli_connect (hostname, username, password); mysqli_select_db($dbc, db_name);
- 4. Executing simple queries •result = mysqli_query(dbc, query); • Result can be true or false. $r = mysqli_query ($dbc, $q); if ($r) { // Worked! • To close: mysqli_close($dbc); • Mysqli_multi_query();
- 5. Retrieving query results •mysqli_fetch_array( ); • while ($row = mysqli_fetch_array($r)) { // D o s o m e t h i n g w it h $ r o w. }
- 6. • Mysqli_free_result($r); • Mysqli_fetch_array($r,MYSQLI_NUM();
- 7. Ensuring secure SQL: •DB security has three broad issues: 1. Protecting the MySQL access information 2.Not revealing too much about the database 3.Being cautious when running queries, particularly those involving user submitted data • mysqli_real_escape_string( ) • $safe = mysqli_real_escape_string ($dbc, data);
- 8. Counting Returned records •returns the number of rows retrieved by a SELECT query. • $num = mysqli_num_rows($r); • Eg: $num = mysqli_num_rows($r); if ($num > 0) { // If it ran OK, display the records. // Print how many users there are: echo "<p>There are currently $num registered users.</p>n"; }
- 9. Updating records withPHP • $num = mysqli_affected_rows($dbc); • Truncate • Update • Eg: changing password
- 10. Sending values toa script Two ways: • <input type="hidden" name="do" value="this" /> • Append to the URL
- 11. Paginating Query Results •Displays the result as a series of pages • LIMIT • Two things to be considered: no.of pages, records to be displayed.