Jump to content

Prepared statements: having trouble with PDO and Mariadb


LivingLearning

Recommended Posts

Hi all,  I've been working out my learning issues slowly and was able to figure out the process to communicate from html form to PDO to Mariadb fine.

I decided to try to work with Prepared Statements via PDO into my Mariadb and am having some probs I can't get through.

I'm trying to simplify things as much as possible so I can work it out, but don't think I've got it.

Can anyone help point out a better approach to using prepared statements for a simple form with a few q's.

I've scaled down my practice files to ease of use.

I'm using my own Apache test server locally, w/ latest ver of apache and Mariadb 10.1.26 on windows 8.1

Here is simple html file:

<!doctype html>
<html lang="en-US">

    <!-- ###################################################################################################/-->
    <!-- Main Page for Monsters of SciFi Tribute -- Testing Only /-->
    <!-- ###################################################################################################/-->

<head>
    <meta charset="UTF-8">
    <title>SciFi Monsters</title>
    <link href="http://localhost/CheckFormData.php" type="text/php" rel="stylesheet">
</head>

<body>
        <br>
        <br>
        <br>
        
        <!-- This section is for: Take a quick Godzilla survey /-->
        <div id="survey-div">
            <form action = "CheckFormData.php" method = "post">
                <fieldset id="survey-fieldset">
                    <legend>Take one of our quick surveys.</legend>
                    <p>
                        <label>1. Who is your <strong>favorite monster</strong> character (Godzilla related or not):</label>
                        <input id="FavMonster" name="FavMonster" type="text" value="" />
                    </p>
                    <p>
                        <label>2. Who is your <strong>favorite villian</strong>:</label>
                        <input id="FavVillian" name="FavVillian" type = "text" />                
                    </p>
                    <p>
                        <label>3. What is your <strong>favorite monster related movie</strong>:</label>
                        <input id="FavMovie" name="FavMovie" type = "text" />
                    </p>     
                    <p>
                        <label>Press 'Submit' when you are done.</label>
                        <input type="submit" value="Submit" name="submit_button" />
                    </p>
                </fieldset>
            </form>
        </div>
    
</body>
</html>

Here is my CheckFormData.php test file (what a mess, haha):

<!DOCTYPE html>
<html lang = "en-US">
    
<head>
    <meta charset = "UTF-8">
    <title>CheckFormData.php</title>
</head>
    
    <!-- The attempt with this file is to verify all user entered data is formatted correctly, then establish
    a database connection, and attempt to communicate that data to the database, all from one file. /-->
    
<body>
    <?php
    // define variables and set to empty values
    // 1st - Set variables for form array $_POST
    $SurveyForm = $_POST;
    $Q1 = $SurveyForm['FavMonster'];
    $Q2 = $SurveyForm['FavVillian'];
    $Q3 = $SurveyForm['FavMovie'];

    // Set any functions up
    
    // validate all form data
    function test_input($data) {
        $data = trim($data);
        $data = stripslashes($data);
        $data = htmlspecialchars($data);
        return $data;
    }
    
    // We are NOT testing for 'required' fields
    if ($_SERVER["REQUEST_METHOD"] == "POST") {
        //Start validation on all Q's
        test_input($Q1);
        test_input($Q2);
        test_input($Q3);
        
        // Start move validation
        if (!preg_match("/^[a-zA-Z ]*$/",$Q1)) {
            $SQErr = "Only letters and white space allowed";
        }    
        if (!preg_match("/^[a-zA-Z ]*$/",$Q2)) {
            $SQErr = "Only letters and white space allowed";
        }
        if (!preg_match("/^[a-zA-Z ]*$/",$Q3)) {
            $SQErr = "Only letters and white space allowed";
        }
    }


    // OK, presuming my validations are working, now attempt to connect to database
    // PDO connection check
    $servername = "localhost";
    $username = "Any";
    $password = "BurgerKing";
    $dbname = "test-survey1";
      try {
        //Attempt MySQL server connection
        $conn = new PDO("mysql:host=$servername; dbname=$dbname", $username, $password);
        
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        // Prepare sql and bind parameters
        $stmt = $conn->prepare("INSERT INTO [test-survey1].favorites (FavMonster, FavVillian, FavMovie) VALUES (:Q1, :Q2, :Q3)");
        $stmt->bindParam(':FavMonster', $Q1);
        $stmt->bindParam(':FavVillian', $Q2);
        $stmt->bindParam(':FavMovie', $Q3);
        
        $Q1;
        $Q2;
        $Q3;
        $stmt->execute();
        
        echo "New records created successfully";

        }    
     catch(PDOException $e) 
        {
        echo "Connection failed: " . $e->getMessage();
        }
    $conn = null;
    ?>

</body>
</html>

** What this all gives me right now is:   SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

 

Link to comment
Share on other sites

The name of the parameters you bind should match the name of the placeholders, not the name of the fields. Your parameter names should be ":Q1", ":Q2" and ":Q3", not ":FavMonster", ":FavVillian" and ":FavMovie"

Link to comment
Share on other sites

Hi and thanks for the response,

I've changed the params as follows:

        // Prepare sql and bind parameters
        $stmt = $conn->prepare("INSERT INTO [test-survey1].favorites (FavMonster, FavVillian, FavMovie) VALUES (:Q1, :Q2, :Q3)");
        $stmt->bindParam(':Q1', $Q1);
        $stmt->bindParam(':Q2', $Q2);
        $stmt->bindParam(':Q3', $Q3);
Was that what you were referring to.

This is my latest error after running it:

Connection failed: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[test-survey1].favorites (FavMonster, FavVillian, FavMovie) VALUES ('Me', 'You',' at line 1

I can't help but feel that perhaps I'm not using the proper commands for my version of Mariadb or something like that.

Is there a way/place to go to make sure that I'm using the correct commands for my ver of Mariadb and PDO.  I can't seem to locate that info anywhere.

I'm running MariaDB ver 10.1.26, Apache 2.4.27, and PHP 7.1.9

Definately getting confused on where to find the correct info to run now and is hindering the learning curve.

Link to comment
Share on other sites

What is [test-survey1]? If the table name is" test-survey", then you should have it between backticks `test-survey`, though I've never seen a table name with a hyphen in it before, I'm not sure it would be valid.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...