Mysql and Php

(2005-12-23)

På svenska

Thanks to Mysql and Php it is easy to create dynamic web sites. A guestbook? A counter? A message board? This page will get you started.

What do you want to store?

Mysql is a database, a program that can store things and then let you retrieve just the things you want, in the order you want them. The first question to ask is: What do you want to store? Name, city and a message? Maybe date och time? It is entirely your decision, but keep in mind that if you want to be able to erase old messages, or show the most recent ones, you will have to save the time and date. Mysql only stores what you tell it to.

How do you want it to be stored?

The next step is to choose which one of Mysql's categories is the best choice for your things. The following are the most basic types:

Column typeDescription
CHARCharacters, for example a name or a city.
DATETIMEDate and time.
INTInteger, for example the number of visits.
TEXTText, for example a message.

For the CHAR type you have to set the maximum number of characters.

For example:

We want to save name, city, message and date and time. In order to store things in Mysql we will first have to create a table. To create a table you type CREATE TABLE followed by the name of the table, the names of the things you want to save and their types:

CREATE TABLE list
(name CHAR(30), city CHAR(30), message TEXT, dt DATETIME);

The above will create a table called list.

Using Php to access Mysql

The first step is to connect to Mysql:

mysql_connect("localhost", "username", "password");

"Localhost" means that Mysql is installed on the same computer as the web server, which is common, but if that is not the case for you, just put your Mysql host there instead. Username and password should be given to you from the server administrator. If you have installed Mysql on your own computer you can choose the password yourself. If you haven't already chosen a password the default is "root" for username and a blank password.

The second step is to pick a database (you can have several in Mysql):

mysql_select_db("the_name_of_the_database");

If you don't have a database you can create one by typing CREATE DATABASE. This command, just like CREATE TABLE, is an SQL command that you send to the Mysql server. To send it through Php you use the function mysql_query:

mysql_query("The_SQL_command");

So, if we for example would want to create a database called lists we would type: mysql_query("CREATE DATABASE lists");

Once the database has been created you use the function mysql_select_db to choose it, and once we have picked a database we can send the CREATE TABLE-command to create the table using the Php-function mysql_query.

Storing things in Mysql

Now that we have both a database and a table we can begin storing data. The SQL command is INSERT INTO and here is an example:

mysql_query("
INSERT INTO list (name, city, message)
VALUES ('Charles', 'London', 'This is a message.')
");
Retrieving data from an HTML form

Here is an example of a form to enable visitors to write something that will be saved in Mysql:

<form method="post" action="store.php">
Namn: <input type="text" name="name" /><br />
Ort: <input type="text" name="city" /><br />

Kommentar: <textarea name="message" cols="40" rows="4"></textarea><br />
<input type="submit" value="Save" />
</form>

Action="store.php" on the first line means that store.php will be called with the text the visitor entered when the visitor clicks the submit button. Therefore, the next step is to create store.php whose sole task will be to store the visitors' text in Mysql.

// Connects to Mysql. Has to be in every php file that uses Mysql.
  // Remember to use your own username and password.
  mysql_connect("localhost", "username", "password");

  // Time to choose the database.

  mysql_select_db("the_name_of_the_database");
  
  // All data the visitor entered will be found in the array
  // $_POST, saved under the name from the HTML form. We
  // will start by simplifying their names.
  $name = $_POST['name'];
  $city = $_POST['city'];
  $message = $_POST['message'];

  // Now, let's use the SQL command INSERT INTO
  // to store the data:
  mysql_query("INSERT INTO list (name, city, message) VALUES
    ('$name', '$city', '$message')
    ");

  // Finally, redirect the visitor to a different page
  // since store.php just stores and doesn't display anything.
  header("Location: list.php");
?>

To let a visitor enter something on a web page to be saved on the server, we need two things: an HTML form to give the visitor somewhere to enter text and php code to save the text.

The next part is about retrieve the data we have saved.

Retrieving data from Mysql

The SQL command for retrieving data is SELECT. You first type the columns you wish to retrieve, then the table and finally which rows you want and the order. In this case we want the name, city and message ordered by the date and time.

SELECT name, city, message FROM list
ORDER BY dt

The command above retrieves all rows from the table. If we would want just the rows where the name is "John" we would type:

SELECT name, city, message FROM list
WHERE name = 'John'

Time to put it together with some php code. Just like before we will use mysql_query to send commands to the database, only this time we want something in return, and that is where we will use $result:

$result = mysql_query("SELECT name, city, message
FROM list ORDER BY dt DESC");

"Desc" means that order should be descending. To get the information from $result we can use a loop with the function mysql_fetch_array:

while ($r = mysql_fetch_array($result))
{
  // One row at a time we will get the information
  // in the variables: $r['name'] $r['city'] och
  // $r['message']
}

The final step is some HTML code to display the information:

<table>


</table>

Kategorier

Start / English /


Fler artiklar