Need feedback on arrays

kpowning

Is there a better way to do this any advice much appreciated.

$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>"; 
} 

}

effim

What exactly is it you're trying to accomplish here? Obviously the code explains some, but I'm not sure what the situation is.

// a bit more straightforward query
$query = "SELECT xcart_classes.* FROM xcart_classes LEFT JOIN xcart_class_options on xcart_classes.classid=xcart_class_options.classid WHERE xcart.classes.class='Color' OR xcart.classes.class='Size'";
$result = mysql_query($query);

while (false !== ($row = mysql_fetch_object($result))) {
    print_r($row);
}

Without knowing more about your database schema and what it is you're trying to accomplish I can't really help much more than that. You should be able to use the output from that to see where the information you need to use is appearing in the $row.

By the way, I prefer to fetch objects as they are easier to reference inside of strings and they're easier to write. I also find them to be easier on the eyes.

$row->column [...]
$row['column'] [...]

laserlight

In addition to what effim suggested, I suggest that you only select option_name since that is the only column that you use from what is retrieved.

Must you use the MySQL extension? I suggest using the PDO extension or MySQLi extension instead, though in this case they would not make a difference.

effim wrote:
By the way, I prefer to fetch objects as they are easier to reference inside of strings and they're easier to write. I also find them to be easier on the eyes.

However, typically, one would escape the variable before concatenating with a string, so I see no real advantage in the former. The latter is a matter of taste. What may be more common is to use the fields retrieved to initialise an object of a type specific to the problem domain, and this can be done with any form of fetching into variables.

effim

No real reason to use PDO if you're not going to need to switch to another database. Mysqli is nice, but Zend_Db is nicer (using Mysqli as an adapter, of course).

😉

laserlight

effim wrote:
No real reason to use PDO if you're not going to need to switch to another database.

I think that the consistency of interface is a "real" reason even if a switch is not planned and never actually happens. If you are planning to accomodate another database, actual database abstraction may be even better, but that is a different story.

kpowning

Thanks for the feedback 🙂

kpowning

what im trying to do is get all colors say available for a t-shirt and then get all sizes available in that color. I hope this helps understand.

effim

kpowning;10914718 wrote:
what im trying to do is get all colors say available for a t-shirt and then get all sizes available in that color. I hope this helps understand.

How about this:

// a bit more straightforward query
$query = "SELECT xcart_classes.* FROM xcart_classes LEFT JOIN xcart_class_options on xcart_classes.classid=xcart_class_options.classid WHERE xcart.classes.class='Color' OR xcart.classes.class='Size'";
$result = mysql_query($query);

while (false !== ($row = mysql_fetch_object($result))) {
    if ($row->class == 'Color') {
        $colors[$row->id] = $row->value;
    } else {
        $sizes[$row->id] = $row->value;
    }
} 

foreach ($colors as $key => $color) {
    echo "Color $key: $color<br>\n";
}

foreach ($sizes as $key => $size) {
    echo "Size $key: $size<br>\n";
}

You'll have to modify the columns to fit what you have. I assumed that the primary key is 'id', the color or size value is in 'value', and the class is either 'Color' or 'Size'.

kpowning

that correct, but how can i echo out

Red = s m l xl
Blue = s l
Orange = xl

for each color? Thanks for the help here.

effim

kpowning;10914720 wrote:
that correct, but how can i echo out

Red = s m l xl
Blue = s l
Orange = xl

for each color? Thanks for the help here.

What's your database structure? The best way to do it is going to be in your SQL query, but I don't know enough about the db structure.

kpowning

Here is my database structure

effim

kpowning;10914724 wrote:
Here is my database structure

You don't have your database structure in such a way that you can pull all of the available sizes out for a specific color because there isn't a link between the size options and the color options. You're basically saying that all colors are available in all sizes, and that colors and sizes available just vary by product.

You're probably going to want to combine colors and sizes together in a single table along with the quantity and the relevant product id. Then you could simply select all rows for that product. It's redundant though. Alternately, you can create separate tables for sizes, colors, and quantities. The quantities table would have the option id (unique for each color-size combination), the id of the size, the id of the color, and the id of the product in addition to whatever other combination-specific data you needed to store (additional price, etc.). Your sizes and colors tables would just store the id of it and the name of it, nothing else necessary.

Read more into data structures for relational databases and you'll find that there are really complex models for storing data and accessing it efficiently. It seems like your in over your head here, no offense. This is pretty complicated stuff.

kpowning

Thats the problem with this software for my store. They add every size no matter what to a variants table then which ever products are not available in certain sizes and colors you need to enter a product exemption into a table called product_ex then if they select that color and size they customer sees in the store this variation is not available.

Thanks for being real and up front. It's all true...

Weedpacket

kpowning wrote:
They add every size no matter what to a variants table then which ever products are not available in certain sizes and colors you need to enter a product exemption into a table called product_ex then if they select that color and size they customer sees in the store this variation is not available

Given thirty seconds to think about it (which included about fifteen to reinsert missing punctuation) that sounds largely the only way to do it. There is no direct relation between size and colour; only between product and size, and product and colour; so any size/colour combination can only exist with respect to a given product (clearly, not having any x-large green t-shirts available doesn't automatically mean you can't get an x-large green hoodie). Not to mention the fact that there may be an arbitrary number of other option categories, so you can't go around creating a new table for each one.

The most significant change I can see would be to replace the exemptions table with a table of available option combos, with foreign keys to products, sizes and colours. List what you do have instead of what you don't have, because you only want to offer what you do have. If you only kept a list of what you don't have (your "exlcusions" table) then you end up having to search through it to find out what's not there.

In the absence of that, a full cartesian product of all three source tables (where the - I think it's called - "class_options" table is used twice, each time filtered by a different classid), filtered by product ID, and subtracting the exemptions, would have the same effect. I haven't seen the exemptions table, so I'd only be guessing if it looked something like

product_id | colour_id | size_id
-----------+-----------+--------
      1    |     3     |   12     // No small red Item 1s! Oh, noes!

(Incidentally, notice how using the forum's formatting tags is much easier and more convenient than cropping and posting a screenshot?)