Sql query: get option_value_id ONLY if the "name" field matches option_value_description

Don't we all love sql queries?

Ok here is one for you that I am am too novice at sql queries to figure out past what I already have.

"SELECT option_value_id FROM " . DB_PREFIX . "product_option_value WHERE product_id = ' " .$productId . " ' AND (option_id = '18' OR option_id = '13')";
This returns the option_value_id's for whatever product_id I provide.

Is it possible to only return the option_value_id IF the "name" in option_value_description is a certain name?

Here is how I got the name but I had to provide the option_value_id from the sql query above
"SELECT name FROM " . DB_PREFIX . "option_value_description WHERE option_value_id = ' " . $eachoptionvalue . " ' "

Now I know there can be queries that use 'left join' type of stuff and this may be the time it wouldn't work but I'd like to know the possibilities....

This may be a quick answer of "no not possible" but I have spent a few hours trying to figure it out and I can't get it to work.

Thank you,
Mike

if you have $eachoptionvalue is an array, example [1,5,9,12] you can use this query
"SELECT name FROM " . DB_PREFIX . "option_value_description WHERE option_value_id IN(". implode( "," , $eachoptionvalue ).")"

boicute.14 wrote: ↑

Thu Feb 14, 2019 11:25 am

if you have $eachoptionvalue is an array, example [1,5,9,12] you can use this query
"SELECT name FROM " . DB_PREFIX . "option_value_description WHERE option_value_id IN(". implode( "," , $eachoptionvalue ).")"

I feared the question would be a little confusing but I am actually not looking for the name for each option value, per se, but I am looking to only get the option_value_ids but only the ones where the name in option_value_description is a name of my choosing.
Thank you for the reply and I hope that is a little more clear or at least not more confusing....
Mike

Same, name is array you choose in ex: query results

boicute.14 wrote: ↑

Thu Feb 14, 2019 2:25 pm

Same, name is array you choose in ex:

Code: Select all

$optionval = ["Yellow", "Green", "RED"];
SELECT * FROM " . DB_PREFIX . "option_value_description WHERE name IN('". implode( "','" , $optionval )."')

query results

Code: Select all

object(stdClass)[5]
  public 'row' => 
    array (size=4)
      'option_value_id' => string '42' (length=2)
      'language_id' => string '1' (length=1)
      'option_id' => string '5' (length=1)
      'name' => string 'Yellow' (length=6)
  public 'rows' => 
    array (size=3)
      0 => 
        array (size=4)
          'option_value_id' => string '42' (length=2)
          'language_id' => string '1' (length=1)
          'option_id' => string '5' (length=1)
          'name' => string 'Yellow' (length=6)
      1 => 
        array (size=4)
          'option_value_id' => string '41' (length=2)
          'language_id' => string '1' (length=1)
          'option_id' => string '5' (length=1)
          'name' => string 'Green' (length=5)
      2 => 
        array (size=4)
          'option_value_id' => string '39' (length=2)
          'language_id' => string '1' (length=1)
          'option_id' => string '5' (length=1)
          'name' => string 'Red' (length=3)
  public 'num_rows' => int 3

Ok but I am actually not looking for what that query puts out....
I am looking for the option_value_ids, this first query, from table product_option_value.
SELECT option_value_id FROM " . DB_PREFIX . "product_option_value WHERE product_id = ' " .$productId . " ' AND (option_id = '18' OR option_id = '13')";
option_value_id
18396
18397
18398
18399

Mike

What this user seem to be looking for is looking for a portion text from the option_value_description according to what it says on the first post.

In order to accomplish this, using the array with implode is not the solution but rather to look inside the description with an SQL function that has a particular criteria which I believe is what is the objective on this topic.

This site explains a great deal with description search by criteria by using MATCH and AGAINST functions: https://examples.javacodegeeks.com/core ... -tutorial/ .

In order to combine your array values separated by an OR SQL statement, you could use the already built-in idea from the catalog/model/account/download.php file with the config_complete_status in the foreach statement that you could integrate and replace that line into your project afterwards.

As an alternative, you could use the already built-in example in the catalog/model/catalog/product.php file with the product description field but I don't suggest it on first sight.

straightlight wrote: ↑

Thu Feb 14, 2019 10:00 pm

What this user seem to be looking for is looking for a portion text from the option_value_description according to what it says on the first post.

In order to accomplish this, using the array with implode is not the solution but rather to look inside the description with an SQL function that has a particular criteria which I believe is what is the objective on this topic.

This site explains a great deal with description search by criteria by using MATCH and AGAINST functions: https://examples.javacodegeeks.com/core ... -tutorial/ .

In order to combine your array values separated by an OR SQL statement, you could use the already built-in idea from the catalog/model/account/download.php file with the config_complete_status in the foreach statement that you could integrate and replace that line into your project afterwards.

As an alternative, you could use the already built-in example in the catalog/model/catalog/product.php file with the product description field but I don't suggest it on first sight.

