Saving to MySQL database via html forms

Question

I am making a php page that retrieves data from a database table and putting it in a table for the user to see via MySQLi commands.

I was wondering how I should approach the reverse situation. I want the user to be able to enter in information into textboxes and the click a button at the bottom of the page called 'save' which will prompt the user "are you sure" before saving to the database. If the user clicks 'yes', then the new entry is inserted into the database.

I have the following code to create the labels and textboxes:

<FORM>
ID: <input type="text" name="id"><br />
NM: <input type="text" name="nm"><br />
Company: <input type="text" name="company"><br />
Address: <input type="text" name="address"><br />
City: <input type="text" name="city"><br />
Zip: <input type="text" name="zip"><br />
State: <input type="text" name="state"><br />
Phone: <input type="text" name="phone"><br />
Website: <input type="text" name="web_site"><br />
</FORM>

However, when it comes to the 'save' button, I can implement the button just fine, but how would I go about saving the information entered into the database?

My initial thought process was to find the values that the user entered. I'm new to PHP and WEB dev in general, but I need to know how to get the value of the text in the textbox. Would I have to sift all the values through via the PHP Post method?

Once I have the information the user wants to enter, I was thinking maybe MySQLi has an insert function, which I found here, http://php.net/manual/en/mysqli.insert-id.php. Then it's just a quick insert and it's in the database after the user gives the 'yes' at the prompt.

Do I have the right idea in mind? Is there a more efficient way to do this?

Any help is greatly appreciated. I've looked around for problems and solutions similar to the ones related to my scenario but there were none. =(

Thanks!

EDIT:

Here is the code I have on the agentprocess.php that the action form sends the information to:

<?php
$agent_nm = $_POST['nm']; // gather all the variables
$company = $_POST['company'];
$address = $_POST['address'];
$city = $_POST['city'];
$zip = $_POST['zip'];
$state = $_POST['state'];
$phone = $_POST['phone'];
$web_site = $_POST['web_site'];
$batch_id = $_POST['batch_id']; // added batch id
//connect
$conn = new mysqli('local', 'admin', 'pass', 'DB');
    if(mysqli_connect_errno()) {
        exit('Connect failed: '. mysqli_connect_error());
    }
//generate the query (doesn't add id because id is autoincremented)
$query = "INSERT INTO t_agent VALUES (NULL, " . $agent_nm . ", " . $company . ", " . $address . ", " . $city . ", " . $zip . ", " . $state . ", " . $phone . ", " . $web_site . ", " . $batch_id . ")";

//insert and close.
mysqli_query($conn, $query);
mysqli_close($conn);

Despite the code here, I've queried the table and the new entry is not there. Am I missing something here?

Thanks in advance!

Answer 1

You should use PDO functions for PHP/MySQL

id field should be autoincrement

<?php
$host= "xxx";
$username="xxx";
$password="xxx";
$database="xxx ";

// Gets data from URL parameters

$name = $_POST['nm'];
//Repeate for all other parameters

// Opens a connection to a MySQL server
try {

// DBH means "DB Handle"

// MySQL with PDO_MYSQL
 $DBH = new PDO("mysql:host=$host;dbname=$database", $username, $password);

}
catch(PDOException $e) {
    echo $e->getMessage();
}
// STH means "Statement Handle"
$STH = $DBH->prepare("INSERT INTO mytable ( id,    nm,company,address,city,zip,state,phone,web_site ) values ( NULL,:nm,:company,:address,:city,:zip,:state,:phone,:web_site)");
$STH->bindParam(':name', $name);
//Repeate for all other parameters
$STH->execute();

//# close the connection
$DBH = null;
?>

Answer 2

this is simple yet a little complex task for web development beginers.

So I am going to give you an full example of what you need to do...

To do the SAVE button check the fastest way is to use javascript confirm dialog and if confirmed to submit form with javascript also.

The Mysql insert part is easy, you need to check if there is data that you submited via form in $_REQUSET (this works better than $_POST or $_GET because it catchs it both.) and then to connect to db and do an insert query...

Everything is explained in this example:

http://pastebin.com/thNmsXvn

But please use some template engine like Smarty because doing php, javascript and html in one file without template is awful and long term will give you only problems.

I think that I was very clear in the example I put on pastebin but if you have some questions feel free to ask...

Just to add, I have removed ID from HTML form because the best solution for managing ID's in MySQL is auto increment option, you configure that when you create table and set it to a specific field. Most usually it is ID, and it must be an integer.

Answer 3

Very simple example, added the label tag to the labels for your input and put it inside of a form.

<form method="post" action="process.php" id="myForm" name="myForm" >
<label for="ID">ID</label>:  <input type="text" name="ID" /><br />
<label for="nm">NM:</label> <input type="text" name="nm"><br />
<label for="company">Company:</label> <input type="text" name="company"><br />
<label for="address">Address:</label> <input type="text" name="address"><br />
<label for="city">City</label>: <input type="text" name="city"><br />
<label for="zip">Zip</label>: <input type="text" name="zip"><br />
<label for="state">State</label>: <input type="text" name="state"><br />
<label for="phone">Phone</label>: <input type="text" name="phone"><br />
<label for="web_site">Website</label>: <input type="text" name="web_site"><br />
<input type="submit" name="submit" />// this is your submit button
</form>

On the process.php page

//get your inputs from the form
$ID = $_POST['ID']; 
//do the same for each of the text inputs

Then you can use mysqli as you described to insert the values into your database, feel free to comment if you need any help with the mysqli part of the question, I didn't include it here since you had the link posted in the original question.

Answer 4

you need to use forms. yes, using the name attributes in your elements, you sift through $_POST(eg. $_POST['company']) for the values you want to store into the DB. here's an example. Use MYSQLi statements instead of mysql as in the eg.