Jump to content

hoto make prepared statement


shadd

Recommended Posts

how can I make this work .I need to pass table name as variable and concat this query all together

    -- Prepare ansstatement
    PREPARE ansstatement FROM
    "CONCAT('INSERT INTO ',?,'(Ans_Id,Answer,answer_img) VALUES("',?,'","',?
        ,'","',?,'")' )";
    -- Define query parameters
    SET @p1 = l_table_name;
    SET @p2 = inQueId;

Link to comment
Share on other sites

1 hour ago, shadd said:

.I need to pass table name as variable

Not allowed. Only values can be passed as parameters, not table or column identifiers.

Having variable table names is oftenindicative of a poor database design.

Don't insert primary keys (Ans_id) in INSERT statements, let them be created automatically.

$stmt = $pdo->prepare("INSERT INTO tablename (answer, answer_image) VALUES (?, ?)");
$stmt->execute( [ $answer, $image_path ] );

 

Link to comment
Share on other sites

If you are trying to enter data, but you don't know exactly how many fields (columns) you are going to be saving to the table then you could do something like the following. Though would still need the correct table's column names and the corresponding data that goes with it. Here's an example ->

function create(array $data, $pdo, $table) {
    try {
        /* Initialize an array */
        $attribute_pairs = [];

        /*
         * Set up the query using prepared states with the values of the array matching
         * the corresponding keys in the array
         * and the array keys being the prepared named placeholders.
         */
        $sql = 'INSERT INTO ' . $table . ' (' . implode(", ", array_keys($data)) . ')';
        $sql .= ' VALUES ( :' . implode(', :', array_keys($data)) . ')';

        /*
         * Prepare the Database Table:
         */
        $stmt = $pdo->prepare($sql);

        /*
         * Grab the corresponding values in order to
         * insert them into the table when the script
         * is executed.
         */
        foreach ($data as $key => $value)
        {
            if($key === 'id') { continue; } // Don't include the id:
            $attribute_pairs[] = $value; // Assign it to an array:
        }

        return $stmt->execute($attribute_pairs); // Execute and send boolean true:

    } catch (PDOException $e) {

        if ($e->errorInfo[1] === 1062) {
            return false;
        }

        throw $e;
    } catch (Exception $e) {
        echo 'Caught exception: ', $e->getMessage(), "\n"; // Not for a production server:
    }

    return true;
}

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.