You are right.... to add one bit of clarification...
If I run the first sql query I will get these (as an example)
option_value_id
18396
18397
18398
18399

If I could combine the two queries and only get the option_value_id for things that have something like "blue" in the 'name' from table option_value_description then maybe the output would only have
option_value_id
18396
18399


I'll look at that file when I can get to a place where I am on my computer to see.

I have it working right now with foreach loops, and it does work well, but with a file I am acting on having 18000 line items I am concerned that I will have a drag on the server each night as it goes through the update because of the 3 foreach loops going through the arrays for comparison for action and if I can reduce the items to work on (like above example it has 1/2 of the items to work on) it will be faster and less intensive.

Mike

I have it working right now with foreach loops, and it does work well, but with a file I am acting on having 18000 line items I am concerned that I will have a drag on the server each night as it goes through the update because of the 3 foreach loops going through the arrays for comparison for action and if I can reduce the items to work on (like above example it has 1/2 of the items to work on) it will be faster and less intensive.

The example I covered above helps you to prevent ending on this road since it allows you to regroup the terms you'd be looking for in your SQL queries as it would also help you to simplify the results so not to obtain 18000 line items. Once the MATCH and AGAINST results being returned, you can also use a preg_match with PHP to only and specifically keep the sentences containing the terms you'd like to keep as this methodology will even simplify even more the results you are looking for with the option value ids in order to optimize your store as compared to the already built-in method of the catalog product descriptions for instance.

The last step would then be to use an array filter with an array unique function or to use an array mapping for your option value ids to ensure having unique IDs of the distinct results you have obtained from your queries.

straightlight wrote: ↑

Fri Feb 15, 2019 1:16 am

I have it working right now with foreach loops, and it does work well, but with a file I am acting on having 18000 line items I am concerned that I will have a drag on the server each night as it goes through the update because of the 3 foreach loops going through the arrays for comparison for action and if I can reduce the items to work on (like above example it has 1/2 of the items to work on) it will be faster and less intensive.

The example I covered above helps you to prevent ending on this road since it allows you to regroup the terms you'd be looking for in your SQL queries as it would also help you to simplify the results so not to obtain 18000 line items.

The clarify. My input feed is 18000+ line items.
I take each line in the file, run the query to get the option_value_ids, run another query to get the name of the option from the option_value_description table.
Then run a compare from one of the fields I get from the line in the file (description) against the name from option_value_description.
If the name matches, I update the information in the product_option_value table for the specific option_value_id.
If the name doesn't match move on to the next option_value_id.

When that last option_value_id is reached, I then retrieve the next line from the file and do it again.

Mike

Still a possible way to do it but, sadly, the file size also matters in this case when replicating the results into an array or object. When too many values are involved, the replicated content on the browser may collapse due to oversized memory buffer allocation size.

G'day Mike - is this following on from this post? I don't think the angle you're taking here will work, esp with the number of rows you're trying to check. I'd consider some paid help for this, cos it's a script that could be written fairly quickly.
Simon

soundzgood-nz wrote: ↑

Tue Feb 19, 2019 12:25 pm

G'day Mike - is this following on from this post? I don't think the angle you're taking here will work, esp with the number of rows you're trying to check. I'd consider some paid help for this, cos it's a script that could be written fairly quickly.
Simon

G'day Simon,
Yes it is, and it completely works.
I feel if I could get the query optimized to do one query to only get the option_value_id only when the name is the same as the 'name' in option_value_description.
As it works now, it works through all option_value_ids from the product and if it finds it right away I update and exit, but if it takes until the 4th option_value_id it 'wastes' 3 cycles to get there.
Now it will not kill me, or the server if this is how it stays but I always like optimizing after I get the functions working.

Mike

So tell us more about the 18k+ products ... do they all have options? Can they be grouped by the type of options they share? Do these options change every update or are they static - eg product id 100 = always has option_value_ids 1 and 50 ... ?

soundzgood-nz wrote: ↑

Sat Feb 23, 2019 2:52 am

So tell us more about the 18k+ products ... do they all have options? Can they be grouped by the type of options they share? Do these options change every update or are they static - eg product id 100 = always has option_value_ids 1 and 50 ... ?

They are actually 18k line items for the file that can be broken down to less than 6K products.
So to take the example from the previous post
redbrick39 18,6
redbrick39 19,10
redbrick39 20,8
redbrick39 21,10

redbrick39 is the product.
18, 19, 20 or 21 is the option.
But not all products have options so a line in the file could be:
brickglue, 40
That would be the product with 40 quantity.

And yes a lot of products share the same options. so I could have redbrick40 with options 18, 19, 20, and 21; redbrick41 with the same options, etc. But of course not every product is that way. Others could have redbrick42 with 31 and 36 as options.
Or caulk10 with blue, black, red, green, truffle, brown as options.

