0

I've spend the whole morning on this and I am absolutely stumped.

I have a mysql table, in which one of the columns are populated like this:

arrOpt['']={price:564.3,msrp:'779', d:'Width: 25"" |Height: 54"" |Weight: 33lbs',sku:6886939,ac:'260.00',ship:'221.95',cShip:'0.00',img:'503-13-35.jpg',imgT:'',oImg:'',shipW:'0',leadL:'7',leadU:'10',partNo:'503-13-35',invChk:'33rd',mfrStk:'',stkShip:'1',bo:'',desc:'',osum:'',upc:'702404269082',packQty:1,ship_via:'FreeTrk',freightUpgradeLvl1:'25.00',site:'home',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 15th'} "

there is also a possibility for it to be like this:

arrOpt['264596']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356623,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'3',leadL:'7',leadU:'14',partNo:'151 Blue',invChk:'31st',mfrStk:'20',stkShip:'1',bo:'',desc:'',osum:'Blue',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264583']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356624,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 Celery',invChk:'31st',mfrStk:'20',stkShip:'1',bo:'',desc:'',osum:'Celery',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264590']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356625,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 Chambray',invChk:'31st',mfrStk:'7',stkShip:'1',bo:'',desc:'',osum:'Chambray',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264597']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356626,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 Ecru',invChk:'31st',mfrStk:'30',stkShip:'1',bo:'',desc:'',osum:'Ecru',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264601']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356627,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 Lavender',invChk:'31st',mfrStk:'25',stkShip:'1',bo:'',desc:'',osum:'Lavender',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264603']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356628,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 Maize',invChk:'31st',mfrStk:'20',stkShip:'1',bo:'',desc:'',osum:'Maize',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264608']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356629,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 Pink',invChk:'31st',mfrStk:'20',stkShip:'1',bo:'',desc:'',osum:'Pink',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264616']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356630,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 White',invChk:'31st',mfrStk:'50',stkShip:'1',bo:'',desc:'',osum:'White',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'}

As you can see there are fields where arrOpt can be either blank, or containing multiple arrOpt with a number inside.

How would I structure the sql statement to grab the number inside the brackets? Like for example, I want to be able to grab "264596,264583,264590" etc from the second example.

Andreas Wong
  • 59,630
  • 19
  • 106
  • 123
chimgrrl
  • 99
  • 12
  • So, the arrOpt column stores JSON(ish ?) data and you want to be able to retrieve that via string functions of mysql. Is that correct? – Gangadhar Apr 11 '12 at 05:29
  • It's not really JSON, it's a javascript array. But it could range from either just 1 element to multiple elements. And yeah, if possible I want to use the string functions in mysql, because php is tooo slow. – chimgrrl Apr 11 '12 at 05:45
  • Does this http://stackoverflow.com/questions/8334438/parsing-mysql-into-javascript-arrays-through-php-and-jquery help you? – Gangadhar Apr 11 '12 at 05:55
  • nope, I can't use php to parse it, I have 2.5 million records to parse through, php would be too slow. I am however open to other languages that would suppose multithreading to accomplish this. – chimgrrl Apr 11 '12 at 06:07

1 Answers1

0

I don't have time to code this up, but you want to create a function using these links as a guide:

Pseudocode:

Pos = 0;
OutString = "";
Length = Len(InString);
WHILE LocationOf "arrOpt['" in right(InString,Length - Pos) > 0
    Start = Location + 8;
    End = Locationof "'" in right(InString,Length - Start + 1);
    OutString = concatenate(OutString,",",Mid(InString,Start,End);
    Pos = End + 1;
EndWhile
OutString = right(OutString,Len(OutString) - 1;
return OutString;
Glorfindel
  • 21,988
  • 13
  • 81
  • 109
DTecMeister
  • 76
  • 4