PHP MySQL functions

admin

17 March 2022

Comments Off

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>

<body>

</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.

webmastertools4u