php cannot seem to insert data into mysql database

Question

I don't know what I'm doing wrong here, but I'm having hard time trying to insert data from a form into the database.

Here is my table

    CREATE TABLE IF NOT EXISTS `accounts` (
  `accid` int(11) NOT NULL AUTO_INCREMENT,
  `acc_number` varchar(50) NOT NULL,
  `cust_name` varchar(255) NOT NULL,
  `cust_tel` varchar(50) NOT NULL,
  `cust_address` varchar(255) NOT NULL,
  `cust_opendate` date NOT NULL,
  `cust_openamount` decimal(13,2) NOT NULL,
  `cust_balance` decimal(13,2) NOT NULL,
  `cust_message` text NOT NULL,
  `cust_openby` varchar(50) NOT NULL,
  PRIMARY KEY (`accid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

The $_session['username'] is set and the database is included and working. when i fill the input and click the submit i get the message New account has been added.

<?php
session_start();

$success = array();
$errors = array();

include('header.php');

if (isset($_SESSION['username']))
{
    include_once('libs/db-con.php');

    if (isset($_POST['submit']))
    {
        $accNumber = $_POST['accnumber'];
        $custName = $_POST['custname'];
        $tel = $_POST['tel'];
        $addess = $_POST['address'];
        $openDate = $_POST['opendate'];
        $openAmount = $_POST['openamount'];
        $balance = $_POST['balance'];
        $message = $_POST['message'];
        $openBy = $_SESSION['username'];

        if (empty($accNumber) || empty($custName) || empty($tel) || empty($addess) || empty($openDate) || empty($openAmount) || empty($balance) || empty($openBy))
        {
            $errors[] = 'All fields are required';
        }else{
            $query = $pdo->prepare("INSERT INTO accounts(accid, acc_number, cust_name, cust_tel, cust_address, cust_opendate, cust_openamount, cust_balance, cust_message, cust_openby)  VALUE(?,?,?,?,?,?,?,?,?)");
            $query->bindValue(1,$accnumber);
            $query->bindValue(2,$custName);
            $query->bindValue(3,$tel);
            $query->bindValue(4,$address);
            $query->bindValue(5,$openDate);
            $query->bindValue(6,$openAmount);
            $query->bindValue(7,$balance);
            $query->bindValue(8,$message);
            $query->bindValue(9,$openBy);

            $query->execute();

            $success[] = 'New account has been added';

            }
        }



        $query = $pdo->prepare("SELECT max(acc_number) FROM accounts");

                $query->execute();

               while($num = $query->fetchAll()){

                if($num[0] == null){

                $accnumber = "100001";

                }else{

                $accnumber = $num[0]++; 
            }    
        }       
?>

           <div class="today-acc">
            <h3>create new account</h3>
            <div id="form-newacc">
                <form action="" method="post">
                <table>
                    <tr>
                        <td><label for="accnumber">Account No:</label><input type="text" name="accnumber" value="<?php echo $accnumber[0]; ?>"></td>
                        <td><label for="custname">Name:</label><input type="text" name="custname"></td>
                        <td><label for="tel">Tel:  </label><input type="text" name="tel"></td>
                    </tr>
                    <tr>
                        <td><label for="address">Address:</label><input type="text" name="address"></td>
                        <td><label for="opendate">Open date:</label><input type="text" name="opendate" id="datepicker"></td>
                        <td><label for="openamount">Open Amount:</label><input type="text" name="openamount"></td>

                    </tr>
                    <tr>
                        <td><label for="balance">Balance:</label><input type="text" name="balance"></td>
                        <td><label for="message">Message:</label><textarea name="message" id="message"></textarea></td>


                    </tr>

                </table>
                        <input type="submit" name="submit" Value="Create">
                        <input type="reset" name="reset" Value="clear">
                </form>
                <?php foreach ($success as $successes) {
                    echo '<div id="success">'.$successes.'</div>';
                }?>
                <?php foreach ($errors as $error) {
                    echo '<div id="errors">'.$error.'</div>';
                }?>
            </div>
            </div>

<?php

}
else{

        header("location: login.php");
    }
         ?>

I updated the code. Thanks in advance

Answer 1

You are trying to insert into 10 columns:

accid, acc_number, cust_name, cust_tel, cust_address, cust_opendate, cust_openamount, cust_balance, cust_message, cust_openby

But you only got 9 question marks:

?,?,?,?,?,?,?,?,?

IT's recommended not to use question marks since it can lead to simple errors like these, it's better to do something like

"INSERT INTO accounts(accid, acc_number, cust_name, cust_tel, cust_address, cust_opendate, cust_openamount, cust_balance, cust_message, cust_openby)  VALUE(:accid, :acc_number, :cust_name, :cust_tel, :cust_address, :cust_opendate, :cust_openamount, :cust_balance, :cust_message, :cust_openby)"

If accid is indeed not suppose to receive a value, instead it shall receive an automatic incremented value, then you should not mention it in your query.

EDIT:

Some thoughts:

1: You check if the session of the username exists with this

isset($_SESSION['username']

but where's the else? You are not doing anything if the username is not set anywhere, try to output some errors that would indicate that the user is not logged in.

2: You assume that the query will work if you execute it, you can never be sure that an executed query will be true.

Instead if supposing that it's always true, like you're doing here:

$success[] = 'New account has been added';

Then you must check whether the insert query returns true or false, you can return error message if the executed query returns false.

$inserted = $query->execute();
$inserted ? $success[] = 'New account has been added' : $success[] = 'New account has not been added';

I'm using the ternary operator in the end, you should take a look at it.

Answer 2

     $blahblah = mysql_query("INTO accounts(accid, acc_number, cust_name, cust_tel,   
cust_address, cust_opendate, cust_openamount, cust_balance, cust_message, cust_openby)VALUE(?,?,?,?,?,?,?,?,?,?)") or die(mysql_error());

you are inserting 9 instead of 10 columns !

Edit

 $blahblah = mysql_query("INTO accounts VALUE('','?','?','?','?','?','?','?','?','?')") or die(mysql_error());  

  `accid` int(11) NOT NULL AUTO_INCREMENT,

the ID must be EMPTY as its auto inserted by your database.! and you are also inserting Char to an Int column.