PHP taking too long to process this SQL. Any SQL experts here?

I am importing a product list from an XML file. The supplier who built the XML file outputs every variation of a product, as a separate product. For example, a T-shirt which has three different sizes and three different colours, is listed in the XML files as 9 separate products, rather than 1 T-shirt with 9 options. I need to try link them together and the best way I can think of is to build a hierarchy.

This is how I have built it using PHP and MYSQL, but it takes forever to process. I’m 99.9999% certain that SQL could do this job directly in a few seconds. The problem is I don’t know to find the children and add them.

Any SQL experts here who can help with this?

// Add parent product into hierarchy
$stmt = "INSERT INTO ProductHierarchy (product_id, is_parent) SELECT product_id, 1 FROM ActiveProducts WHERE product_id NOT IN (SELECT product_id FROM ProductHierarchy) GROUP BY name";
$result = mysqli_query($sql_connect, $stmt);

// Get list of parents from hierarchy
$stmt = "SELECT ph.product_id, FROM ProductHierarchy ph INNER JOIN ActiveProducts ap ON ph.product_id = ap.product_id";
$result = mysqli_query($sql_connect, $stmt);

while ($row = mysqli_fetch_assoc($result)) {

    // Add new child to parent        
    $substmt = "INSERT INTO ProductHierarchy (product_id, is_child, parent_id) SELECT product_id, 1, '" . $row['product_id'] . "' FROM ActiveProducts WHERE name = '" . $row['name'] . "' and product_id NOT IN (SELECT product_id FROM ProductHierarchy)";
    $subresult = mysqli_query($sql_connect, $substmt);

A big hurdle in your logic is your first SQL statement is going to add EVERY record in ActiveProducts into ProductHierarchy, regardless of whether it’s a parent ID.

A better approach would be to handle this while importing the records initially. As it comes in, search the product hierarchy for that product name. If found, set the is_child and set the parent_id to that found record value, otherwise set the is_parent accordingly (note: You could just have one boolean - it’s either a parent or it’s not. If it’s not, then logically, it’s a child)

Hi Dave,

Data originally comes from the XMLimport and is moved into ActiveProducts using a INSERT INTO SELECT x,x,x,x FROM bla WHERE bla. I tried doing this logic in PHP & MySQL and it took 20 odd minutes to run so just had SQL do it directly. The same problem with building the hierarchy, I don’t know how to do that directly in SQL.

Products are import from an XML file into XMLImport. From here new products are moved into ActiveProducts like so…

INSERT INTO ActiveProducts (name,type,size,colour,sku,weight,instock,quantity,price,image1,image2,image3,image4,image5,image6,description,dateadded,updated)
SELECT name,type,size,colour,sku,weight,instock,quantity,price,image1,image2,image3,image4,image5,image6,description,NOW(),1
FROM XMLImport WHERE sku NOT IN (SELECT sku FROM ActiveProducts)

What I want to do here is say…

If name doesn’t already exist in ActiveProduct, then Parent_ID = NULL
Else, Parent_ID = Product_ID (of the first record returned ordered by product_id)

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.