Integrating PHP with MySQL

PHP & MySQL

The real power of the MySQL databases is their ability to interact with PHP to produce dynamic and user driven web pages. The basic concepts of getting PHP to interact with MySQL are described below. There are two methods of doing this: Procedural based (prior to PHP 4) and Object based (PHP 5+). This page will show both so that readers can backwards engineer code if need be. Before making any connections between PHP and MySQL it is important that data going into a database be in the correct format. For this reason, always check and filter the data using javascript checks. Also, to ensure that POST variables have values, use the if($_POST['varname']) to determine if information is entered.

Connecting to MySQL

The procedural based approach is as follows: $connPRO = @mysqli_connect("server", "username", "password"); $dbPRO = @mysqli_select_db("dbname", $connPRO); if(!dbPRO) echo "There was an error connecting to the database."; While the OOP approach is: $connOOP=new mysqli("server", "username", "password", "dbName"); if(mysqli_connect_errno()) echo "Connection failed: ".mysqli_connect_error(); The main difference in the options above is that the Object based approach allows for the selection of the database in the same statement. For the purposes of this class, we will use the OOP approach and will use localhost as the server, root as the username and empty quotes as the password.

This connection script is typically stored in a separate file and included in pages where needed. If several databases are in play, each would have its own connection script and a unique $conn variable for distinction.

Query the Database

To begin, we can write MySQL queries and stored them in PHP variables. e.g. $sql="INSERT INTP student($firstName, $lastName); Then, to execute the command, we need to call a function from the database connection object. $result=$connOOP->query($sql);.

Handle the Query Results

If your query requests information, such as a SELECT statement, the results of that query are stored as an array in $results. So, to get the information out, we set up a while loop and use a process that will return the information as an associative array where the keys are the database field names. while ($row-=$result->fetch_assoc()){ echo $row['firstName']; }

Counting Results

To count the results of a query, use the COUNT method. This can be accomplished as follows: $sql=SELECT COUNT(*) from STUDENTS where id="123";

Putting It All Together

$conn=new mysqli("localhost", "root", "", "certgen") or die("error"); $sql = "select student_id from student_award order by student_id ASC;"; $result=$conn->query($sql); while($row=$result->fetch_assoc()){ echo $row['student_id']."<br/>"; }