Jump to content


Photo

Help with making a Product Database


  • Please log in to reply
81 replies to this topic

#61 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 02 October 2006 - 08:06 PM

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


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



#62 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 08:09 PM

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?

#63 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 02 October 2006 - 08:15 PM

If it's ALWAYS one letter too much:

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

#64 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 09:56 PM

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.

#65 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 03 October 2006 - 02:44 PM

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?

#66 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 03 October 2006 - 03:13 PM

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
<?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>";
}

?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#67 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 03 October 2006 - 04:18 PM

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

#68 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 04 October 2006 - 08:40 PM

I'm a big hopeless at this :/

How can I intergrate the above code you gave barand with my existing 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;
}

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

Again, any help is much appeciated. ;)

#69 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 04 October 2006 - 09:33 PM

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
<?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;
}
?>

To get the dropdown,

echo getSizeSelect ($product_id);

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#70 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 04 October 2006 - 09:56 PM

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?

#71 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 04 October 2006 - 10:13 PM

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

<?
$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;
}
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#72 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 04 October 2006 - 10:18 PM

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?

#73 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 04 October 2006 - 10:26 PM

See reply #68

Put that function in your script.

I usually put function definitions at the top of my scripts.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#74 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 04 October 2006 - 10:40 PM

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?

#75 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 04 October 2006 - 10:49 PM

Oops!

Change the start of function definition from

function getSizeSelect ($prodid) {

to

function getSizeSelect ($id) {
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#76 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 04 October 2006 - 11:00 PM

Brilliant!! One last thing, you may notice at the bottom of my code this "buy now" link which goes to my checkout:

<a href="http://ww6.aitsafe.c...oduct={$product['band_name']} - {$product['product_name']} ({$product['category_name']})&price={$product['product_price']}">Buy Now</a>

If you go to that link you can see it nicely echos the band product, band name, prod cat and product price. Is there a way by whatever is selected i the size dropdown gets echoed, so I can add the echoed size into the link above?

#77 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 04 October 2006 - 11:03 PM

No, in a word.

At the time you create that link, no size has been selected.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#78 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 04 October 2006 - 11:08 PM

On reflection, you might be able to do it with AJAX, creating the link code when the size is selected
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#79 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 04 October 2006 - 11:12 PM

How would I go about with doing that.. AJAX is something I haven't heard of.

Otherise, I'm just going to need to think how I can get round this. Either way, what you've done for me is great!

#80 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 04 October 2006 - 11:14 PM

See the link in my sig
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users