asked on

Insert multiple html form fields into mysql table using PHP

Here is my HTML Code:

<html>
<head>
<title>Record Insertion>
</head>
<body>
<form action="insert.php" method="POST">
<p>The First Hale Database<br>
Age <input type="text" name="Age" size="2"><br>
First Name <input type="text" name="First_Name" size="30"><br>
Last Name <input type="text" name="Last_Name" size="30"> <br>
<p><input type="submit" name="submit" value="Add Me"></p>
</form>
</body>
</html>

Here is my PHP code:

<?php
$mysqli = new mysqli("localhost", "root", "pass", "test");

if (mysqli_connect_errno()) {
      printf("Connect failed: %s\n", mysqli_connect_error());
      exit();
} else {
      $sql = "INSERT INTO testTable (Age)
            VALUES ('".$_POST["Age"]."')";
            $res = mysqli_query($mysqli, $sql);

-----------------missing the rest of the form INSERT INTO here only inserting Age------------------------

      if ($res === TRUE) {
            echo "record has been inserted";
      } else {
            printf("could not insert record: %s\n", mysqli_error($mysqli));
      }

            mysqli_close($mysqli);
      }
?>

Using text books. I am training myself with mysql and php. I have inserted my first value into my first database/database table using the above form and script. However this is just basic and as you can see I have additional form fields in my html that i would like to insert into my test database/testtable. the database is ready to accept these posts, however what is an "experienced" way the PHP code should read for the rest of my HTML form fields?

PanaG

Hi bcmeyer1983,

Lets say you have fields name "FirstName" and "LastName", basically, you just modify the SQL statement to include more values like this:

$sql = "INSERT INTO testTable (Age,FirstName,LastName)
VALUES ('" . $_POST["Age"] . "','" . $_POST['First_Name'] . "','" . $_POST['Last_Name'] "')";
$res = mysqli_query($mysqli, $sql);

The SQL statement will resolve to something like this in the end:

INSERT INTO testTable (Age,FirstName,LastName) VALUES ('19','John','Doe')

It is recommended that you sanitize your values coming in and not to use $_POST['var'] directly in building your SQL statement. Otherwise you will be open to SQL Injection attacks. For more info, read this article: http://www.askbee.net/articles/php/SQL_Injection/sql_injection.html

Hopefully this helps,

bcmeyer1983

ASKER

I am getting errors with the use of those quotes and such?

Joe Wu

what exactly is the error and where? also you may need to check your datatype, if your age is an integer field, then you can insert the 19 without quotes, like below as an example:

INSERT INTO testTable (Age,FirstName,LastName) VALUES (19,'John','Doe')

Hope this helps.

bcmeyer1983

ASKER

Worked passed that one. Heres the new error: could not insert record: Uknown column 'john' in 'field list'
however if I use all numbers for age, firstname, lastname it inserts....?

Joe Wu

Basically in a nutshell, if your field type is a integer, you can insert without quotes (like age) but if it is a varchar then you will need (and it is compulsory yes) the quotes around 'John' assuming that FirstName is a varchar or similar.

Hope this helps. If not, please post what query you have tried to execute exactly?

PanaG

A bit further on the integer versue varchar issues,

Really it doesn't matter so much what type of field is in the database, the bottom line is that if you are trying to insert a number you don't need quotes, however if you are trying to insert anything else (text), you need to put single quotes around the value. Having said that, if you try to insert text into an integer field in the database, 0 will be stored for the value.

Here's a little function that I find handy to deal with the quotes issue:

function quote_smart($value)
{
// Stripslashes
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
// Quote if not integer
if (!is_numeric($value)) {
$value = "'" . addslashes($value) . "'";
}
return $value;
}

Add the code above to your PHP file and then modify the insert code to something like this:

$inAge = quote_smart($_POST["Age"]);
$inFirst_Name = quote_smart($_POST["First_Name"]);
$inLast_Name = quote_smart($_POST["Last_Name"]);

$sql = "INSERT INTO testTable (Age,FirstName,LastName) VALUES
($inAge,$inFirst_Name,$inLast_Name)";

$res = mysqli_query($mysqli, $sql);

This also makes your code more readable

Let us know if you continue to have problems,

bcmeyer1983

ASKER

Here is what i have so far:

<?php
$mysqli = new mysqli("localhost", "root", "pass", "test");

if (mysqli_connect_errno()) {
      printf("Connect failed: %s\n", mysqli_connect_error());
      exit();
} else

$inAge = ($_POST["age"]);
$inFirst_Name = ($_POST['firstname']);
$inLast_Name = ($_POST['lastname']);

$sql = "INSERT INTO testTable (Age,FirstName,LastName) VALUES
($inAge,$inFirst_Name,$inLast_Name)";

$res = mysqli_query($mysqli, $sql);

if ($res === TRUE) {
            echo "record has been inserted";
      } else {
            printf("could not insert record: %s\n", mysqli_error($mysqli));
      }

            mysqli_close($mysqli);


?>

Here is my error: Uknown column 'john' in 'field list'

I get the error after entering 23, john, doe as my form values.

However if i enter all numbers: 23, 23, 23

"Record inserted"

mysql database are varchar for the first and last name.

ASKER CERTIFIED SOLUTION

PanaG

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial