MYSQL INSERT INTO not inserting data or throwing errors

Question

I've got a head scratcher here. My code is not inserting data into the customer_upload table upon clicking the submit button. It does successfully redirect me afterwards to the success message as shown in my code.

I've checked that the submitted data is posting, and I'm pretty sure the script is getting hung up on the mysql_query. I checked this by commenting out the mysql_query and header redirect in my php code, and echoing out the variables $key, $firstname, $lastname, and$company upon clicking submit. Everything looked fine..

Here is what my database looks like:

Database

Here is my code (I know MySQL is deprecated! I'll switch over soon!):

<?php 
header('Expires: Sun, 01 Jan 2014 00:00:00 GMT');
header('Cache-Control: no-store, no-cache, must-revalidate');
header('Cache-Control: post-check=0, pre-check=0', FALSE);
header('Pragma: no-cache');

include('../includes/connection.php');
$thisPage = "File Transfers";

$keyrand = rand(100000000, 999999999);

if(isset($_POST['submit'])) {
    $key = mysql_real_escape_string($_POST['key']);
    $firstname = mysql_real_escape_string($_POST['firstname']);
    $lastname = mysql_real_escape_string($_POST['lastname']);
    $company = mysql_real_escape_string($_POST['company']);
    $date= date("F j, Y"); 

    mysql_query("INSERT INTO customer_uploads (key, firstname, lastname, company, date) VALUES ('$key', '$firstname', '$lastname', '$company', '$date')");

    header("LOCATION: /filetransfers.php?message=success");

}
?>

HTML form:

                                <form method="post">
                                <div class="row">
                                    <div class="col-md-5">
                                        <div class="form-group">
                                            <label>Key</label>
                                            <input name="keyvisible" type="number" class="form-control" placeholder="<?php echo $keyrand ?>" disabled="disabled">
                                            <input name="key" type="hidden" class="form-control" value="<?php echo $keyrand; ?>">
                                        </div>
                                    </div>
                                </div>


                                <div class="row">
                                    <div class="col-md-6">
                                        <div class="form-group">
                                            <label>First Name</label>
                                            <input name="firstname" id="category" type="text" class="form-control" placeholder="First Name">
                                        </div>
                                    </div>
                                    <div class="col-md-6">
                                        <div class="form-group">
                                            <label>Last Name</label>
                                            <input name="lastname" type="text" class="form-control" placeholder="Last Name"?>
                                        </div>
                                    </div>
                                </div>

                                <div class="row">
                                    <div class="col-md-6">
                                        <div class="form-group">
                                            <label>Company</label>
                                            <input name="company" type="text" class="form-control" placeholder="Company"?>
                                        </div>
                                    </div>
                                </div>

                                <button name="submit" type="submit" id="NewCustomerUploadSubmit" class="btn btn-info btn-fill pull-right">Add Upload Key</button>
                                <div class="clearfix"></div>
                            </form>

Any ideas on what I've done wrong?

Let PHP tell you what's wrong - do at the very least `mysql_query("INSERT.....") or die (mysql_error());` - this will tell you what the error is. General error-reporting may also be handy: `error_reporting(E_ALL);` `ini_set('display_errors', 1);` - And, once you switch to a more modern API (like MySQLi or PDO), be sure to use prepared statements. I recommend you do it right away, there's no reason to wait! :-) — Qirel, Jan 13 '17 at 15:40
Thats so helpful, didn't know that existed! Here is what it threw: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key, firstname, lastname, company, date) VALUES ('335100910', 'adfasdf', 'ljaf',' at line 1 — Ajility, Jan 13 '17 at 15:51
^ and that, I knew. See the duplicate this was closed with; that one popped right out at me. MySQL even told you about it `near 'key` << — Funk Forty Niner, Jan 13 '17 at 15:51
I don't see the date posted in Values in the error, however my code looks correct.. — Ajility, Jan 13 '17 at 15:51
Welcome to the world of mysql ;-) You'll get to know those (words) by heart after a while. — Funk Forty Niner, Jan 13 '17 at 15:55
@Fred-ii- Thank you.. my apologies for the mundane issue. I'm learning coding at home as an amateur. Don't think I'll be making that mistake again. Works great after changing key to id — Ajility, Jan 13 '17 at 15:59
@SamiAji This list might be helpful in the future then ;-) http://dev.mysql.com/doc/refman/5.7/en/keywords.html — Qirel, Jan 13 '17 at 16:00
@SamiAji Seeing the other answer wasn't modified, I decided to post my own answer after reopening the question. and you're welcome. — Funk Forty Niner, Jan 13 '17 at 16:00
Switch now. All you're doing is learning technology that you're going to be throwing away. — Andy Lester, Jan 13 '17 at 16:08

ojovirtual · Answer 1 · 2017-01-13T15:48:54.547

3

There is a field missing:

mysql_query("INSERT INTO customer_uploads (key, firstname, lastname, company, date) 
VALUES ('$key', '$firstname', '$lastname', '$company')");

You are not passing the date value, so the query fails.

Edit

Use mysql_query like this:

if (!mysql_query($query))
    die(mysql_error());

so you can check errors.

edited Jan 13 '17 at 15:48

answered Jan 13 '17 at 15:40

ojovirtual

3,332
16
21

1

Good observation! Unfortunately I was troubleshooting and never hit undo. I had that in there! Re-pasted the code. Thank you for your answer – Ajility Jan 13 '17 at 15:45
this answer needs to be modified; it's incorrect and the question closed based on their use of a certain word ;-) – Funk Forty Niner Jan 13 '17 at 15:55

score 3 · Accepted Answer · edited May 23 '17 at 10:29

key in MySQL is a reserved word and if you're planning on still using it, it must be encapsulated in ticks:

mysql_query("INSERT INTO customer_uploads (`key`, firstname, lastname, company, date) 
VALUES ('$key', '$firstname', '$lastname', '$company', '$date')");

Reference:

https://dev.mysql.com/doc/refman/5.5/en/keywords.html

You're also open to an SQL injection here, so use a prepared statement

https://en.wikipedia.org/wiki/Prepared_statement
mysql_real_escape_string() is still prone injection
Consult: SQL injection that gets around mysql_real_escape_string() on this.

Consider moving over to either the mysqli_ or PDO API (with a prepared statement), since the mysql_ API is in deprecation and removed from PHP 7.

"not inserting data or throwing errors"

That's because you needed to check for errors on the query.

Reference on using mysql_error() on the query:

http://php.net/manual/en/function.mysql-error.php

MYSQL INSERT INTO not inserting data or throwing errors

2 Answers2