Create form send to database

xtent · June 1, 2019

So completely new here.

I've created a database that has 4 fields full_name, company, email and serial_no with 2 sample records.

I would like to create a form with those fields, so the data I enter into the form when I press submit it goes into the database.

Config File:

<?php
$mysqli = new mysqli('localhost', 'username', 'pass', 'database_name');
    if($mysqli->connect_error)
        {
        echo $mysqli->connect_error;
        }
    ?>

Index.php

<html lang="en">
<head>
<meta charset="UTF-8">
<title>Add Record Form</title>
</head>
<body>
<form action="insert.php" method="post">
    <p>
        <label for="Fullname">Fullname:</label>
        <input type="text" name="full_name" id="Fullname">
    </p>
	<p>
        <label for="Company">Company:</label>
        <input type="text" name="Company" id="Company">
    </p>
	<p>
        <label for="emailAddress">Email Address:</label>
        <input type="text" name="email" id="emailAddress">
    </p>
    <p>
        <label for="Serial">Serial:</label>
        <input type="text" name="Serial" id="Serial">
    </p>
    
    <input type="submit" value="Submit">
</form>
</body>
</html>

Insert.php

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "username", "pass", "database_name");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Escape user inputs for security
$full_name = mysqli_real_escape_string($link, $_REQUEST['full_name']);
$company = mysqli_real_escape_string($link, $_REQUEST['company']);
$email = mysqli_real_escape_string($link, $_REQUEST['email']);
$serial_no = mysqli_real_escape_string($link, $_REQUEST['serial_no']);
 
