WeberDev.com PHP and MySQL Code

LOG IN
BEGINNER GUIDESPHP CLASSESCODE SEARCHARTICLES SEARCHPHP FORUMSPHP MANUALPHP FUNCTIONS LISTWEB SITE TEMPLATES
Start typing to search for PHP and MySQL Code Snippets and Articles Search


Submit a code Example / Snippet Join us on FaceBook
Submit a code Example / Snippet Submit Your Code
Search Engine Optimization Monitor SEO Monitor
Web Site UpTime Monitor UpTime Monitor
Your Personal Examples List My Favorite Examples
Your Personal Articles List My Favorite Articles
Edit Account Info Update Your Profile
PHP Code Search
Web Development Forums
Learn MySQL Playing Trivia
PHPBB2 Templates
Web Development Resources
Web Development Content
PHPClasses
PHP Editor
PHP Jobs
Vision.To Design
Ajax Tutorials
PHP Programming Help
PHP/MySQL Programming
Webmaster Resources
Webmaster Forum
XML meta language
website builder



Go Back Add a Comment Send this Article to a friend Add this Article to your personal favoritest for easy future access to your favorite Code Examples and Articles. Submit a code example Print this code example.
BACK ADD A COMMENT SEND TO A FRIEND ADD TO MY FAVORITES SUBMIT AN ARTICLE PRINT
Title : Beginners guide to PHP and MySQL
Categories : PHP, Beginner Guides, Databases, MySQL, Installation
Rafi Ton
Rafi Ton
Date : 2000-01-27
Grade : 3 of 5 (graded 6 times)
Viewed : 166846
Search : More Articles by Rafi Ton
Action : Grade This Article
Tools : My Favotite Articles


Submit your own code examples 
 


Ok, I'm convinced ! I want to use PHP and MySQL, but how do I install and run the stuff ?
What are the basic things I have to know, use and do to get a simple PHP-MySQL application working?

This tutorial is down to basics. No fancy coding, exemplary scripts etc. just the basics.

One more thing, there are a lot of tutorials out there dealing with UNIX based machines, this tutorial will focus on Windows based platforms. Nevertheless, except of the installation part, which is more or less Windows specific, the other parts are identical for all platforms.

In this tutorial we will build step by step a small web site using PHP and MySQL with the following features:

1. Viewing the database;
2. Adding an entry to the database;
3. Modifying and entry on the database;
4. Deleting an entry from the database.

On the way we will work both with the MySQL terminal and PHP scripts to get the feel of both.



Section 1 - Collecting and setting up the necessary items.

Ok, having said that, let's get down to business. In order to start the PHP- MySQL wonder we need some basic stuff:

1. Web server running;
2. PHP extension for you web server;
3. MySQL server running;

First step, Getting and installing a Web Server:

Let's assume that you don't have a web server running on your machine. There are few nice web server applications available for Windows. Just one thing, in order to run php/mysql the web server you choose has to support extensions.

One of the easiest servers to install on Windows and to install PHP on is OmniHTTPd http://omnicron.ca/httpd/download.html), which is a very nice free web server.

Once you got the installation file from the site, double click it and install the web server. If you chose to install the web server with all the default values then you will have it installed at c:\httpd. Once installed successfully, the installation program will launch your web browser with a lame welcome message and you'll get a new icon on the windows tray.

Ok, done with that.

Next, Downloading and installing the PHP distribution:

Getting the PHP Win32 distribution is easy: just go to http://www.php.net/downloads.php and choose the best location for you.

The PHP installation on OmniHTTPd is very simple.

First Unzip the installation file to your disk at, lets say, e:\php.

Copy the file, 'php3-dist.ini' to your '%WINDOWS%' directory and rename it to 'php3.ini' (c:\windows on Win95/95 and c:\winnt on WinNT/Win2k).

Edit your 'php3.ini' file as follows:

Change the 'extension_dir' setting to point to your php-install-dir (c:\php), or where you have placed your 'php3_*.dll' files.

Choose which modules you would like to load when PHP starts. You can uncomment the: 'extension=php3_*.dll' lines to load these modules (make sure you uncommented extention=php3_mysql.dll). Some modules require you to have additional libraries installed on your system for the module to work correctly.

