Home » Database » How to update a MySQL table with PHP

How to update a MySQL table with PHP



You can update a MySQL table, either with PHP or through its command prompt, by adding (inserting) new records to it or modifying the existing records. But in this tutorial we’re going to explore how you can update a MySQL table with PHP. You may have to do this when creating an online application in PHP that involves saving new information or modifying existing information.

In order to modify a MySQL table you first need to establish a database connection. In PHP, you can establish a MySQL database connection using the following lines of code:

<?php
$host=”localhost”;
$user_name=”username”;
$pwd=”password”;
$database_name=”your_database”;
$db=mysql_connect($host, $user_name, $pwd);
if (mysql_error() > “”) print mysql_error() . “<br>”;
mysql_select_db($database_name, $db);
if (mysql_error() > “”) print mysql_error() . “<br>”;
?>

This basically first connects to your MySQL server (in most of the cases it is localhost but if it is different you’ll get this information from your host), and then selects the database that contains the table.

Just to make it a bit convenient let’s assume the table name is students having the following structure:

  • first_name
  • last_name
  • roll_number
  • attendance

And we’ll also suppose that a form is being submitted through POST and the following variables contain the information to be saved:

<?php
$fname=”Some first name”;
$lname=”Some last name”;
$rollnumber=”Some roll number”;
$attendance=150;
?>

If this is a new record being saved in the MySQL table you’ll use the following PHP code:

<?php
$r = mysql_query(“insert into students (first_name, last_name, roll_number, attendance) values (‘” . $fname . “‘, ‘” . $lname . “‘, ‘” . $rollnumber . “‘, ” . $attendance . “)”;
?>

The three single quotes are actually single quotes within double quotes because when you are saving alphanumeric values you have to enclose them within quotes. The quotes here may appear “fancy” due to the selected font of the blog, otherwise they have to be simple quotes.

Similarly, if you want to update existing values in a MySQL table you use the following PHP code:

<?php
$r = mysql_query(“update students set first_name='” . $fname . “‘, last_name='” . $lname . “‘, roll_number='” . $rollnumber . “‘, attendance=” . $attendance;
?>

But the problem here is, if you simply use the update command like this, you may end up updating a record where the pointer currently is, whereas you’d like to update a selected record. Suppose you want to update the record of a student with roll number “120956”. You do it like this:

<?
$r = mysql_query(“update students set first_name='” . $fname . “‘, last_name='” . $lname . “‘, roll_number='” . $rollnumber . “‘, attendance=” . $attendance . ” where roll_number=’120956′”;
?>

This is how you can update a MySQL table with PHP.