// Attempt insert query execution
$sql = "INSERT INTO licjwe(full_name, company, email, serial_no) VALUES ('$full_name', '$company', '$email', '$serial_no')";
if(mysqli_query($link, $sql)){
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
 
// Close connection
mysqli_close($link);
?>

This is the error I am getting:

ERROR: Could not able to execute INSERT INTO database_name(full_name, company, email, serial_no) VALUES ('test', '', 'email@home.com', ''). Table 'table_name' doesn't exist

Not sure what I am doing.

Basically I want to have the form so I can enter the data, once its been entered, it lets me know its been successfully updated, then loads the page again.

Please help. Where have I gone wrong?

ginerjm · June 1, 2019

1 - If you are defining your form as using POST, then use POST, not REQUEST.

2 - IMHO - caps and lowercase will bite you in the a.. every time. Pick a case (I prefer lower) and use it except when absolutely necessary (which would be when?).

3 - your problem is the upper and lower cases you are using for your names. PHP is a case-sensitive language. That is why you got into trouble here. The names on your fields have to match the indices you use in the $_POST array.

xtent · June 1, 2019

So I've made all the changes as suggested, when I press submit I get this error:

ERROR: Could not able to execute INSERT INTO licjwe(fullname, company, email, serial) VALUES ('', '', '', ''). Table 'licjwe.licjwe' doesn't exist

So I imagine its referring to this line:

// Attempt insert query execution
$sql = "INSERT INTO licjwe(fullname, company, email, serial) VALUES ('$fullname', '$company', '$email', '$serial')";

ginerjm · June 1, 2019

I can only imagine what your corrected code looks like. I"ll just have to imagine what the solution could be....

Edited June 1, 2019 by ginerjm

Barand · June 1, 2019

1 hour ago, xtent said:

This is the error I am getting:


ERROR: Could not able to execute INSERT INTO database_name(full_name, company, email, serial_no) VALUES ('test', '', 'email@home.com', ''). Table 'table_name' doesn't exist

and

40 minutes ago, xtent said:


ERROR: Could not able to execute INSERT INTO licjwe(fullname, company, email, serial) VALUES ('', '', '', ''). Table 'licjwe.licjwe' doesn't exist

Do you know the difference between a database and a table? Is there a table called "licjwe" in your database?

ginerjm · June 1, 2019

Do you not want to show us the updated code? And - are these error messages ACTUALLY EXACTLY what you are posting here? Seem very wrong. Do you have php error checking turned on?

xtent · June 2, 2019

I've corrected the above with the correct table information instead of the database name. I've changed it all to lowercase as recommended.

I've gone to add a record and it says it added the record.

Question:

So I've checked the database and look for the new record it said its inserted, the new record shows a new increment but the data is missing. So if I go back and add another record, this time I get an error instead of it saying a new record has been added.

The error is:

ERROR: Could not able to execute INSERT INTO clients2(fullname, company, email, serial_no) VALUES ('', '', '', ''). Duplicate entry '' for key 'email'

Barand

Thanks for pointing out the difference between the table and database, my mistake. Thank you.

ginerjm

Updated code? The code I have provided above is the code, below is the database

CREATE DATABASE IF NOT EXISTS `licjwe` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE `licjwe`;

-- --------------------------------------------------------
--
-- Table structure for table `clients2`
--

CREATE TABLE IF NOT EXISTS `clients2` (
  `client_id` int(10) unsigned NOT NULL auto_increment,
  `user_name` varchar(255) collate utf8_bin NOT NULL,
  `company` varchar(255) collate utf8_bin NOT NULL,
  `email` varchar(255) collate utf8_bin NOT NULL,
  `serial_no` varchar(255) collate utf8_bin NOT NULL,
  `license_no` int(10) unsigned NOT NULL DEFAULT 1,
  `validations_no` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY  (`client_id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `serial` (`serial_no`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Our company clients' AUTO_INCREMENT=3;

--
-- Dumping data for table `clients2`
--

INSERT INTO `clients2` (`client_id`, `user_name`, `company`, `email`, `serial_no`, `license_no`) VALUES
(1, 'Person a', 'Company1', 'smith@west.com', '111-222-111-222', 2),
(2, 'Person b', 'Company2', 'west@home.com', '222-222-222-111', 2);

xtent · June 2, 2019

ginerjm

Your comment " And - are these error messages ACTUALLY EXACTLY what you are posting here? Seem very wrong. " What are you trying to imply here? I've posted everything that I am having a problem with.

xtent · June 2, 2019

I've enabled the errors suggested by ginerjm and it now shows:

Notice: Undefined variable: _post in insert.php on line 12
Notice: Undefined variable: post in insert.php on line 13
Notice: Undefined variable: post in insert.php on line 14
Notice: Undefined variable: post in insert.php on line 15

Whilst i do this I am too trying to work out the answer for myself, so please when you reply, understand that I am new at this, please don't give me the answer, but guide me in the right direction so I may try and understand this myself, thank you.

Now with the error Notice: Undefined variable, it says something about the variable that it wasn't previously defined, so does that mean I need to define fullname, company etc? Does that mean I need the tell the database the values of those first?

farukweb · June 2, 2019

you have missed input type name

<input type="submit" name="submit" value="Submit">

if(isset($_POST[''submit'])){

//get the form data

}

xtent · June 2, 2019

Not sure how I go about entering this.

Edited June 2, 2019 by xtent

ginerjm · June 2, 2019

you made all those changes but never re-posted the new code for us to debug

xtent · June 2, 2019

<?php
/*  */
$link = mysqli_connect("localhost", "user", "pass", "db");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Escape user inputs for security
$fullname = mysqli_real_escape_string($link, $post['fullname']);
$company = mysqli_real_escape_string($link, $post['company']);
$email = mysqli_real_escape_string($link, $post['email']);
$serial_no = mysqli_real_escape_string($link, $post['serial_no']);
 
// Attempt insert query execution
$sql = "INSERT INTO clients2(fullname, company, email, serial_no) VALUES ('$fullname', '$company', '$email', '$serial_no')";
if(mysqli_query($link, $sql)){
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
 
// Close connection
mysqli_close($link);
?>

<html lang="en">
<head>
<meta charset="UTF-8">

</head>
<body>
<form action="insert.php" method="post">
    <p>
        <label for="fullname">fullname:</label>
        <input type="text" name="fullname" id="fullname">
    </p>
	<p>
        <label for="company">company:</label>
        <input type="text" name="company" id="company">
    </p>
	<p>
        <label for="email">email:</label>
        <input type="text" name="email" id="email">
    </p>
    <p>
        <label for="serial_no">serial:</label>
        <input type="text" name="serial_no" id="serial_no">
    </p>
    
    <input type="submit" name="submit" value="Submit">

</form>
</body>
</html>

Edited June 2, 2019 by xtent

ginerjm · June 2, 2019

Those undefined errors are because of sloppy coding. LOOK at your attempts to grab the input values. At least you are now making valid references to the html inputs but you are not using the correct array name, namely "$_POST". Programming is very much an EXACT science. So - $post and $_post are incorrect and you need to learn soon that you must be on the lookout for these kind of 'easy' errors.

You should also be validating your inputs to make sure that you have the required values needed to create a new record. If the user doesn't give you something, send them an error message and make them re-submit the form. You can assign a variable to the input's value= attribute so that when you send back the form their input (that the DID provide) shows up the second time

xtent · June 2, 2019

I am reading w3schools as well and trying different things

<?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// escape variables for security
$fullname = mysqli_real_escape_string($con, $_POST['fullname']);
$company = mysqli_real_escape_string($con, $_POST['company']);
$email = mysqli_real_escape_string($con, $_POST['email']);
$serial_no = mysqli_real_escape_string($con, $_POST['serial_no']);

$sql="INSERT INTO clients2 (fullname, company, email, serial_no)
VALUES ('$fullname', '$company', '$email', '$serial_no')";

if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}
echo "1 record added";

mysqli_close($con);
?>

The part where you mention " assign a variable to the input's value= attribute so that when you send back the form their input (that the DID provide) shows up the second time " is this part referring to entering data and when the data has been entered successfully the form reloads allowing for new data to be entered but also shows that a record had been entered as well?

I can now submit data

maxxd · June 2, 2019

10 minutes ago, xtent said:

I am reading w3schools

Don't do that. Try PHP The Right Way or codecademy - there are a lot of tutorials and lessons out there. If you don't know whether or not they're any good, ask here first. Someone can tell you.

w3shools.com is a great place to look up what exactly you need to Google in order to figure out how something works, but it's not a great place to actually learn how something works.

Edited June 2, 2019 by maxxd

xtent · June 2, 2019

2 minutes ago, maxxd said:

Don't do that. Try PHP The Right Way or codecademy - there are a lot of tutorials and lessons out there. If you don't know whether or not they're any good, ask here first. Someone can tell you.

w3shools.com is a great place to look up what exactly you need to Google in order to figure out how something works, but it's not a great place to actually learn how something works.

Thank you.

ginerjm · June 2, 2019

You really should be validating your input. That was why I suggested using the value attributes off the input tags so that if you had to send the form back for corrections the original input that was entered would show up and the user would not have to re-type it if it was one of the correct fields. You also need to CHECK things to make sure that they actually HAPPEN. You are blindly assuming that everything is just a-ok despite your own problems with coding. SQL calls can fail just like writing lines of php code. Check the results of the inputs to be sure they are what you expect. Check the result of the query execution to be sure it ran. Check the mysqli function that tells you if a row was actually inserted instead of just saying it.

xtent · June 7, 2019

@ginerjm

Its interesting how you comment that I make sure the data is being inserted and too check the results of the inputs, you obviously have seen something and noticed that the data wont insert in the db and as such that has happened. Now before you reply, remember that I came here asking for help, don't talk down to me please like I am some kid, just because you understand the theory behind what is happening here, your comments always come across offensive even though you may not mean it. I am new at this and have no idea what I am doing hence why I was just trying to wing it, the URL is irrelevant on whats going on since I am the only one accessing it and no one else will have access to the URL.

That being said, I still don't want the answer in plain text, but please ease off on me, I am trying here even though it no longer works, it says the record went to the table successfully yet it didn't.

Thank you.

ginerjm · June 7, 2019

I am confused.....

phppup · June 7, 2019

There are many more qualified than me to debug your code, but I recognize your format.

Double check everything with a fine tooth comb. A single semi-colon or comma can ruin all your work and make you crazy.

Look at the MySQL database and confirm that the database name and table name are spelled EXACTLY identically to what you have in your scripting.

I also don't know that you need $con in these lines

$fullname = mysqli_real_escape_string($con, $_POST['fullname']);
$company = mysqli_real_escape_string($con, $_POST['company']);
$email = mysqli_real_escape_string($con, $_POST['email']);
$serial_no = mysqli_real_escape_string($con, $_POST['serial_no']);

the redundancy may be causing an issue. I would try

$fullname = mysqli_real_escape_string($_POST['fullname']);
$company = mysqli_real_escape_string($_POST['company']);
$email = mysqli_real_escape_string($_POST['email']);
$serial_no = mysqli_real_escape_string($_POST['serial_no']);

If all else fails, I have found it useful to remove lines of code to establish a single successful effort.

Eliminate company, email, and serial_no and SIMPLY work with getting a SUCCESSFUL insertion of $fullname. Then, build from there (to ensure that no single variable is destroying your entire effort.)

phppup · June 7, 2019

Also, you have EMAIL and SERIAL NO. as UNIQUE keys. From a novice standpoint, I found this to be difficult for testing.

Again, I would re-create the table with the SIMPLEST standards and seek success. Then, re-develop and grow so that you can control your experimentation and understand hardships as you overcome them. This helped me learn and develop.

Barand · June 7, 2019

30 minutes ago, phppup said:
I also don't know that you need $con in these lines
$fullname = mysqli_real_escape_string($con, $_POST['fullname']);

A look at the manual would confirm that the $con IS required

phppup · June 7, 2019

To Barand, I see. It appears to be required because of mysqli_real_escape_string . Since my code is ONLY being tested locally, I have been negligent with security and focused on functionality.

I suppose when I go live I will want to utilize the mysqli_real_escape_string with the proper connectivity.

I'm a bit surprised that this needs to be done individually for each variable. Is there a method to simply address "ALL VARIABLES" somehow?

PS: Glad that was my only faux pas in my post. ALWAYS LEARNING.

Barand · June 7, 2019

You should use prepared statements and bind user input as parameters rather than using mysqli_real_escape_string().

Better still, as you are still learning, put your efforts into learning PDO instead of mysqli.

If $con is a PDO connection, the above code becomes

$stmt = $con->prepare("INSERT INTO clients2 (fullname, company, email, serial_no)
                       VALUES (?,?,?,?)
                       ";
$stmt->execute( [ $_POST['fullname'], $_POST['company'], $_POST['email'], $_POST['serial_no'] ]  );

IE prepare the query putting placeholders for the user inputs then execute it passing the values in an array.

Sign In

Create form send to database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information