Avatar of bcmeyer1983
bcmeyer1983 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?
PHPMySQL Server

Avatar of undefined
Last Comment
PanaG

8/22/2022 - Mon
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,

ASKER
bcmeyer1983

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
bcmeyer1983

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,
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
bcmeyer1983

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question