PHP MySQL functions
How to add,
extract, update or delete data
Well, now that our database table "mytable" is
created (see previous tutorial), let's learn how to input, extract or update the data. Let's
assume we have the following HTML form: <html>
<head>
<title>HTML form</title>
</head>
<body>
<form method="POST" action="mysql.php">
<p>Your name: <input type="text" name="name" size="20"></p>
<p>Your e-mail: <input type="text" name="email" size="20"></p>
<p><input type="submit" value="Go!"></p>
</form>
</body>
</html> You can save it as form.html. The
ACTION of the above form points to mysql.php file that will
contain the PHP script. When the visitor types in their name and e-mail
within the HTML form, and presses the "Go!" button, mysql.php
file will be called. Let's suppose we want to insert the user's
information into the MySQL database table "mytable" from the
previous section. The mysql.php would be: <?php
$dbname = "databasename";
$tablename = "mytable";
mysql_connect("$hostname", "$username",
"$password") or die("Unable to connect to database");
@mysql_select_db("$dbname") or die("Unable to select
database");
$query = "INSERT INTO $tablename VALUES ('$name', '$email')";
$result = mysql_query($query);
print "Dear $name. Your name and e-mail ($email) has been inserted
into our database.";
mysql_close();
?> Well, now let's find out how to extract the
data from the MySQL database. If we have a considerable amount of
information and want to print out the data of all Johns: $query = "SELECT * FROM $tablename WHERE name = 'John'";
$result = mysql_query($query);
$num = mysql_num_rows($result);
$i = 0;
IF ($num == 0) :
PRINT "No data";
ELSEIF ($num > 0) :
PRINT "Data that matched your query: $num<BR>";
WHILE ($i < $num) :
$name = mysql_result($result,$i,"name");
$email = mysql_result($result,$i,"email");
PRINT "The e-mail of $name is: $email.<BR>";
$i++;
ENDWHILE;
ENDIF; As you can see we have two new MySQL
functions here: mysql_num_rows()
- this function gets number of rows in result ($result).
mysql_result() - this function
returns the contents of one database cell. Recommended
high-performance alternatives for SELECT statements: mysql_fetch_row(),
mysql_fetch_array(). You
can also easily update or delete the data in your database using mysql_query()
function: $query = "UPDATE $tablename
SET name = "Smith" WHERE name = "John";
$result = mysql_query($query); or if you want to
delete any of the data: $query =
"DELETE FROM $tablename WHERE name = "John";
$result = mysql_query($query);
Well, now you can write your own PHP scripts. Of course, this is a small
piece of what PHP is capable of. For more information, news and
documentation about PHP and MySQL visit http://www.php.net
and http://www.mysql.com.
|
PHP functions