Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by Eiyad Ziyadah
Eiyad Ziyadah
19,250 Points

How to insert a record into Mysql table using PHP?

I have the following data posted by HTML form:

<?php
    wo_no = $_POST["wo_no"];
                $call_date = $_POST["call_date"];
                $wo_start_date = $_POST["wo_start_date"];
                $wo_end_date = $_POST["wo_end_date"];
                $wo_status = $_POST["wo_status"];
                $request_type = $_POST["request_type"];
                $caller_input = $_POST["caller_input"];
                $caller_name = $_POST["caller_name"];
                $caller_phone = $_POST["caller_phone"];
                $fe_assigned = $_POST["fe_assigned"];
                $technical_remarks = $_POST["tech_remarks"];
                $work_hours = $_POST["work_hours"];
                $travel_hours = $_POST["travel_hours"];
?>

How can I create a new record in my table using PDO??

2 Answers

Robert Richey
Robert Richey
Courses Plus Student 16,352 Points

tl;dr - after including this function definition somewhere, it's just one line of code to call the function to update a table with a new record.

I cringe at the thought of manually typing in all those field names and values. A while back, I wrote a function that updates a table with a new record containing an arbitrary number of fieldname and value pairs. Here is my GitHub. Or, here is the code if not comfortable with Git. Just make sure the fieldnames match what is already in your table.

Please forgive the naming inconsistencies (some are camelCase, some are underscore_separated). I may fix it at a later time, but not anytime soon. If you have any questions about what is going on inside the function, feel free to ask - I'd be happy to respond.

Just to further clarify the arguments:

  • $dbh is a PDO object with an open database connection
  • $table is the name of the table to be updated in your database
  • $assoc is an associative array of fieldname and value pairs
<?php
    function insert_into ($dbh, $table, $assoc) {
        $fields_arr = array ();

        foreach ($assoc as $key => $val) {
            array_push ($fields_arr, "`" . $key . "`");
        }

        $fields = implode (",", $fields_arr);
        $namedPlaceholders = array ();

        foreach ($assoc as $key => $val) {
            array_push ($namedPlaceholders, ":" . $key);
        }

        $values = implode (",", $namedPlaceholders);

        $sql = "INSERT INTO $table ($fields) VALUES ($values)";

        try {
            $sth = $dbh->prepare ($sql);
            reset ($namedPlaceholders);
            foreach ($assoc as $key => &$val) {                 
                $sth->bindParam (current ($namedPlaceholders), $val);
                next ($namedPlaceholders);
            }
            $sth->execute ();           
        } catch (PDOException $e) {
            echo $e->getMessage ();
        }
    }

?>

Calling the function in your code would look like this:

<?php
insert_into($databaseHandle, $table_name, $data);
// that was easy!
?>
Eiyad Ziyadah
Eiyad Ziyadah
19,250 Points

Thanks Robert, the function helped a lot. but it only worked after I removed the php public declaration. anyways, I used it in my project. I tried to modify it or create new function for updating a table the same way but till now I couldn't do it, I am still beginner ;) but I will keep trying.

again, thanks a lot for the help.

Robert Richey
Robert Richey
Courses Plus Student 16,352 Points

Thanks a lot, and good catch on that public keyword. This function was once part of a class containing functions for interacting with a database. I still regard myself as a beginner. After a day or two of coding and refactoring, this is the result of wanting to write the following generic SQL statement:

<?php
$sql = "INSERT INTO $table ($fields) VALUES ($values)";
?>
Jeremy Hayden
Jeremy Hayden
1,740 Points
Jeremy Hayden
Jeremy Hayden
1,740 Points

If you already have your Mysql connection script running then you can INSERT with the following code:

$sql = "INSERT INTO YOUR_TABLE_NAME (YOUR_FIELD_1,YOUR_FIELD_2) VALUES ('$call_date', '$wo_start_date')";

$result = mysql_query($sql);

Just replace the field names with the ones used in your table. You can add expand this to include all your data. Just make sure for every value entered there is a field entered as well. I suggest starting with one or two fields for debugging purpose to test the connection. Then add 1 or 2 more fields and test again. It is a pain adding 20 fields and getting an error and not being able to spot it.