Building a photo array from database tables

Question

So I have been fighting this for months now and am hoping to find a solution here once and for all. I am using phppwinty as a php library for pwinty.com.

In the default example provided by the author, the images array is created like the example below with all the variables hard coded as an example.

$photo1 = $pwinty->addPhoto($order, "4x6", "http://www.picisto.com/photos/picisto-20120608100135-925870.jpg", "1", "ShrinkToFit");
$photo2 = $pwinty->addPhoto($order, "6x6", "http://www.picisto.com/photos/picisto-20120601010631-895061.jpg", "4", "ShrinkToFit");
$photo3 = $pwinty->addPhoto($order, "5x7", "http://www.picisto.com/photos/picisto-20120626210525-763773.jpg", "3", "ShrinkToFit");

(note: in training, I have found out we don't necessarily need to add the integer after photo. instead of: photo1 it can just be photo.)

Now the $order variable is fine to stay. Next is the size, image source, and quantity. These are the only 3 variables I am worried about at the moment. With the shopping cart I have these unfortunately are broken up into 3 different tables.

-- order_options -- The table for the size variable
   --option_value -- The column for the size variable 
   --order_id -- Relationship column 

-- order_products -- The table for the product id's and qty in the order 
   -- product_id -- The column for the product id variable 
   -- product_quantity -- The column for the quantity variable
   -- order_id -- Relationship column 

-- products -- The table for the image source variable 
   -- product_image -- The column for the image source variable 
   -- product_id -- Relationship column

I get the order_id into the script with a get variable: $order_id = $_GET['id'];

I have attempted to JOIN all the tables together with relationships and have got nothing but errors, Though I don't beleive the problem is in the JOIN but the way I have the foreach set up.

My JOIN and foreach looks like this right now:

foreach ($db->query("SELECT orderproducts.product_id, orderoptions.option_value, products.product_image FROM order_products as orderproducts INNER JOIN order_options as    orderoptions ON orderproducts.order_id = orderoptions.order_id INNER JOIN products as products ON orderproducts.product_id = products.product_id WHERE              orderproducts.order_id = $order_id AND orderproducts.product_id = $product_id") as $row)
    $order_variables[] = $row;

if (count($order_variables) > 0): 
    foreach ($order_variables as $row):

        $product_id = $row['product_id'];

        $product_quantity = $row['product_quantity'];

        $size = $row['option_value'];

        $product_source = "https://www.alphahq.org/uploads/images/".$row['product_image']."";

        $photo = $pwinty->addPhoto($order, $size, $product_source, $product_quantity, "ShrinkToFit");

    endforeach;
endif;

The reason I say I believe the problem is in how the foreach is set up is because the 1 error I get in my browser when I run the script is as follows:

Warning: Invalid argument supplied for foreach() in /home/www/alphahq.org/libraries/phppwinty/print.php on line 35

According to my code editor line 35 is the foreach statement from above.

    foreach ($db->query("SELECT orderproducts.product_id, orderoptions.option_value, products.product_image FROM order_products as orderproducts INNER JOIN order_options as    orderoptions ON orderproducts.order_id = orderoptions.order_id INNER JOIN products as products ON orderproducts.product_id = products.product_id WHERE              orderproducts.order_id = $order_id AND orderproducts.product_id = $product_id") as $row)
    $order_variables[] = $row;

I hope I have been descriptive enough to finally get a solution to this darn problem once in for all. Thank you for your help in advance and hope we can work out a solution together. Happy coding.

If I can provide any more information that you believe would be helpful, please simply comment kindly below requesting the information.

Crackertastic · Accepted Answer · 2013-12-12 18:24:25Z

1

$db->query() will return a result set. Unless you are doing something in the query() method, you are working with a resource, not an object or array. foreach needs something it can iterate over.

Have you considered using:

$result = $db->query("SELECT orderproducts.product_id, orderoptions.option_value, products.product_image FROM order_products as orderproducts INNER JOIN order_options as orderoptions ON orderproducts.order_id = orderoptions.order_id INNER JOIN products as products ON orderproducts.product_id = products.product_id WHERE orderproducts.order_id = $order_id AND orderproducts.product_id = $product_id");

$order_variables = array();

while($row = $result->fetch_array()) {
    $order_variables[] = $row;
}

That should produce a multi-dimensional array with your DB rows in $order_variables.

(Please note that I used the OOP version of MySQLi in my example, I am not sure if that is what you are using. If not, modify as necessary.)

EDIT

Looking at your query a little closer, I believe part of the problem is that your variable values are not surrounded in single quotes. This may work better for you instead. MySQL likes its values (especially non-numerical ones) in single quotes:

$result = $db->query("SELECT orderproducts.product_id, orderoptions.option_value, products.product_image FROM order_products as orderproducts INNER JOIN order_options as orderoptions ON orderproducts.order_id = orderoptions.order_id INNER JOIN products as products ON orderproducts.product_id = products.product_id WHERE orderproducts.order_id = '$order_id' AND orderproducts.product_id = '$product_id'");

edited Dec 12, 2013 at 18:24

answered Dec 12, 2013 at 18:05

Crackertastic

4,9232 gold badges33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Crackertastic Over a year ago

Thanks :-), I happened to see the question as it popped up. Luck of the draw.

user2948950 Over a year ago

is the query trying to use $product_id just like my $order_id? The product id is not pre-populated like the order id is.

user2948950 Over a year ago

The reason I ask is because I am getting this error:

Fatal error: Call to a member function fetch_array() on a non-object in /home/www/alphahq.org/libraries/phppwinty/print.php on line 39

Crackertastic Over a year ago

According to your query and intended method of assignment, it looks like your $product_id variable is supposed to be coming from the product_id field in your order_products table

Crackertastic Over a year ago

In that case it looks like you may have a problem with your query. Can you run the query in an sql client or phpMyAdmin?

|

Collectives™ on Stack Overflow

Building a photo array from database tables

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related