Learning PHP (6) – Connecting to a database

Everyone needs to store and get data from a database every now and then. MySQL can be an excellent choice for some.

Creating a table
This is how you create a simple table from the CLI, you can recognize the values if you use systems like phpmyadmin for modifying database structures.

mysql> CREATE TABLE mytable (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(255) NOT NULL,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.02 sec)

The MySQL reserved are upper cased, all the text in lower case are things
that you can change. This basically creates a table named ‘mytable’ with
two columns; “id” and “name”, id is an INT(eger) and name is a varchar of
max 255 characters.

AUTO_INCREMENT means that id will be automatically incremented if not defined.

Connecting to MySQL from PHP
Disclaimer: This is a very basic example, if you are coding for production use please at least look at the function mysql_real_escape_string() or a library
that will handle insertion and such.

The example above inserts a record in MySQL, the id will be automatically set to 1 (for the first row you insert, 2 for the next, etc.). To read this record out from the database again you can do something like this:

Warning:  mysql_connect() [function.mysql-connect]: Unknown MySQL server host 'mysqlhost' (1) in /home/espen/virtualhosts/www.cfg.no/htdocs/wp-content/plugins/exec-php/includes/runtime.php(42) : eval()'d code on line 26

Warning:  mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /home/espen/virtualhosts/www.cfg.no/htdocs/wp-content/plugins/exec-php/includes/runtime.php(42) : eval()'d code on line 27
Data from MySQL
Warning:  mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/espen/virtualhosts/www.cfg.no/htdocs/wp-content/plugins/exec-php/includes/runtime.php(42) : eval()'d code on line 31

This piece of code will perform a “SELECT * FROM mytable” query (the * means every column from the table, you could get only the name by doing “SELECT name FROM mytable“.
Since there can be multiple rows we perform a while loop, so while $row gets fed with data from the mysql_fetch_assoc() function it will print the ‘id’ and ‘name’ columns from the table.

Select specific row(s) only (match on name)

Warning:  mysql_connect() [function.mysql-connect]: Unknown MySQL server host 'mysqlhost' (1) in /home/espen/virtualhosts/www.cfg.no/htdocs/wp-content/plugins/exec-php/includes/runtime.php(42) : eval()'d code on line 43

Warning:  mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /home/espen/virtualhosts/www.cfg.no/htdocs/wp-content/plugins/exec-php/includes/runtime.php(42) : eval()'d code on line 44
Data from MySQL
Warning:  mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/espen/virtualhosts/www.cfg.no/htdocs/wp-content/plugins/exec-php/includes/runtime.php(42) : eval()'d code on line 48

Now we used the LIKE keyword in MySQL, the percent (%) sign is a wildcard.
This will get every row from the table where name begins with an E.

Homework: Now try to combine $_GET and $_POST + MySQL.

Tags: Learning PHP, mysql