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++; }
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.