Help with making a Product Database

downfall · October 2, 2006

Select by Band problem.. I changed the some table and field names from the category select links, so I now get a band list with links. This is the code I'm using to make this happen:

[code]$sql = "SELECT band_id, band_name
FROM bands
ORDER BY band_name";
$res = mysql_query($sql) or die(mysql_error());
while (list($id, $cat) = mysql_fetch_row($res)) {
echo "<a href='details3.php?id=$id'>$cat</a>\n";
}

echo <<<_HTML

$list

_HTML;[/code]

So that seems to work fine, I will need to put it in a dropdown but that can be done later. The problem is when I click one one of the band links i get a blank page.

This is the code I used (suggested by barand a few posts ago):

[code]$cat = $_GET['id'];

$sql = "SELECT p.*
FROM products p
INNER JOIN band_products bp ON p.product_id = bp.product_id
WHERE bp.band_id = '$band'
ORDER BY p.product_name";
$result = mysql_query($sql) or die (mysql_error());
while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){
echo <<<HTML
<table>
<tr>
<td>{$product['product_name']}</td>
</tr>
<tr>
<td>{$product['band_name']}</td>
</tr>
<tr>
<td>{$product['product_price']}</td>
</tr>
</table>
<br><br>
HTML;
}[/code]

I get a blank screen. Any ideas why a blank screen?

alpine · October 2, 2006

Assuming that the last code is the target page of your links, you are naming the GET['id'] as $cat but in your db-query you are trying to find WHERE id = '$band' ,these don't match up as you are no defined variable called $band

Study this example:
[code]
<?php

$id = $_GET['id']; // get from the adress bar, your previous link

$sql = "SELECT p.*
FROM products p
INNER JOIN band_products bp ON p.product_id = bp.product_id
WHERE bp.band_id = '$id'
ORDER BY p.product_name";
$result = mysql_query($sql) or die (mysql_error());
while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){
echo <<<HTML
<table>
<tr>
<td>{$product['product_name']}</td>
</tr>
<tr>
<td>{$product['band_name']}</td>
</tr>
<tr>
<td>{$product['product_price']}</td>
</tr>
</table>
<br><br>
HTML;

?>
}

[/code]

downfall · October 2, 2006

Thanks alpine, and for explaining the error. It's working great :D

To put the band lists links into a dropdown menu, is it just a matter of putting the standard HTML form tags around the echoed $list?

alpine · October 2, 2006

Yes :)

echo "<option value='$id'>$cat</option>";

or whatever...

downfall · October 2, 2006

Looks like im going to need some help for what looked a breeze... spent last 45 minutes getting nowhere!!

This is the current code ive got for the band select, with the form near the bottom:

[code]$sql = "SELECT band_id, band_name
FROM bands
ORDER BY band_name";
$res = mysql_query($sql) or die(mysql_error());
while (list($id, $cat) = mysql_fetch_row($res)) {
echo "<a href='details4.php?id=$id'>$cat</a>\n";
}

?>

<form name="myform" method="POST">
<select name="mydropdown">
<option value=<?'$id'>$cat?></option>
</select>
</form>[/code]

I've tried changing $cat and $id to $list, as $list is what echoed out the band links normally before trying into a dropdown.

But all i'm getting is a blank dropdown menu!!

alpine · October 2, 2006

Try this one:
[code]

<?php

$sql = "SELECT band_id, band_name
FROM bands
ORDER BY band_name";
$res = mysql_query($sql) or die(mysql_error());
$option = "";
while (list($id, $cat) = mysql_fetch_row($res)) {
$option .= "<option value=\"$id\">$cat</option>";
}

echo <<<_HTML

<form name="myform" method="get" action="details4.php">
<select name="id" onchange="this.form.submit();">
$option
</select>
</form>

_HTML;

?>

[/code]

downfall · October 2, 2006

Alpine, perfecto!

The main site navigation, browse by category and browse by band, are complete :)

So the last "main" thing I need for the site is a search. Can anyone guide me to what code needs altering to my previous display results page for it to display search results in matching words entered in search box? Here is the code that displays results by band name:

[code]
<?php

$id = $_GET['id']; // get from the adress bar, your previous link

$sql = "SELECT p.*
FROM products p
INNER JOIN band_products bp ON p.product_id = bp.product_id
WHERE bp.band_id = '$id'
ORDER BY p.product_name";
$result = mysql_query($sql) or die (mysql_error());
while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){
echo <<<HTML
<table>
<tr>
<td>{$product['product_name']}</td>
</tr>
<tr>
<td>{$product['band_name']}</td>
</tr>
<tr>
<td>{$product['product_price']}</td>
</tr>
</table>
<br><br>
HTML;

?>
}
[/code]

Any ideas wgat would need changing for a search? Also, would I need to create a new table, and if so, what field names are required?

alpine · October 2, 2006

Not tested, but i guess you can do that ;) - and no, you dont need a new table for that.
I named this file "search.php" (in form action), note that it's only this one file for the whole search
[code]
<?php

