Home arrow PHP arrow Page 2 - Website Database Basics With PHP and MySQL

HTML talks to PHP talks to MySQL - PHP

The World Wide Web (WWW) does only one thing - provide information. If you have information about something, you can share it with the world by building a website. As your website grows you may run into two problems: Your website has so much information that visitors can't quickly find what they want and visitors want to give you information. Both of these problems can be solved by building a database on a website. This introductory article shows you how to do this using basic PHP-MySQL interaction.

TABLE OF CONTENTS:
  1. Website Database Basics With PHP and MySQL
  2. HTML talks to PHP talks to MySQL
  3. Verifying form data
  4. Using cookies to identify and track visitors
  5. Weird SQL: What The Books Don't Tell You
  6. Checkboxes and other HTML form processing
By: Thomas Kehoe
Rating: starstarstarstarstar / 100
January 11, 2000

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
  1. Mixing HTML and PHP
  2. PHP prints to HTML
  3. PHP submits data to MySQL
  4. PHP retrieves data from MySQL
  5. Testing whether your query worked

Mixing HTML and PHP

Let's examine how helloworld.php worked. I assume you know the basics of HTML, so the first two lines and the last two lines should be familiar to you:


<html> <body> <?php print "Hello, world."; ?> </body> </html>


<html> means that this is an HTML document, intended to be read by a browser such as Netscape or Internet Explorer.

