Manipulate PHP Array for Google Product Feed (sizes to records)

[John_A]

I have an existing PHP array ($products) with various fields in it.... id / title / description / link / image link etc. etc. as per requirements for a Google Products feed (https://support.google.com/merchants/answer/7052112).

My issue is that, for clothing, Google want a separate record for each size available. My array includes the "size" field as a comma separated list e.g. 6,8,10,12,14 or XS,S,M,L,XL etc. etc.

So before creating the feed (a text file, which is currently outputting fine except for the size field issue) I need to duplicate each id where there's more than 1 size in that field, for each size, and then manipulate the fields a little so that (ignoring all the duplicated fields that would remain unchanged) instead of the single record : -

id            size                                                                        item group id
52           6,8,10,12,14,16,18,20,22,24,26

I'd have 11 records, item group id would be what the id is, the size appended to the id, and the size field only one (in sequence of those from the original), so: -

id            size        item group id

52-6       6              52
52-8       8              52
.
..
...
52-24     24           52
52-26     26           52

That's just one product....there are quite a lot, each with multiple sizes, but all in the same format within the $products array.

As always, any help / pointers / solutions much appreciated!

[Barand]

What code do you have so far?

[John_A]

It turns out I don't actually have the sizes in the $product array as above, they're a query within the loop that builds the feed in it's current state.

$result is the query to fetch all products...

$count = $dbA->count($result);
for ($f = 0; $f < $count; $f++) {
    $record = $dbA->fetch($result);

    $productSizes = "";
    $result2 = $dbA->query("SELECT $ExtraFieldsValues.content From $ExtraFields, $ExtraFieldsValues Where $ExtraFields.name = 'size' and $ExtraFields.extraFieldID = $ExtraFieldsValues.extraFieldID and $ExtraFieldsValues.prodID =".$record["prodID"]." Order By $ExtraFieldsValues.position");
    $count2 = $dbA->count($result2);
    for ($f2 = 0; $f2 < $count2; $f2++) {
        $record2 = $dbA->fetch($result2);
        $productSizes .= ",".$record2["content"];
    }
    if (strlen($productSizes) > 0){
        $productSizes = substr($productSizes,1);
    }

    echo $productSizes . '<br/>';
}

 

The $products array includes the other fields I referenced above along with quite a few others: -

 

Quote

Array
(
    [prodID] => 52
    [title] => widget
    [description] => for removing things
    [price] => 1.00 GBP
    .
    ..
    ...
)

 

but does NOT include the item group id field, which should be added blank or populated as above for each product.

I'm not sure if this makes it easier, or harder?

[Barand]

There has to be a more efficient way of achieving your goal. Running queries inside loops like that is not the best way.

What are the structures of the tables you are querying (the two in that query and the one for the outer query)?

[John_A]

5 minutes ago, Barand said:

There has to be a more efficient way of achieving your goal. Running queries inside loops like that is not the best way.

What are the structures of the tables you are querying (the two in that query and the one for the outer query)?

There probably is but it's for an automatic google product feed that will run once every month so efficiency isn't really a concern.

It's part of a bespoke ecommerce script I inherited and can't really change much on, the table structures are what they are and can't be changed at this stage.

So what I have is the $products array which includes most of the fields I need straight out of it, but the sizes needs to be a 2nd query within that. The $product array should be updated on-the-fly with duplicates where necessary with only one size in each, and the id and item group id updated too.  

[John_A]

Going through the array for sizes was messing with the feed output later on, so I changed it to a foreach instead. The plan is for the first foreach loop to manipulate the product array as necessary, then once done a separate foreach going through the now correct array to output the feed. Here's the one I need to fix: -

 

foreach ($result as $key => $thisProduct) {
	echo $thisProduct["prodID"] . '<br/>';

	$thisproductSizes = "";
	$sizesResult = $dbA->query("SELECT $ExtraFieldsValues.content From $ExtraFields, $ExtraFieldsValues Where $ExtraFields.name = 'size' and $ExtraFields.extraFieldID = $ExtraFieldsValues.extraFieldID and $ExtraFieldsValues.productID =".$thisProduct["prodID"]." Order By $ExtraFieldsValues.position");
	$sizesCount = $dbA->count($sizesResult);
	for ($sf2 = 0; $sf2 < $sizesCount; $sf2++) {
		$sizesRecord = $dbA->fetch($sizesResult);
		$thisproductSizes .= ",".$sizesRecord["content"];
	}
	if (strlen($thisproductSizes) > 0){ // there's at least one size so needs to be dealt with
		$thisproductSizes = substr($thisproductSizes,1);
		
		
		// do stuff here to make one product into many
		// each with item_group_id = prodID,
		// and id = prodID-xx (size), with only one size


	} else {
		$thisproductSizes = "";
	}
	echo $thisproductSizes . '<br />';
}

But, thinking about it, if there's only 1 size that's fine, it's if there's 2 or more that there'll be an issue...

Edited June 5, 2020 by John_A

[Barand]

OK, if you're happy to continue coding your queries like that, I'll leave you to it.

Edited June 5, 2020 by Barand

[John_A]

57 minutes ago, Barand said:

OK, if you're happy to continue coding your queries like that, I'll leave you to it.

OK, thanks for your help so far. Partially thanks to your input I changed my approach completely, now manipulating the feed as I create it line by line later in the same file, rather than messing with the array beforehand, and have it working just as I need.

Hopefully, one day, I'll be as awesome as you and not need to ask for help.

Stay safe, and thanks again!

[Barand]

It had occurred to me that an alternative approach to the problem might be on the cards, but without knowing what you were starting with there wasn't much anyone else could do. Hence the request for the input tables.

[Zane]

I completely agree with Barand.  There is definitely a more efficient way of doing this, but it is your choice to keep doing it this way.  You could at least set up a sandbox/staging version of your site and practice with different approaches, but that's just me saying things.

In any case, we need more information:

What does your table schema look like?

What kind of output are your receiving?

What do the arrays look like once you've looped through everything?

Why aren't you just exploding on the comma?  

Step by step, what is going on here.  Echo it all out and show it to us.  We only have a snippet of your code and very vague output.  I completely understand the concept of the format Google Product Feeds is looking for.  You want the id of the product to be the ID in the database concatenated with a "-" and the size number.  You also want to maintain that id before concatenating and use it for your item_group_id.  Rather than just slapping a snippet of code on here, please walk us through the whole process.  Without any substance, there isn't really a question, is there?  GIGO is what they call it.  Your answers will only ever be as good as the question you start with.

Here's a snippet of code I like to use for debugging, which would definitely help you out in improving your question.

echo "<pre>", print_r($data, false), "</pre>";

$data can be anything, a string, an array, an object, a boolean, it doesn't matter.  print_r's first argument is expected to be mixed.

 

[John_A]

Thanks both for your input.

As I said, it was a bespoke shopping cart script, that I inherited, which already had a Google feed but the sizes were in a comma separated list for each product (used to be OK with Google) but now needs to be one size per product. I wrote neither the initial script nor the google feed add-on. I've been tasked with this, and not a lot of time to do it. I'm already working on a local dev server, not the live site. TBH it would have taken me longer to do everything you suggest to ask the question "properly" than it did for me to fix it myself.

I didn't explode the commas because the CSV list is built from another query on the database for the prodID, all individual rows as product options for size, so didn't see the point in building it again then exploding it to get back to where I started. It's a CSV in the original as that used to be OK with Google.

I'm pretty sure I gave as much information as was needed to answer / help if someone was inclined to. In fact I posted a duplicate elsewhere, and it was answered with a solution - too late for me but it looked like it would have worked. Hey ho. Lesson learnt.

[Barand]

therefore someone else in another forum had their time wasted too.