Back To Programming / Scripting / Coding Forum

Associating columns in database

  • shibbyy05
  • Beginner
  • Beginner
  • No Avatar
  • Joined: Sep 07, 2005
  • Posts: 64
  • Loc: Ohio
  • Status: Offline

Post November 12th, 2009, 4:03 pm

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
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post November 12th, 2009, 4:03 pm

  • lostboy
  • Expert
  • Expert
  • No Avatar
  • Joined: Jun 03, 2005
  • Posts: 511
  • Loc: Just north of Toronto
  • Status: Offline

Post November 15th, 2009, 8:50 pm

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'];
   }
  }
 }
}
  2. //..query code
  4. if($result){
  5.  if(mysql_num_rows($result)>0){
  6.   $arrSku = array();
  7.   while($rows = mysql_fetch_array($result)){  
  9.    //see if the sku value is present in the array and add the skudiff
  10.    if(in_array($rows['sku'])){
  11.     $arrSku[$rows['sku']] .= ','.$rows['skudiff'];
  12.    }else{
  13.     $arrSku[$rows['sku']] = $rows['skudiff'];
  14.    }
  15.   }
  16.  }
  17. }
Lostboy

Cat, the other other white meat
  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13455
  • Loc: Florida
  • Status: Offline

Post November 15th, 2009, 11:44 pm

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 ?
Strong with this one, the sudo is.

Page 1 of 1

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: Kurthead+1 and 213 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
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.