Page 1 of 1

This is probably easy...

Posted: Sat Nov 13, 2004 8:46 am
by patch2112
Hello, I have a elementary understanding of PHP, but can't figure out how to do this. I've tried a couple of different ways without any success. Any help would be great, thanks in advance...

I'm creating a website for a client that offers a clothing line. Each item has several different colors availilbe. I've created a mysql database with a table which includes a column for each color with a N default and a Col_Y for true response, and a table with all of the colors including the link for the little .gif of the color. Note, the names of the colors in the product table match exactly the names of the colors in the colors table.

I'm trying to write a script that will loop through all of the product columns looking for the Col_Y entry (no prob here). Once it finds one, I want it to take that column name and find it's equivilant in the colors table and then return the img URL in that record. I've tried a couple different ways, with no luck, so I'm hoping someone can give me a good direction to go, then I can ask more questions about how to go about it!

Thanks again, sorry so long.
Philip

Posted: Sat Nov 13, 2004 8:56 am
by djot
-
Hi,

first of all it would be enough to have only one field "color" that may hold "none or default", "red", "blue", "green" ... . Also you don't have to loop through the results, just write an accurate sql statement that e.g. selects all blue items.

Code: Select all

SELECT * FROM clothes_table WHERE color='blue';
djot
-

thanks

Posted: Sat Nov 13, 2004 9:16 am
by patch2112
Thanks for your quick response.

Each item has several 10-15 colors and the names are all different because they come from several different manufactures (Royal Purple tapestry!). I can get the info from the current product that finds which colors that particular item has, but I can't figure out how to extract the url from the colors table.

Posted: Sat Nov 13, 2004 12:47 pm
by djot
-
Hi,

You have an item that is available in 10-15 colors, now I got it. You should read about database normalization and have a table for clothings(items) and another one for just the colors available. (If you don't have too many items and only a small sized database you can forget about that. Anyway normalized tables would be the recommended way.)

So where is the problem getting the URL out of the results? Don't you know how to get the results? Or don't you know how to loop through the results with e.g. a while loop?

I assume, you don't know anything about mysql, correct?


djot
-

ok

Posted: Sat Nov 13, 2004 1:20 pm
by patch2112
I have read through the normalization and think my db is sufficient, it's more of a programming roadblock now. I tried to clean it up as much as possible and add notes that might help. Thanks for your help djot.

Code: Select all

<?php
#Connect to MySQL
$conn = mysql_connect ("this",that,"and the other")
or die("Err: Connection Failed");

#select the specified database
$rs = mysql_select_db("jeap", $conn)
or die ("Err: Database Selection Failed");

#select the product record to be used (currently static for testing)
$rs_product = mysql_query("select * from products where product_id = 19")
or die("Didn't select records");

#loop through arrays to get values
while ($get_info = mysql_fetch_row($rs_product))&#123;
foreach ($get_info as $field)
&#123;
#Match $field to column name, then to color_name in jeap.colors
if ($field == "Col_Y")&#123;#product table has column for each color, Col_Y is used for positive (only in colors)

#HERE IS THE PROBLEM, HOW DO I MAKE A VARIABLE ($FIELD_COL) WITH THE COLUMN NAME FOR $FIELD

#Selects color info (gif url/name) based on having the same name as $field_col then creates the html to display
$rs_colors = mysql_query("select * from colors where color_name = "$field_col"");
$color_selected .= "<img src="$rs_colors&#1111;color_img]" alt="$rs_colors&#1111;color_name]" width="30" height="13" border="1"> ";
&#125;
&#125; 
&#125;
?>

Posted: Sat Nov 13, 2004 1:41 pm
by patch2112
Also, I've tried mysql_fetch_field but I didn't really understand from the manual how to use it properly. All the references I could find on it were just mirrors of the manual. Thanks again.

PHP Script

Posted: Sat Nov 13, 2004 3:01 pm
by Taylormanj
Nevermind

Posted: Sat Nov 13, 2004 3:04 pm
by djot
-
Great posting Taylormanj! :) Where this topic belongs to/in?

djot
-

Thanks

Posted: Sat Nov 13, 2004 5:20 pm
by patch2112
Ok, I figured it out...after 2 days of trying. I thought I would post the script in case anybody might be trying to do the same thing. Note that there are two tables in the database that are used, 1 for the products and 1 for the colors. The entries for color_id match exactly the columns for the colors in the product tables, and that "$Col_Y" is used for the positive result.

Thanks for your help djot! Any advice on making it better is appreciated. I'll look at the ' after I get the page finished (this is one little part) and the product_id will be passed from a link, not user input.

Code: Select all

<?php

#Connect to MySQL
$conn = mysql_connect ("IP",User,"pw")
or die("Err: Connection Failed");

#select the specified database
$rs_db = mysql_select_db("db", $conn)
or die ("Err: Database Selection Failed");

#select the product record to be used (currently static for testing)
$rs_product = mysql_query("select * from products where product_id = 19")
or die ("Didn't select records");

#translate resource ID into row
$row_col = mysql_fetch_row($rs_product)
or die("Err, translate");

#get size of $row_col for loop
$size = count($row_col)
or die ("Didn't get size");

#loop through arrays to get values
for ($i=0; $i < $size; $i++)
&#123;
#located columns with "Col_Y" as result
if ($row_col&#1111;$i] == "Col_Y")
&#123;
$field_col = mysql_fetch_field($rs_product, $i)
or die("Didn't fetch field");

#Selects color info (gif url/name) based on having the same name as $field_col then creates the html to display
$rs_colors = mysql_query("select * from colors where color_name = "$field_col->name"")
or die("Didn't select colors based on field_co");

#translate resource ID into row
$row_col_colors = mysql_fetch_array($rs_colors)
or die("Err, translate #2");

#generate html
$color_selected .= "<img src="$row_col_colors&#1111;color_img]" alt="$rs_colors&#1111;color_name]" width="30" height="13" border="1"> ";
&#125;
&#125;
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<? 
echo ("$color_selected <br>");
?>
</body>
</html>