<body> sets out the body of the HTML document, which is displayed to the user (as opposed to the header's invisible information).

</body> and </html> close the body and the HTML document.

Alternative means to the same end

helloworld.php is an HTML document with a embedded PHP script. We could alternatively write a PHP script with embedded HTML commands:


<?php print "<html>"; print "<body>"; print "Hello, world."; print "</body>"; print "</html>"; ?>


Either way produces the same result. With some PHP functions (e.g., cookies) you have to use the latter method (PHP scripts with embedded HTML).

PHP prints to HTML

The simplest way for PHP to talk to HTML is to throw some text onto the browser. We did that with the PHP script:


<?php print "Hello, world."; ?>


Let's go over the print function more carefully.

PHP scripts always begin with <?php. You can also use <? but some applications (e.g., XML, FrontPage) prefer the full <?php. A PHP script is closed with ?>.

This PHP script has one line:


print "Hello, world.";


PHP lines always end with a semi-colon (;).

print is the PHP function that sends text to the browser. Between the print and ; we put a string (strings are text to be read literally). Strings are denoted by quotation marks. Whatever is between the quotation marks will be sent to the browser.

Alternative means to the same end

There are several other ways to send "Hello, world." to your browser. The following file is print.php.




<html> <body> <?php print "This uses the print function."; print "<p>"; echo "This uses the echo function.", " ",; echo "P.S. You can add a second string", " ",; echo "if you separate strings with a comma."; print "<p>"; printf ("This uses the printf function."); print "<p>"; printf ("The printf function is mostly used to format numbers."); print "<p>"; printf ("Remember the parentheses with printf."); ?> </html> </body>


print.php produces the following output:

This uses the print function.
This uses the echo function. 
P.S. You can add a second string if you 
separate strings with a comma.
This uses the printf function.
The printf function is mostly used to format numbers.
Remember the parentheses with printf.  


print is the simplest function for throwing text onto the browser window.

echo is like print but you can add additional strings, separated by commas.

printf will format numbers as integers, scientific notation, etc. printf requires parentheses.

Parentheses are an issue with the three print functions:

  • echo must not have parentheses.
  • printf must have parentheses.
  • print works with or without parentheses.


Printing strings and numbers is easy, but how do you print arrays? (Arrays are sets of things.) If you try



print $myarray;



the result will be Array, in other words, PHP informing you that $myarray is an array. This is useful when you're not sure whether a variable is an array, but doesn't help when you want to see the contents of the array.

You could use the implode function to convert the array into a string, then print the string. The first argument is the array, the second argument is a delimiter to separate the array elements:



$implodedarray = implode ($myarray, ", "); print $implodedarray;



Another way to print an array uses array_walk. This function executes a function on each element in an array. You must create the function to be executed — you can't use a PHP function such as print:



function printelement ($element) { print ("$element<p>"); } array_walk($myarray, "printelement");



PHP submits data to MySQL

I assume you know something about HTML forms. The following submitform.html is very simple:


<html> <body> <form action=submitform.php method=GET> First Name: <input type=text name=first_name size=25 maxlength=25> Last Name: <input type=text name=last_name size=25 maxlength=25> <p> <input type=submit> </form> </body> </html>


submitform.html produces the following
form:



First Name:
Last Name:





When you enter data in the fields and then click Submit, the form sends the data to submitform.php. Here is submitform.php:


<html> <body> <?php mysql_connect (localhost, username, password); mysql_select_db (dbname); mysql_query ("INSERT INTO tablename (first_name, last_name) VALUES ('$first_name', '$last_name') "); print ($first_name); print (" "); print ($last_name); print ("<p>"); print ("Thanks for submitting your name."); ?> </body> </html>


In line 3, you must replace "username" and "password" with your username and password. In line 5 you must replace "dbname" with the name of your MySQL database. In line 13 you must replace "tablename" with the name of one of your MySQL tables.

If you open submitform.html, enter a name, and press Submit, you should see the name you entered printed on a new webpage. Also note that in the browser URL address window, the URL includes the two fields, looking something like:


…/submitform.php?first_name=Fred&last_name=Flintstone
Because we used the GET form method, the fields are passed from submitform.html to the URL header of submitform.php. PHP then automatically creates variables for each input field name passed from the HTML form. PHP variables always start with a dollar sign, so the variables are $first_name and $last_name.

Check that your entered name was successfully entered into your database. Open MySQL, and at the mysql> prompt, enter


select * from tablename;


You should get a table showing the name that you entered:


+------------+------------+ | first_name | last_name | +------------+------------+ | Fred | Flintstone | +------------+------------+ 1 rows in set (0.00 sec)


Let's examine how submitform.php works:

The first two lines are:


mysql_connect (localhost, username, password); mysql_select_db (dbname);


These two functions open the MySQL database.

The next line does the work:


mysql_query ("INSERT INTO tablename (first_name, last_name) VALUES ('$first_name', '$last_name') ");


mysql_query enables PHP to throw SQL commands at the MySQL database. You can put any SQL commands after the mysql_query function. The SQL commands must be enclosed in parentheses and quotations.

Here's a weird thing: MySQL lines end with a semi-colon (;) and PHP lines end with a semi-colon, but MySQL lines in PHP lines don't end with a semi-colon. In other words, when you're at the MySQL command line you would type in:


INSERT INTO tablename (first_name, last_name) VALUES ('$first_name', '$last_name');


But when included in a PHP line, you leave off the semi-colon. The weird part is that SELECT and INSERT will work with or without the extra semi-colon, but UPDATE won't work. I haven't experimented with DELETE, which is the fourth SQL command.

After that, there are five print lines to display the entered name (separated by a space) and a thank-you closing line (preceded by a paragraph code).

PHP retrieves data from MySQL

Now we'll create another HTML form to search the database. We'll call it searchform.html:


<html> <body> <form action=searchform.php method=GET> Search For: <p> First Name: <input type=text name=first_name size=25 maxlength=25> <p> Last Name: <input type=text name=last_name size=25 maxlength=25> <p> <input type=submit> </form> </body> </html>


You will also have to create the following file searchform.php:


<html> <body> <?php mysql_connect (localhost, username, password); mysql_select_db (dbname); if ($first_name == "") {$first_name = '%';} if ($last_name == "") {$last_name = '%';} $result = mysql_query ("SELECT * FROM tablename WHERE first_name LIKE '$first_name%' AND last_name LIKE '$last_name%' "); if ($row = mysql_fetch_array($result)) { do { print $row["first_name"]; print (" "); print $row["last_name"]; print ("<p>"); } while($row = mysql_fetch_array($result)); } else {print "Sorry, no records were found!";} ?> </body> </html>


Remember to replace "username", "password", "dbname", and "tablename".

When you open searchform.html you should see a form. If you enter a name and click Submit, you should get a new webpage showing the full record or records matching your search terms.

Going over searchform.php carefully, it begins with the familiar lines to open the MySQL database. Then there are four lines:


if ($first_name == "") {$first_name = '%';} if ($last_name == "") {$last_name = '%';}


These lines check if the form fields are empty. The if function is followed by parentheses, and what's in the parentheses is the statement to be tested. The statement $first_name == "" means "The variable $first_name is empty." Note that the double equals signs means "equals". A single equals sign means "assign the value from what's on the right to the variable on the left."

The next line is what is done when the if statement is evaluated as true. What's done is a PHP line, so it ends with a semi-colon. (Note that the if function doesn't end with a semi-colon.) The PHP line is put in curly brackets.

