how to insert different comma separated values into different coloumns in mysql

Issue

I am taking two comma separated values from the database from different coloumn(supplierrouteallocation:1000,1200 and routesymbol:10xx,12xx) and i want to insert this value into the another table into the different column for example like this

route    symbol

1000     10xx

1100     11xx

but i tried its not working can anyone helpme .thanks

my code

$routes = explode(",",$supplierrouteallocation);
$symbol = explode(",",$routesymbol);

$count=count($routes,$symbol);
for($i=0;$i<$count;$i++)
{

$sql_insertroute="INSERT INTO `fms`.`routestable` (
    `id` ,  

`route` 
`routesymbol`       
)
VALUES (NULL ,'$routes[$i]','$symbol[$i]')
";

mysql_query($sql_insertroute);

}

Solution

You’ve got various mistakes :

• count() only accepts a single array
• you’re missing a coma in your request : ('id', 'route'[,] 'routesymbol')

Here’s a piece of code which should work for you :

// get data
$routes = explode(",",$supplierrouteallocation);
$symbols = explode(",",$routesymbol);

// prepare the request
$sql_insertroute = "INSERT INTO `fms`.`routestable` (`id`, `route`, `routesymbol`) VALUES ";

// create an insert line per data couple
$vals = array();
$n = max(count($routes), count($symbols));
for($i=0;$i<$n;$i++) {
    $route = isset($routes[$i]) ? "'".$routes[$i]."'" : "NULL";
    $symbol = isset($symbols[$i]) ? "'".$symbols[$i]."'" : "NULL";
    array_push($vals, "(NULL, $route, $symbol)");
}

// join those lines with comas, and add'em to the request
$sql_insertroute .= implode(', ', $vals);

Answered By – zessx

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0