Home     Wordpress     Codex

Learning PHP (6) – Connecting to a database

June 16th, 2009 by admin | Filed under Learning PHP.

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: ,

4 Responses to “Learning PHP (6) – Connecting to a database”

  1. admin | 16/06/09

    If you run into any trouble, the mysql_error() function will return the last error message from MySQL. :-)

  2. bheesham | 17/06/09

    you should edit this to use echo instead of print. print is a bit slower than echo.

    Here’s where I got the info from.
    http://reinholdweber.com/?p=3

    It’s some good tips.

  3. Espen | 17/06/09

    I agree with you, echo is on average 20% faster than print, but please just hear me out on my
    reason for not editing the post.

    How much CPU time does each of those spend?
    There are also a bunch of other optimization tricks you can do like ‘using single quotes instead of double quotes’, but (I may speak for only myself here) I don’t like “micro-optimization” (as people tend to call this).

    Check out this post: http://fabien.potencier.org/article/8/print-vs-echo-which-one-is-faster

    Also I want to state that I am concentrating on getting basic knowledge out there so people can grow on that.
    Optimization of code should of course be considered, but I believe it’s better to actually get stuff done.
    hardware is also cheap today.
    One other reason that I am using print is because I also do a lot of perl, and the convenience and ignorance is bliss. :-D

  4. admin | 17/06/09

    Also be aware that unset() doesn’t actually free memory.
    For reference see http://stackoverflow.com/questions/584960/whats-better-at-freeing-memory-with-php-unset-or-var-null

Share Your Thoughts