bind_param issue PHP prepared Insert statement

Question

I have a user reg form which then inserts into the db however its throwing up a bind param error

if(isset($_POST['submit'])){
// Login Query
// Connection
$mysqli = new mysqli("localhost", "root", "", "pg");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
// Query 
$query = "INSERT INTO `affiliates` (aid,affname,title,fname,lname,add1,add2,add3,city,county,country,pcode,email,password,paymethod) VALUES (,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
$stmt = $mysqli->prepare($query);
$stmt->bind_param('ssssssssssssss',$_POST['affname'],$_POST['title'],$_POST['fname'],$_POST['lname'],$_POST['add1'],$_POST['add2'],$_POST['add3'],$_POST['city'],$_POST['county'],$_POST['country'],$_POST['pcode'],$_POST['email'],$_POST['password'],$_POST['paymethod']); // If 2x Variables are used etc 's' would become 'ss',$_GET['VAR'],$_GET['VAR']
// Bind Results
$stmt->execute();
//$result = $stmt->get_result()->fetch_all();
}

I cant work out whats wrong can anyone help thanks

Updated Form & Query Code

        <form id="form1" name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF'];?>">
    <p>
    <label for="textfield"></label>
    <input type="text" name="affname" id="textfield" />
    </p>
    <p>
    <label for="textfield"></label>
    <input type="text" name="title" id="textfield" />
    </p> <p>
    <label for="textfield"></label>
    <input type="text" name="fname" id="textfield" />
    </p> <p>
    <label for="textfield"></label>
    <input type="text" name="lname" id="textfield" />
    </p> <p>
    <label for="textfield"></label>
    <input type="text" name="add1" id="textfield" />
    </p> <p>
    <label for="textfield"></label>
    <input type="text" name="add2" id="textfield" />
    </p> <p>
    <label for="textfield"></label>
    <input type="text" name="add3" id="textfield" />
    </p> <p>
    <label for="textfield"></label>
    <input type="text" name="city" id="textfield" />
    </p> <p>
    <label for="textfield"></label>
    <input type="text" name="county" id="textfield" />
    </p> <p>
    <label for="textfield"></label>
    <input type="text" name="country" id="textfield" />
    </p> <p>
    <label for="textfield"></label>
    <input type="text" name="pcode" id="textfield" />
    </p> <p>
    <label for="textfield"></label>
    <input type="text" name="email" id="textfield" />
    </p>
    <p>
    <label for="textfield"></label>
    <input type="text" name="password" id="textfield" />
    </p>
    <p>
    <label for="textfield"></label>
    <input type="text" name="paymethod" id="textfield" />
    <input type="hidden" value="" name="aid" />
    </p>
    <p>
    <input type="submit" name="submit" id="submit" value="Submit" />
    </p>
    </form>
    <?php
    if(isset($_POST['submit'])){
    // Login Query
    // Connection
    $mysqli = new mysqli("localhost", "root", "", "pg");
    if ($mysqli->connect_errno) {
      echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }
    // Query 
    $query = "INSERT INTO `affiliates` (aid,affname,title,fname,lname,add1,add2,add3,city,county,country,pcode,email,password,paymethod) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param('sssssssssssssss',$_POST['aid'],$_POST['affname'],$_POST['title'],$_POST['fname'],$_POST['lname'],$_POST['add1'],$_POST['add2'],$_POST['add3'],$_POST['city'],$_POST['county'],$_POST['country'],$_POST['pcode'],$_POST['email'],$_POST['password'],$_POST['paymethod']); // If 2x Variables are used etc 's' would become 'ss',$_GET['VAR'],$_GET['VAR']
    // Bind Results
    $stmt->execute();
    //$result = $stmt->get_result()->fetch_all();
    }

I have amended the query and now have the correct amount of vars being passed However it now shows NO errors but also does not insert the data

That SQL isn't valid, so your prepare is probably failing. I can guess the error. `(,?,?,?,?,?,?,?,?,?,?,?,?,?,?` missing end bracket and something after the opening. — Jonnix, Aug 11 '17 at 09:21

score 1 · Answer 1 · answered Aug 11 '17 at 09:27

Change

$query = "INSERT INTO `affiliates` (aid,affname,title,fname,lname,add1,add2,add3,city,county,country,pcode,email,password,paymethod) VALUES (,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

to :

$query = "INSERT INTO `affiliates` (aid,affname,title,fname,lname,add1,add2,add3,city,county,country,pcode,email,password,paymethod) VALUES (NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

or to

$query = "INSERT INTO `affiliates` (affname,title,fname,lname,add1,add2,add3,city,county,country,pcode,email,password,paymethod) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

Michael GEDION · Accepted Answer · 2017-08-11T09:35:18.890

0

As your aid is autocremented just change the query to this, no need to insert it in the query.

$query = "INSERT INTO affiliates (affname,title,fname,lname,add1,add2,add3,city,county,country,pcode,email,password,paymethod) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

edited Aug 11 '17 at 09:35

answered Aug 11 '17 at 09:23

Michael GEDION

879
8
16

Just added closing bracket ( spotted after I pasted code ) Also the aid is an auto increment value that doesnt get passed in the query so would the ? still need to be present ? – Chris Aug 11 '17 at 09:25
Ok, if aid is autocrement no need to insert, let SQL do the job. just change the $query to the updated query i have posted. – Michael GEDION Aug 11 '17 at 09:36
Perfect thanks... don't know why but when i left the $_POST['aid'] in even though t wasnt passing data the query would just not work as soon as i remove it from the query & stored params it works fine That was frustrating ! Thanks :) – Chris Aug 11 '17 at 09:39

score 0 · Answer 3 · answered Aug 11 '17 at 09:29

you trying to insert 15 columns

1)aid

2)affname

3)title

4)fname

5)lname

6)add1

7)add2

8)add3

9)city

10)county

11)country

12)pcode

13)email

14)password

15)paymethod

but here you are metioning only 14
 1 2 3 4 5 6 7 8 9 10 11 12 13 14 
,?,?,?,?,?,?,?,?,?,?, ?, ?, ?, ?
 s s s s s s s s s s  s  s  s  s

as well as here only 14

$_POST['affname'],$_POST['title'],$_POST['fname'],$_POST['lname'],$_POST['add1'],$_POST['add2'],$_POST['add3'],$_POST['city'],$_POST['county'],$_POST['country'],$_POST['pcode'],$_POST['email'],$_POST['password'],$_POST['paymethod']

I gave updated query etc although no errors now being shown but nothing being stored — Chris, Aug 11 '17 at 09:34

bind_param issue PHP prepared Insert statement

3 Answers3