if(!empty($_GET['s']))
{
$s = htmlspecialchars($_GET['s']);

$sql = "SELECT p.*
FROM products p
INNER JOIN band_products bp ON p.product_id = bp.product_id
WHERE p.product_name LIKE '%$s%'
ORDER BY p.product_name";
$result = mysql_query($sql) or die (mysql_error());
if(mysql_num_rows($result) == 0)
{
print "<p>No Product-name found containing <b>$s</b>...</p>";
}
else
{
while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){
echo <<<HTML
<table>
<tr>
<td>{$product['product_name']}</td>
</tr>
<tr>
<td>{$product['band_name']}</td>
</tr>
<tr>
<td>{$product['product_price']}</td>
</tr>
</table>
<br><br>
HTML;
}
}
}

echo <<<_HTML

<form name="mySearchForm" method="get" action="search.php">
Search products:
<br />
<input type="text" name="s" value="" />
<br />
<input type="submit" name="submit" value="Search!" />
</form>

_HTML;

?>
[/code]

downfall · October 2, 2006

Fantastic. Works perfect.

I really am litterally almost there. There's just one thing I remembered I should in include. I've just been trying, but can't seem to do it. I'm trying to put category name somewhere on the display products by band page, so by each product has by it wether it's a tee, poster etc.

[code]
$id = $_GET['id']; // get from the adress bar, your previous link

$sql = "SELECT p.product_name, p.product_price, b.band_name
FROM products p
INNER JOIN band_products bp ON p.product_id = bp.product_id
INNER JOIN bands b ON bp.band_id = b.band_id
WHERE bp.band_id = '$id'
ORDER BY p.product_name, b.band_name";
$result = mysql_query($sql) or die (mysql_error());
while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){
echo <<<HTML
<table>
<tr>
<td>{$product['product_name']}</td>
</tr>
<tr>
<td>{$product['band_name']}</td>
</tr>
<tr>
<td>£{$product['product_price']}</td>
</tr>
<tr>
<td><a href="http://ww6.aitsafe.com/cf/add.cfm?userid=88166920&product={$product['band_name']} - {$product['product_name']}&price={$product['product_price']}">Buy Now</a></td>
</tr>
</table>
[/code]

Could you help with this one last thing!! I'm almost finished!! :) :)

Barand · October 2, 2006

[code]
$sql = "SELECT p.product_name, p.product_price, b.band_name, c.category_name
FROM products p
INNER JOIN band_products bp ON p.product_id = bp.product_id
INNER JOIN bands b ON bp.band_id = b.band_id
INNER JOIN categories c ON c.category_id = p.category_id
WHERE bp.band_id = '$id'
ORDER BY p.product_name, b.band_name";
[/code]

alpine · October 2, 2006

And always think about db-security, in this case on the get $id

[code]

$id = $_GET['id']; // get from the adress bar, your previous link
settype($id,"integer");

[/code]

downfall · October 2, 2006

Thanks alpine,
and
Thanks barand.

I don't have a clue if this is possible with php, but at the moment my product listing results look like this:

Metallica - Here We Go Again (T-Shirts)

As you can see, it says "T-Shirts". In the database I entered all the categories with an "s" on the end (eg. hats, t-shirts etc) as in needs to be like that to look right for the product listing menu. But, is there any way I can take off the "s" off the end of every category name for the products results display page I use? So it would just say, for example:

Metallica - Here We Go Again (T-Shirt)

Is this possible?

alpine · October 2, 2006

If it's ALWAYS one letter too much:

$category_name = substr($category_name, 0, -1);

downfall · October 2, 2006

Is there anywhere specific that line needs to go alpine? I just put it in a emty line but there is still an s on the category names.

Also, is there a way with a dropdown menu I can select a size (small,medium,large etc) and whatever gets selected, gets echoed on the same page? I don't want the outcome to be done after a buttons pushed. I need it so you select a size via dropdown, and then a few lines down the outcome is there automatically.

downfall · October 3, 2006

Don't worry about getting rid of the last letter of the categories, I've got that worked out now :)

I'm in a bit of a dilema for how to keep and display stock levels of products.

For t-shirts, hoodys etc i have 4 sizes of each product: small,med,large and x-large. So for these products I would need a small, medium, large and extra large option on the product listing page.

But as I'm selling othr merchandise such as band calenders , posters, I obviously don't want a small, mediumm, large, extra large size option for a poster or calender!

So in my databse in the "products" table, I have included the fields:

stock,stock_small, stock_medium, stock_large, stock_extra_large

I've made this to keep track of each stock size (eg. size_small etc.) I've just put "stock" at the beginning which is entered for all products that don't have sizes.

Am I on the right lines here? Can anyone tell me how I can only display a product size (in a select size dropdown) if it has a value of 1 or greater in my database field for that size (ie. Stock_small)? And if there are no values in any stock_small, stock_medium, stock_large, stock_extra_large fields in my table then NO dropdown size select even appears?

Can anybody help me with this?

Barand · October 3, 2006

Repeating fields like that are a bad idea. Instead have another table

[pre]
product prod_size
---------------- --------------
product_id --+ stock_id (pk)
product_name +--- product_id (fk)
product_small_image size
product_large_image stock_level
product_price
category_id (fk)
[/pre]

