Skip to content
Advertisement

SQL adding data into 1 row using while loop

I need to get data into the same row in SQL, I am using a while loop. This might not be the right option. What I need to do is get all the data into one row. The code I have is adding a new row through each iteration. So first row must have data in column 1 through 5 not in a new row (see pic for how it is adding data now)

$length = count($row);
$i = 1     
$j=1;
while ($i <= $length ){
 
$sql1="insert into Anser ([$i]) values ($j) ";
$stmt1 = sqlsrv_query($conn, $sql1);
if($stmt1 === false){
  die( print_r(sqlsrv_errors(), true));
}
  $i++;
  $j++;
}

SQL DB screenshot

Advertisement

Answer

This expression:

insert into Anser ([$i]) values ($j)

Inserts one row into the table, for each column. That is why you are getting multiple rows. What you want is to specify all columns at the same time:

insert into Answer([1], [2], [3], [4], [5])
     values (1, 2, 3, 4, 5);

Note that if when you construct the values clause, you should be using parameters rather than munging the query string with literal values. This has multiple benefits:

  • It protects against SQL injection attacks.
  • It prevents syntax errors.
  • It allows the query to be compiled only once, which can be a benefit if there are large numbers of inserts.
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement