PHP code for inserting data into MySQL

Question

I'm trying to set up a web page to manage my database. I have a table with 15 rows and I'm trying to write a script to insert data into a MySQL database via a form which has a dropdown menu. I keep getting this error:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Staff Database Update Form</title>
<style type="text/css">
.arial {
    font-family: Verdana, Geneva, sans-serif;
    font-size: 10px;
}
.arial {
    font-size: 9px;
}
.arial1 {
    font-size: 9px;
}
.ARIALBOLD {
    font-weight: bold;
    text-align: center;
}
.A1 {
    font-family: Arial, Helvetica, sans-serif;
}
.A9 {
    font-size: 9px;
}
.Centre {
}
</style>
</head>

<body class="Centre">
<span class="ARIALBOLD">STAFF INFORMATION</span>
<form method="post" action="insert.php"> 
  <table width="442" border="0" align="center">
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial"><strong>NAME</strong></span></th>
      <td width="1">&nbsp;</td>
      <td colspan="5">      <span class="arial">
        <input type="text" name="staff_name" id="staff_name" />
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial"><strong>ADDRESS</strong></span></th>
      <td>&nbsp;</td>
      <td colspan="5">      <span class="arial">
        <textarea name="home_address" id="home_address" cols="30" rows="5"></textarea>
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial">CITY</span></th>
      <td>&nbsp;</td>
      <td colspan="5">        <span class="arial">
        <select name="city" id="city">
          <option value="London">London</option>
          <option value="Luton">Luton</option>
          <option value="Birmingham">Birmingham</option>
          <option value="Manchester">Manchester</option>
          <option value="Reading">Reading</option>
          <option value="Coventry">Coventry</option>
          <option value="Brighton">Brighton</option>
          <option value="Porthsmoth">Porthsmoth</option>
        </select>
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial">AREA</span></th>
      <td>&nbsp;</td>
      <td colspan="5">        <span class="arial">
        <select name="area" id="area">
          <option value="City">City</option>
          <option value="North">North</option>
          <option value="South">South</option>
          <option value="West">West</option>
          <option value="East">East</option>
          <option value="North West">North West</option>
          <option value="North East">North East</option>
          <option value="South West">South West</option>
          <option value="South East">South East</option>
        </select>
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial">PROFESSION</span></th>
      <td>&nbsp;</td>
      <td colspan="5">        <span class="arial">
        <select name="profession" id="profession">
          <option value="RGN">RGN</option>
          <option value="Physiotherapist">Physiotherapist</option>
          <option value="Radiographer">Radiographer</option>
          <option value="SALT">SALT</option>
          <option value="Pharmacist">Pharmacist</option>
          <option value="Doctor">Doctor</option>
          <option value="HCA">HCA</option>
          <option value="Porter">Porter</option>
        </select>
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial">SPECIALITY</span></th>
      <td>&nbsp;</td>
      <td colspan="5">      <span class="arial">
        <input type="text" name="speciality" id="speciality" />
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial">COMPANY</span></th>
      <td>&nbsp;</td>
      <td colspan="5">      <span class="arial">
        <input type="text" name="company" id="company" />
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial">HOME TEL.</span></th>
      <td>&nbsp;</td>
      <td colspan="5">      <span class="arial">
        <input type="text" name="home_tel" id="home_tel" />
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial">MOBILE</span></th>
      <td>&nbsp;</td>
      <td colspan="5">      <span class="arial">
        <input type="text" name="mobile_tel" id="mobile_tel" />
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial">EMAIL</span></th>
      <td>&nbsp;</td>
      <td colspan="5">      <span class="arial">
        <input type="text" name="email_address" id="email_address" />
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial">PROFESSIONAL BODY</span></th>
      <td>&nbsp;</td>
      <td colspan="5">        <span class="arial">
        <select name="pro_body" id="pro_body">
          <option value="NMC">NMC</option>
          <option value="AHP">AHP</option>
          <option value="GMC">GMC</option>
          <option value="Others">Others</option>
        </select>
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial">REGISTRATION</span></th>
      <td>&nbsp;</td>
      <td colspan="5">      <span class="arial">
        <input type="text" name="reg_no" id="reg_no" />
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial">EXPIERY DATE</span></th>
      <td>&nbsp;</td>
      <td colspan="5">      <span class="arial">
        <input type="text" name="exp_date" id="exp_date" />
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row"><span class="arial">PROFILE</span></th>
      <td rowspan="2">&nbsp;</td>
      <td colspan="5" rowspan="2">      <span class="arial">
        <textarea name="profile" id="profile" cols="30" rows="5"></textarea>
      </span></td>
    </tr>
    <tr>
      <th colspan="4" align="left" class="A9" scope="row">&nbsp;</th>
    </tr>
    <tr>
      <th colspan="10" align="left" class="arial" scope="row">&nbsp;</th>
    </tr>
    <tr>
      <th width="26" align="left" class="arial" scope="row">&nbsp;</th>
      <th width="22" align="left" class="arial" scope="row">&nbsp;</th>
      <th width="22" align="left" class="arial" scope="row">&nbsp;</th>
      <th width="19" align="left" class="arial" scope="row">&nbsp;</th>
      <th align="left" class="arial" scope="row">&nbsp;</th>
      <th width="30" align="left" class="arial" scope="row">&nbsp;</th>
      <th width="13" align="left" class="arial" scope="row">&nbsp;</th>
      <th width="13" align="left" class="arial" scope="row">&nbsp;</th>
      <th width="56" align="left" class="arial" scope="row"><span class="arial">
        <input type="submit" name="submit" id="submit" value="Submit" />
      </span></th>
      <th width="156" align="left" class="arial" scope="row"><span class="arial">
        <input type="reset" name="Reset" id="reset" value="Reset" />
      </span></th>
    </tr>
  </table>
  <p>&nbsp;</p>
