I have an SQL Server database that looks like this (simplified):
productcode
title
barcode
material
price
stock
DA-0000
Ring
58320
Gold Κ14
100.00
0
DA-0154
Bracelet
32340
Gold Κ18
200.00
1
DA-0154
Bracelet
32341
Gold Κ14
100.00
0
DA-0154
Bracelet
NULL
Silver
100.00
1
I want to group items that have the same productcode, turning them into a ‘main’ product, and have the rest as ‘variants’ of the main products. Items with a NULL barcode should be listed after the ones with a specified barcode. Example result:
I only know a little SQL. I don’t know whether your level of expertise is much above, the same or below mine so apologies if I pitch this at the wrong level.
Firstly, I didn’t know it was possible to have multiple rows in one cell like that. But even given that it is then I suspect it may not be a wise move? I would imagine making queries of that could be very complicated and problematic.
What do you ultimately want to do with the database? I suspect whatever you wish to do could be better and more easily done by keeping the database as it is and querying it.
Thank you @wake689. I am not really sure I am approaching the problem in a wise way, as you said.
This database comes from an ERP that I have minimal control over. Its content must be plugged to a system that expects an associative array (php) of products and its variants.
At first I thought of doing a query to get the distinct ‘main’ products, and then query each one in a loop to get its variants. Then I learned that queries in loops are a bad idea.
My problem in short is how to build this array of products and variants from the flat database rows.
Not necessarily. I just meant that I think it would be better to just perform one SQL query and then use PHP to obtain the PHP array that you need, rather than doing many SQL queries inside PHP loops.
I’m afraid I don’t have enough SQL knowledge to know how far you could get with a SQL query before starting with the PHP.
I’m trying to visualise the form that the PHP array that you need would take, by looking at the table you showed. Would it be a three dimensional array?
I hope someone with greater knowledge turns up soon to help out.