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

No errors? Is $_SESSION['username'] set? Are the includes working? Is $_POST['submit'] set? In other words.. could be anything. Turn on your errors and find out. — putvande
– putvande, Commented Aug 10, 2013 at 19:23

Jonast92 · Accepted Answer · 2013-08-10 20:07:51Z

2

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.

edited Aug 10, 2013 at 20:07

answered Aug 10, 2013 at 19:30

Jonast92

4,9671 gold badge20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

kaneo Over a year ago

I removed the accid and its still the same

Jonast92 Over a year ago

Well this is a start, I'll look better at it.

Kosh Over a year ago

why he need to check the username session while he doesnt have any users yet? weird !

kaneo Over a year ago

I'M going to update the code above and include the form so you can see the complete code.

Jonast92 Over a year ago

That's not the point. Maybe he's a user creating a new user, you can't be sure, that's his implementation, I'm just saying what he should do if his implementation is correct. Did you down vote this, @k0sh

|

Kosh · Accepted Answer · 2013-08-10 19:46:53Z

-1

     $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.

edited Aug 10, 2013 at 19:46

answered Aug 10, 2013 at 19:33

Kosh

6,3433 gold badges38 silver badges68 bronze badges

10 Comments

kaneo Over a year ago

I removed 1 column and nothing change still the same. its weird

Jonast92 Over a year ago

He has already mentioned that he skipped the accid column in his insert query so your edit is pointless.

Kosh Over a year ago

@Jonast92 i re-edit my post !. he was given the ID a char while its int

Jonast92 Over a year ago

@k0sh He was never giving it a value, how could he be giving him a char if he was never inserting anything?

Kosh Over a year ago

when he did this $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(?,?,?,?,?,?,?,?,?)"); its obvious that the first inserted value is the accid, which when he inserted the 9 chars to the database, its ordered base on his selection at the insert stage. so when he said Value(?) as the first one then the database automatically will think that he inserting to the accid DUUUUH

|

Collectives™ on Stack Overflow

php cannot seem to insert data into mysql database

2 Answers 2

8 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related