Now in order to answer the other questions becomes a bit harder. The options themselves are pretty static so almost always redbrick39 will have the same options but a different quantity.
Also we use JNeuhoff's importexport tool so that can change option_value_ids per product so those are not always static #s.

Thank you,
Mike

If option ids are static, can you not loop through product ids and avoid text field searches?

soundzgood-nz wrote: ↑

Sun Feb 24, 2019 2:51 am

If option ids are static, can you not loop through product ids and avoid text field searches?

That piece I don't know 100%. I was under the impression using the import/export tool it changes the option ids when you do an import. I'll have to look through the logic again as I looked recently and I saw the two pieces that gave me pause but I didn't pursue further...
/ reuse old options and old option_values where possible
$optionIds = array(); // indexed by [name][type]
$optionValueIds = array(); // indexed by [name][type][value][image]
And
// start transaction, remove product options and product option values from database
$sql .= "DELETE FROM `".DB_PREFIX."product_option`;\n";
$sql .= "DELETE FROM `".DB_PREFIX."product_option_value`;\n";
$this->multiquery( $database, $sql );

It should be quick to look and see but I'm a bit too beat to think it through at the moment.
Thank you for your reply,
Mike

If you're looking to use a custom code from the import / export tool, contact the extension developer to see how this could be done with the multiquery method.

straightlight wrote: ↑

Sun Feb 24, 2019 3:58 pm

If you're looking to use a custom code from the import / export tool, contact the extension developer to see how this could be done with the multiquery method.

This is NOT what I am doing.
I use the import export tool in normal upkeep of my site.
IF that tool changes the option IDs then it would alter make so the option IDs are not static. I was answering your question, not trying to use code from JNeuhoff.

Mike

Cue4cheap wrote: ↑

Sun Feb 24, 2019 12:55 pm

soundzgood-nz wrote: ↑

Sun Feb 24, 2019 2:51 am

If option ids are static, can you not loop through product ids and avoid text field searches?

That piece I don't know 100%. I was under the impression using the import/export tool it changes the option ids when you do an import. I'll have to look through the logic again as I looked recently and I saw the two pieces that gave me pause but I didn't pursue further...
/ reuse old options and old option_values where possible
$optionIds = array(); // indexed by [name][type]
$optionValueIds = array(); // indexed by [name][type][value][image]
And
// start transaction, remove product options and product option values from database
$sql .= "DELETE FROM `".DB_PREFIX."product_option`;\n";
$sql .= "DELETE FROM `".DB_PREFIX."product_option_value`;\n";
$this->multiquery( $database, $sql );

It should be quick to look and see but I'm a bit too beat to think it through at the moment.
Thank you for your reply,
Mike

Cue4cheap wrote: ↑

Sun Feb 24, 2019 9:26 pm

straightlight wrote: ↑

Sun Feb 24, 2019 3:58 pm

If you're looking to use a custom code from the import / export tool, contact the extension developer to see how this could be done with the multiquery method.

This is NOT what I am doing.
I use the import export tool in normal upkeep of my site.
IF that tool changes the option IDs then it would alter make so the option IDs are not static. I was answering your question, not trying to use code from JNeuhoff.

Mike

One thing is for sure, the multiquery method is not part of the core and is part of an extension.

straightlight wrote: ↑

Sun Feb 24, 2019 9:56 pm

Cue4cheap wrote: ↑

Sun Feb 24, 2019 12:55 pm

soundzgood-nz wrote: ↑

Sun Feb 24, 2019 2:51 am

If option ids are static, can you not loop through product ids and avoid text field searches?

That piece I don't know 100%. I was under the impression using the import/export tool it changes the option ids when you do an import. I'll have to look through the logic again as I looked recently and I saw the two pieces that gave me pause but I didn't pursue further...
/ reuse old options and old option_values where possible
$optionIds = array(); // indexed by [name][type]
$optionValueIds = array(); // indexed by [name][type][value][image]
And
// start transaction, remove product options and product option values from database
$sql .= "DELETE FROM `".DB_PREFIX."product_option`;\n";
$sql .= "DELETE FROM `".DB_PREFIX."product_option_value`;\n";
$this->multiquery( $database, $sql );

It should be quick to look and see but I'm a bit too beat to think it through at the moment.
Thank you for your reply,
Mike

Cue4cheap wrote: ↑

Sun Feb 24, 2019 9:26 pm

straightlight wrote: ↑

Sun Feb 24, 2019 3:58 pm

If you're looking to use a custom code from the import / export tool, contact the extension developer to see how this could be done with the multiquery method.

This is NOT what I am doing.
I use the import export tool in normal upkeep of my site.
IF that tool changes the option IDs then it would alter make so the option IDs are not static. I was answering your question, not trying to use code from JNeuhoff.

Mike

One thing is for sure, the multiquery method is not part of the core and is part of an extension.

How can we be so miss-communicating ?
You asked me if the the option ids are static.
I replied I don't know as I use a contrib to make site updates so maybe the option ids are not static.

Simple but you went sideways on me.....