Database Programming :: Lessons :: SQL in PHP
Connecting to a MySQL Server
In order to run SQL queries through PHP you must first connect to the mySQL server. You can do so using the following code:
$db_connection = new PDO("mysql:host=db.yhscs.us;dbname=YOURDATABASENAME", "YOURUSERNAME", "YOURPASSWORD");
For this class the host will always be "db.yhscs.us," but the database will change depending on the project you are working on. The username and password are the same username and password you use to get into phpMyAdmin. $db_connection is simply the name of the variable used to store the database connection. You can call it anything you like. $dbh has been a standard name used in the past, and stands for "database handle," but your name should be descriptive so if you can't remember what $dbh stands for then go with a more descriptive name.
PDO stands for PHP Data Objects and is an interface for accessing databases that has been included with PHP since version 5.1. It is far more secure than PHP's built-in SQL functions since there is less of a worry about SQL injection.
Executing Queries
Once you have made a connection to your database you can run queries on the database. You do so by first preparing the query.
$query=$db_connection->prepare("SELECT * FROM `Pokemon` WHERE `type` = ? ORDER BY `pokedexNumber`");
The question marks in the statement above serve as placeholders for input data. You should always use placeholders when you are receiving data from outside the script to mitigate SQL injection attempts. The query is prepared without values for the placeholders so any SQL statements in the data will not be interpreted as SQL.
The next step is to execute the query with values instead of placeholders.
$query->execute(array("Fire"));
$count=$query->rowCount();
$result=$query->fetchAll();
The execute() function is empty if there are no placeholder in the query. Otherwise, an array is used to hold the value for each placeholder in the order they appear in the query. The rowCount() function returns the amount of resulting rows, and the fetchAll() function returns an array containing all of the resulting data.
If you have a lot of placeholders, you may find it easier to use an associative array in the execute function like so:
$query=$db_connection->prepare("SELECT * FROM `Pokemon` WHERE `type` = :type ORDER BY `pokedexNumber`");
$query->execute(array(':type'=>"Fire"));
$count=$query->rowCount();
$result=$query->fetchAll();
Displaying Results
You can use a loop to traverse the results of a SQL query.
echo("<h1>Fire Type Pokemon</h1>");
for ($i=0; $i<$count; $i++)
{
$pokemon = $result[$i]['pokedexNumber'].":".$result[$i]['name'];
echo("$pokemon<br>"); // Output Example - 136:Flareon
}
The above loop gets the Pokedex number and name for all the fire type Pokemon and concatenates them using periods (.), which is the concatenation symbol in PHP instead of a plus sign like Java and C#.