The PHP FAQ (http://www.php.net/FAQ.php3) has more information on where to get supporting libraries. You can also load a module dynamically in your script using: dl("php_*.dll");

The DLLs for PHP extensions are prefixed with 'php3_'. This prevents confusion between PHP extensions and their supporting libraries.

Now follow few simple steps to configure OmniHTTPd:

Right click on the blue OmniHTTPd icon in the system tray and select Properties;

Click on Web Server Global Settings;

Click On the 'External' tab. Now, enter: ".php3" at the "Virtual" field, "c:\php\php.exe" on the "actual" field and click on "Add";

Choose the Mime tab and enter: "wwwserver/stdcgi" on the "Virtual" field, ".php3" on the "Actual" field and click "Add".

Click OK.

Choose "Default Virtual Settings" on that same window;

Now, click on the "Server" tab;

Under the "Default Index" field enter "index.php3" (separate by a comma). This will tell the web server to look for index.php3 file also.

Click OK.

Ok, that should be enough. Lets test if PHP is working:

Create a small text file named index.php3 with the following text:


<?
echo "I made it!";
?>




Now, launch your browser and point it to the localhost. You should get "I made it!" on the screen.

Now, getting and installing MySQL:

Go to http://www.mysql.com/downloads/index.html, get the MySQL-Win32 distribution, unzip the zip file to a temp directory and run the installation file (setup.exe).

If you chose the default typical installation all of MySQL files will be installed at c:\mysql.

Now, if you followed all the steps, every thing should work properly.

Let's make some tests:

First, you have to run the MySQL demon by mysqld-shareware.exe located at c:\mysql\bin. You'll see a dos window opens and after few seconds closes. If no error messages appeared MySQLd is running.

If you want to check MySQL, just run c:\mysql\bin\mysql. You'll see a terminal window with "mysql>" prompt. That's good; we are talking with the MySQL server.





Section 2 - Creating and manipulating a MySQL database:

First we have to create the database and the table we want to use. We will call our database "example" and create a table called "tbl" with few columns: id number, first name, last name and info. Creating the database and defining the
table is done through the mysql terminal - just double click or run c:\mysql\bin\mysql.exe.

To see which tables are defined on MySQL use (Note that the 'Mysql>' is the terminal prompt:

Mysql> show databases; <Enter>

This command should display something like this:



+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.01 sec)

In order to define a new database (example) type in:

Mysql> create database example; <Enter>

You should see a reply like:
Query OK, 1 row affected (0.17 sec)

That's good, we have a new database. Now we can create a table inside the database, but first we have to choose the new database:

Mysql> use example; <Enter>

The responds should be:
Database changed

Now we can create the table with the following columns:

Index number - which will be a integer;
User name - which will be a maximum 30 char text field;
Last name - which will be a maximum 50 char text field;
free text - which will be a maximum 100 char text field;
To create this table, type the following command at the MySQL prompt:

MySQL> create table tbl (idx integer(3), UserName varchar(30), LastName varchar(50), FreeText varchar(100));<enter>

The responds should be something like:
Query OK, 0 rows affected (0.01 sec)

Ok, Let's see how the table looks from the MySQL prompt, by typing the command:

MySQL> show columns from tbl; <enter>

We should get the following result:



+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| idx      | int(3)       | YES  |     | NULL    |       |
| UserName | varchar(30)  | YES  |     | NULL    |       |
| LastName | varchar(50)  | YES  |     | NULL    |       |
| FreeText | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Here we can see the content of table "tbl" which we just created.

Now we can see what is in the table. Let's type the following command:

MySQL> select * from tbl;<enter>

This command asks to display all the data in the "tbl" table. The output should be:
Empty set (0.07 sec)

The reason we got this responds is that we don't have any data inserted in the table. Let's insert some data into the table by typing:

MySQL> insert into tbl values (1,'Rafi','Ton','Just a test');<enter>
Query OK, 1 row affected (0.04 sec)

As we can see, we inserted the values into the table inthe order that we defined the table earlier because this is the default cell order. We could set the order of data by using the following syntax:

MySQL> insert into tbl (idx,UserName,LastName,FreeText) values (1,'Rafi','Ton','Just a test');<enter>

Ok, now we can try and see the content of the table:

MySQL> select * from tbl;<enter>

This time the result is:



+------+----------+----------+-------------+
| idx  | UserName | LastName | FreeText    |
+------+----------+----------+-------------+
| 1    | Rafi     | Ton      | Just a test |
+------+----------+----------+-------------+
1 row in set (0.00 sec)

Now we can see the structure of the table and the content of each cell.

Let's say we want to delete this set of data. To achieve this we should type:

MySQL> delete from tbl where idx=1 limit 1;<enter>
Query OK, 1 row affected (0.00 sec)

Ok, some explanation is in order. We are telling MySQL to delete from "tbl" the row that has idx value of 1 and limiting the deleted rows to 1. If we didn't limit the rows deleted by 1, then all the rows holding idx value of 1 would have been deleted (in this case we had only one row, but nevertheless I added it just to make the point clear).

Unfortunately, we have again an empty table, so let's fill it again:

MySQL> insert into tbl values (1,'Rafi','Ton','Just a test');<enter>
Query OK, 1 row affected (0.04 sec)

Another thing we can do is to modify the content of a specific cell using the "update" command:

MySQL>update tbl set UserName='Berber' where UserName='Rafi';<enter>
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

This command will seek all the UserName cells with value of "Rafi" and will change it to "Berber". Note that the "set" section and the "where" section are not necessarily have to be the same. We can change one column by searching another. Furthermore, we can perform a search by two or more conditions:

MySQL>update tbl set UserName='Rafi' where UserName='Berber' and LastName='Ton';<enter>
Query OK, 1 row affected (0.04 sec)

This query searched both columns for their content and changed the content of UserName.



Section 3 - Combining PHP and MySQL

In this section we will build a simple PHP based web site to control the aforementioned MySQL table.

We will build the following site structure (I'll resume you all know some basic html):

1. index.php3 for the front end and viewing the table;
2. add.php3 to add entries to the table;
3. Modify.php3 to edit the table content;
4. del.php3 to delete entries from the table;

First, we want to view the database:

Consider the following script.

Index.php3:


<html>
<head><title>Web Database Sample Index</title>
</head>
<body bgcolor=#ffffff>
<h2>Data from tbl</h2>

<?
mysql_connect
() or die ("Problem connecting to DataBase");
$query = "select * from tbl";
$result = mysql_db_query("example", $query);

if (
$result) {
echo
"Found these entries in the database:<br><p></p>";
echo
"<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00FFFF>User Name</td>
<td align=center bgcolor=#00FFFF>Last Name</td>
<td align=center bgcolor=#00FFFF>Domain Name</td>
<td align=center bgcolor=#00FFFF>Request Date</td>
</tr>"
;

while (
$r = mysql_fetch_array($result)) {

$idx = $r["idx"];
$user = $r["UserName"];
$last = $r["LastName"];
$text = $r["FreeText"];

echo
"<tr>
<td>$idx</td>
<td>$user</td>
<td>$last</td>
<td>$text</td>
</tr>"
;
}
echo
"</table>";

} else {
echo
"No data.";
}

mysql_free_result($result);
include (
'links.x');
?>

</body>
</html>


Ok, some explaining:

We begin with normal html tags to create the html tructure. When we want to escape from html and move to PHP we open a PHP section with <? This tells the webserver to treat the following text as PHP syntax and not plain html. To close the PHP section we used ?> tag.

The mysql_connect() command tells PHP to establish a connection to the MySQL server. If the connection is established successfully the script will continue, if not it will print the die message "Problem connecting to DataBase" (More information about mysql_connect and other PHP functions could be found on http://www.php.net under documentation).

Now, if the MySQL was installed like we discussed above this should be enough, but if you are using a pre-installed MySQL (like ISP) you should use the following command:

mysql_connect (localhost, username, password);

We placed the query we want to perform on the MySQL in $query, and then the script executes it by using mysql_db_query command:

$result = mysql_db_query("example", $query);

while the "example" varible is the name of the database and $query is the needed MySQL query.

We used the MySQL command select (as described above) to get all the data from the table:

$query = "select * from tbl";

Few words about $result the function, the function returns a positive MySQL result identifier to the query result, or false on error. This is not the result itself but just an identifier that can be later translated to the information we need.

Now we want to check whether we have any data in the database, and is so to print it formatted in a html table structure.

To check whether we have data we used the if command with the following syntax:


if (argument) {

"do something;"

} else {

"do something different;"

}



while "do something" is your set of commands to be executed when the argument=true, and "do something different" is the set of commands to be executed if the argument=false.

Notice that we printed some html tags to build the html table structure using the echo command. Only printed text from PHP command will be inserted as html - not the PHP commands themselves.

Another command we used is the while instruction, which uses the following syntax:


while (argument)) {
"something to do";
}



The while loop will repeat itself as long as the argument=true and will execute the set of instructions inside the { } set.

Here we combined the while loop with the PHP function $r=mysql_fetch_array ($result). This function gets one row that corresponds to the result identifier and places the result in an associative array $r, with the column names as the array identifiers. In our script we will get an array: $r['idx'], $r['UserName'], $r['LastName'] and $['FreeText'].

We could also use the function mysql_fetch_row which places the results in a regular array and then we would get an array: $r[0], $r[1], $r[2] and $r[3] with corresponding values.

For further information about those function please visit http://www.php.net.

Now, that we have all the information, we can print it inside the html table with:


echo "<tr>
<td>$idx</td>
<td>$user</td>
<td>$last</td>
<td>$text</td>
</tr>";






Now we can free the MySQL connection and free some resources by using the mysql_free_result($result); function.

Another useful feature of PHP is the ability to include text files inside the script. Let's assume we have a repeating set of code (e.x. set of links to other pages), we can use the include function and save us some coding and time. Furthermore, if we want to change the code all we need to do is change the included file and it will affect all the pages we included the file.

Here we created a text file called Links.x that holds the entire link menu we want to use on every page:


<p></p>
<ul>
<li><a href="index.php3">Home</a>
<li><a href="add.php3">Add a new entry to the DataBase</a>
<li><a href="edit.php3">Edit an entry</a>
<li><a href="del.php3">Delete an entry from the DataBase</a>
</ul>


The include syntax is:

Include ('included_text_file');

Now we can close the PHP section with ?> and the html page with </body></html>.



Adding data to the database using forms

Let's review the following code:


<html>
<head><title>Add an entry to the database</title>
</head>
<body bgcolor=#ffffff>

<h1>Add an entry</h1>

<form method="post" action="add2tbl.php3">
<table width=90% align=center>

<tr><td>Index:</td><td><input type=text name="idx" size=3 maxlength=3></td></tr>

<tr><td>UserName:</td><td><input type=text name="UserName" size=40
maxlength=100></td></tr>

<tr><td>LastName:</td><td><input type=text name="LastName" size=40
maxlength=100></td></tr>

<tr><td>FreeText:</td><td><input type=text name="FreeText" s=40 maxlength=100></td></tr>

<tr><td></td><td><input type=submit value=add></td></tr>

</form>
</table>

<?php include ('links.x');?>

</body>
</html>


Assuming you are familiar with forms, this is rather a simple script. We designed a form based html page, which calls for add2tbl.php3 script upon submission. Now, the form consists with 4 fields corresponding our MySQL table:
index number, FirstName, LastName and FreeText. Notice that we called the form fields like the MySQL table names, but this is not necessary just convenient.

Again we added the link set with the include command using <? include ('links.x');?> (like we explained before).

Let's take a look at add2tbl.php3 script:


<html>
<body>

<?
if ($UserName)
{

mysql_connect() or die ("Problem connecting to DataBase");
$query = "insert into tbl values ('$idx','$UserName','$LastName','$FreeText')";
$result = mysql_db_query("example", $query);

echo
"Data inserted. new table:<br><p></p>";
$query = "SELECT * FROM tbl";
$result = mysql_db_query("example", $query);

if (
$result) {
echo
"<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00FFFF>idx</td>
<td align=center bgcolor=#00FFFF>User Name</td>
<td align=center bgcolor=#00FFFF>Last Name</td>
<td align=center bgcolor=#00FFFF>Free Text</td>
</tr>"
;

while (
$r = mysql_fetch_array($result)) {
$idx = $r["idx"];
$user = $r["UserName"];
$last = $r["LastName"];
$free = $r["FreeText"];

echo
"<tr>
<td>$idx</td>
<td>$user</td>
<td>$last</td>
<td>$free</td>
</tr>"
;

}
// end of while loop

echo "</table>";

} else {

echo
"No data.";

}
// end of if ($result)

} else {

echo
"No UserName Entered. Please go back and reenter UserName";

}
// end of if ($UserName)

echo "<p></p>";

include (
'links.x');

?>

</body>
</html>


Explanation:

This page consists from two major parts. The first one takes the data from the previous form and inserts it into the database and the second part prints the new table from the database. The second part is identical to what I've
demonstrated in the viewing the database section.

The first part:

First we connect to the database as usual using mysql_connect().

Then we use the following query:

$query = "insert into tbl values ('$idx','$','$LastName','$FreeText')";

This query takes $idx, $UserName, $LastName and $FreeText which were transferred from the previous form and insert them into the table tbl.

Notice that I've used some remarks on the script. To state a remark just use "//" and the server will ignore the rest of the line.

Simple, isn't it ?



Editing an entry from the database:

Let's assume we want to modify an existing entry from the database. We saw earlier that there is a SQL command called set which sets values in existing database cells. We will use this command to modify entries on the database.

Consider the following script:

edit.php3:



<html>
<head><title>Editing an entry from the database</title>
</head>

<body bgcolor=#ffffff>
<h1>Edit an entry</h1

<?
mysql_connect
() or die ("Problem connecting to DataBase");
$query = "select * from tbl";
$result = mysql_db_query("example", $query);

if (
$result) {
echo
"Found these entries in the database:<br>";
echo
"<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00ffff>idx</td>
<td align=center bgcolor=#00FFFF>User Name</td>
<td align=center bgcolor=#00FFFF>Last Name</td>
<td align=center bgcolor=#00FFFF>Free Text</td>
</tr>"
;

while (
$r = mysql_fetch_array($result)) {
$idx = $r["idx"];
$user = $r["UserName"];
$last = $r["LastName"];
$text = $r["FreeText"];

echo
"<tr>
<td align=center>
<a href=\"editing.php3?idx=$idx&user=$user&last=$last&text=$text\">$idx</a></td>
<td>$user</td>
<td>$last</td>
<td>$text</td>
</tr>"
;

}

echo
"</table>";

} else {

echo
"No data.";

}

mysql_free_result($result);
include (
'links.x');

?>

</body>
</html>


As you can see we have some familiar code here. The first part just prints the Database inside a table. Notice there is one different line:

<a href=\"editing.php3?idx=$idx&user=$user&last=$last&text=$text\">$idx</a>

This line creates a link to editing.php3 and transfers some variables to the new script. Very similar to the form, but with a link. We transfer the information in pairs: the variable and its value. Notice that in order to print
the " sign we have to use \" or else the server will interpret it as a part of the PHP script and as printed information.

We want to transfer the complete content of a database table row so we will have the exact data from the table and we will be able to modify it easily.

Editing.php3:


<html>
<head><title>Editing an entry</title>
</head>

<body bgcolor=#ffffff>
<h1>Editing an entry</h1>
<form method="post" action="editdb.php3">

<table width=90% align=center>

<tr><td>idx:</td><td><?php echo "$idx";?></td></tr>

<tr><td>UserName:</td><td><input type=text name=UserName size=40 maxlength=100
value="<?php echo "$user";?>"></td></tr>

<tr><td>LastName:</td><td><input type=text name=LastName size=40 maxlength=100
value="<?php echo "$last";?>"></td></tr>

<tr><td>Free Text:</td><td><input type=text name=FreeText size=40 maxlength=100
value="<?php echo "$text";?>"></td></tr>

<tr><td></td><td><input type=submit value="Edit it!"></td></tr></table>

<input type=hidden name=idx value="<?php echo "$idx";?>">

</form>

<?php include ('links.x');?>
</body>
</html>


Well, this script is rather simple. The only interesting thing is when the form is printed it holds the current cell data which was transferred from the previous page using the value= command within the <input type=> command.

Now if we don't change the information of the cell it will transfer the current value, which is the default one, but if we change the value of the field it will change the value to the new value. Now we can transfer the new values to another script which will change the information on the MySQL table.

editdb.php3:


<?php
mysql_connect
() or die ("Problem connecting to DataBase");

$query = "update tbl set
idx='$idx',UserName='$UserName',LastName='$LastName',FreeText='$FreeText' where
idx='$idx'"
;

$result = mysql_db_query("example", $query);
$query = "SELECT * FROM tbl";
$result = mysql_db_query("example", $query);

if (
$result) {
echo
"Found these entries in the database:<br><p></p>";
echo
"<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00FFFF>idx</td>
<td align=center bgcolor=#00FFFF>User Name</td>
<td align=center bgcolor=#00FFFF>Last Name</td>
<td align=center bgcolor=#00FFFF>Free Text</td>
</tr>"
;

while (
$r = mysql_fetch_array($result)) {

$idx = $r["idx"];
$user = $r["UserName"];
$last = $r["LastName"];
$text = $r["FreeText"];

echo
"<tr>
<td>$idx</td>
<td>$user</td>
<td>$last</td>
<td>$text</td>
</tr>"
;

}

echo
"</table>";

} else {

echo
"No data.";

}

mysql_free_result($result);

include (
'links.x');

?>


Basically, the only interesting thing about this is the following line:


$query = "update tbl set
idx='$idx',UserName='$UserName',LastName='$LastName',FreeText='$FreeText' where idx='$idx'";



Notice that this is the same syntax that was explained earlier at the MySQL section. Another thing, note that this script changes the rows in the table where idx=$idx, if we have more than one row with the same idx then all the rows with the corresponding idx will be changes. If we want to be more specific we can change the where statement to the following:


$query = "update tbl set idx='$idx',UserName='$UserName',
LastName='$LastName',FreeText='$FreeText' where idx='$idx'
and UserName='$UserName' and LastName='$LastName' and
FreeText='$FreeText'";


This syntax will check all the cells and not only idx.



Deleting a record from the database:

Well, deleting is easy. Again, we have two scripts: one to select the row to be deleted (basically the same as selecting the row to be edited as explained above) and the second to actually delete the row and to print the new table.

del.php3:


<html>
<head><title>Deleting an entry from the database</title>
</head>

<body bgcolor=#ffffff>
<h1>Del an entry</h1>

<?
mysql_connect
() or die ("Problem connecting to DataBase");
$query = "select * from tbl";
$result = mysql_db_query("example", $query);

if (
$result) {
echo
"Found these entries in the database:<br><p></p>";
echo
"<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00ffff>idx</td>
<td align=center bgcolor=#00FFFF>User Name</td>
<td align=center bgcolor=#00FFFF>Last Name</td>
<td align=center bgcolor=#00FFFF>Free Text</td>
</tr>"
;

while (
$r = mysql_fetch_array($result)) {
$idx = $r["idx"];
$user = $r["UserName"];
$last = $r["LastName"];
$text = $r["FreeText"];

echo
"<tr>
<td align=center>
<a href=\"dele.php3?
idx=$idx&UserName=$user&LastName=$last&FreeText=$text\">$idx</a></td>
<td>$user</td>
<td>$last</td>
<td>$dtext</td>
</tr>"
;

}

echo
"</table>";

} else {

echo
"No data.";

}

mysql_free_result($result);
include (
'links.x');
?>

</body>
</html>


This is a very familiar script we used it the edit section, so see the explanation there.

dele.php3:


<?php

mysql_connect
() or die ("Problem connecting to DataBase");
$query = "delete from tbl where idx='$idx' and UserName='$UserName' and
LastName='$LastName' and FreeText='$FreeText'"
;

$result = mysql_db_query("example", $query);
$query = "SELECT * FROM tbl";
$result = mysql_db_query("example", $query);

if (
$result) {
echo
"Found these entries in the database:<br><p></p>";
echo
"<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00FFFF>idx</td>
<td align=center bgcolor=#00FFFF>User Name</td>
<td align=center bgcolor=#00FFFF>Last Name</td>
<td align=center bgcolor=#00FFFF>Free Text</td>
</tr>"
;

while (
$r = mysql_fetch_array($result)) {
$idx = $r["idx"];
$user = $r["UserName"];
$last = $r["LastName"];
$text = $r["FreeText"];

echo
"<tr>
<td>$idx</td>
<td>$user</td>
<td>$last</td>
<td>$text</td>
</tr>"
;

}

echo
"</table>";

} else {

echo
"No data.";

}

mysql_free_result($result);

include (
'links.x');

?>


This script is also very familiar, the only new thing is the delete query syntax:


$query = "delete from tbl where idx='$idx' and UserName='$UserName'
and LastName='$LastName' and FreeText='$FreeText'";



This query deletes a row then all the cells match the information transferred from the previous script. Easy.

Well, that's all for now folks.

Questions and remarks will be accepted gladly.

Rafi Ton, [email protected]









How TO Install PHP, Apache and MySQL on Linux or Unix
Categories : PHP, MySQL, Apache, Installation, Beginner Guides
How To add paging (Pagination) with PHP and MySQL
Categories : PHP, Beginner Guides, Databases, MySQL, HTML and PHP
Counting - Creating a more sophisticated GIF based counter using PHP and MySQL
Categories : Beginner Guides, MySQL, PHP, To PHP, To MySQL
Creating an IE-Only Database Driven Menu System With PHP, MySQL and DHTML
Categories : PHP, MySQL, Databases, DHTML
Case Study: Handling MySQL Growth With a PHP Class
Categories : Databases, MySQL, PHP
PHP, MySQL and Authentication 101
Categories : PHP, Databases, MySQL, Authentication
Building A Persistent Shopping Cart With PHP and MySQL
Categories : PHP, MySQL, Databases, Ecommerce
Practical Date and Time examples with PHP and MySQL
Categories : Databases, MySQL, PHP, Date/time
Start Using MySQL
Categories : MySQL, Databases, To MySQL, Beginner Guides
Alternating row colors with PHP and mySQL
Categories : PHP, Databases, MySQL, HTML and PHP
Miles To Go Before I Sleep...
Categories : PHP, Calendar, Databases, MySQL
Multicolumn Output from a Database with PHP
Categories : PHP, Databases, HTML and PHP, MySQL
Jump Start to Easy URLs
Categories : PHP, Beginner Guides, MySQL, File System, To PHP
Simple Connection to MySQL with PHP
Categories : PHP, MySQL, Databases
Date Arithmetic With MySQL
Categories : PHP, Databases, MySQL, Date Time
Richard Still wrote : 128
Clear and concise. I went from zero to database site in just a couple of hours. Thank you very much!
Ryan Sinn wrote : 129
clear and concise information...
excellent explaination and code

converted it to MSSql in no-time.
Anonymous wrote : 134
Wow man~. The best tutorial I even read... Even for a
Chinese (Me) and for a young child (me). Seriously the
best on the net. Great tutorial- Good that it`s free!
Anonymous wrote : 135
Good!!
Kevin Chan wrote : 137
Hello, I just kinda fallowed this code to write most of
my php programs, but then I foudn out a problem,
how can I make it so that when people enter "<" it
converts it into proper html code instead of making
it html? exmaple, if someone types </tr> in my
table, it totally screws it up.. anyway to fix it?
Boaz Yahav wrote : 138
instead of using < and > you simply use < and >
Boaz Yahav wrote : 139
Do I feel sheepish now :)

instead of < use & l t ; (with out the spaces i put so you
can actually see this)

instead of > use & l g ; (with out the spaces i put so you
can actually see this)

Lord Mansfield wrote : 143
Simply the best. Stright to the point and excellent coding. Thanks!
Anonymous wrote : 146
Excellent tutorial! Priceless because of those
installation instructions for windows, everything else
out there is for UNIX!
Surine Yuen wrote : 155
I think your tutorial is great, but I have a problem
with editing. The problem is that it would allow me to
edit a record when I have spacing in between word
(i.e. if there is spacing in the FreeText field, it
would allow you to edit it).

Please can you give me any advice or solutions to this
problem asap.


Anonymous wrote : 157
Excellent tutorial!

However..why can`t I actually see data in the
UserName, Last Name and Free Text columns?
I can see data in the idx column though!

Thanks!

p.s. I`m using PWS on Win98.
Anonymous wrote : 158
thank you so very, very much, ton. your tutorial is great for those of us still stuck on windose.
Jamie Fowles wrote : 170
well done help me loads i always look back to this page
m rishea wrote : 172
I am running Win200Pro. I am using OmniHTTPd/2.08
and it is working. But I cannot get MySql which is mysql-
3.23.35a-win.zip to actually connect to localhost. I
appreciate any help.
Anonymous wrote : 179
Good for mysql and php beginner !!
Anonymous wrote : 184
Thank you TON...

This is the best Tutorial about PHP & MySQL in
action that I have came across.

As a Newbie, I`m very surprised to have installed
and run the scripts you placed here without much
problem on my virtual server.

I`m wondering how to:
1) sort the records by just clicking the table headings
2) control the number of records displayed per page
3) place links to several record pages
4) incorporate a simple search into it.

Thanks for the magnificent tutorial.

Azman
nesli Kunduz wrote : 188

Hi I have a problem in editing an entry in the DB.
my entry in DB looks like
`A & B` but when I pass parameter
from edit.php3 to editing.php3 I get only `A` shown
and `& B` part is lost.
Any help will be highly appreciated
Anonymous wrote : 189
Hi Ton,

I managed to incorporate this script with search
function, control number of displays per page and
sort the display according to the table headers after
studying all your tutorials.


Thank you

Azman

Anonymous wrote : 207
In the php-sql tutorial there seems to be no support for php
installation on windows ME
Anonymous wrote : 210
Thanks Ton i made a song lyrix page that uses html only
for design.Its www.zone.ee/lyrix

Quetion :how do you make php script to check if a string
is in the external page and then return the answer in
true/false string
Anonymous wrote : 212
Hey, Ton.

This is EXACTLY what I needed. Thank you for a clear
and concise tutorial.
Adam Symonds wrote : 213
I have used this tutorial, the guest book tutorial
(http://www.weberdev.com/ViewArticle.php3?
ArticleID=26) and the simple search tutorial
(http://www.weberdev.com/ViewArticle.php3?
ArticleID=81) and I have everything working but the
edit secion and the delete section.
With the delete section it going through alreat but it
comes up again without removing anything and the edit
section I keep getting a error message to look at line 51
which is: mysql_freeresult($result);.

Anyone have any thoughts/suggestions?

Thanks
Anonymous wrote : 215
I get an error message saying unable to connect to
database server what could be the problem.
Anonymous wrote : 234
If I want to put my database on a server located at
www.xxxx.xxx, how can I do it?
Anonymous wrote : 236
I`m really pleased with this tutorial. I`ve only been
working with PHP for couple of days and this was really
helpful. It took me couple of hours to get through all of
this though, as I was working with another table that
had around 9 fields. But now everything is working just
fine, just have to fix the layout on my page a little bit.

Thanks alot and keep up the good work.
Matt Martin wrote : 238
very good tutorial! I like the fact that someone actually
bothered to show us the proper commands and bits of
pieces like that!! much better than webmonkey.com!
Matt Martin wrote : 241
How do you alphabetically sort the data once it has
been pulled from the database? I got told something
like this "select sort by (field) from table;" is this correct?
Josh Rowell wrote : 243
This is a great beginner`s tutorial. It tells you the basics
of both PHP and MySQL without talking in circles. This is
much better than any other tutorial I`ve found so far.
Anonymous wrote : 250
GREAT TUTORIAL!!
but got a problem, almost everything works fine but in
the index.php file it is only showing the idx row,
everything else he is not showing??
can some one help me with this??
Anonymous wrote : 252
I appreciate tutorials that don`t assume I`m a complete
idiot. Didn`t have to wade through lots of obvious
nonsense, nice and concise. Good job, thanks!
Martin Temny wrote : 260
Great tutorial, thanks to it I made my first working
PHP&MySQL system.:-)

But this cannot works:

$query = "update tbl set
idx=`$idx`,UserName=`$UserName`,
LastName=`$LastName`,FreeText=`$FreeText` where
idx=`$idx`
and UserName=`$UserName` and
LastName=`$LastName` and
FreeText=`$FreeText`";

because it will be changed only in row, where is now
changes, it means it could be change but only to the
some data.:))
Wesley Hegarty wrote : 270
i see this has been mentioned before but i cannot get
the UserName or the Last Name to display. idx and free
etxt display fine. does anyone know a reason for this?
Even with this slight flaw i found this a very helpful
place to start using php and databases. Exelent tutorial.