</form>
</body>
</html>

My PHP code:

<?php
  $host="localhost"; // Host name 
  $username=""; // Mysql username 
  $password=""; // Mysql password 
  $db_name=""; // Database name 
  $tbl_name="staff"; // Table name 

  // Connect to server and select database.
  mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
  mysql_select_db("$db_name")or die("cannot select DB");

  // Get values from form 
  $staff_name=$_POST['staff_name'];
  $home_address=$_POST['home_address'];
  $city=$_POST['city'];
  $area=$_POST['area'];
  $profession=$_POST['profession'];
  $speciality=$_POST['speciality'];
  $company=$_POST['company'];
  $home_tel=$_POST['home_tel'];
  $mobile_tel=$_POST['mobile_tel'];
  $email=$_POST['email'];
  $pro_body=$_POST['pro_body'];
  $reg_no=$_POST['reg_no'];
  $exp_date=$_POST['exp_date'];
  $profile=$_POST['profile'];

  // Insert data into mysql 
  $sql="INSERT INTO $tbl_name(SID, staff_name, home_address, city, area, profession, speciality, company, home_tel, mobile_tel, email, pro_body, reg_no, exp_date, profile)VALUES('NUL', '$staff_name', '$home_address', '$city', '$area', '$profession', '$speciality', '$company', '$home_tel', '$mobile_tel', '$email', '$pro_body', '$reg_no', '$exp_date', '$profile')";
  $result=mysql_query($sql);

  // if successfully insert data into database, displays message "Successful". 
  if($result){
    echo "Successful";
    echo "<BR>";
    echo "<a href='insert.php'>Back to main page</a>";
  } else {
    echo "ERROR";
  }
?> 

<?php 
  // close connection 
  mysql_close();
?>

It seems like I'm posting this comment at least once a day, but you should really read up on SQL injection attacks. — lc., Jul 30 '12 at 3:45
Note that the first value you pass to your sql query is 'NUL' and not 'NULL'. Typically in that situation you want to simply not include that column in the query. Note, too, that you don't want to dynamically pass the table name if you already know the table and columns as you do here. Finally, you should look up PHP PDO and not use the deprecated mysql_query functions, which are vulnerable to security issues. — Nathaniel Ford, Jul 30 '12 at 3:49

NewInTheBusiness · Answer 1 · 2012-07-30 03:47:22Z

up vote 0 down vote

$sql="INSERT INTO $tbl_name(SID, staff_name, home_address, city, area, profession, speciality, company, home_tel, mobile_tel, email, pro_body, reg_no, exp_date, profile) VALUES('NUL', '$staff_name', '$home_address', '$city', '$area', '$profession', '$speciality', '$company', '$home_tel', '$mobile_tel', '$email', '$pro_body', '$reg_no', '$exp_date', '$profile')";

Maybe a space before

VALUES

A good start at least;)

answered Jul 30 '12 at 3:47

NewInTheBusiness
46819

1

Make sure all those literal PHP values in that query have been escaped using mysql_real_escape_string() first. – SamT Jul 30 '12 at 3:49

Thank you all for your comments, – nader Aug 4 '12 at 12:39

add comment

Sanket R. Patil · Answer 2 · 2012-07-30 04:17:50Z

up vote 0 down vote

For email you have name and id 'email_address' and in the insert query you are trying to fetch with

$email=$_POST['email']

edited Jul 30 '12 at 4:17

answered Jul 30 '12 at 3:51

Sanket R. Patil
1316

add comment

asked	1 year ago
viewed	568 times
active	1 year ago

Explore our sites

PHP code for inserting data into MySQL

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql or ask your own question.

Hot Network Questions

Explore our sites

PHP code for inserting data into MySQL

2 Answers

Your Answer

Sign up or login

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql or ask your own question.

Related

Hot Network Questions