convert | separated values into new table using PHP

Question

Edit: I forgot to add the explode part that I'm having the issues with. I need the query result exploded.

I have been messing with this for a while and have a workable procedure in mysql, however I want to accomplish this as part of a larger script. I have a table filled with IDs and several columns of data with "|" separated values. How can I use or edit the below PHP to query and insert normalized results into a new table?

If I run this with an actual string: "40|180|408|360|40|166|80|59"; It will insert values (not the ID, which I also need) but when I try to pass in query results, I get "Array to string conversion" errors. Any guidance would be appreciated.

$query = "Select id, imageSize from T1";
$result = mysqli_query($conn, $query);
$myArray = explode('|', $result);

foreach($myArray as $value) {
   $sql = "INSERT INTO testExplode VALUES ($value)";
$result = mysqli_query($conn, $sql);
}

You need to fetch the first query data in to a variable before using to foreach — Abhik Chakraborty, Nov 19 '14 at 16:29
**WARNING**: When using `mysqli` you should be using parameterized queries 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 to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). — tadman, Nov 19 '14 at 17:09

vaso123 · Accepted Answer · 2014-11-19T16:42:16.327

2

If you want to insert all of your results then:

$query = "Select id, imageSize from T1";
$myArray = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($myArray)) {
    $sql = "INSERT INTO testExplode VALUES (" . mysqli_real_escape_string($conn, $row['imageSize']) . ")";
    mysqli_query($conn, $sql);
}

//If just only one:

$query = "Select id, imageSize from T1";
$myArray = mysqli_query($conn, $query);

$row = mysqli_fetch_assoc($myArray);
$sql = "INSERT INTO testExplode VALUES (" . mysqli_real_escape_string($conn, $row['imageSize']) . ")";
mysqli_query($conn, $sql);

NOTE:

Avoid sql injecions by escaping your variables in your querys.

EDIT:

Based on the OP comment.

$query = "Select id, imageSize from T1";
$myArray = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($myArray)) {
    $values = explode('|', $row['imageSize']);
    foreach ($values as $value) {
        $sql = "INSERT INTO testExplode VALUES (" . mysqli_real_escape_string($conn, $value) . ")";
    mysqli_query($conn, $sql);
    }

}

edited Nov 19 '14 at 16:42

answered Nov 19 '14 at 16:32

vaso123

12,347
4
34
64

sorry I forgot a pretty important part of my question -- the value i'm inserting needs to be exploded. I can't freakin figure it out. – caro Nov 19 '14 at 16:39
if your using mysqli shouldn't you be using prepared statements ? – Oliver Bayes-Shelton Nov 19 '14 at 16:44
1

Yes, you should, but it is procedural way. This time i've just want to show the logic of how it should be done, and not confuse OP with prepared statements. That should be the next step. – vaso123 Nov 19 '14 at 16:45
also is $value ever coming from a user ? surely you don't need to escape it if it comes from inside your application ? – Oliver Bayes-Shelton Nov 19 '14 at 16:45
Yeah, your right there dude sorry. Forget sometimes some people are still new to stuff :) – Oliver Bayes-Shelton Nov 19 '14 at 16:46
thank you so much for that answer, one last thing as I'm super new to PHP -- how can I also insert the id that has been queried that pertains to each exploded value? – caro Nov 19 '14 at 16:50
`$sql = "INSERT INTO testExplode VALUES ('".$row["id"]."' , " . mysqli_real_escape_string($conn, $value) . ")";` If you have 2 fields in your testExplode. If not, check mysql manual, how to insert data. – vaso123 Nov 19 '14 at 16:52
1

thanks again for all the help, i'll read up on prepared statements. :) – caro Nov 19 '14 at 17:00

convert | separated values into new table using PHP

1 Answers1