Title: Program interface to MySQL from PHP ($$$)
Course: PHP/MySQL Course

Opening the DB, and creating a table

Read pages 361 to 365 about opening a DB and creating a table.

Notes:
- the new mysqli_connect( ) creates a object that represents the connection, if called with the first three arguments only, it just connects to the mySQL server without a specific DB, if called with four arguments, you supply the DB name to use in the fourth argument. The first argument can be the value null to indicate that you are connecting to the server as localhost.

- Here is example code to connect to the mySQL server and create a db named testDB:

<?
$conn = new mysqli( NULL, "username", "password"); // conn to mysql with localhost

if (mysqli_connect_errno()) {
printf("Connection failed: %s\n", mysqli_connect_error());
exit();
} else {
$sql = "CREATE DATABASE testDB;"
$result = mysql_query($conn, $sql);
if($result == TRUE) {
echo "DB: testDB has been created";
} else {
printf("DB create failed: %s\n", mysqli_error());
}
}
mysqli_close($conn);
?>

Question?

In the code on page 364, what value will $res have if the table could not be created because of an error?

- false|FALSE|False

Inserting Data (Create)

Read about inserting a Record into a table on pages 365 to 368.

Notes:
- SECURITY WARNING: if you write the code as shown on page 367 for inserting the data from a user form into a database, you are opening yourself up to a type of very dangerous attack called 'sql injection'. Use the following code instead (note the addition marke in blue)

<?
$mysqli = mysqli_connect( "localhost", "joeuser", "somepass", "testDB");

if(mysqli_connect_errorno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
} else {
$data = mysql_real_escape_string($_POST['testfield']);
$sql = "INSERT INTO testTable (testfield)
VALUES ('$data')');"
$res = mysqli_query($mysqli, $sql);
if($res==TRUE) {
echo "A record has been inserted.";
} else {
printf("Could not insert record: %s\n", mysqli_connect_error());
}

mysqli_close();
}
?>

The mysql_real_escape_string() should be used on ALL data incomming form a form prior to it being placed inside any SQL statement.

Question?

what php function causes the sql statement to be executed by the database?

- mysqli_query()
- mysql_real_escape_string()
- mysqli_connect()
- mysqli_execute()

Retrieving Data using PHP (Read)

Read page 369 to 370 about retrieving data

Notes:
- The book shows one technique for retrieving rows of data from a sql select statement. In this technique, you have a loop that keeps calling mysqli_fetch_array to process each row, the data is returned from the call as an associative array with the keys set to the column(field) names.

Question?
What function will return the number of rows read by the query?

- mysqli_num_rows(|\(\))