niche

You also can use python to display the results from your queries.

Voila !

SELECT  
		nconnectz.itemandsize, nconnectz.pkg_quantity
	FROM    
		nconnectz  
	WHERE   
		NOT EXISTS
			(
				SELECT  
					*
				FROM    
					itemandsize  
				WHERE   
					CONCAT_WS(',', nconnectz.itemandsize, nconnectz.pkg_quantity)  = CONCAT_WS(',', itemandsize.itemandsize, '0.00')
        )

This code works with cols that are not concatenated:

SELECT  
		item3 
	FROM    
		nconnectz t1
	WHERE   
		NOT EXISTS
			(
				SELECT  
					item3
				FROM    
					itemandsize t2
				WHERE   
					t1.item3 = t2.item3
        )
	GROUP BY item3

I think this is the same query with concatenated columns

SELECT  
		CONCAT_WS(',', itemandsize, pkg_quantity) item4 
	FROM    
		nconnectz t1
	WHERE   
		NOT EXISTS
			(
				SELECT  
					CONCAT_WS(',', itemandsize, '0.00') item4
				FROM    
					itemandsize t2
				WHERE   
					t1.item4 = t2.item4
        )
	GROUP BY item4

the error is: Unknown column 't1.item4' in 'where clause'

what's causing the error?

Anyone have a reference for how JOINing a variable works?

As in:

UPDATE nconnectz.test
JOIN (SELECT @rank := 20) r
SET route = @rank := @rank + 1

They're all potentially different.

In this case, the first pass returned 7 rows and 8 columns.  There will always be varying variability in each column.

I suppose my big decision is how much I want to do with SQL and how much with PHP.

Seems to me PHP is easier to work with than SQL after I use SQL to whittle down 100,000 rows down down to 7.

This topic is about how to take 7 rows down to 2 with columns of varying variability AND  pushing my SQL experience in an effort to pay down and avoid code debt..

Just by changing the ORDER BY I can prep an array for targeting in a loop to zero in on those 2 rows.

I know every situation is different. 

How did you strike the balance between potentially long queries in a model and a few more PHP lines in a view as you became a guru?  

OK.  I'm following you.

The core of my query is 26 lines (more or less).

So,  you're saying that sql needs 26 lines (more or less) to zero - in on each column and get the precise data i want.

So, 8 columns means a query of 200 lines (more or less).

Right?

Thanks jsg. 

1st Situation

SELECT store, vendor_id, item_id,  MIN(price) AS minprice FROM (
...
) AS subset3 GROUP BY vendor_id

Looks like all I need is to GROUP BY vendor_id to get two rows returned  store,  vendor_id, item_id, minprice (I expected two rows)

However, when I add id to the SELECT

2nd Situation

SELECT id, store, vendor_id, item_id,  MIN(price) AS minprice FROM (
...
) AS subset3 GROUP BY vendor_id

I get the same data I received in the 1st Situation except the ids are wrong

What do you think?

EDIT:

datetimes don't correspond to the min price either

EDIT 2:

I'm back to returning 7 rows when my GROUP BY list = my SELECT list (less minprice)

SELECT id, datetime2,store, vendor_id, item_id,  MIN(price) AS minprice FROM (
...
) AS subset3 GROUP BY id, datetime2,store, vendor_id, item_id

The first query  correctly selects 7 rows.

The second query correctly selects the lowest prices from based on the 1st query.

How do I join these two queries?  

NOTE: Only difference between the 1st and 2nd query is the first and last line of the 2nd query.

QUERY 1:
 

 

SELECT id
      , nconnectz.item_id
      , nconnectz.vendor_id
      , nconnectz.store
       , nconnectz.datetime2
      , nconnectz.itemandsize
      , nconnectz.pkg_quantity
      , nconnectz.pkg_price
      , nconnectz.price
    FROM ( SELECT item_id
      , pkg_quantity    
      , vendor_id
      , MAX(datetime2) AS latest
    FROM nconnectz
    WHERE item_id IN (7)
      AND vendor_id IN (5,2)
    GROUP
      BY item_id , vendor_id, pkg_quantity
    ) AS subset
    INNER
      JOIN nconnectz
        ON nconnectz.item_id   = subset.item_id    
        AND nconnectz.vendor_id = subset.vendor_id               
        AND nconnectz.datetime2 = subset.latest
        AND  nconnectz.pkg_quantity = subset.pkg_quantity
    ORDER BY item_id ASC , vendor_id ASC, pkg_quantity ASC

QUERY 2:
 

    SELECT MIN(price) AS minprice FROM (
    SELECT id
      , nconnectz.item_id
      , nconnectz.vendor_id
      , nconnectz.store
       , nconnectz.datetime2
      , nconnectz.itemandsize
      , nconnectz.pkg_quantity
      , nconnectz.pkg_price
      , nconnectz.price
    FROM ( SELECT item_id
      , pkg_quantity    
      , vendor_id
      , MAX(datetime2) AS latest
    FROM nconnectz
    WHERE item_id IN (7)
      AND vendor_id IN (5,2)
    GROUP
      BY item_id , vendor_id, pkg_quantity
    ) AS subset
    INNER
      JOIN nconnectz
        ON nconnectz.item_id   = subset.item_id    
        AND nconnectz.vendor_id = subset.vendor_id               
        AND nconnectz.datetime2 = subset.latest
        AND  nconnectz.pkg_quantity = subset.pkg_quantity
    ORDER BY item_id ASC , vendor_id ASC, pkg_quantity ASC
   ) AS subset3 GROUP BY  vendor_id    

Can you test it in a new version?  It's the best way to find out.

This produces the lowest price subset of the query in the OP.

Do you see any issues?

SELECT *, MIN(price) AS minprice FROM (
	SELECT id
	  , nconnectz.item_id 
	  , nconnectz.vendor_id
 	  , nconnectz.datetime2
	  , nconnectz.itemandsize
	  , nconnectz.price
	FROM ( SELECT item_id   
	  , vendor_id 
      , MAX(datetime2) AS latest 
	FROM nconnectz 
    WHERE item_id IN (' . $in . ') 
	  AND vendor_id IN (' . $in2 . ')
    GROUP
      BY item_id , vendor_id
	) AS subset 
	INNER
	  JOIN nconnectz
        ON nconnectz.item_id   = subset.item_id    
        AND nconnectz.vendor_id = subset.vendor_id               
        AND nconnectz.datetime2 = subset.latest
    ) AS subset2 
	GROUP
  	  BY item_id , vendor_id 	

My nconnectz table also has a price column.

I've figured out how to get the latest datetime:

SELECT nconnectz.item_id 
	  , nconnectz.vendor_id
 	  , nconnectz.datetime2
	FROM ( SELECT item_id   
	  , vendor_id 
      , MAX(datetime2) AS latest 
	FROM nconnectz 
    WHERE item_id IN (' . $in . ') 
	  AND vendor_id IN (' . $in2 . ')
    GROUP
      BY item_id , vendor_id
	) AS subset 
	INNER
	  JOIN nconnectz
        ON nconnectz.item_id   = subset.item_id    
        AND nconnectz.vendor_id = subset.vendor_id               
        AND nconnectz.datetime2 = subset.latest 

What should my thinking be to select the latest datetime with the lowest price?

thanks dsonesuk!

$haystack = ",18,19,42,";
$needle = ",18,";
if (strpos($haystack,$needle)) {
	echo 'TRUE';
} else {
	echo 'FALSE';
}	

This returns FALSE. 

What am I missing?

Turn's out the problem was in my bootstrap.

Thanks to ingolme and jsg.  It was jsg's post that got me to thinking more broadly and ingolme's last post that zeroed in on the real problem.

Thanks again for your patience.

Thanks for the question jsp.

Things just got weirder.

Yes, I can select 'Coffee / Tea' directly.

Seems I can get the same results even after I  change 'Coffee / Tea' to ' '  in the table (new issue)!

This is a live Godaddy server.

I wonder if it has some kind of a virus or malware? 

everything else works as expected.

EDIT:

Cleared cache, got same results on chrome, ff, and ipad. 

hmmm.

Thanks for your patience.

I expect 2 rows returned from the my code.

I get zero rows back and no errors.

I have visually verified that 2 rows exist with the 'coffee / tea' string.

I have also verified that error reporting is working.

all error reporting is on. 

no error message is displayed.

EDIT:

Just found out that client manually entered coffee / tea.

Does that make a difference?

here's my simple example:

try {
            $sql = 'SELECT     itemandsize FROM itemandsize WHERE  sub_category  = :sub_category'  ;
            $stmt = $this->db->prepare($sql);
        } catch (PDOException $e) {
            echo $e->getMessage() . '<br>';
        }        
        
        try {
            $stmt->execute(array(':sub_category' => $var));    
        } catch (PDOException $e) {
            echo $e->getMessage() . '<br>';
        }    

$var = 'coffee / tea'

I'm certain that the slash is the problem, but don't know why.  

Do you know why?

I thought treating $var in the array auto resolved all formatting issues.

Obviously not.

$var = 'coffee - tea' works fine

EDIT:

Is it just that slashes are used for escaping?

My experience of how mysql determines what result goes first varies, that why I use the ORDER BY keyword.

don't understand your question

need to do a join

https://www.w3schools.com/sql/sql_join.asp

$ was the givaway sorry about that.   

try putting px after 0

EDIT:

else, restore to the version before the changes

without access to the offending code that's producing the offending code, I think it's a google problem. 

$nt1 is an array

https://www.w3schools.com/js/js_arrays.asp

EDIT:

the info between the commas are arguments

addOption is a function (jquery i think)