Jump to content


Photo

Help with making a Product Database


  • Please log in to reply
81 replies to this topic

#41 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 10:43 AM

Thanks, getting closer :)

No error, But, it now says:

Unknown column 'name' in 'field list'

Any ideas?

#42 alpine

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

Posted 02 October 2006 - 10:47 AM

Your error is here:

SELECT name, price

No field named "name" in your table products

#43 Barand

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

Posted 02 October 2006 - 10:49 AM

try "product_name" maybe?
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

#44 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 11:04 AM

product_name - it works :D

I really do owe a huge thanks to alpine, huggie and barand! All you help is really appreciated!

My category listings now is basically complete so I can give the band select a go.

But theres one thing that would be useful for the category listings page thats just been fixed.

At the moment it looks like this (when the T-shirt link is pressed for example:

T-Shirt Name

$9.99


I would prefer it to have the band the t-shirt belongs to with it, like:

Band Name - T-Shirt Name

$9.99


Is this easy to do??

#45 Barand

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

Posted 02 October 2006 - 11:19 AM

Change query to

<?php
$sql = "SELECT p.product_name, p.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 p.category_id = '$cat'
        ORDER BY p.product_name, b.band_name";
?>

As discussed earlier, some products may have more than one band
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

#46 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 11:31 AM

When I click a category now, it says:

Unknown column 'bp_product_id' in 'on clause'

I tried changing bp_product_id to band_products.product_id as a guess but then I get a different error which I probably created myself by changing that text: Unknown table 'band_products' in on clause

#47 Barand

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

Posted 02 October 2006 - 11:34 AM

Sorry. That should have been "bp.product_id"

I made a couple of other edits so copy query again.
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

#48 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 11:45 AM

That seems to work with no errors :)

How can I successfully echo the band name into the HTML below? As you can see below, I added {$bands['band_name']} in a new table row but the band name doesn't appear for that product.. just the product name and price appear.

$cat = $_GET['id'];

$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 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>{$bands['band_name']}</td>
    </tr>
    <tr>
     <td>{$product['product_price']}</td>
    </tr>
   </table>
   <br><br>
HTML;
}


#49 alpine

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

Posted 02 October 2006 - 12:34 PM

You have to call it from the mysql_fetch_array you are using - thats $product

so {$product['band_name']} should do it

#50 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 01:52 PM

Thanks alpine :) I'm sure the browse by category section of my website is now complete at last :D

Now on to browse by band in a dropdown menu... :)

#51 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 02:19 PM

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:

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

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):

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

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

#52 alpine

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

Posted 02 October 2006 - 02:37 PM

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

?>
}



#53 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 03:14 PM

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?

#54 alpine

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

Posted 02 October 2006 - 03:19 PM

Yes  :)

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

or whatever...

#55 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 04:34 PM

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:

$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>

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!!

#56 alpine

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

Posted 02 October 2006 - 05:23 PM

Try this one:

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

?>



#57 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 06:10 PM

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:

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

?>
}

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?

#58 alpine

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

Posted 02 October 2006 - 06:59 PM

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

?>


#59 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 07:35 PM

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.

$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>

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

#60 Barand

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

Posted 02 October 2006 - 07:42 PM

$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";

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