Wes
Rodel Nagales wrote : 271
It`s a great tutorial for newbies but I like to ask a
question. How come whenever I input a blank space
in between characters it doesn`t display the text that
I`ve typed in. I was able to view it in mysql and was
successfully entered but whenever I want to edit a
certain field on the html form it doesnt display any
data on the text box. I use IE5 and its ok but when
I use netscape its not displaying the characters that
I`ve typed in originally whenever a certain field
contains blank spaces !!! I`m using linux as a server
for this application.
Can you help me with this? I will appreciate your
comment on this.
Anonymous wrote : 273
Well done that man. Thanks very much!
Felix Hrdlicka wrote : 279
in your tutorial you explain the installation of a
webserver, that can be downloaded under
www.omnihttpd.ab.ca this page is down,
unfortunately. Only an expired version of this webserver
can be found on the net.
peter watson wrote : 290
Many thanks for a fantastic tutorial....one of the few
that hasn`t left me going `huh?` at some point along
the way. Clear and concise but with everything
important included....what else does a newbie need?
Anonymous wrote : 291
Great place to start for a beginner like me. I was able
to get a simple DB up and running on my ISP in about 4
hours. That included the display and add function. Next
step the edit and delete function. Thank you for the
great tutorial.
Anonymous wrote : 293
Was wondering if someone might update this info?
It appears that many have found this tutorial highly
valuable -- however, I`m finding it dated, and having a
number of problems which are requiring running around
in circles. Biggest hurdle right now is finding OmniHTTPd,
www.omnicron.ab.ca gets: unable to locate server.
Boaz Yahav wrote : 294
The OmniHTTPd download URL was updated.
glen2s y wrote : 303
It`s very nice ,I have seen! Thanks!
Anonymous wrote : 309
Loved yer tutorial, first one that makes sense...
unfortuanly like all tutorials before it doesn`t work
completly for me...

I can view the records, but not add one with out saying
the User name is not completed... or edit a record or
delete... close but something I`m doing is missing...
Randy Krakowski wrote : 313
Can`t seem to get the "Beginners guide to PHP and
MySQL" to work. I did notice that php client that I
downloaded had some php4 files, where as this tutorial
seemed to be instructing about php3. I am wondering if
there is a conflict there.

When I test my php file on localhost, I get a "Security
Alert!", telling me that "The PHP CGI cannot be
accessed directly. ".

Sincerely,
Randy Krakowski
Torin Jensen wrote : 318
Great introduction to PHP and MySQL! It is true however
that some of the information about third party programs
like OmniHTTPd is old and could be updated to make the
tutorial completly perfect.

A few people asked something like "How do you sort
the data", one way to sort the entries according to their
idx number could be to replace the line:

$query = "select * from tbl";

with

$query = "select * from tbl order by idx ASC";

- idx could of course also be any of the other coloumns
and ASC can be substituted by DESC making the order
the opposite.
Torin Jensen wrote : 324
"How come whenever I input a blank space
in between characters it doesn`t display the text that
I`ve typed in."

Ton Rafi suggests:
<td>UserName:</td><td><input type=text
name=UserName size=40 maxlength=100
value="<?php echo "$user";?>"></td>

But if you are doing it with echo like:

echo "<td>UserName:</td><td><input type=text
name=UserName size=40 maxlength=100
value=$user></td>";

only the first word in $user is shown as value in the text
field beacause html doesn`t get that the second word is
also part of the value.
You can see that it is a html/browser mixup by viewing
the source of the document when it only displays the
first name. If the $user variable was "Torin Jensen" the
source of the code above after being fetched from the
server would be:

