PDA

View Full Version : Help with PHP MYSQL Logic


tux
03-03-2010, 07:17 PM
Hello Guys,

Probably not gonna be easy to explain this but here goes.

I am building the admin side of my website and currently building a page to upload new images to the site. I have a form that I fill out to include the image file, title, description etc. I am also including my current list of products as Check Boxes so I can choose which products are to be shown for a particular image.

I have an images table and a products table. My problem is that I cant get my head round the logic I need to make this work.

I was thinking maybe having a third table with imageID and then a field for each product but then how would I call the correct products as each image is displayed.

Can anyone give me some ideas as to the logic I need.

Cheers all.

edbr
03-04-2010, 01:13 AM
not sure i followed. its early here. if you had a field in your image table you could insert the product ids, you want associated with the image. thats how i read it anyway

tux
03-04-2010, 07:00 AM
Each image may have lots of different products allocated to it.

So do you mean have one field in the images table with all relevant productIDs in it? eg. 1 2 3

If so, how would I call them from the product table in my SELECT statement?

edbr
03-04-2010, 07:27 AM
if you delimit with commas for instance you could then explode and use if condition to echo . im not saying this the best way mind, :) but its how i approached it in the past

explode(",","$a");
if ($a== "$row['prod_id']")

something like that anyway

tux
03-04-2010, 07:44 PM
Thanks for that Edbr,

Heres where I am so far.

<?php
$imageID = 1;

$query = sprintf("SELECT productIDs FROM images WHERE imageID = '$imageID'");
$result = @mysql_query($query);
$productIDs = @mysql_fetch_array($result);
$productIDs = $productIDs['productIDs'];

$prodID = explode(",","$productIDs");//this returns an array containing [0]=>1 [1]=>2 [2]=>3 [3]=>4

$query = sprintf("SELECT * FROM products WHERE productID = '$prodID[0]'");//this obviously returns just one record
$result = @mysql_query($query);
$products = @mysql_fetch_array($result);


do{
echo $products['productName'];
}while($products = @mysql_fetch_array($result));
?>

How can I get the query to loop through all the id's in the array? Bearing i mind the array could contain more or less id's.

Thanks, Paul

edbr
03-05-2010, 01:12 AM
right i suspected i hadnt thought this through enough.
im thinking 1 join the tables
2 use foreach to return the arrays as a $value
3 use a while loop with condition if $value == yada yada
trouble is not sure that will fly.
i will try and test it later if i get a chance asim abit concerned im talking gibberish here :)

tux
03-05-2010, 07:01 AM
lol I talk gibberish all the time mate. :-D

im thinking 1 join the tables

I thought of maybe doing this but dont have a common field in both tables. The nearest would be the productIDs field in the image table but that would have lots of id's in it so wont match the productID in the products table.

2 use foreach to return the arrays as a $value

I tried this but it kept returning just one record, unless I was doing something wrong. Heres what I tried....

foreach($prodID as $value){
$query = sprintf("SELECT * FROM products WHERE productID = '$value'");
$result = @mysql_query($query);
$products = @mysql_fetch_array($result);
}

3 use a while loop with condition if $value == yada yada

How would this be coded?

Cheers Edbr.

Regards Paul

edbr
03-05-2010, 07:17 AM
sorry i am full on ill have a look later
but re the join i would add 2 tables each with a value of 1 for all records and join with them , it would match then :)

davidj
03-05-2010, 09:32 AM
if joining tables create a view in MYSQL

Less overhead and quicker

tux
03-05-2010, 12:07 PM
I'm now using this ......

<?php
$imageID = 1;

$query = sprintf("SELECT productIDs FROM images WHERE imageID = '$imageID'");
$result = @mysql_query($query);
$productIDs = @mysql_fetch_array($result);
$productIDs = $productIDs['productIDs'];

$prodID = explode(",","$productIDs");//this returns an array containing [0]=>1 [1]=>2 [2]=>3 [3]=>4

foreach($prodID as $value){
$query = sprintf "SELECT * FROM products WHERE productID = '$value'");
$result = @mysql_query($query);
$products = @mysql_fetch_array($result);
}

do{
echo $products['productName'];
}while($products = @mysql_fetch_array($result));
?>

.... but it only returns one record in my do/while loop. But, when I print_r out the query result ( $products ) it has all of the records requested from the $prodID, which is 4 rows. Why would that be?

davidj
03-05-2010, 01:20 PM
I just scanned your code

There is a much better way but need some time to digest your requirements

// $prodID returns an array containing [0]=>1 [1]=>2 [2]=>3 [3]=>4


$in = implode(',',$prodID); // implode array and assign comma delimited values to var

$query = "SELECT * FROM products WHERE productID IN ($in)"; // use $in variable in IN clause

$result = mysql_query($query); //query db (Note I omitted the error suppressor @. Handle errors better)

while($products = @mysql_fetch_array($result)){


// loop through $products array results here

}

tux
03-05-2010, 02:50 PM
Ahhhh, 'IN' is the missing link in my logic. Thanks Dj.

My productsIDs field in my images table was already delimited with commas, thats why I exploded it to get rid of them, so no need to implode it. I take it that if I make the productIDs field a number, say 1234 ( productIDs 1, 2, 3 & 4 ) then I can use 'IN' in my SELECT Statement to look for productIDs in my product table. Yes?

I'll try it when I get home from work.

Thanks.

davidj
03-05-2010, 03:04 PM
IN clause just takes a comma delimited values wrapped in brackets

IN (1,2,3,4,5,6)

It will return every record which match the IN clause

davidj
03-05-2010, 03:06 PM
Additional

No need to use @ error suppressor

You should handle these errors in your code

tux
03-05-2010, 06:17 PM
Ok, big thanks Dj,

Here is what I've ended up with for anyone who has been following this thread.

$imageID = 1;

$query = sprintf("SELECT productIDs FROM images WHERE imageID = '$imageID'");
$result = @mysql_query($query);
$productIDs = @mysql_fetch_array($result);
$productIDs = $productIDs['productIDs'];

$query = sprintf("SELECT productName FROM products WHERE productID IN ($productIDs)");
$result = @mysql_query($query);
$products = @mysql_fetch_array($result);


do{
echo $products['productName']."<br>";
}while($products = @mysql_fetch_array($result));

This now outputs all the product names for the requested image.

Additional

No need to use @ error suppressor

You should handle these errors in your code

I will make an effort to do this Dj.

Thanks again to you and also Edbr for your help.