I want to output query results in a particular format but don't know how

[Steveinid]

I have a database with stores and products. The stores are in one table and the products are in another. I have them 'joined' in a lookup table.

Now I want to display the stores and their associated products. The output would look like this:

Store1

• product 1
• product 2
• product 3

Store 2

• product 1
• product 2
• product 3

Not all stores have the same products. They are separated by category and sub category. Users would display the products using a drop down list or some other method of choosing the list of products.

I have been able to display each product individually so the output is like this:

Store 1

• product 1

Store 1

• product 2

etc....

Here is the code I'm using:

Quote

                while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                        {
                        echo  "<b>" . $row['store_name'] . "</b>" . " " . $row['address'];
                        echo "<br><br>";
                    }
                        echo $row['product_name']
                                . " - "
                                . $row["current_price"]
                                . "<br><br>";
                    }
                    echo "<br><br>";

I'm sure there are security issues. At the moment i'm not concerned with that. When I get it figured out then I'll address the security issues.

Any help is greatly appreciated.

 

Steve

 

 

 

[mac_gyver]

to output data in groups, by store, you would index/pivot the data using the store id as the main array index. this will give you a sub-array of the product data per store. PDO has a fetch mode that will do this for you, PDO::FETCH_GROUP. to get this to work, you would SELECT the store id as the first column being selected, then just use PDO::FETCH_GROUP in a fetchAll() statement. you would then use two nested foreach(){} loops, the first one would loop over the stores, getting the sub-array of product data per store, the second one would loop over the sub-array of product data for the current store. to get the unique store data, in the first foreach(){} loop, you can either use current() or reference the [0] element of the sub-array of product data.

also, if you set the default fetch mode to assoc when you make the database connection, you won't have to specify it in each fetch statement.

Edited July 12, 2022 by mac_gyver

[Steveinid]

I imagined something similar using the FOREACH loop but wasn't sure how to go about it. I'm new at this and am struggling.

Thank you, your reply is a great help.

 

Steve

 

Edited July 17, 2022 by Steveinid

[phppup]

This is not a forum where someone is going to write the code for you.

I suggest you learn the way I do, and use a search engine to help you visit tutorial websites dedicated to your task.

You need to find information about using SELECT statements, so "PHP select statement" would be a good start.

Some sites are more beneficial than others, so you may want to visit a few to understand variations and implementation.

Most good sites will demonstrate a concept and then allow you to click NEXT to expand on your knowledge.

You will likely need information to SELECT, use a WHERE statement, and understand JOIN.

Good luck, and come back if you run into issues that require coding refinement. That's where this forum is most helpful.

Edited July 17, 2022 by phppup
Typos

[Barand]

4 minutes ago, phppup said:

so "PHP select statement" would be a good start.

"SQL select statement" would be a better start. Select statements are not PHP.

[mac_gyver]

@phppup, i'm not sure what you read, but the OP is already querying for the correct data, is looping over the result from that query, and producing output. the only issue is needing some specific direction on how to output the store information only once per set of store data (and some extra {} in the posted code.)

for the OP, here's an outline of code that will do this -

// in your database specific code, index/pivot the data when you fetch it using the store id (first column selected)
$result_data = $stmt->fetchAll(PDO::FETCH_GROUP);


// at the point of producing the output
if(!$result_data)
{
	echo 'There is no data to display.';
}
else
{
	foreach($result_data as $arr)
	{
		// start a new store section here...
		
		// reference elements in $arr[0] to display the unique store information
		
		// loop over the sub-array of product data for the current store
		foreach($arr as $row)
		{
			// reference elements in $row to display product information
		}
		// end the current store section here
	}
}

 

[phppup]

@Steveinid and @mac_gyver Sorry if I missed something.  I stand corrected and apologize.

Edited July 18, 2022 by phppup

[Steveinid]

@mac_gyver Thanks again for the help. I think this is exactly what I'm looking for. I'm a beginner and I'm a little slow understanding a lot of this. I'll let you know how it turns out.

@phppup No worries. I'm not one for handouts. There are plenty of people who don't want to put in the time so I understand. I hope to get to the point where I can start helping others myself. It's a long way out but It may happen. We'll see. I just got a new book called "PHP & MYSQL" by Jon Duckett. It looks very promising for a beginner like me. It explains in detail how all of this stuff works. Unfortunately it doesn't have FETCH_GROUP in it.

[Steveinid]

I spent quite a bit of time figuring this out but I finally got the results I was asking for. It successfully outputs the name of the store followed by their respective products. I had to use the current() function I couldn't figure out how to "reference elements in $arr[0] to display the unique store information" so I ended up using the current() function which worked perfectly. Here is the code I am using:

            $result_data = $stmt->fetchAll(PDO::FETCH_GROUP);
                if(!$result_data)
                    {
                            echo 'There is no data to display.';
                    }
                            else 
                        {       
                            foreach($result_data as $arr)
                                {
                                    echo "<B>" ;
                                    echo current($arr[0]); 
                                    echo "</B>";
                                    echo "<br>";
                            foreach($arr as $row)
                                {
                                    echo $row['product_name'] 
                                        . " - " 
                                        . $row["current_price"] 
                                        . "<br>"
                                        . "<br><br>";
                                }
                            end($result_data);
                            }
                        }

So, now, I have run into another problem and I don't think the above code is capable of solving it. I mentioned that I wanted the store name to display followed by its products. That is exactly what I got. WELL, since some of the stores have the same name, I need to add other unique information with the store name like the address. (I didn't think about this when I requested help) I tried to add the additional information using what I thought was the obvious solution... inserting echo current($arr[1]) which holds the address information after echo current($arr[0]) which is the store name. It didn't work. I don't think FETCH_GROUP is the end solution to my goal.

Would a subquery in the SELECT statement be the solution I'm looking for?

Thanks again for the great help. It's appreciated.

Steve

[mac_gyver]

$arr[0] is the first row of data, which is an array. to 'reference elements in it' would look like $arr[0]['store_name'], $arr[0]['address'], ... you can always use print_r() or var_dump() on variables to see what they hold. if current($arr[0]) resulted in the store name, that means that the store name is the first column in the SELECT ... list. the id column in any database table should be an autoincrement primary index, which is unique, i.e. each different store, regardless of the name will have have its own id value. when i stated to select the store id as the first column being selected in order to make this work, this is the column i meant. if you don't have an autoincrement primary index column, you need to add it. if  you then select this as the first column in the SELECT ... list, the method i have posted will work for multiple stores, even with the same name.

[Steveinid]

Thank you for the quick reply. I tried things like echo $arr[0] or echo $arr[store_name] etc... but nothing ever worked. I spent most of Sunday working on this. It's a good thing programming is not like looking at the sun. I'd be blind by now

I used the print_r() successfully and was able to view the array while I was working on it. It made things much clearer. I do have the first column as the ID and it is the "autoincrement primary index". My lookup table is dependent on the ID of the store table and the product table so they have to be unique. I did SELECT the ID column first as you suggested. It was a key factor in making this work. I originally had the store name selected first.

FINALLY, because of your last post all is working well. I am able to display the additional information along with the store name. You have helped me more than you know. When I'm done with this project I'll send you a link so you can see what you did. It is greatly appreciated.

Thank you

Steve