Save in a single row the result of variable PHP (while/loop) from mysql query

Question

When I query, the result is composed of more than one value (WHILE/LOOP), for example:

   $query_sku = mysqli_query($connect, "SELECT t.column_name
        FROM skus AS s
            INNER JOIN table AS t ON t.column_id = s.column_id
                WHERE s.id_sku = '{$id_sku}'");
WHILE($reg = mysqli_fetch_object($query_sku )){
  $variable = $reg->column_name;
echo "<br/">;
}

The result of this query/while is:

BATERIA  
PARA
CÂMERA
CANON
PowerShot ELPH 330 HS

When I save the values in the database, the values are saved in different rows.

But, I want you to save in a single row:

BATERIA PARA CÂMERA CANON PowerShot ELPH 330 HS

How can I do this? Thanks!

I already tried to do this:

while($reg = mysqli_fetch_object($query_sku ))
    $variable .= " ".$reg->column_name;

But, the result is a repetition of same values:

PowerShot ELPH 330 HS BATTERY (RECHARGEABLE)PowerShot ELPH 330 HS BATTERY (RECHARGEABLE) CAMERAPowerShot ELPH 330 HS BATTERY (RECHARGEABLE) CAMERA CANON

**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Jan 04 '19 at 16:16
Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. — tadman, Jan 04 '19 at 16:16
The 90's bad internet tutorials on the internet still have a bad after taste.. 9 out of the 10 questions on stackoverflow with the PHP or MySQL tags and where the database is being queried contains some kind off [SQL injection vulnerability](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Raymond Nijland, Jan 04 '19 at 16:17
@RaymondNijland It's only 9/10 because of advocacy and raising awareness. It was 10/10 before. — tadman, Jan 04 '19 at 17:54

score 0 · Answer 1 · answered Jan 04 '19 at 16:18

youn could get the result direct using a select

SELECT group_concat( t.column_name  SEPARATOR ' ')
FROM skus AS s
INNER JOIN table AS t ON t.column_id = s.column_id
WHERE s.id_sku = '{$id_sku}'

and for insert you could use an insert/select

insert into my_table  (my_col)
SELECT group_concat( t.column_name  SEPARATOR ' ')
FROM skus AS s
INNER JOIN table AS t ON t.column_id = s.column_id
WHERE s.id_sku = '{$id_sku}'

anyway you should take a look a binding param and avoid the use of php var in sql (you ar at risk for sqlinjectio)

score 0 · Accepted Answer · answered Jan 04 '19 at 16:19

0

Putting aside the SQL injection vulnerabilities of your code. You can flatten the result to one column like this:

$variables = [];
while($reg = mysqli_fetch_object($query_sku)){
  $variables[] = $reg->column_name;
}

$oneColumn = implode(' ', $variables);

answered Jan 04 '19 at 16:19

madflow

7,718
3
39
54

Works fine! Thanks for the advice! ;) – Rogério de Sá Jr. Jan 04 '19 at 16:30

Save in a single row the result of variable PHP (while/loop) from mysql query

2 Answers2