MySql : View Data from Database

Now we want to retrieve the data from the database and display them as links on the page. The SQL statement used to get data from a database is SELECT. The format is:
SELECT (columns) FROM (table)
WHERE (exclusive criteria)

For our database we want so select all columns, so a '*' is used instead of listing out each column. We also only want to select a specific category of links, let's say "Local Docs" is the cateogry we want. So our SELECT SQL statement would be:
SELECT * FROM links
WHERE category = 'Local Docs'

Single quotes specify a string value in the WHERE clause, if we were using a column which was a number value no quotes would be needed. The WHERE part is optional, if you want to select everything from the database you can leave off the WHERE portion. Review the SQL Tutorial for more examples of what can be done with SELECT statements and WHERE clauses.



The script to display links out of the database starts with the usually code to initialize the database connection.
$usr = "--username--";
$pwd = "--password--";
$db = "linksdb";
$host = "localhost";

$cid = mysql_connect($host,$usr,$pwd);
if (!$cid) { echo("ERROR: " . mysql_error() . "\n"); }
?>

The next part of the script is setting up and executing the SQL statement. This should look familiar to the previous pages. I set the category as a variable at the beginning so the code can be copied and pasted for other category selects and only one change is needed.
$category = "Local Docs";

$SQL = " SELECT * FROM links ";
$SQL = $SQL . " WHERE category = '$category' ";
$retid = mysql_db_query($db, $SQL, $cid);
if (!$retid) { echo( mysql_error()); }

The SELECT statement returns rows of data from the database. We need to loop through that data and display the information we want, which will be our links. The command to grab a row is:
$row = mysql_fetch_array($retid);

This sets $row as an array holding one record from the database, with the column names as the "keys" for the array. So to retrieve the siteurl value from that array you would use:
$siteurl = $row["siteurl"];

