Jump to content

Help with making a Product Database


downfall

Recommended Posts

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?
Link to comment
Share on other sites

  • Replies 81
  • Created
  • Last Reply
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]
Link to comment
Share on other sites

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!!
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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!! :) :)
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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. ;)
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.