Assistance with prepare statement

Question

Edit: Error: Column count doesn't match value count at row 1

I have been trying for a long time now (hours if not days with multiple attempts) to set up a prepared statement to stop SQL injection attacks and I just cannot get my head around it. Could someone help me out with this and point out where I have went wrong? I want to learn how to do this so I can use it in future but at this rate I will never get it.

The form:

            <form action="php/xaddPlayerSkills.php" method="post">      <!--player skills form to be added-->
        playerID  : <input type="int" name="playerID" value="<?php  echo $playerID ?>" readonly> </td></tr>
        SquadID: <input type="text" name="squadID"><br>
        Passing: <input type="text" name="passing" value="Standard: Spin: Pop:"><br>
        Tackling: <input type="text" name="tackling" value="Front: Rear: Side: Scrabble:"><br>
        Kicking: <input type="text" name="kicking" value="Drop: Punt: Grubber: Goal:"><br>

        Comments: <input type="text" name="comments"><br>
        Date: <input type="date" name="date"><br>

        <input type="Submit" value = "Add ">
        </form>

This is my processing page:

<?php session_start(); include('functions.php');


        $sheetNo="";
        $playerID=$_POST['playerID'];

        $squadID=$_POST['squadID'];
        $passing=$_POST['passing'];
        $kicking=$_POST['kicking'];
        $tackling=$_POST['tackling'];
        $comments=$_POST['comments'];
        $date=$_POST['date'];

                    /*  Use for error testing - Uncomment to check variable values when executed
                    ini_set('display_errors', 'On'); ini_set('html_errors', 0); error_reporting(-1);
                    print_r($_POST); */






                  //sets up and executes the connection using the information held above

/* THERE IS CONNECTION INFORMATION HERE BUT I HAVE REMOVED IT AS IT IS CREDENTIALS */

                  $con=mysqli_connect($host,$user,$userpass,$schema);
                  // Error handling: If connection fails, the next lines of code will error handle the problem and if possible, give a reason why.
             if (mysqli_connect_errno())
               {
               echo "Failed to connect to MySQL: " . mysqli_connect_error();
               }



            $result= mysqli_query($con,"INSERT INTO playerSkills VALUES (playerID,squadID,passing,tackling,kicking,comments,date)");

            $insert=$con->prepare($result);
            $insert->bind_param("isssssd",$playerID,$squadID,$passing,$tackling,$kicking,$comments,$date);
            $insert->execute();
            $insert->close();

        mysqli_close($con);
        header ("location: ../databasePlayers.php");
        ?>

You seem to have an extra `$sheetNo` in your bind statement. Apart from that you should add what the problem is exactly. — jeroen, May 05 '17 at 09:33
@jeroen thanks for pointing this out but unfortunately I still get an error — Thomas Murray, May 05 '17 at 09:39
@mi6crazyheart I have a new one now but at least it is feedback. I'll edit my post and put it on the top. Thanks for your comment. — Thomas Murray, May 05 '17 at 09:43
Can't say it's error or not but, in your ```bind_param("isssssd")``` 1st parameter should be an INTEGER but in your case it's going as STRING may be. Can u check that once? — Suresh, May 05 '17 at 09:48
One more your INSERT query has multiple VALUES. It should have one only. Like - ```$result= mysqli_query($con,"INSERT INTO playerSkills (playerID,squadID,passing,tackling,kicking,comments,date) VALUES('$playerID','$squadID','$passing','$tackling','$kicking','$comments','$date')") or die("Error: ".mysqli_error($con));``` — Suresh, May 05 '17 at 09:54

Jay Blanchard · Answer 1 · 2017-05-05T13:32:26.110

You have a couple of problems in your code, but the most notable is the placeholders in the query, which should ?'s instead of things like VALUES (playerID,squadID,passing,tackling,kicking... and that you're using a type double, d, to describe a date:

$con=mysqli_connect($host,$user,$userpass,$schema);
// Error handling: If connection fails, the next lines of code will error handle the problem and if possible, give a reason why.
if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

$result= "INSERT INTO playerSkills VALUES (?,?,?,?,?,?,?)";

$insert=$con->prepare($result);
$insert->bind_param("issssss",$playerID,$squadID,$passing,$tackling,$kicking,$comments,$date); // change d to s for the date
$insert->execute();
$insert->close();

Read the docs for clarification on the data types. d is for doubles, not dates. Then look at the examples for what you should use as placeholders.

EDIT: Caution - if one of these columns is an AUTO INCREMENT column you should not include it in the query as the database will take care of making sure the column is updated properly.

Assistance with prepare statement

1 Answers1