form_page.php
< form method="post" action="enter_it.php">
First Name : <input type="text" name="first_name" /><br />
Last Name : <input type="text" name="last_name" /><br />
Phone Number : <input type="text" name="phone_number" /><br />
< input type="submit" name="submit" value="submit" />
< /form>

Once the SUBMIT button is clicked on, the information entered will pass over to the "enter_it.php" page through the $_POST[ ] variable.

enter_it.php
< ?php

// connect to server, database, table.
include ("db_connect.inc");

// add the new information into the database
$query="INSERT INTO address_book (first_name,last_name,phone_number) VALUES ('$_POST[first_name]','$_POST[last_name]','$_POST[phone_number]')";
if(!mysql_db_query($dbname,$query,$link_id)) die(sql_error());
echo "success in database entry.";

echo "<br />";
echo "<a href=\"form_page.php\">Click here to return to the form page.</a>";
?>

To explain the new coding snippit :

$query="INSERT INTO address_book (first_name,last_name,phone_number) VALUES ('$_POST[first_name]','$_POST[last_name]','$_POST[phone_number]')";

We are using a variable $query to contain the command information we want to perform. This keeps our coding easy to maintain.

$query="INSERT INTO address_book (first_name,last_name,phone_number) VALUES ('$_POST[first_name]','$_POST[last_name]','$_POST[phone_number]')";

INSERT INTO prepares the database for new incoming data. address_book is stating which table is going to receive the new information and what columns (by name) will be affected.

$query="INSERT INTO address_book (first_name,last_name,phone_number) VALUES ('$_POST[first_name]','$_POST[last_name]','$_POST[phone_number]')";

The VALUES of the following variables contain the new information to be entered into the specified columns.

if(!mysql_db_query($dbname,$query,$link_id)) die(sql_error());

IF the following value is not true, the script will stop (die) and print out the error.

mysql_db_query is a command used to perform the actual insert command. it uses the database name, query command, and server link to make it all happen.


Query, as in, to ask a question. Working with a database, this concept will be put to very good use.

On the INSERT tutorial page, you've already seen a QUERY in action. The query asked the database to insert the new information into the specified database table.

The SELECT property can be used in a mysql_query command to choose specific information in the database table.

$sql = mysql_query("SELECT * FROM table_name");

The variable $sql is being used to find the information in the address_book table. The * is a symbol meaning "all". It will look at all of the records FROM the specified table.

To look at each row in the specified area, the next step is to use a loop and the mysql_fetch_row command with the SELECT information.

while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2] <br />";
}

WHILE the statement is TRUE, the loop will continue.

The mysql_fetch_row command will take the supplied information and read the data from the next available row of the database.

The ECHO command being used to display the data during each row loop is a simple one for example purposes. The columns of a database are numbered starting at zero. The variable $row is assigned the current row contents during each loop.

david_tes

The WHERE property of a query makes it possible to single out specific rows.

$sql = mysql_query("SELECT * FROM table_name WHERE some equation");
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2] <br />";
}

The query will only pick out the rows that provide a TRUE result according to the WHERE equation. Example...

$sql = mysql_query("SELECT * FROM address_book WHERE first_name='David'");

while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2] <br />";
}

The query would search the address_book table and compare all of the data in the first_name column for the specified value. The WHILE loop then prints out the results found.

The equation used for the WHERE property can be a large number of possibilities. You can use variables, additional equations with &&, alternative equations with ||, and so on.

CONDITIONALS are operators which allow you to get more specific (or general) in your query.

= equals
< less than
> greater than
< = less than or equal to
> = greater than or equal to
!= not equal to
IS NOT NULL has any value
IS NULL has no value at all
BETWEEN within a specified range
NOT BETWEEN outide of a specified range
OR one of two equations are true
|| same as OR
AND both equations are true
&& same as AND
NOT both equations are false
! same as NOT

$sql = mysql_query("SELECT * FROM table_name WHERE some equation && another equation");

while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2] <br />";
}

The query will only pick out the rows that provide a TRUE result according to the WHERE equation. In this case, there would be TWO parts that must be true to return a result. Example...

