Data not being added to SQL, No Errors

Question

I am losing my mind on this piece of code. It appears to work. No errors, however nothing is being added to mysql database. I have checked db connection it is working.

I hope you can help.

Entry form

<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="initial-scale=1, maximum-scale=1">
        <title>Create a Company</title>
        <link rel="stylesheet" type="text/css" href="/PITAKER/V2/css.css"/>

</head>
<body>
    <h2 class="header"> Create a Company </h2>
    <form action="processcompany.php" method="post">

        <input class="entry" placeholder="Company Name" name="companyname" type="text" required="required"><br>
        <input class="entry" placeholder="GST/VAT/ABN/TAX No" name="taxno" type="text" required="required"><br>
        <input class="entry" placeholder="Address" name="address1" type="text" value=""><br>
        <input class="entry" placeholder="Suburb/County" name="suburb" type="text" value=""><br>
        <input class="entry" placeholder="State" name="state" type="text" value=""><br>
        <input class="entry" placeholder="Post/Zip Code" name="postcode" type="text" value=""><br>
    <input class="entry" placeholder="Country" name="country" type="text" value=""><br>
    <input class="entry" placeholder="Primary Contact" name="primarycontact" type="text" value=""><br>
  <input class="entry" placeholder="Primary Email" name="primaryemail" type="text" value=""><br>
  <input class="entry" placeholder="Subscription Type" name="subscriptiontype" type="text" value=""><br>
  <input class="entry" placeholder="Subscription Status" name="subscriptionstatus" type="text" value=""><br>
  <input class="entry" placeholder="Subscription End Date" name="subscriptionenddate" type="text" value=""><br>

        <input class="button" type="submit">

    </form>
</body>

php script to add to mysql

<?php
include 'db.php';

$companyname=$_POST['companyname'];
$taxno=$_POST['taxno'];
$address1=$_POST['address1'];
$suburb=$_POST['suburb'];
$state=$_POST['state'];
$postcode=$_POST['postcode'];
$country=$_POST['country'];
$primarycontact=$_POST['primarycontact'];
$primaryemail=$_POST['primaryemail'];
$subscriptiontype=$_POST['subscriptiontype'];
$subscriptionstatus=$_POST['subscriptionstatus'];
$subscriptionenddate=$_POST['subscriptionenddate'];


$sql = "INSERT INTO `companies` 
                ( `companyid` , `accountno` , `companyname` , 
                `taxno` , `address1` , `address2` , `suburb` , 
                `state` , `postcode` , `country` , `primarycontact` , 
                `primaryemail` , `subscriptiontype` , `subscriptionstatus` , 
                `subscriptionenddate` , `datecreated` ) 
        VALUES ( NULL , 
                NULL , 
            '".mysqli_real_escape_string($conn,$_POST['companyname'])."' , 
            '".mysqli_real_escape_string($conn,$_POST['taxno'])."' , 
            '".mysqli_real_escape_string($conn,$_POST['address1'])."' ,
             NULL , 
            '".mysqli_real_escape_string($conn,$_POST['suburb'])."' , 
            '".mysqli_real_escape_string($conn,$_POST['state'])."' , 
            '".mysqli_real_escape_string($conn,$_POST['postcode'])."' , 
            '".mysqli_real_escape_string($conn,$_POST['country'])."' , 
            '".mysqli_real_escape_string($conn,$_POST['primarycontact'])."' , 
            '".mysqli_real_escape_string($conn,$_POST['primaryemail'])."' , 
            '".mysqli_real_escape_string($conn,$_POST['subscriptiontype'])."' , 
            '".mysqli_real_escape_string($conn,$_POST['subscriptionstatus'])."' , 
            '".mysqli_real_escape_string($conn,$_POST['subscriptionenddate'])."' )";

mysqli_query($conn, $sql);

mysqli_close($conn);
?>

my database file

<?php

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$db = 'a34511pidata';

$conn = mysqli_connect($dbhost,$dbuser,$dbpass);
mysqli_select_db($conn, $db);

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

echo "Database Connected Ok..";

?>

The usual procedure for troubleshooting this kind of problem is to dump or echo the contents of the query text string (`$sql` in your case). Often you can spot the problem immediately. If not, try issuing the query via some kind of MySQL client, and see what happens. ALSO, `mysqli_query()` returns true if it succeeds. Read this. http://php.net/manual/en/mysqli.query.php If it fails you need to check for errors. Read this. http://php.net/manual/en/mysqli.error.php You're welcome to [edit] your question with more details if you stlll have trouble after doing those things. — O. Jones, Jan 26 '17 at 13:19
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Jan 26 '17 at 13:32
Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` in your `database.php` I bet that will be an eye opener — RiggsFolly, Jan 26 '17 at 13:33
___If you cannot read it you cannot debug it___ Now I edited the query its easy. You have 16 column names and only 15 values so the query is failing — RiggsFolly, Jan 26 '17 at 13:39

RiggsFolly · Accepted Answer · 2017-01-26T14:03:47.657

If you make your code readable you will find it easier to debug.

Also when using mysqli_ you need to check for errors after you issue a query.

You should also be using prepared and parameterized queries to protect yourself against SQL Injection attacks

Your actual error was that you had 16 columns listed and only 15 variables in the VALUE list.

I filled the missing column with a NOW() I assume that would be what was required for a datecreated column.

<?php
include 'db.php';

$sql = "INSERT INTO `companies` 
                ( `companyid`, `accountno`, `companyname` , 
                `taxno` , `address1`, `address2`, `suburb` , 
                `state` , `postcode`, `country`, `primarycontact` , 
                `primaryemail`, `subscriptiontype` , 
                `subscriptionstatus`, `subscriptionenddate`,    
                `datecreated` ) 
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW())";

$stmt = $conn->prepare($sql);
if ( ! $stmt ) {
    echo $stmt->error;
    exit;
}

// I dont know your data type so you may need to check the data types I used here
$stmt->bind_param('iisssssssssssss',
                    NULL, NULL, 
                    $_POST['companyname'],
                    $_POST['taxno'],
                    $_POST['address1'],
                    NULL , 
                    $_POST['suburb'],
                    $_POST['state'], 
                    $_POST['postcode'], 
                    $_POST['country'], 
                    $_POST['primarycontact'], 
                    $_POST['primaryemail'], 
                    $_POST['subscriptiontype'], 
                    $_POST['subscriptionstatus'], 
                    $_POST['subscriptionenddate']
                );



$stmt->execute();
if ( ! $stmt ) {
    echo $stmt->error;
    exit;
}

mysqli_close($conn);
?>

I have to say I find it a little odd that you are passing NULL as the second parameter i.e. for the column accountno. That may well be your next error, but that depends on how that column is defined in your database.

Thank you, I have amended the code and it worked. Although the NULL did not work. I did a work around for that. I am trying to now add so this data gets added to this table and more data gets added to another table. I tried to amend the code however it still not being added to the second table I am not getting an error message. What is the best way to show you my workings? amend the original post? Thanks for you help. — Raggs, Feb 01 '17 at 03:46

Data not being added to SQL, No Errors

1 Answers1