The % is SQL's character string wildcard. (Don't confuse it with SQL's * column name wildcard.) The first two lines together mean "If the 'first name' field is empty, search for any first name." The latter two lines similarly checks if the $last_name variable is empty, it searches for any last name. This enables you to find everyone named "Fred" or everyone named "Flintstone".


$result = mysql_query ("SELECT * FROM tablename WHERE first_name LIKE '$first_name%' AND last_name LIKE '$last_name%'" ");


This line does most of the work. When mysql_query does a QUERY, the result is an integer identifier. The result identifier could be "2".

This query selects all the columns from the specified table, then searches for records in which the "first_name" column matches the "$first_name" variable from searchform.html and the "last_name" column matches the "$last_name" variable. Note the % wildcard: this enables the user to type only the first few letters of a name, e.g, "Flint" finds "Flintstone".


if ($row = mysql_fetch_array($result)) { do { print $row["first_name"]; print (" "); print $row["last_name"]; print ("<p>"); } while($row = mysql_fetch_array($result)); } else {print "Sorry, no records were found!";}


The last part of the script throws the search results onto the new webpage. mysql_fetch_array grabs the first row of the query results. This function's argument is the result identifier ($result). Successive calls to mysql_fetch_array will return the next rows of the query.

The array variable $row is created and filled with the first row of the query results.

If a matching record was found, then the block in the outermost curly brackets is done:


do { print $row["first_name"]; print (" "); print $row["last_name"]; print ("<p>"); } while($row = mysql_fetch_array($result));


This is a do…while loop. Unlike a while loop, the block of code is done, then afterwards a decision is made whether to do the block of code again. A while loop decides first whether to do the block of code.

What is done is inside the nest set of curly brackets:


print $row["first_name"]; print (" "); print $row["last_name"]; print ("<p>");


The integer $row specified which record to print. The first time through, the first "first_name" is printed, followed by a space, and then the first "last_name" is printed, followed by a paragraph mark.

Now we get to the while decision. The MySQL database is called again, using the mysql_fetch_array function. mysql_fetch_array calls the next row until no rows are left. The do block is then executed again.

When no rows are left, mysql_fetch_array returns false, the do…while loop is stopped, and the if function is exited.

Why didn't we just implode the results of mysql_fetch_array($result) and print the imploded string? This results in printing each element twice. mysql_fetch_array($result) allows you to specify elements by the field name (e.g., "first_name" or by the number of the field, with the first field being "0", the second field is "1" and so on. Thus we could have written this block of code as:


print $row[0]; print (" "); print $row[1]; print ("<p>");


We could also shorten these four lines of code into one line:


echo $row[0], " ", $row[1], "<p>";


If no matching records were found, then mysql_fetch_array returns nothing and no value can be assigned to $row, and the else clause is executed:


else {print "Sorry, no records were found!";}



Testing whether your query worked

Did your SELECT, DELETE, or other query work? Good question, and not always easy to answer.

Testing an INSERT query is relatively simple:



$result = mysql_query ("INSERT INTO tablename (first_name, last_name) VALUES ('$first_name', '$last_name') "); if(!$result) { echo "<b>INSERT unsuccessful:</b> ", mysql_error(); exit; }



But that code doesn't work with a SELECT query. Instead, I use:



$selectresult = mysql_query ("SELECT * FROM tablename WHERE first_name = '$first_name' AND last_name = '$last_name' "); if (mysql_num_rows($selectresult) == 1) { print "Your SELECT query was successful."; } elseif (mysql_num_rows($selectresult) == 0) { print "Your SELECT query was not successful."; exit; }



And that code doesn't work with a DELETE queries. Here's how to test those:



$deleteresult = mysql_query ("DELETE FROM tablename WHERE first_name = '$first_name' AND last_name = '$last_name' "); if (mysql_affected_rows($deleteresult) == 1) { print "Your DELETE query was successful."; } elseif (mysql_affected_rows($deleteresult) != 1) { print "Your DELETE query was not successful."; exit; }


 
 
>>> More PHP Articles          >>> More By Thomas Kehoe
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: