Query Help - Please

[kpowning]

I have the following code:

	$vas = mysql_query("SELECT * FROM xcart_classes WHERE productid='1'"); //edit product id
	while ($tes = mysql_fetch_array($vas)){
		$mark = array($tes['classid']);

	foreach ($mark as $col){
		#echo $col."<br>";

		$getoptions = mysql_query ("SELECT * FROM xcart_class_options WHERE classid = '".$col."'");
		while($getop = mysql_fetch_array($getoptions)){
			$optvals = array($getop['option_name']);

				foreach ($optvals as $aops){
					echo $aops."<br>";
				}

		}

	}
}

This code shows this:

Red
White
Blue
L
M
S

How can I make this same code insert all possible options for each color and size:
Red - L M S
White - L M S
Blue - L M S

[dagon]

you want a join, something like:

"SELECT * FROM xcart_classes
LEFT JOIN xcart_class_options on xcart_classes.classid=xcart_class_options.classid
WHERE productid='1'"

its a bit of guesswork without seeing the db structure.

[kpowning]

Here is the database structure for xcart_class options and xcart_classes please let me know. Thank you very much for your help.

[kpowning]

Anyone PLEASE - this is the last thing keeping me from completing my script. Thanks in advance.

[dagon]

Have some patiences, i'll look at it after work.

[kpowning]

Thanks man. Basically Here is what I have going on with this script I have a script that grabs each color and size available for a product which is listing in a .csv file column named "Color-:-Size~QTY" on one row. So we break this down using the following:

$Productcolor = #row from .csv file
$regexp = '#(?<=^|;)(\b.*\b)\s*-:-\s*(\b.*\b)\s*~\s*(\b.*\b)(?=;|$)#U';
preg_match_all($regexp, $Productcolor, $matches, PREG_SET_ORDER);

$colors = count($matches); // count all arrays
$order = 1; // define showorder used for options

for($i=0; $i<$colors; $i++){

echo $matches[$i][1];
 // hand each seperate color, size, qty code 
}

In this situation not all products have all sizes available for each color even though xcart wants us to specify every option anyways. So this is what I'm trying to do here. Then I think I have to run the script again or make another script that will rerun and check the variants against what product options are actually available and then insert into xcart_product_options_ex what to exclude the options that are not available. What do you think? Do I need to run this script twice or can we make some code to do this all at once? I REALLY APPRECIATE IT.
Attached is the xcart_product_options_ex table with Red - S being not a valid choice for this sample product.

[dagon]

is post 1 and 6 the same issue?

[kpowning]

yea I can give you the full code that I have if you want. Basically my main code file has whats listed in post 6 and my test file to try to seperate and just figure out each variant and insert them all and then check them again against whats actually for sale that is in post 1. So yes your seeing two different codes, but im hoping that whatever we figure out in post once I can copy and insert into part of my main coding on post 6. HOPE THAT MAKES SENSE. THX

[dagon]

i don't want to wade through your code, if you have a specific question, post, perhaps you need to hire someone.

[kpowning]

Ok well if you dont mind helping me with my original question that would be great. I'm trying to learn this, not get free help to be honest. I want to learn and understand, but I'm stuck on this and was just hoping you wouldnt mind pointing me in the right directions and explaining the best ways. Sorry. I'm not trying to waste your time...

[kpowning]

Ok here is what I came up with it works, but is there a better way?

$e = mysql_query("SELECT * FROM xcart_classes LEFT JOIN xcart_class_options on xcart_classes.classid=xcart_class_options.classid WHERE class='Color'");
$f = mysql_query("SELECT * FROM xcart_classes LEFT JOIN xcart_class_options on xcart_classes.classid=xcart_class_options.classid WHERE class='Size'");
while ($y = mysql_fetch_array($e)){
	$varcolors[] = array($y['option_name']);
}
while ($a = mysql_fetch_array($f)){
	$varsizes[] = array($a['option_name']);
}
$cntvarsizes = count($varsizes);
$cntvarcolors = count($varcolors);
#echo "<pre>".print_r($k,1)."</pre>";
for($b=0; $b<$cntvarsizes; $b++){

for ($f=0; $f<$cntvarcolors; $f++){
	echo $varcolors[$f][0]." = ".$varsizes[$b][0]."<br>";
}

}

[kpowning]

Dagon does this look right, or is there a better way? I didnt hear anything yesterday.