increment numeric porition of a column name inside a SQL statement

Question

I have a table that contains columns q1,q2,q3,q4,q5,.. as you can see the columns names consists of letter q and numbers i,i+1,i+2,i+3,... inside a foreach loop, I want to loop through an array and add each array element to a single column. e.g. arr = [A,B,C,D] A goes to q1, B goes to q2,.. Here is my attempt:

$selected_ans = $_POST['options'];
$answersArr = implode(' ', $selected_ans);
foreach ($answersArr as $ans) {
    // column name consisting of letter q and a numeric value
    $int = "q"+1;
    $sql = "INSERT INTO MyGuests ($int)VALUES ($ans) WHERE username = $VALID_USER";
    $conn->query($sql);
    // increment the numeric value
    $int++;
}

This does not work so how can this be correctly implemented? thanks

implode is going to join array elements so there is no use using foreach I think you need to use explode — Sugumar Venkatesan, Aug 10 '17 at 11:08
$int = "q"+1; must be $int = "q"+$int; and initially $int value is 1 — Saroj, Aug 10 '17 at 11:08
@vSugumar thanks for the comment so how can I correctly add the elements into an array? — jimiss, Aug 10 '17 at 11:09
If you find yourself with a table with columns q1, q2, a3, etc, then you can be sure that something has gone very, very wrong. — Strawberry, Aug 10 '17 at 11:14
INSERT query doesn't support the WHERE clause: https://stackoverflow.com/questions/485039/mysql-insert-where-query You either need to construct a full INSERT query and run it once or run an empty INSERT and run UPDATE queries inside the loop. — Kita, Aug 10 '17 at 11:17
@Strawberry I agree its a bad design. how should I handle a table that is suppose to hold a users answer to a list of questions? — jimiss, Aug 10 '17 at 11:21
The topic of database normalisation is surely exhausted. It ill behoves us to rehearse those strategies here. — Strawberry, Aug 10 '17 at 11:23

score -1 · Answer 1 · answered Aug 10 '17 at 11:30

I think this is more like what you're looking for. It's a bit sloppy, but it'll get the job done and is a good starting point for you to refactor and improve upon.

$selected_ans = $_POST['options'];
// Make sure to sanitize the raw input
foreach ($selected_ans as $k => $v) {
    $selected_ans[$k] = '"' . mysqli_real_escape_string($v) . '"';
}
// Dynamically create a list of column names
$columns = [];
for ($i = 1; $i <= count($selected_ans); $i++) {
    array_push($columns, 'q' . $i);
}
$sql = "INSERT INTO MyGuests (username, " . implode(", ", $columns) . ") VALUES (\"" . mysqli_real_escape_string($VALID_USER) . "\", " . implode(", ", $selected_ans) . ")";
$conn->query($sql);

increment numeric porition of a column name inside a SQL statement

1 Answers1