Dinesh DiNu Dinesh DiNu - 2 months ago 9
PHP Question

Combine Single Column Data into Double Column Data in php

I have 600 rows of data in one table and table structure is,

Table Name: city_Data

------------------------------
cityId | cityName
------------------------------
1 | chennai
2 | madurai
3 | trichy
4 | kovai
...
...
...
------------------------------


cityId - autoincrement

now i would like to mix this table data and inset into another table. this table's name is city_Mix.

---------------------------------------
mixId | city1 | city2
---------------------------------------
1 | chennai | madurai
2 | chennai | trichy
3 | chennai | kovai
4 | madurai | chennai
5 | madurai | trichy
6 | madurai | kovai
7 | trichy | chennai
...
...
...
---------------------------------------


here, city1 and city2 are
should not be same
and mixId - autoincrement

how to do this? anyone plz help me with sample code..

Answer

try the following query, which uses join to combine the city names which dont match and insert the dat to city_mix table

INSERT INTO city_mix
(city1,city2) select t1.cityName,t2.cityName from Table1 t1 join Table1 t2 on t1.cityName != t2.cityName;

http://sqlfiddle.com/#!9/2e01ea/3