[Work Around] Strange includes in mySQL data

For Articles relating to more than one ISC version
Post Reply
Snooper
Posts: 264
Joined: Sat Jun 26, 2010 9:22 pm

[Work Around] Strange includes in mySQL data

Post by Snooper »

I am currently working on ‘adding’ a few tools to ISC.. As always, (nothing new) it’s a fight!!

Also I do happen to like how a customers list is displayed when you click on the + plus symbol along side a customers order. So I am going to send a version of this over to an email that I normally send out to customers and include product images.

Image

However, and reason for this posting; I was to discover the data ‘constructed’ in the background was giving me a genuine head ache and perhaps evidence of the mix and match approach used by ISC when writing their cart.

To explain. The example image section shown above is incomplete (as you may note), but this has much of its information taken from the mySQL table isc_order_products

The data held here otherwise alphanumeric and clear text readable. If it says (by example) “Black Grecian Maxi Dress” and if it says ‘1356/Cobolt’, then you can be sure it reads exactly as written.
However, take a read of the data held that mentions ‘Colour’. We have (by example) Colour: Colbolt or (say) Colour Black-White. What you get in mySQL in table isc_order_products under column ordprodoptions is not just text, it’s more..

Image

So to get from this – Image to – Image you will need some script to filter out the ‘rubbish’…

I tried a number of variations of this and no doubt missed the best solution. The variation I used is based on the following -

Code: Select all

<?php

