#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9

    Prepared Statements Failing To Insert


    I am new to using prepared statements so if I should take this in a completely other direction I will do so. However, here is what I have been using to date. When I process the following script the page processes, however, nothing gets inserted into the database and the page returns no errors.

    Thanks for taking a look.

    PHP Code:
    <?php
    // Connect to database.
    $link mysql_connect('FOO','FOO','FOO');
    if (!
    $link) {
        die(
    'Could not connect: ' mysql_error());
    }

    // Select the current database.
    $db_selected mysql_select_db('FOO'$link);
    if (!
    $db_selected) {
        die (
    'Could not select the database : ' mysql_error());
    }

    // Here we go
    if (isset($_POST['atq_pickup_city'])) 
    $atq_pickup_city    sanitizeMySQL($_POST['atq_pickup_city']);
    if (isset(
    $_POST['atq_pickup_state'])) 
    $atq_pickup_state   sanitizeMySQL($_POST['atq_pickup_state']);
    if (isset(
    $_POST['atq_pickup_zip'])) 
    $atq_pickup_zip     sanitizeMySQL($_POST['atq_pickup_zip']);
    if (isset(
    $_POST['atq_delivery_city'])) 
    $atq_delivery_city  sanitizeMySQL($_POST['atq_delivery_city']);
    if (isset(
    $_POST['atq_delivery_state'])) 
    $atq_delivery_state sanitizeMySQL($_POST['atq_delivery_state']);
    if (isset(
    $_POST['atq_delivery_zip'])) 
    $atq_delivery_zip   sanitizeMySQL($_POST['atq_delivery_zip']);
    if (isset(
    $_POST['atq_make'])) 
    $atq_make           sanitizeMySQL($_POST['atq_make']);
    if (isset(
    $_POST['atq_vehicle_runs'])) 
    $atq_vehicle_runs   sanitizeMySQL($_POST['atq_vehicle_runs']); 
    if (isset(
    $_POST['atq_carrier_type'])) 
    $atq_carrier_type   sanitizeMySQL($_POST['atq_carrier_type']);

    //Prepare statement
    $query 'PREPARE statement FROM "INSERT INTO atq_auto VALUES(?,?,?,?,?,?,?,?,?)"';
    mysql_query($query);

    //Set Values
    $query 'SET @atq_pickup_city = "'.$atq_pickup_city.'",' .
             
    '@atq_pickup_state   = "'.$atq_pickup_state.'",' .
             
    '@atq_pickup_zip     = "'.$atq_pickup_zip.'",' .
             
    '@atq_delivery_city  = "'.$atq_delivery_city.'",' .
             
    '@atq_delivery_state = "'.$atq_delivery_state.'",' .
             
    '@atq_delivery_zip   = "'.$atq_delivery_zip.'",' .
             
    '@atq_make           = "'.$atq_make.'",'.
             
    '@atq_vehicle_runs   = "'.$atq_vehicle_runs.'",' .
             
    '@atq_carrier_type   = "'.$atq_carrier_type.'"';
    mysql_query($query);
        
    //Execute
    $query 'EXECUTE statement USING @atq_pickup_city,@atq_pickup_state,@atq_pickup_zip ,@atq_delivery_city,@atq_delivery_state,@atq_delivery_zip,@atq_make,@atq_vehicle_runs,@atq_carrier_type';
    mysql_query($query);

    //Deallocate
    $query 'DEALLOCATE PREPARE statement';
    mysql_query($query);

    function 
    sanitizeString($var){
        if (
    get_magic_quotes_gpc()) $var stripslashes($var);
        
    $var htmlentities($var);
        
    $var strip_tags($var);
        return 
    $var;
    }
    function 
    sanitizeMySQL($var){
        
    $var mysql_real_escape_string($var);
        
    $var sanitizeString($var);
        return 
    $var;
    }
    ?>
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    44
    Rep Power
    6
    try running the queries with phpMyAdmin
    and see if you get any errors
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9
    I removed the PHP and put the following into phpMyAdmin:
    PHP Code:
    PREPARE statement FROM "INSERT INTO atq_auto VALUES (?,?,?,?,?,?,?,?,?,?,?)";

    SET @atq_pickup_city    'atq_pickup_city';
    SET @atq_pickup_state   'atq_pickup_state';
    SET @atq_pickup_zip     'atq_pickup_zip';
    SET @atq_delivery_city  'atq_delivery_city';
    SET @atq_delivery_state 'atq_delivery_state';
    SET @atq_delivery_zip   'atq_delivery_zip';
    SET @atq_make           'atq_make';
    SET @atq_model          'atq_model';
    SET @atq_vehicle_runs   1;
    SET @atq_carrier_type   1;
        
    EXECUTE statement USING @atq_pickup_city, @atq_pickup_state, @atq_pickup_zip, @atq_delivery_city, @atq_delivery_state, @atq_delivery_zip, @atq_make, @atq_model, @atq_vehicle_runs, @atq_carrier_type
    I get the follow error:

    Error
    SQL query:

    EXECUTE statement USING @atq_pickup_city , @atq_pickup_state , @atq_pickup_zip , @atq_delivery_city , @atq_delivery_state , @atq_delivery_zip , @atq_make , @atq_model , @atq_vehicle_runs , @atq_carrier_type ;

    MySQL said:

    #1210 - Incorrect arguments to EXECUTE

    I also get this from the query window:

    PREPARE statement FROM "INSERT INTO atq_auto VALUES (?,?,?,?,?,?,?,?,?,?,?)";# MySQL returned an empty result set (i.e. zero rows).


    SET @atq_pickup_city = 'atq_pickup_city';# MySQL returned an empty result set (i.e. zero rows).

    SET @atq_pickup_state = 'atq_pickup_state';# MySQL returned an empty result set (i.e. zero rows).

    SET @atq_pickup_zip = 'atq_pickup_zip';# MySQL returned an empty result set (i.e. zero rows).

    SET @atq_delivery_city = 'atq_delivery_city';# MySQL returned an empty result set (i.e. zero rows).

    SET @atq_delivery_state = 'atq_delivery_state';# MySQL returned an empty result set (i.e. zero rows).

    SET @atq_delivery_zip = 'atq_delivery_zip';# MySQL returned an empty result set (i.e. zero rows).

    SET @atq_make = 'atq_make';# MySQL returned an empty result set (i.e. zero rows).

    SET @atq_model = 'atq_model';# MySQL returned an empty result set (i.e. zero rows).

    SET @atq_vehicle_runs = 1;# MySQL returned an empty result set (i.e. zero rows).

    SET @atq_carrier_type = 1;# MySQL returned an empty result set (i.e. zero rows).


    EXECUTE statement USING @atq_pickup_city, @atq_pickup_state, @atq_pickup_zip, @atq_delivery_city, @atq_delivery_state, @atq_delivery_zip, @atq_make, @atq_model, @atq_vehicle_runs, @atq_carrier_type;

    EXECUTE statement USING @atq_pickup_city, @atq_pickup_state, @atq_pickup_zip, @atq_delivery_city, @atq_delivery_state, @atq_delivery_zip, @atq_make, @atq_model, @atq_vehicle_runs, @atq_carrier_type;

    I really have no clue how to troubleshoot this error. Obviously it has something to do with the Execute statement but I haven't a clue where to start. Everything looks to be in order but who knows!

    Thanks for looking.
  6. #4
  7. No Profile Picture
    I haz teh codez!
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2003
    Posts
    2,547
    Rep Power
    2337
    If I might ask, why not use mysqli or PDO -- instead of the deprecated mysql_* functions -- which support prepared statements within the PHP API?
    I ♥ ManiacDan & requinix

    This is a sig, and not necessarily a comment on the OP:
    Please don't be a help vampire!
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    124
    Rep Power
    9
    I would certainly want to do this as close to best practice as possible. It's why I wanted to move to PREPARED STATEMENTS in the first place. Any reading suggestions? Google returns a ton of information but I would like to start with the basics.

    Comments on this post

    • ptr2void agrees : Have some rep for wanting to do the right thing!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    44
    Rep Power
    6
    Originally Posted by gmrstudios
    I would certainly want to do this as close to best practice as possible. It's why I wanted to move to PREPARED STATEMENTS in the first place. Any reading suggestions? Google returns a ton of information but I would like to start with the basics.
    www.php.net/manual/en/book.pdo.php

    what version of mysql are you using?

IMN logo majestic logo threadwatch logo seochat tools logo