Jump to content

niche

Members
  • Content count

    3,375
  • Joined

  • Last visited

Everything posted by niche

  1. https://www.w3schools.com/sql/sql_alter.asp Then, use the CONCAT_WS keyword
  2. niche

    registry backup

    Experience tells me that If i'm thinking about restoring the registry, I'm probably going to get unexpected consequences. So, I'll probably just reinstall the system and get a good night's sleep. Having the backup is one thing. Using it is another.
  3. niche

    registry backup

    I agree that it's a bad idea. EDIT: I would opt for a fresh install.
  4. https://www.w3schools.com/cssref/pr_background-color.asp
  5. niche

    php put inside css file security

    this might help https://css-tricks.com/css-variables-with-php/
  6. niche

    Dropping a table

    Do a 'SELECT * FROM closed' . If no error, it's still there unless corrupted. closed is not a reserved word, but close is.
  7. niche

    php put inside css file security

    More context please
  8. niche

    How to mark visited images

    Not with CSS. I'd redesign the image with divs positioned around the image , with each div having a JS listener, in place of the finger points.
  9. niche

    sqlite startup

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

    Not exists with concatenated columns

    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') )
  11. 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?
  12. niche

    JOINing a variable

    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
  13. niche

    Adding a 2d constraint

    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?
  14. niche

    Adding a 2d constraint

    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?
  15. niche

    Adding a 2d constraint

    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?
  16. niche

    Adding a 2d constraint

    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
  17. niche

    Adding a 2d constraint

    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
  18. niche

    delete_directory

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

    Adding a 2d constraint

    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
  20. niche

    strpos

    $haystack = ",18,19,42,"; $needle = ",18,"; if (strpos($haystack,$needle)) { echo 'TRUE'; } else { echo 'FALSE'; } This returns FALSE. What am I missing?
  21. niche

    strpos

    thanks dsonesuk!
  22. niche

    Slash using PDO's bindparam

    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?
  23. niche

    Slash using PDO's bindparam

    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.
  24. niche

    Slash using PDO's bindparam

    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.
  25. niche

    Slash using PDO's bindparam

    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.
×