Associating columns in database

  • shibbyy05
  • Beginner
  • Beginner
  • shibbyy05
  • Posts: 64
  • Loc: Ohio

Post 3+ Months Ago

Alright, I have been struggling with creating this script for a little while and I was hoping someone could help me out a bit.

Lets say I have 2 columns in the MySql database (Sku, SkuDiff).

Sku has a listing of skus which contains duplicate skus. The duplicates are there because there are associated skus in SkuDiff. So, for Example:

Sku | SkuDiff
----------------
1234 | 1234-1
1234 | 1234-2
1234 | 1234-3

(It's actually similar to the above example, so I figured using regular expressions I could associate)

What I need to do is create a script that creates a csv based on the given data that would display like below.

Sku | SkuDiff
----------------
1234 | 1234-1, 1234-2, 1234-3
4567 | 4567-1, 4567-2, 4567-3

If you could point me in the right direction guys that would be great!
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • lostboy
  • Expert
  • Expert
  • lostboy
  • Posts: 511
  • Loc: Just north of Toronto

Post 3+ Months Ago

Its seems you are a looking for a cross-tab query. You can try it using the articles methods, or you can query it and use PHP to process the query and the resultset into what you need it to be. I can guess that the query is something you already have, so lets look at the processing. The below should create an array with SKUs as the key and the comma separated values as the value portion of the array. Once you have this array,, it's a simple thing to loop thru the array and print out the key and value sections into a file
Code: [ Select ]

//..query code

if($result){
 if(mysql_num_rows($result)>0){
  $arrSku = array();
  while($rows = mysql_fetch_array($result)){  

   //see if the sku value is present in the array and add the skudiff
   if(in_array($rows['sku'])){
    $arrSku[$rows['sku']] .= ','.$rows['skudiff'];
   }else{
    $arrSku[$rows['sku']] = $rows['skudiff'];
   }
  }
 }
}
  1. //..query code
  2. if($result){
  3.  if(mysql_num_rows($result)>0){
  4.   $arrSku = array();
  5.   while($rows = mysql_fetch_array($result)){  
  6.    //see if the sku value is present in the array and add the skudiff
  7.    if(in_array($rows['sku'])){
  8.     $arrSku[$rows['sku']] .= ','.$rows['skudiff'];
  9.    }else{
  10.     $arrSku[$rows['sku']] = $rows['skudiff'];
  11.    }
  12.   }
  13.  }
  14. }
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

Are you trying to do this with just an SQL query so you can run something like the following ?

Code: [ Select ]
~/$ cat query.sql | mysql -uuser -ppass > output.csv


Is this for a web application where someone requests the CSV file on the fly and something like PHP/ASP/etc will communicate between MySQL and the browser ?

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: No registered users and 102 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.