Database output

davidmyers

I have a table with products in it. What I want to do is have a page that displays all of the different brands of the different products. I have a field named "brand" and I can get every value of brand to be output, but I don't want to put the same brand name out twice. I need some way to check and make sure that the brand which is in the current instance of "brand" that is being dealt with hasn't already been output onto the page and if it has then I want it to be skipped. Basically I have a bunch of fields with brands in them and I want to output one of each brand that is a value. Any suggestions?

NogDog

SELECT [color=red]DISTINCT[/color] brand FROM table_name ORDER BY brand

davidmyers

Wooow. I'm sorry, I'm just learning PHP and had no idea it was that simple. I actually had everything you gave me except for the DISTINCT. Thanks for the help, it worked perfectly!

davidmyers

I have a new question regarding this. Another field I have is "id", go figure. I need to select that along with the brand. When I changed the above code to do this I tried doing it like this:

SELECT DISTINCT brand, id FROM table_name ORDER BY brand

but that changed it to look for a distinct brand with that distinct id value which totally negated my original intention for this code. I have been able to do another query and get it output the ids with the brands, but they aren't matched up correctly. What is happening is it is outputting the different queries completely unrelated to each other. I hope that makes sense. If not, I can explain further. What I need is a way to do what the original code did, but also get the id of each of those results.

NogDog

Are you trying to use this query for two different purposes? If so, it may make more sense to execute two separate queries which are structured for their specific purposes. If this is not the case, then I may need a more detailed description of what you need these data rows for.

It might be that you can use a GROUP BY clause on the brand column, then doing a GROUP_CONCAT() on the id column, creating a delimited list of ids for each brand.

davidmyers

Ok, here is some more detail.

Using the code you originally supplied, I was able to have a page that displayed all of the distinct values of the field brand without any duplicates. What I am now trying to add on is a link to a dynamic page which gets it's information based upon the value of "id" which is passed to it from the link on the page which displays all of the items with a specific value of "brand".

ie:
I have a page which displays the different brands using the aforementioned code. Each brand type which is output is a link to page which I want to output every item (row) which has that specific value for "brand". I then want to get the id from each of those rows and get it matched to it's specific item so that I can make a link for that item to a dynamic product page which uses that specific id as the basis for it's information gathering.

I don't know if what you suggested can accomplish this or not as I am still in the early learning stages of PHP but there is no place to learn this type of thing quickly. Thanks again for the help.

laserlight

Yes, that can be done. You have the right idea: pass the brand name as a key/value pair.

If you are using the PDO extension, you might write something like:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo '<a href="brand.php?brand="' . urlencode($row['brand']) . '">'
        . htmlspecialchars($row['brand']) . "</a><br />\n";
}

You can adapt this for say, MySQLi, quite easily.

The [man]urlencode/man and [man]htmlspecialchars/man should be used to escape the data in the URL and the text respectively.

In brand.php, you would check $_GET['brand'] and then retrieve from the database using its value.

NogDog

You probably want to put the brand ID in the link, e.g.:

echo "<a href="show_stuff.php?brand='".$row['brand_id']."'>".$row['brand_name']."</a>";

Then in show_stuff.php you would get the brand ID from the $GET array, and use that in a query to get all items with that brand:

$query = "SELECT * FROM table_name WHERE brand_id=".(int)$_GET['brand'];

(I'm assuming that brand_id is an integer, and therefore casting the value from $_GET to an integer via the b[/b] expression so as to avoid the possibility of an SQL injection attack.)

davidmyers

Ok. Could you explain what exactly is going on here:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC))

What is $stmt?
That and also, I've already got a while loop going which takes the information from the DISTINCT query that is at the top of this page. Would I replace that while loop with this one? Or would I somehow combine the two?

Btw, I know I'm asking alot, but I figure it's better to learn it from people and getting something done rather than buy a book, be confused, and hope I can get everything working in less than a couple of weeks. Thx again.

laserlight

Could you explain what exactly is going on here:

Well, what database API are you using? What I wrote is similiar to:

while ($row = mysql_fetch_assoc()) {
    echo '<a href="brand.php?brand="' . urlencode($row['brand']) . '">'
        . htmlspecialchars($row['brand']) . "</a><br />\n";
}

davidmyers

I honestly don't know exactly what is set up as I'm just using a basic server package at 1&1.