$sql = mysql_query("SELECT * FROM address_book WHERE first_name='David' && last_name='Stanley'");

while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2] <br />";
}

The query would search the address_book table and compare all of the data in the first_name column AND the last_name column for the specified values. The WHILE loop then prints out the results found if both equations are found TRUE.

Doing a search through data is easy enough when the equations produce a definite yes or no situation. There may be times though you will want to perform a more general search.

The LIKE and NOT LIKE have two search helper symobls. The underscore _ character that looks for one character and the percentage % character that looks for zero or more characters.

$sql = mysql_query("SELECT * FROM table_name WHERE columnname LIKE value%");

while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2] <br />";
}

The query will only pick out the rows that provide a TRUE result according to the WHERE equation. The equation will equal the LIKE VALUE plus some possible extra characters afterwards. Example...

$sql = mysql_query("SELECT * FROM address_book WHERE last_name LIKE 'Stan%'");

while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2] <br />";
}

The query would search the address_book table and compare all of the data in the last_name column for any values starting with 'Stan' and ending with zero or more characters afterwards. The LIKE search is not case sensitive, so it will accept anything starting with 'stan' as well. The WHILE loop then prints out the results found if both equations are found TRUE.

Queries using the LIKE or NOT LIKE parameters may be a bit slower than a normal query search considering they are a broader value and do not take advantage of any indexing.

The underscore and percentage characters (also known as wildcard characters) can be used in front, at the end, or both ends of a value.

If you want to have an underscore or percentage character actually be part of the search value, put an escape slash \ in front of the character.

The underscore wildcard can be used a number of times to find a specific number of characters. Example, this would be used in an equation to return a value of 'Stan' plus 3 characters (since there are 3 underscores)...

$sql = mysql_query("SELECT * FROM address_book WHERE last_name LIKE 'Stan___'");

while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2] <br />";
}

As you may have guessed, the NOT LIKE parameter will do the opposite of LIKE. It will produce results that are NOT LIKE the specified criteria.

Many people refer to a database as a "relational" database. More than one table is in relation to another table. Two smaller pieces of information that make a whole when combined.

There are going to be times when you will want to take a bit of information from two (or more) tables with a certain criteria. Using an address book for example, you may have one table of friends and another table of how much money they owe you. You can set the criteria to find a specific criteria that appears in both tables and produce the results. You are "joining" the information from both tables to create a full result.

A popular join is called the INNER JOIN.

$sql = mysql_query("SELECT * FROM phone, owes WHERE phone.first_name=owes.first_name");

while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row [1] $row[2] $row[3] <br />";
}

The above example will compares or joins the two tables PHONE and OWES. It will look in the column called FIRST_NAME in the PHONE table and it will look in the column called FIRST_NAME in the OWES table. If any information matches in those two columns in the seperate tables, it will be put into the results.

In total, there are 4 columns of information being looked at. The above example is printing out all of the columns just to show which information is being passed into the results. You may choose exactly which data to use and display as required for a real application.

The following results would be created...
Chris 418.2594 Chris 5
Lorna 942.4827 Lorna 5
Chris 418.2594 Chris 2

The important part of an INNER join is that if no matches are found, no results are created.

Another form of join is called the LEFT JOIN. It behaves in the same way as the inner join, but it will produce extra results if there is a match in one table but not the other.

$sql = mysql_query("SELECT * FROM phone LEFT JOIN owes ON phone.first_name=owes.first_name");
while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2] $row[3]<br />";
}

The comma between the table names have been replaced by the words LEFT JOIN. The word WHERE has been replaced by the word ON.

During an inner join, only records matching in both tables would be placed into the results. During a left join, all of the records of the first table will be placed into the results. If the records match in the second table, they will be added into the results otherwise a NULL result will be added.

The results of a LEFT JOIN using the above database information would produce these results...
Christine 516.6785
Chris 418.2594 Chris 5
Chris 418.2594 Chris 2
David 725.1581
Jennifer 458.2158
Lorna 942.4827 Lorna 5

Again, the example is printing out all 4 columns of pertinent information. In a real application, you would probably just print out the essential columns.