<td>UserName:</td><td><input type=text
name=UserName size=40 maxlength=100
value=Torin Jensen></td>

To make the browser understand that both Torin and
Jensen should be part of the value it should be put
between either " " or ` ` - but since php would make an
error if you used " " due to the echo command you have
to use ` `. The code would then be:

echo "<td>UserName:</td><td><input type=text
name=UserName size=40 maxlength=100
value=`$user`></td>";

hopefulle that wasn`t too confusing.
steve newman wrote : 325
this is a good tutorials but the coding is just a lil bit out
of date for the latest version of php :) apparently the
function mysql_db_query is deprecatated. but the
functions it provides to use instead (mysql_select_db()
and mysql_query() ) just seem to make things worse.
the code worked great and all using mysql_db_query()
but there were warnings and notises. i got fed up in the
end and dicided to turn debug messages off :)
owen fowke wrote : 327
as a newbie to PHP & MySQL I found this tutorial very
informative and helpful.

however I seen to be having a problem.
no matter what I try I cannot get PHP to carry the
variables over to the next page or insert the data into
the tables.
I can retreive the data no problem at all.

I am running an apache 2.0 server with PHP 4.2.2 &
MYSql 3.23

I have duplicated all of your example PHP pages exactly
as well as the datadases`s & tables and set appropriate
user permissions within mysql
I suspect it is an issue with the apache server security
settings, however I cannot find any more info on how to
change these
Any help would be very much appreciated.
Daniel Elfving wrote : 334
To Owen...

