CODEWITHSUNDEEP
phpsqlmysqli
user1416564
user1416564

Reputation: 401

Insert multiple columns into table using mysqli

I'm trying to insert form data into my sql table. The form data is a long questionnaire that has multiple questions. This means that the table I'm inserting into has multiple columns, 30 to be exact.

Is there a way for me to quickly insert one row of 30 columns with minimal or efficient code? Perhaps I can have the "name" value in my form be equal to the variable name in the my table? My form is a mixture of normal text fields and some checkbox groups.

I'm using php and hoping to use mysqli prepared statements.

TLDR: Can I shorten this ?: 

$query = "INSERT INTO table (a, b, c, d, e , f , g , h ,i j, ........) 
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,....)";

mysqli_stmt_bind_param($stmt,'sssiiisiiiiiiiiiisss...', ....);

Or do I need to just brute force it?

Upvotes: 0

Views: 2508

Answers (2)

Rayhan Muktader
Rayhan Muktader

Reputation: 2106

You could try to call mysqli_stmt_bind_param using call_user_func_array and pass in an array of parameters:

$sql_link = mysqli_connect('localhost', 'my_user', 'my_password', 'world'); 
$type = "isssi"; 
$param = array("5", "File Description", "File Title", "Original Name", time()); 
$sql = "INSERT INTO file_detail (file_id, file_description, file_title, file_original_name, file_upload_date) VALUES (?, ?, ?, ?, ?)"; 
$sql_stmt = mysqli_prepare ($sql_link, $sql); 

call_user_func_array('mysqli_stmt_bind_param', array_merge (array($sql_stmt, $type), $param); 
mysqli_stmt_execute($sql_stmt);

source

Upvotes: 1

TiMESPLiNTER
TiMESPLiNTER

Reputation: 5889

Afaik no you can't shorten it.

But you could change your DB model that a result record only contains one answer which is linked to the question and to the user the answer is from. So you are also variable on how many questions there are in a quiz. And you don't have an oversize of 28 columns if the quiz only have 2 questions.

table: user
-----------
id (PK)
username

table: quiz
-----------
id (PK)
title (FK)

table: question
---------------
id (PK)
question
quiz_idfk (FK)


table: answer
-------------
id (PK)
answer
question_id (FK)
user_id (FK)

With that model the insert of a result would be (only pseudo code):

foreach($_POST['answer'] as $qid => $ans) {
    // sql: INSERT INTO answer SET answer = :ans, question_id = :qid, user_id = :uid

    // :ans the answer text ($ans)
    // :qid the question id ($qid)
    // :uid the currently logged in user
}

Upvotes: 1

Related Questions