$user_name = "xxxx";  // Change all XXX's to match your requirements
$password = "xxxxxx";
$database = "xxxxxxxxxx";
$server = "xxxxxxxxxx";
$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {

$result = mysql_query("SELECT isc_order_products.ordprodname
     , isc_order_products.ordprodsku
     , isc_order_products.ordprodoptions
FROM
  xxxxxxxxxx.isc_order_products");  // I have stayed inside one table for this example

	function clean_string($string) {
  	$string =  preg_replace('/[^a-z\\040\\.\\-\/]/i', ' ', $string); // Filter non alphanumeric
        $string =  preg_replace('/\b\w\b(\s|.\s)?/', '', $string); // Filter any single letter
  		return $string;
  	}   

while ($row = mysql_fetch_assoc($result)) { 

   $string = $row['ordprodoptions']; // Pass SQL data for local use
   $str = clean_string($string); // Run clean up of data string

echo "<table border='0' width='70%'><tr> // Display findings from table
		<td>". $row['ordprodname'] ."</td>
	</tr>
	<tr>
		<td>". $row['ordprodsku'] ."</td>
	</tr>
	<tr>
		<td>". $row['ordprodoptions'] ."</td>  // Output of un-cleaned data string
	</tr>
	<tr>
		<td>". $str ."</td>  // Output of cleaned data string
	</tr>    
</table>"; }

}
mysql_close($db_handle);
?> 
The out put from the above script shows two lines of information required but excludes quantity count, plus how the original colour data looks un-cleaned against the same data cleaned...

Image

The key script elements from above -

function clean_string($string) {
$string = preg_replace('/[^a-z\\040\\.\\-\/]/i', ' ', $string); // Filter non alphanumeric
$string = preg_replace('/\b\w\b(\s|.\s)?/', '', $string); // Filter any single letter
return $string;
}

$string = $row['ordprodoptions']; // Pass SQL data for local use
$str = clean_string($string); // Run clean up of data string


*** Note - Once running my test code, you might find the 'clean' seems to have failed on a number of occasions. Not so.. That would be a sale that may have been dropped and so nolonger contains the active value of '1' in its makeup (a:1: active while a:0: inactive) . ISC does not house keep dead data and easy enough to filter.
Last edited by Snooper on Tue Jun 12, 2012 10:11 pm, edited 5 times in total.
ISC 5.5.4 Ultimate : Being used here -- http://www.kdklondon.com
Martin
Site Admin
Site Admin
Posts: 1854
Joined: Wed Jun 17, 2009 6:30 pm
Location: South Yorkshire UK
Contact:

Re: Strange includes in mySQL data

Post by Martin »

Unless I miss my mark the data you're calling messy is simply serialised..

Look at the insert and select functions for the data type and you'll find the relevant method for compiling and extracting the data in a useable format.
Snooper
Posts: 264
Joined: Sat Jun 26, 2010 9:22 pm

Re: Strange includes in mySQL data

Post by Snooper »

Errmmm... :oops: Yes *tut* or course it is Serialised data, and a very relivant detail left off from top posting...

While I might not know as much as I’d like about SQL, I am blimmin sure to store trivia data in this format in any SQL database is totally pointless, let alone renders it unsearchable.... Aughh !!

Anyway, before going the route (above) I had tried parsing data from ordprodoptions into the function unserialize() -

a:1:{s:6:"Colour";s:13:"Abstract Spot ";} == $row['ordprodoptions']

Code: Select all

$col = unserialize($row['ordprodoptions']);
print_r($col);
But this returns – Array { [Colour] => Abstract Spot } Array
The serialised data used in this example I believe should be – Array ( [Colour] => Abstract Spot )

Given the differances in how the line of data can be recovered and how ideally it should be, either route there is still some cleaning to do. Yes I could 'back track' from the output in admin and see how ISC resolved this, but the list of need to do's has grown. The above then, was my way of just getting the job done in my life time using minimum code.. :lol:

Here is a neat little ditti or two you 'might' find useful and use to 'test' approches on the subject...

Code: Select all

<?php
$str = <<<EOF 
a:1:{s:6:"Colour";s:13:"Abstract Spot";}
EOF;
echo "<textarea rows='8' cols='50'>";print_r(unserialize($str));echo "</textarea>";
?>
The output..

Image

** Note textarea !!..

To prove the above..

Code: Select all

$array = htmlspecialchars_decode($string); // $string value as given from top posting.
$array = unserialize($array);
print_r($array);  
Which gives the output as - Array ( [Colour] => Abstract Spot )
Last edited by Snooper on Wed Jun 06, 2012 9:21 am, edited 2 times in total.
ISC 5.5.4 Ultimate : Being used here -- http://www.kdklondon.com
Snooper
Posts: 264
Joined: Sat Jun 26, 2010 9:22 pm

Re: Strange includes in mySQL data

Post by Snooper »

And an update to the script (from top posting)...

Add the following line - $string = substr_replace($string, "Colour : ", 6, 9);

So the function 'clean_string' now reads..

Code: Select all

	function clean_string($string) {
  		$string =  preg_replace('/[^a-z\\040\\.\\-\/]/i', ' ', $string);
        $string =  preg_replace('/\b\w\b(\s|.\s)?/', '', $string);
        $string = substr_replace($string, "Colour : ", 6, 9);
  		return $string;
  	}   
This outputs a change to Colour Abstract Spot to read Colour : Abstract Spot

As implide by Martin, there are other ways to un-serialise a string. I'd like to see worked examples.
ISC 5.5.4 Ultimate : Being used here -- http://www.kdklondon.com
Martin
Site Admin
Site Admin
Posts: 1854
Joined: Wed Jun 17, 2009 6:30 pm
Location: South Yorkshire UK
Contact:

Re: [Work Around] Strange includes in mySQL data

Post by Martin »

I think you've made a classic mistake in looking at just one record because you appear to have gone down a blind alley instead of looking at it in a wider context.

The output you got originally with essentially an array of:
colour, Abstract Spot

...would be easily resolved with a simple odd/even handler

Code: Select all

$col = unserialize($row['ordprodoptions']);
for($i=0;$i<(count($col)+2);($i=$i+2)) {
	$options[$col[$i] = $col[($i+1)];
}
That would pair up the key to values and turned a single dimensional array into a relational one... All the substring nonsense has missed out on what the unserialise() actually does and will immediately break as soon as you try to apply it to any other value in your DB..


... and I'll add a caveat... I haven't actually looked any harder at the data to check my logic so I could well be wrong, but you need to back up some and do what I suggested at the beginning. Look at the actual code and reverse engineer instead of trying to come up with some exotic solution. What you're trying to do is make up a new language to explain a few fragments rather than using the Rosetta Stone...
Snooper
Posts: 264
Joined: Sat Jun 26, 2010 9:22 pm

Re: [Work Around] Strange includes in mySQL data

Post by Snooper »

Okay.. Not trying to invent anything or construct a new language from nothing, just trying to reproduce similer to the image below, removing the hyperlink but adding a thumbnail image in its place; And then pass the whole over to a template for emailing out and print for use as a picking/check list.

Image

Yes I know something of sorts is in place already. But as it stands, the Invoice, Invoice CartItems and Packing Slip are three seperate e-mails under a number of Admin functions. Not one includes an actual product image. Ideally I'd like a page that can serve for all and have total control over. So my starting point is a customers 'final' purchace list which has as damn everything I need simply because the SQL to recall this information includes that important link to an image. And that link refuses to behave when transfered over to current e-mail scripts (at least what I tired did not from what I thought I saw from ISC code)..

Semantics of accuracy I agree is important. And true, my approach to programming is far from ‘ideal’.. But I sell fashion not program for a living. Anything I attempt is firstly by trial and error as I familiarise myself with processes. But once I get something to work I at least can see I have a target ‘end product’. The code between start and product, can be refined and made tidy later, and be correctly inserted into ISC without busting it. I don't have time anylonger to back trace through folders of crap code, so I am commited to coding which is for me at least, quicker and provides results today (ish) and not when ever.

Posting here is in hope people who know the subject might step up and tidy or even improve on an 'idea' publicly shared. A starting point to build something from or add to something already in place or being worked on.

Sorry you don’t approve of my methods and thank you for your suggested alternative. :| blimmin !!!
ISC 5.5.4 Ultimate : Being used here -- http://www.kdklondon.com
Martin
Site Admin
Site Admin
Posts: 1854
Joined: Wed Jun 17, 2009 6:30 pm
Location: South Yorkshire UK
Contact:

Re: [Work Around] Strange includes in mySQL data

Post by Martin »

Important thing to note that this isn't about taking anything personally...

I had a bad day and you decided to chuck out my advice to find the relevant code and instead opted to reinvent the wheel...

If you've got a decent code editing system that can search a whole project (eg: Eclipse IDE) you can use that to find the serialize() function and then reverse engineer the process. That's effectively how I've resolved numerous issues in ISC, by working out where the problems starts, looking for keywords, functions, etc... and then working back until I've found the source of start point to resolve the issue.

In this instance you've chosen to use just a very limited amount of data to create a solution that will break as soon as you attempt use it against any other variation data such as shoe size, dress size, pattern, material...

So, apologies for the abrupt manner but trying to save you from a lot more work later on.
Snooper
Posts: 264
Joined: Sat Jun 26, 2010 9:22 pm

Re: [Work Around] Strange includes in mySQL data

Post by Snooper »

No Martin, nothing has been taken ‘personally’. The nature of this forum is to debate, provide opinion and to suggest solutions. As it should be and as it should continue to do so.

And while my reply may have been a shade curt or seemingly so, it was given with poor but genuine gratitude for the ‘direction’ you forwarded.

I shall re-visit Eclipse. Although I am totally unaware of how to use this developer tool, let alone use as you suggest you are doing.

I also apologise for appearing ungrateful.
ISC 5.5.4 Ultimate : Being used here -- http://www.kdklondon.com
Martin
Site Admin
Site Admin
Posts: 1854
Joined: Wed Jun 17, 2009 6:30 pm
Location: South Yorkshire UK
Contact:

Re: [Work Around] Strange includes in mySQL data

Post by Martin »

Good stuff... Everyone has bad days...

As far as using Eclipse... Like anything it takes a little getting used to but things like manuals and how-to guides tend to help (Yes, I find "read the manual" a bit of an alien concept too ;)).

What it allows you to create a project based on your ISC files and then index it... from that you can then use the file search tool to search across the entire project for keywords, functions, etc... and backtrace how things are being called (or not) and basically act like something of a detective.

I tend to take the process for granted now unless I'm on a machine where Eclipse or something similar hasn't been installed and then it all comes crashing back... Trying to do the same thing one file at a time is nothing short of pure torture.
Post Reply