Now it's a simple query to build a menu of sizes with stock
[code]
<?php

$sql = "SELECT size FROM prod_size
WHERE product_id = '$id'
AND stock_level > 0";
$res = mysql_query($sql) or die(mysql_error());

if (mysql_num_rows($res) > 0) {
echo "<select name='size'>";
echo "<option value=''> - select size -</option>";
while ($row = mysql_fetch_row($res)) {
echo "<option value='$row[0]'> $row[0]</option>";
}
echo "</select>";
}

?>
[/code]

downfall · October 3, 2006

thanks barand, i'll give that a go :)

downfall · October 4, 2006

I'm a big hopeless at this :/

How can I intergrate the above code you gave barand with my existing code:

[code]
$cat = $_GET['id'];

$sql = "SELECT p.product_name, p.product_price, b.band_name, c.category_name
FROM products p
INNER JOIN band_products bp ON p.product_id = bp.product_id
INNER JOIN bands b ON bp.band_id = b.band_id
INNER JOIN categories c ON c.category_id = p.category_id
WHERE p.category_id = '$cat'
ORDER BY p.product_name, b.band_name";
$result = mysql_query($sql) or die (mysql_error());
while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){
echo <<<HTML
<table>
<tr>
<td>{$product['product_name']}</td>
</tr>
<tr>
<td>{$product['category_name']}</td>
</tr>
<tr>
<td>{$product['band_name']}</td>
</tr>
<tr>
<td>{$product['product_price']}</td>
</tr>
<tr>
<td><a href="http://ww6.aitsafe.com/cf/add.cfm?userid=88166920&product={$product['band_name']} - {$product['product_name']} ({$product['category_name']}) $size &price={$product['product_price']}">Buy Now</a></td>
</tr>
</table>
<br><br>
HTML;
}
[/code]

When I do it I get the select box come up with nothing in and visible php code!

Again, any help is much appeciated. ;)

Barand · October 4, 2006

If you are listing several products and want a select dropdown for each one then best way is to wrap the code as a function and call the function for each of the products listed
[code]<?php
function getSizeSelect ($prodid) {
$resultStr = '';
$sql = "SELECT size FROM prod_size
WHERE product_id = '$id'
AND stock_level > 0";
$res = mysql_query($sql) or die(mysql_error());

if (mysql_num_rows($res) > 0) {
$resultStr = "<select name='size'>";
$resultStr .= "<option value=''> - select size -</option>";
while ($row = mysql_fetch_row($res)) {
$resultStr .= "<option value='$row[0]'> $row[0]</option>";
}
$resultStr .= "</select>";
}

return $resultStr;
}
?>[/code]

To get the dropdown,

[code]echo getSizeSelect ($product_id);[/code]

downfall · October 4, 2006

Bit confused still! I think what I'm having trouble is intergrating the new sql query with my stock with my old sql query when asking for my product table data etc..

Could you tell me how i merge the 2 queries together - the existing one with the product/categorie tables and the new stock table?

Barand · October 4, 2006

You need to get the product_id in your select query then use that in the function call

[code]
<?
$cat = $_GET['id'];

$sql = "SELECT p.product_name, p.product_price, b.band_name, c.category_name, p.product_id
FROM products p
INNER JOIN band_products bp ON p.product_id = bp.product_id
INNER JOIN bands b ON bp.band_id = b.band_id
INNER JOIN categories c ON c.category_id = p.category_id
WHERE p.category_id = '$cat'
ORDER BY p.product_name, b.band_name";
$result = mysql_query($sql) or die (mysql_error());
while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){

$dropdown = getSizeSelect($product['product_id']); ## get the text for the dropdown
## and output with the other data
echo <<<HTML
<table>
<tr>
<td>{$product['product_name']}</td>
</tr>
<tr>
<td>{$product['category_name']}</td>
</tr>
<tr>
<td>{$product['band_name']}</td>
</tr>
<tr>
<td>{$product['product_price']}</td>
</tr>
<tr>
<td>$dropdown</td>
</tr>
<tr>
<td><a href="http://ww6.aitsafe.com/cf/add.cfm?userid=88166920&product={$product['band_name']} - {$product['product_name']} ({$product['category_name']}) $size &price={$product['product_price']}">Buy Now</a></td>
</tr>
</table>
<br><br>
HTML;
}
?>
[/code]

downfall · October 4, 2006

I'm getting this error:

Fatal error: Call to undefined function: getsizeselect() in /home/rockrag/public_html/details4.php on line 36

which is this line:

$dropdown = getSizeSelect($product['product_id']); ## get the text for the dropdown

any ideas how to fix this?

Barand · October 4, 2006

See reply #68

Put that function in your script.

I usually put function definitions at the top of my scripts.

downfall · October 4, 2006

thanks barand, getting better and no errors,

but isn't showing the dropdown menu? I definately have put some stock in the database. Is there something wrong with the code?

Barand · October 4, 2006

Oops!

Change the start of function definition from

function getSizeSelect ($prodid) {

to

function getSizeSelect ($id) {

Sign In

Help with making a Product Database

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information