Jump to content

Archived

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

downfall

Help with making a Product Database

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?

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites
Yes  :)

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

or whatever...

Share this post


Link to post
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!!

Share this post


Link to post
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]

Share this post


Link to post
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?

Share this post


Link to post
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]

Share this post


Link to post
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!! :) :)

Share this post


Link to post
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]

Share this post


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

Share this post


Link to post
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?

Share this post


Link to post
Share on other sites
If it's ALWAYS one letter too much:

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

Share this post


Link to post
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.

Share this post


Link to post
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?

Share this post


Link to post
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]

Share this post


Link to post
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. ;)

Share this post


Link to post
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]

Share this post


Link to post
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?

Share this post


Link to post
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]

Share this post


Link to post
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?

Share this post


Link to post
Share on other sites
See reply #68

Put that function in your script.

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

Share this post


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

Share this post


Link to post
Share on other sites
Oops!

Change the start of function definition from

function getSizeSelect ($prodid) {

to

function getSizeSelect ($id) {

Share this post


Link to post
Share on other sites

×

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.