MySQL Insert Into PHP Not Working

Question

I am currently looking to run a basic insert query using PHP to submit HTML form data to MySQL database. Unfortunately however the insert process isnt running. In my Insert syntax I have tried including $_POST[fieldname], ive tried including variables as below, and ive even played around with different apostrphes but nothing seems to be working.

as a side dish, im also getting truck load of wamp deprication errors which is overwhelming, ive disabled in php.ini and php for apache.ini file and still coming up.

If anyone can advise what is wrong with my insert and anything else id be much thankful.

Ill keep this intro straightfoward. Person logs in, if they try to get in without login they go back to login page to login. I connect to database using external config file to save me updating in 50 places when hosting elsewhere. Config file is working fine so not shown below. database is called mydb. Im storing the text field items into variables, then using the variables in the insert query. unitID is an auto increment field so I leave that blank when running the insert. Unfortunately nothing is going in to the mysql database. Thanks in advance. PS the text fieldnames are all correctly matched up

<?php
    //Start the session
    session_start();
    //check the user is logged in
    if (!(isset($_SESSION['Username']) )) {
        header ("Location: LoginPage.php?i=1");
        exit();
    }
    //Connect to the database
    include 'config.php';
    $UserName = $_SESSION['Username'];
    $UserIdentification = $_SESSION['UserID'];          
    if(isset($_GET['i'])){
        if($_GET['i'] == '1'){
            $tblName="sightings";
            //Form Values into store
            $loco =$_POST['txtloco'];
            $where =$_POST['txtwhere'];
            $when =$_POST['txtdate'];
            $time =$_POST['txttime'];
            $origin =$_POST['txtorigin'];
            $dest =$_POST['txtdest'];
            $headcode =$_POST['txtheadcode'];               
            $sql= "INSERT INTO sightings (unitID, Class, Sighted, Date, Time, Origin, Destination, Headcode, UserID) VALUES ('','$loco', '$where', '$when', '$time', '$origin', '$dest', '$headcode', '$UserIdentification')";
            mysql_select_db('mydb');
            $result=mysql_query($sql, $db);
                if($result){
                    $allocationsuccess = "Save Successful";
                    header ('Refresh: 2; url= create.php');
                }
                else {
                    $allocationsuccess = "The submission failed :(";
                }
        }   
    }
?>

[Little Bobby](http://bobby-tables.com/) says **[you may be at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/)**. Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) with [parameterized queries](https://stackoverflow.com/a/4712113/5827005). I recommend `PDO`, which I [wrote a function for](http://paragoncds.com/grumpy/pdoquery/#function) to make it extremely easy, clean, and more secure than using non-parameterized queries. Also, [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) may help you choose between `MySQLi` and `PDO` — GrumpyCrouton, Sep 25 '17 at 14:40
**Please**, don't use `mysql_*` functions for new code. They are no longer maintained and the community has begun the [deprecation process](http://news.php.net/php.internals/53799), and `mysql_*` functions have been officially removed in PHP 7. Instead you should learn about [prepared statements](https://en.wikipedia.org/wiki/Prepared_statement) and use either `PDO` or `mysqli_*`. If you can't decide, [this article will help to choose your best option](http://php.net/manual/en/mysqlinfo.api.choosing.php). — GrumpyCrouton, Sep 25 '17 at 14:40
`deprication errors which is overwhelming, ive disabled in php.ini and php for apache.ini file and still coming up.` hmm I wonder why that is? Perhaps stop using depreciated functions? Why would you try to ignore the errors? That is just irresponsible. — GrumpyCrouton, Sep 25 '17 at 14:41
This question has now been resolved many thanks for the help — trainmania100, Sep 26 '17 at 15:46

ADyson · Accepted Answer · 2017-09-25T14:54:57.910

"unitID is an auto increment field so I leave that blank when running the insert"

That's not how it works. You have to omit it completely from the INSERT statement. The code thinks you're trying to set that field to a blank string, which is not allowed.

$sql= "INSERT INTO sightings (Class, Sighted, Date, Time, Origin, Destination, Headcode, UserID) VALUES ('$loco', '$where', '$when', '$time', '$origin', '$dest', '$headcode', '$UserIdentification')";

should fix that particular issue. MySQL will generate a value automatically for the field and insert it for you when it creates the row.

If your code had been logging the message produced by mysql_error() whenever mysql_query() returns false then you'd have seen an error being generated by your query, which might have given you a clue as to what was happening.

P.S. As mentioned in the comments, you need to re-write your code with a newer mysql code library and better techniques including parameterisation, to avoid the various vulnerabilities you're currently exposed to.

MySQL Insert Into PHP Not Working

1 Answers1