When the mysql_fetch_array command is called next, it moves to the next data record returned by the SELECT. If there are no more rows of data the command returns false. So to loop through all rows of data we can setup a while statement with the mysql_fetch_array in it. So here is the code to loop through the data and display it to the screen:
while ($row = mysql_fetch_array($retid)) {
$siteurl = $row["siteurl"];
$sitename = $row["sitename"];

echo ("
$sitename
\n");
}
echo ("");

?>

You can download the complete script of viewing data from clik the title of this posting

MySql : Insert Data

We need to get some data into the database. To do so we use an HTML form to collect the data, and then insert it into the database using PHP. Both these functions can be done on the same page.

The form can submit to the same page it is on. This page checks if the POST method is used. If so, insert into the database. I find it easier to have the form and database insert operation on the same page. Makes one less file to keep track of.

Here is the HTML for the form:

Category:
Site Name:
Site URL:
Description:

The above HTML snippet has the table elements removed because they are for display purpose only. The HTML for the form below is displayed using a table.



This form gathers the information we want to insert into the database and submits back to the same page it is on. We now need the PHP code to process this information. The first block of code is setting up the connection to the database. This is the same as used on the create table page.



This form gathers the information we want to insert into the database and submits back to the same page it is on. We now need the PHP code to process this information. The first block of code is setting up the connection to the database. This is the same as used on the create table page.

?>?>?>

The second part is run only when the request method on the page is a POST. This is when the form is submitted back to the page. We need to create our SQL statement for the insert. The SQL format for an INSERT is:
INSERT INTO -tablename-
(column_name1, column_name2, ...) VALUES
(data1, data2, ... )

Where the first column name matches to the first data in each of the sets. Remember PHP variables are automatically initialized with the form field names submitted to the page. So the PHP code to submit the query and check for errors is:
?>?>?>

Escaping Data
When creating SQL statements, string values are delimited using apostrophes (see above code). So what happens when there is an apostrophe in the data you are trying to insert? A SQL error will occur if, for example, the description variable included an apostrophe. Because you do not know what the user will type in, you must assume they are entering all sorts of bad data.

To insert an apostrophe into the database using SQL you need to "double-up" the apostrophes. That is, put two apostrophes in the text where you want just one. For example, to insert the phrase "what's up?" into a database, the SQL code looks like:
INSERT INTO mytable (phrases) VALUES ('what''s up?')

In PHP there is a string function which allows you to do just this on variables quite easily: str_replace This function replaces one value with another in a string. So before you insert data in the database you should replace all single apostrophes with double-apostrophes. For the example variable, the PHP code is:
$description = str_replace("'","''",$description);

Note: This does not insert two apostrophes into the database, just one. So when you pull the data out of the database, it will contain only single apostrophes.

For complte script clik the title of posting

MySql : Create Database

We know what data we want and what we want to with that data. Now we need to create the database to store the data. The first thing to do is create an empty database to hold our table. For this tutorial, I name the database linksdb. In MySQL you use the following command from the command line to create the new database.

mysqladmin -p create linksdb

The "-p" flag is used so it will prompt you for your password to MySQL. Most setups require a username and password. Read the MySQL doumentation on how to setup the username and password using the mysqladmin tool. It is fairly straight forward, however you may need root access.

Test connecting to the database using the mysql client. On the command line:

mysql -p linksdb

You should get a mysql> prompt if it connects to the database. Else, it returns an error saying Unknown database 'linksdb'


Creating the Database Table
Next we need to create the table in the database. Tables are created using SQL statements, and can be created using the mysql client tool or PHP. The nice thing about using a PHP script to create the table is you can save the script to use later. If something goes wrong, you can recreate the tables. Or, you can use it to refer to the database schema.

The SQL command to create a table is:
CREATE TABLE tablename (
column1 column1type,
column2 column2type,
etc.... )

The table we want to create is:
Table Name: links

Columns: id (integer - primary link key)

sitename (50 characters)

siteurl (75 characters)

description (text field - lots of text)

category (50 characters)


So the SQL code to create the categories table is:
CREATE TABLE links (
id INT NOT NULL AUTO_INCREMENT,
sitename VARCHAR(50),
siteurl VARCHAR(75),
description TEXT,
category VARCHAR(50),
PRIMARY KEY(id) );

The id column is the primary key for this table. In order to be a primary key the column can not be null (NOT NULL). I also set it to automatically increment the number (AUTO_INCREMENT) so when each record is added the id will increase by one. The last line specifies that the id column will be the primary key for this table. A primary key is a unique number for that specific record or row of data.

The sitename, siteurl, and category column each are specifed as a VARCHAR or variable character field each with their respective maximum length. A VARCHAR field holds characters from 0 up to its maximum specified length. Most databases VARCHAR limit is 255 characters. If you have a field which may require more characters you should use a TEXT field, which is what is used for the description field. A text field can hold lots of data, usually dependent on the database.

The most common data types are INT, VARCHAR, TEXT and DATETIME. Look in the MySQL documentation about creating databases for information about other available datatypes in MySQL, and more specifics the data types it supports.


Executing SQL in PHP
The code to execute a SQL statement in PHP has 3 steps:
Connect to database
Create statement
Execute statement

1.The command to connect to the database is:

$cid = mysql_connect($host,$usr,$pwd);

Where $host, $usr, and $pwd are previously specified. Host refers to the machine running MySQL and the username and password to connect to that MySQL machine. This command returns a connection id, which is used for to identify this connection in later queries.

2.Creating the SQL statement simply consists of assigning the SQL statement such as the one above to a string. I usually have it run over multiple lines concatanating the string together as I go, this makes it easier to read.

3.The command to actual send the database the SQL command is:

$result = mysql_db_query($db,"$SQL",$cid);

Where $db is the database to query, $SQL is the SQL statement, and $cid is the connection id created above. This returns a 1 if executed correctly, and undefined or false if an error occurred.

Putting all of this together, and adding a little error detection and displaying of the errors gives us the following script, which you can download and load on to your web server running PHP. Note: Opening the file through the web server will execute the script and create the database tables.

Download: PHP Script to create tables (create_table.phps)
Note: You must edit the username and password at the top of this script to reflect your system.


Problems: The most common problem when running this script is connecting to the database. Make sure you replace the username and password variables with your username and password setup to connect to your MySQL server. You can trouble shoot connection problems using the mysql client from the shell, use mysql -? for help.