So far you have learnt how to put information into your MySQL database, view the information in it and select which information you would like to view. In this part I will show you how to do the two final actions, updating your database and deleting records from it.
The Update Script
Last week I explained how to create a link for each record to point to your update script. By using the $id variable you output links which would pass the correct ID to the script so that it can update the database. Using this you can then create the update script, which will actually have two sections to it.
Displaying The Update Page
The first part of the update script uses the single record selection from last week but adds a little HTML to it to make it more useful. First of all, we connect to the database and select the appropriate record.
$id=$_GET['id'];
$username="username";
$password="password";
$database="your_database";
mysql_connect(localhost,$username,$password);
$query=" SELECT * FROM contacts WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) { $first=mysql_result($result,$i,"first"); $last=mysql_result($result,$i,"last"); $phone=mysql_result($result,$i,"phone"); $mobile=mysql_result($result,$i,"mobile"); $fax=mysql_result($result,$i,"fax"); $email=mysql_result($result,$i,"email"); $web=mysql_result($result,$i,"web"); Space For Code ++$i; } Where 'Space For Code' is in this script is where the code for the update page will go. This is, in fact, just HTML formatting for the output:
As you can see, this code will output a standard form, but instead of having blank boxes like on the form for inserting a new record, this one already has the current information from the database inserted into it. This makes it much more effective for an update script.
Updating The Database
The next stage of this script is to actually update the database. This is a simple operation and just involves a new query for the database:
$query = "UPDATE contacts SET first = '$ud_first', last = '$ud_last', phone = '$ud_phone', mobile = '$ud_mobile', fax = '$ud_fax', email = '$ud_email', web = '$ud_web' WHERE id = '$ud_id'";
This query tells the database to update the contacts table where the ID is the same as the value stored in $ud_id (which as you can see from the form on the previous page was set as the id of the record we are updating) and to set the following fields to the specified values (which were set using the form on the previous page).
This query could then be integrated into a simple script:
$ud_id=$_POST['ud_id'];
$ud_first=$_POST['ud_first'];
$ud_last=$_POST['ud_last'];
$ud_phone=$_POST['ud_phone'];
$ud_mobile=$_POST['ud_mobile'];
$ud_fax=$_POST['ud_fax'];
$ud_email=$_POST['ud_email'];
$ud_web=$_POST['ud_web'];
$username="username";
$password="password";
$database="your_database";
mysql_connect(localhost,$username,$password);
$query="UPDATE contacts SET first='$ud_first', last='$ud_last', phone='$ud_phone', mobile='$ud_mobile', fax='$ud_fax', email='$ud_email', web='$ud_web' WHERE id='$ud_id'";
mysql_query($query);
echo "Record Updated";
mysql_close();
This code would update the database and give the user a confirmation.
Deleting Records
The final part of the contacts database which needs to be created is a page to delete records. As with the Update page, this should have a record ID sent to it in the URL e.g.:
delete.php?id=9
The code to do this is the same as to update the database, except with a slightly different MySQL query. Instead of the UPDATE query you should use:
DELETE FROM contacts WHERE id='$id'
This would then be used with the connection and confirmation code as above.
Loops
At this time it seems appropriate to mention another use of loops with a database. As well as using a loop to get information from a database as we have before, you can also use loops to execute queries. For example, if you wanted to change all the records in the database with the last name Smith to have the website www.smith.com:
Standard Database Connection Code
$query=" SELECT * FROM contacts WHERE last='Smith'";
$result=mysql_query($query);
$num=mysql_numrows($result);
$i=0;
while ($i < $num) { $id=mysql_result($result,$i,"id"); $query1="UPDATE contacts SET web='http://www.smith.com' WHERE id='$id'"; mysql_query($query); ++$i; } mysql_close(); Of course, this could have been achived far easier and quicker using: $query1="UPDATE contacts SET web='http://www.smith.com' WHERE last='Smith'"; and no loop. Part 8 Part 7 Introduction So far you have learnt how to put information into your MySQL database, view the information in it and select which information you would like to view. In this part I will show you how to do the two final actions, updating your database and deleting records from it.
The Update Script
Last week I explained how to create a link for each record to point to your update script. By using the $id variable you output links which would pass the correct ID to the script so that it can update the database. Using this you can then create the update script, which will actually have two sections to it.
Displaying The Update Page
The first part of the update script uses the single record selection from last week but adds a little HTML to it to make it more useful. First of all, we connect to the database and select the appropriate record.
$id=$_GET['id'];
$username="username";
$password="password";
$database="your_database";
mysql_connect(localhost,$username,$password);
$query=" SELECT * FROM contacts WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) { $first=mysql_result($result,$i,"first"); $last=mysql_result($result,$i,"last"); $phone=mysql_result($result,$i,"phone"); $mobile=mysql_result($result,$i,"mobile"); $fax=mysql_result($result,$i,"fax"); $email=mysql_result($result,$i,"email"); $web=mysql_result($result,$i,"web"); Space For Code ++$i; } Where 'Space For Code' is in this script is where the code for the update page will go. This is, in fact, just HTML formatting for the output:
As you can see, this code will output a standard form, but instead of having blank boxes like on the form for inserting a new record, this one already has the current information from the database inserted into it. This makes it much more effective for an update script.
Updating The Database
The next stage of this script is to actually update the database. This is a simple operation and just involves a new query for the database:
$query = "UPDATE contacts SET first = '$ud_first', last = '$ud_last', phone = '$ud_phone', mobile = '$ud_mobile', fax = '$ud_fax', email = '$ud_email', web = '$ud_web' WHERE id = '$ud_id'";
This query tells the database to update the contacts table where the ID is the same as the value stored in $ud_id (which as you can see from the form on the previous page was set as the id of the record we are updating) and to set the following fields to the specified values (which were set using the form on the previous page).
This query could then be integrated into a simple script:
$ud_id=$_POST['ud_id'];
$ud_first=$_POST['ud_first'];
$ud_last=$_POST['ud_last'];
$ud_phone=$_POST['ud_phone'];
$ud_mobile=$_POST['ud_mobile'];
$ud_fax=$_POST['ud_fax'];
$ud_email=$_POST['ud_email'];
$ud_web=$_POST['ud_web'];
$username="username";
$password="password";
$database="your_database";
mysql_connect(localhost,$username,$password);
$query="UPDATE contacts SET first='$ud_first', last='$ud_last', phone='$ud_phone', mobile='$ud_mobile', fax='$ud_fax', email='$ud_email', web='$ud_web' WHERE id='$ud_id'";
mysql_query($query);
echo "Record Updated";
mysql_close();
This code would update the database and give the user a confirmation.
Deleting Records
The final part of the contacts database which needs to be created is a page to delete records. As with the Update page, this should have a record ID sent to it in the URL e.g.:
delete.php?id=9
The code to do this is the same as to update the database, except with a slightly different MySQL query. Instead of the UPDATE query you should use:
DELETE FROM contacts WHERE id='$id'
This would then be used with the connection and confirmation code as above.
Loops
At this time it seems appropriate to mention another use of loops with a database. As well as using a loop to get information from a database as we have before, you can also use loops to execute queries. For example, if you wanted to change all the records in the database with the last name Smith to have the website www.smith.com:
Standard Database Connection Code
$query=" SELECT * FROM contacts WHERE last='Smith'";
$result=mysql_query($query);
$num=mysql_numrows($result);
$i=0;
while ($i < $num) { $id=mysql_result($result,$i,"id"); $query1="UPDATE contacts SET web='http://www.smith.com' WHERE id='$id'"; mysql_query($query); ++$i; } mysql_close(); Of course, this could have been achived far easier and quicker using: $query1="UPDATE contacts SET web='http://www.smith.com' WHERE last='Smith'"; and no loop. Part 8 Part 7 Introduction So far you have learnt how to put information into your MySQL database, view the information in it and select which information you would like to view. In this part I will show you how to do the two final actions, updating your database and deleting records from it. The Update Script Last week I explained how to create a link for each record to point to your update script. By using the $id variable you output links which would pass the correct ID to the script so that it can update the database. Using this you can then create the update script, which will actually have two sections to it. Displaying The Update Page The first part of the update script uses the single record selection from last week but adds a little HTML to it to make it more useful. First of all, we connect to the database and select the appropriate record. $id=$_GET['id']; $username="username"; $password="password"; $database="your_database"; mysql_connect(localhost,$username,$password); $query=" SELECT * FROM contacts WHERE id='$id'"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); $i=0; while ($i < $num) { $first=mysql_result($result,$i,"first"); $last=mysql_result($result,$i,"last"); $phone=mysql_result($result,$i,"phone"); $mobile=mysql_result($result,$i,"mobile"); $fax=mysql_result($result,$i,"fax"); $email=mysql_result($result,$i,"email"); $web=mysql_result($result,$i,"web"); Space For Code ++$i; } Where 'Space For Code' is in this script is where the code for the update page will go. This is, in fact, just HTML formatting for the output:
As you can see, this code will output a standard form, but instead of having blank boxes like on the form for inserting a new record, this one already has the current information from the database inserted into it. This makes it much more effective for an update script.
Updating The Database
The next stage of this script is to actually update the database. This is a simple operation and just involves a new query for the database:
$query = "UPDATE contacts SET first = '$ud_first', last = '$ud_last', phone = '$ud_phone', mobile = '$ud_mobile', fax = '$ud_fax', email = '$ud_email', web = '$ud_web' WHERE id = '$ud_id'";
This query tells the database to update the contacts table where the ID is the same as the value stored in $ud_id (which as you can see from the form on the previous page was set as the id of the record we are updating) and to set the following fields to the specified values (which were set using the form on the previous page).
This query could then be integrated into a simple script:
$ud_id=$_POST['ud_id'];
$ud_first=$_POST['ud_first'];
$ud_last=$_POST['ud_last'];
$ud_phone=$_POST['ud_phone'];
$ud_mobile=$_POST['ud_mobile'];
$ud_fax=$_POST['ud_fax'];
$ud_email=$_POST['ud_email'];
$ud_web=$_POST['ud_web'];
$username="username";
$password="password";
$database="your_database";
mysql_connect(localhost,$username,$password);
$query="UPDATE contacts SET first='$ud_first', last='$ud_last', phone='$ud_phone', mobile='$ud_mobile', fax='$ud_fax', email='$ud_email', web='$ud_web' WHERE id='$ud_id'";
mysql_query($query);
echo "Record Updated";
mysql_close();
This code would update the database and give the user a confirmation.
Deleting Records
The final part of the contacts database which needs to be created is a page to delete records. As with the Update page, this should have a record ID sent to it in the URL e.g.:
delete.php?id=9
The code to do this is the same as to update the database, except with a slightly different MySQL query. Instead of the UPDATE query you should use:
DELETE FROM contacts WHERE id='$id'
This would then be used with the connection and confirmation code as above.
Loops
At this time it seems appropriate to mention another use of loops with a database. As well as using a loop to get information from a database as we have before, you can also use loops to execute queries. For example, if you wanted to change all the records in the database with the last name Smith to have the website www.smith.com:
Standard Database Connection Code
$query=" SELECT * FROM contacts WHERE last='Smith'";
$result=mysql_query($query);
$num=mysql_numrows($result);
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$query1="UPDATE contacts SET web='http://www.smith.com' WHERE id='$id'";
mysql_query($query);
++$i;
}
mysql_close();
Of course, this could have been achived far easier and quicker using:
$query1="UPDATE contacts SET web='http://www.smith.com' WHERE last='Smith'";
and no loop.
The Update Script
Last week I explained how to create a link for each record to point to your update script. By using the $id variable you output links which would pass the correct ID to the script so that it can update the database. Using this you can then create the update script, which will actually have two sections to it.
Displaying The Update Page
The first part of the update script uses the single record selection from last week but adds a little HTML to it to make it more useful. First of all, we connect to the database and select the appropriate record.
$id=$_GET['id'];
$username="username";
$password="password";
$database="your_database";
mysql_connect(localhost,$username,$password);
$query=" SELECT * FROM contacts WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) { $first=mysql_result($result,$i,"first"); $last=mysql_result($result,$i,"last"); $phone=mysql_result($result,$i,"phone"); $mobile=mysql_result($result,$i,"mobile"); $fax=mysql_result($result,$i,"fax"); $email=mysql_result($result,$i,"email"); $web=mysql_result($result,$i,"web"); Space For Code ++$i; } Where 'Space For Code' is in this script is where the code for the update page will go. This is, in fact, just HTML formatting for the output:
As you can see, this code will output a standard form, but instead of having blank boxes like on the form for inserting a new record, this one already has the current information from the database inserted into it. This makes it much more effective for an update script.
Updating The Database
The next stage of this script is to actually update the database. This is a simple operation and just involves a new query for the database:
$query = "UPDATE contacts SET first = '$ud_first', last = '$ud_last', phone = '$ud_phone', mobile = '$ud_mobile', fax = '$ud_fax', email = '$ud_email', web = '$ud_web' WHERE id = '$ud_id'";
This query tells the database to update the contacts table where the ID is the same as the value stored in $ud_id (which as you can see from the form on the previous page was set as the id of the record we are updating) and to set the following fields to the specified values (which were set using the form on the previous page).
This query could then be integrated into a simple script:
$ud_id=$_POST['ud_id'];
$ud_first=$_POST['ud_first'];
$ud_last=$_POST['ud_last'];
$ud_phone=$_POST['ud_phone'];
$ud_mobile=$_POST['ud_mobile'];
$ud_fax=$_POST['ud_fax'];
$ud_email=$_POST['ud_email'];
$ud_web=$_POST['ud_web'];
$username="username";
$password="password";
$database="your_database";
mysql_connect(localhost,$username,$password);
$query="UPDATE contacts SET first='$ud_first', last='$ud_last', phone='$ud_phone', mobile='$ud_mobile', fax='$ud_fax', email='$ud_email', web='$ud_web' WHERE id='$ud_id'";
mysql_query($query);
echo "Record Updated";
mysql_close();
This code would update the database and give the user a confirmation.
Deleting Records
The final part of the contacts database which needs to be created is a page to delete records. As with the Update page, this should have a record ID sent to it in the URL e.g.:
delete.php?id=9
The code to do this is the same as to update the database, except with a slightly different MySQL query. Instead of the UPDATE query you should use:
DELETE FROM contacts WHERE id='$id'
This would then be used with the connection and confirmation code as above.
Loops
At this time it seems appropriate to mention another use of loops with a database. As well as using a loop to get information from a database as we have before, you can also use loops to execute queries. For example, if you wanted to change all the records in the database with the last name Smith to have the website www.smith.com:
Standard Database Connection Code
$query=" SELECT * FROM contacts WHERE last='Smith'";
$result=mysql_query($query);
$num=mysql_numrows($result);
$i=0;
while ($i < $num) { $id=mysql_result($result,$i,"id"); $query1="UPDATE contacts SET web='http://www.smith.com' WHERE id='$id'"; mysql_query($query); ++$i; } mysql_close(); Of course, this could have been achived far easier and quicker using: $query1="UPDATE contacts SET web='http://www.smith.com' WHERE last='Smith'"; and no loop. Part 8 Part 7 Introduction So far you have learnt how to put information into your MySQL database, view the information in it and select which information you would like to view. In this part I will show you how to do the two final actions, updating your database and deleting records from it.
The Update Script
Last week I explained how to create a link for each record to point to your update script. By using the $id variable you output links which would pass the correct ID to the script so that it can update the database. Using this you can then create the update script, which will actually have two sections to it.
Displaying The Update Page
The first part of the update script uses the single record selection from last week but adds a little HTML to it to make it more useful. First of all, we connect to the database and select the appropriate record.
$id=$_GET['id'];
$username="username";
$password="password";
$database="your_database";
mysql_connect(localhost,$username,$password);
$query=" SELECT * FROM contacts WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) { $first=mysql_result($result,$i,"first"); $last=mysql_result($result,$i,"last"); $phone=mysql_result($result,$i,"phone"); $mobile=mysql_result($result,$i,"mobile"); $fax=mysql_result($result,$i,"fax"); $email=mysql_result($result,$i,"email"); $web=mysql_result($result,$i,"web"); Space For Code ++$i; } Where 'Space For Code' is in this script is where the code for the update page will go. This is, in fact, just HTML formatting for the output:
As you can see, this code will output a standard form, but instead of having blank boxes like on the form for inserting a new record, this one already has the current information from the database inserted into it. This makes it much more effective for an update script.
Updating The Database
The next stage of this script is to actually update the database. This is a simple operation and just involves a new query for the database:
$query = "UPDATE contacts SET first = '$ud_first', last = '$ud_last', phone = '$ud_phone', mobile = '$ud_mobile', fax = '$ud_fax', email = '$ud_email', web = '$ud_web' WHERE id = '$ud_id'";
This query tells the database to update the contacts table where the ID is the same as the value stored in $ud_id (which as you can see from the form on the previous page was set as the id of the record we are updating) and to set the following fields to the specified values (which were set using the form on the previous page).
This query could then be integrated into a simple script:
$ud_id=$_POST['ud_id'];
$ud_first=$_POST['ud_first'];
$ud_last=$_POST['ud_last'];
$ud_phone=$_POST['ud_phone'];
$ud_mobile=$_POST['ud_mobile'];
$ud_fax=$_POST['ud_fax'];
$ud_email=$_POST['ud_email'];
$ud_web=$_POST['ud_web'];
$username="username";
$password="password";
$database="your_database";
mysql_connect(localhost,$username,$password);
$query="UPDATE contacts SET first='$ud_first', last='$ud_last', phone='$ud_phone', mobile='$ud_mobile', fax='$ud_fax', email='$ud_email', web='$ud_web' WHERE id='$ud_id'";
mysql_query($query);
echo "Record Updated";
mysql_close();
This code would update the database and give the user a confirmation.
Deleting Records
The final part of the contacts database which needs to be created is a page to delete records. As with the Update page, this should have a record ID sent to it in the URL e.g.:
delete.php?id=9
The code to do this is the same as to update the database, except with a slightly different MySQL query. Instead of the UPDATE query you should use:
DELETE FROM contacts WHERE id='$id'
This would then be used with the connection and confirmation code as above.
Loops
At this time it seems appropriate to mention another use of loops with a database. As well as using a loop to get information from a database as we have before, you can also use loops to execute queries. For example, if you wanted to change all the records in the database with the last name Smith to have the website www.smith.com:
Standard Database Connection Code
$query=" SELECT * FROM contacts WHERE last='Smith'";
$result=mysql_query($query);
$num=mysql_numrows($result);
$i=0;
while ($i < $num) { $id=mysql_result($result,$i,"id"); $query1="UPDATE contacts SET web='http://www.smith.com' WHERE id='$id'"; mysql_query($query); ++$i; } mysql_close(); Of course, this could have been achived far easier and quicker using: $query1="UPDATE contacts SET web='http://www.smith.com' WHERE last='Smith'"; and no loop. Part 8 Part 7 Introduction So far you have learnt how to put information into your MySQL database, view the information in it and select which information you would like to view. In this part I will show you how to do the two final actions, updating your database and deleting records from it. The Update Script Last week I explained how to create a link for each record to point to your update script. By using the $id variable you output links which would pass the correct ID to the script so that it can update the database. Using this you can then create the update script, which will actually have two sections to it. Displaying The Update Page The first part of the update script uses the single record selection from last week but adds a little HTML to it to make it more useful. First of all, we connect to the database and select the appropriate record. $id=$_GET['id']; $username="username"; $password="password"; $database="your_database"; mysql_connect(localhost,$username,$password); $query=" SELECT * FROM contacts WHERE id='$id'"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); $i=0; while ($i < $num) { $first=mysql_result($result,$i,"first"); $last=mysql_result($result,$i,"last"); $phone=mysql_result($result,$i,"phone"); $mobile=mysql_result($result,$i,"mobile"); $fax=mysql_result($result,$i,"fax"); $email=mysql_result($result,$i,"email"); $web=mysql_result($result,$i,"web"); Space For Code ++$i; } Where 'Space For Code' is in this script is where the code for the update page will go. This is, in fact, just HTML formatting for the output:
As you can see, this code will output a standard form, but instead of having blank boxes like on the form for inserting a new record, this one already has the current information from the database inserted into it. This makes it much more effective for an update script.
Updating The Database
The next stage of this script is to actually update the database. This is a simple operation and just involves a new query for the database:
$query = "UPDATE contacts SET first = '$ud_first', last = '$ud_last', phone = '$ud_phone', mobile = '$ud_mobile', fax = '$ud_fax', email = '$ud_email', web = '$ud_web' WHERE id = '$ud_id'";
This query tells the database to update the contacts table where the ID is the same as the value stored in $ud_id (which as you can see from the form on the previous page was set as the id of the record we are updating) and to set the following fields to the specified values (which were set using the form on the previous page).
This query could then be integrated into a simple script:
$ud_id=$_POST['ud_id'];
$ud_first=$_POST['ud_first'];
$ud_last=$_POST['ud_last'];
$ud_phone=$_POST['ud_phone'];
$ud_mobile=$_POST['ud_mobile'];
$ud_fax=$_POST['ud_fax'];
$ud_email=$_POST['ud_email'];
$ud_web=$_POST['ud_web'];
$username="username";
$password="password";
$database="your_database";
mysql_connect(localhost,$username,$password);
$query="UPDATE contacts SET first='$ud_first', last='$ud_last', phone='$ud_phone', mobile='$ud_mobile', fax='$ud_fax', email='$ud_email', web='$ud_web' WHERE id='$ud_id'";
mysql_query($query);
echo "Record Updated";
mysql_close();
This code would update the database and give the user a confirmation.
Deleting Records
The final part of the contacts database which needs to be created is a page to delete records. As with the Update page, this should have a record ID sent to it in the URL e.g.:
delete.php?id=9
The code to do this is the same as to update the database, except with a slightly different MySQL query. Instead of the UPDATE query you should use:
DELETE FROM contacts WHERE id='$id'
This would then be used with the connection and confirmation code as above.
Loops
At this time it seems appropriate to mention another use of loops with a database. As well as using a loop to get information from a database as we have before, you can also use loops to execute queries. For example, if you wanted to change all the records in the database with the last name Smith to have the website www.smith.com:
Standard Database Connection Code
$query=" SELECT * FROM contacts WHERE last='Smith'";
$result=mysql_query($query);
$num=mysql_numrows($result);
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$query1="UPDATE contacts SET web='http://www.smith.com' WHERE id='$id'";
mysql_query($query);
++$i;
}
mysql_close();
Of course, this could have been achived far easier and quicker using:
$query1="UPDATE contacts SET web='http://www.smith.com' WHERE last='Smith'";
and no loop.
Explore more on PHP form creation and handling :
Post a Comment