Combine Single Column Data into Double Column Data in php

Question

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..

i just want to mix the datas and pass this values to showing location purpose. i dnt know how to combine datas and insert into new table. sofar i hav nt do nothing. — Dinesh DiNu
– Dinesh DiNu, Commented Oct 1, 2016 at 1:18
if you just want to mix what you will get is a table of 6000*6000 rows which is 36 million rows. is that what you want? — e4c5
– e4c5, Commented Oct 1, 2016 at 1:25
yes.. sorry.. thats not 6000.. thats 600. totally 359400 rows will be generated. — Dinesh DiNu
– Dinesh DiNu, Commented Oct 1, 2016 at 1:27

jophab · Accepted Answer · 2016-10-01 03:19:58Z

2

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

answered Oct 1, 2016 at 3:19

jophab

5,54714 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dinesh DiNu Over a year ago

hi jophab, thanks for the code. this is works as perfect. but i need the BETWEEN condition from your code. i tried with following code but not works, INSERT INTO city_mix (city1,city2) select t1.cityName,t2.cityName from citydata WHERE cityId BETWEEN 1 AND 2 t1 join citydata t2 on t1.cityName != t2.cityName; where is the error. if you know plz tel me.. thanks.

jophab Over a year ago

I think cityId is ambigious. Use t1.cityId before BETWEEN

Benjamin Dean · Accepted Answer · 2016-10-01 17:23:19Z

I'm making some assumptions about what you are specifically asking, if you were to supply a little more information it might help:

Does city_Mix already contain the field city2 or is that the column you wish to join from city_Data?
Are you doing this in PHP or SQL?
Is city_Mix really a self-iteration of the city_Data table?
Is the data you've presented representational of a database table, or a PHP data structure?

Once again, I am making some assumptions here, but I'll try to help based on what you've provided...

It appears that city_Mix matches #3 from my question list (so you want to take a list of items, and for each item in the list, map it to all other items on the list). If this assumption is correct, here is one way to approach the solution:

Using SQL

// You'll probably want to use an INNER JOIN to accomplish this quickly, something like the following

(SELECT 'cityName' FROM `city_Data` c INNER_JOIN `city_Data` m ON m.cityName != c.cityName)

Using PHP

FYI: I would recommend against this in favor of doing this with SQL, but wanted to provide it as "a way to do it" since you did not indicate which way you were approaching the solution.

<?php

$cityMixData = []; //Final data table as you've indicated you desire

//Connect to your database
$con = mysql_connect("localhost","inmoti6_myuser","mypassword");
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}
mysql_select_db("databaseName", $con);

// Define our SQL query
$query = "SELECT DISTINCT `cityName` FROM `city_Data` ORDER BY `cityId`";

// Execute the query
$cities = mysql_query($query);

$i = 1; // Iterator index
$cityMixData = [];
// Loop through the list of unique city names and map all other items accordingly
while( $city = mysql_fetch_array( $cities, MYSQL_ASSOC ) ) {
    $tmpArr = $cities;
    unset($tmpArr[$row[$i - 1]); // Remove self
    $cityMap = array_values($tmpArr); // reindex array
    $mixRow = array($mixId => $i, $city1 => $city['cityName'], $city2 => $cityMap); // Create a row with auto-increment ID, city1 === unique city name, city2 === list of unique cities sans-city1
    $cityMixData[] = $mixRow; // Add row to cityMixData which is multi-dimensional array
    $i++; // Increment iterator
}

// Connect to DB
// Create city_Mix table if not exist
// Appropriately generate SQL based on $cityMixData structure, might get messy
// Save to DB
?>

If you answer the questions I posed above, I will be able to help more.

hello Benjamin Dean, thanks for the code. now i get completed that table.. happy coding.. :-)

Collectives™ on Stack Overflow

Combine Single Column Data into Double Column Data in php

2 Answers 2

2 Comments

Using SQL

Using PHP

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Using SQL

Using PHP

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related