Add this to your code:
$idx=$_REQUEST[`idx`];
$UserName=$_REQUEST[`UserName`];
$LastName=$_REQUEST[`LastName`];
$FreeText=$_REQUEST[`FreeText`];
Daniel Elfving wrote : 335
...and read this:
http://www.weberdev.com/AddArticleComment.php3?ArticleID=22

I hope this will help...
Daniel Elfving wrote : 336
Oups... Cut and paste... =)

Read this...
http://www.php.net/manual/en/language.variables.predefined.php

I hope it will help...
Iliana Ruiz wrote : 339
To autor, Krakowski and Owen:

This is the BEST Tutorial i have found, but it`s true there
are differences with PHP4. That`s the reason why when
you try to add a register it does not recognize the
variables.

Try adding:

import_request_variables("gP", "r_");

after
<body>
<?

at file add2tbl.php3

The reference to form variables must be changed also
from:
$UserName
to:
$r_UserName

$r_idx, $r_LastName, $r_FreeText and so on
watch not to change table variables.

You can also rename all references and archives from
php3 to php

It was adecuate to me.

keith gosnell wrote : 340
Great tutorial, very helpful....but....
I keep getting "variable undefined" when I try to edit or
add entries!
These are the lines I keep getting errors for on the
editing page:

$query = "update tbl set
idx=`$idx`,UserName=`$UserName`,LastName=`$LastNam
e`,FreeText=`$FreeText` where
idx=`$idx`";

