Insert multiple columns into table using mysqli

Question

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?

Community · Accepted Answer · 2023-11-17 20:11:10Z

1

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

edited Nov 17, 2023 at 20:11

CommunityBot

11 silver badge

answered Nov 7, 2013 at 14:32

Rayhan Muktader

2,1263 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TiMESPLiNTER · Accepted Answer · 2013-11-07 14:10:57Z

1

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
}

edited Nov 7, 2013 at 14:10

answered Nov 7, 2013 at 14:02

TiMESPLiNTER

5,9193 gold badges31 silver badges68 bronze badges

1 Comment

user1416564 Over a year ago

Thank you for responding. while this isn't quite what I was looking for, it is definitely something that would be useful in the future. For my needs it really isn't worth the effort to change up the DB in this manner. But thanks!

Collectives™ on Stack Overflow

Insert multiple columns into table using mysqli

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related