also on the add page:

if ($UserName)

this comes back as undefined as well?! Im puzzled can
anyone offer help?! If further explaination is needed I
would be happy to corespond through email!
Thanks
Keith
Susan Moore wrote : 342
I give up!

I followed the instructions, naming things ...php4 instead
of ...php3. The Web server came up fine, but I coudl NOT
get PHP running.

I`ll just pay someone to run my Web site!
Sergio Cruz wrote : 350
Thank you very much, very helpful!
With the omnicron server installed and running how do I get
Frontpage to recognize it, so that I can see an HTML
database file on the local server browser?
Thanks again for your help!
Sergio
Mark Arnold wrote : 352
At last a PHP/MySQL tutorial written in PLAIN ENGLISH! and
devoted to Windows users too. I don`t profess to be a
computer wizard and I`m a total novice when it comes to
Unix and Linux, so finally after weeks of searching, I
finally found a down to earth tutorial. Learning something
like this isn`t hard if it is explained clearly. We all
remember a good teacher and on the Net, I`ve now found a
good teacher.
Ea Efkar wrote : 354
Thanks a lots!. Your tuturial is absolutely excellent...it
just works when i finished all the tutorial...but the
point is i just wondering why my own can`t work...i just
copy your file and edit it the way i understand the
scripts but it just not work in proper order....

I changed the database...i create the new one...named
investment...and the table is called form and a lot of
data in there...how can i view the data vertically instead
horizontally...pls give an example of scripts to view data
in various ways...pls..i`m desperately need it for my
system project...

one more things...i just failed to edit and delete the
data...i can`t edit the data and delete it...the links are
good but when i want to delete the data i can`t select and
choose the data that i desire to delete...pls help me...
soeren pedersen wrote : 355
Great tutorial. Easy and understandable for any newbie

BTW It`s easy to transform the scripts in the article to
PHP4, just alter the extentions from .php3 to .php, it
works just fine with apache, anyway

Keep it up, the good job
christophe s wrote : 357
Fisrt of all, great tutorial, helped me a lot.

Now question : is it normal that when you change your IP
adress because you are connected to another network, your
mysql server doesn`t run anymore. It says unable to connect
to MySql server on `localhost` (10061)

some info would be handy, tanx.
Cheryl Kilpatrick wrote : 359
I have found the code very good. However having problems
with the edit function. It will not write back the edits
to the DB. Also having some caching problems. If anyone
has any ideas or hints. Please let me know. Thanx.
Wally Long wrote : 361
Awesome tutorial! I`ve searched dozens of web pages, and
couldn`t figure out the edit and delete functions. Your
page had me up and running in a couple hours.
Manish Jani wrote : 365
Excellent tutorial ! I need to clarify something though. I am new
to database and hence I am not sure if I should be pointing to it
as an error. At the end of the editdb.php section, you have
mentioned an alternate way to update the DB so that all
records with the same idx do not get updated. For a quick
reference, your section follows:
======================================
Notice that this is the same syntax that was explained earlier
at the MySQL section. Another thing, note that this script
changes the rows in the table where idx=$idx, if we have more
than one row with the same idx then all the rows with the
corresponding idx will be changes. If we want to be more
specific we can change the where statement to the following:

$query = "update tbl set idx=`$idx`,UserName=`$UserName`,
LastName=`$LastName`,FreeText=`$FreeText` where idx=`$idx`
and UserName=`$UserName` and LastName=`$LastName` and
FreeText=`$FreeText`";
======================================

I am doubtful if this code would work to its expected effect. My
reason is that the values referred by the $UserName and
respective variables reference the values updated by the user.
In this case, the where clause shall never match any of the
records in the database, because the database contains the
unedited values, while the variables contain the updated
values.

Please do clarify on this. I am pretty new to both PHP and
MySQL and hence if I am under a misconception it would help
me learn better.

Many thanks for the wonderful tutorial for novices to the world
of PHP and MySQL.

Warm regards,

Manish Jani
niaz ahmed wrote : 369
Hi iam niaz... this is my first attempt to PHP. this website really
works nice.... thank u
S. M. Ibrahim Lavlu wrote : 386
I can`t belive that, I read this tutorial for free. Great job.
Tom Rice wrote : 392
Wow that is a fantastic guide! I wanted to start building a
database for my website but knew practically nothing about
php/mySQL. Through reading through your guide I've managed
to learn a lot of the basic commands for drawing data from
and inputting data into databases. I copied your code onto
my site, and through your instructions I was able to adapt
it to do exactly what I wanted. Thanks for writing this
great guide, it's been an excellent help and I've learnt a
lot from it. Good work.
Michael bayani wrote : 403
hi there.. thanks god i found your tutorials. anyway things is
working great . but when im at the edit section.. when i try to
edit the entry.. it doesn't show any value at the form.. all the
fileds are blank.. its seem that the value from the edit page
doesn't get by the editing page..
can you pls help me with thiss..