Barand

Have you remembered the "[]" in the file input field name? <input type='file' name='filestoupload[]' multiple>

Perhaps this is why

Have you considered defining email and phone columns as UNIQUE then trapping dupe key errors on insert? That should be far better than searching for potential dupes before inserting. (You would have to cleanse your data of current duplicates first, however) Yes thanks. The ~3000 duplicates in my last post are from your data. Perhaps, for a single search, you could mysql> SELECT id -> FROM customers -> WHERE email = '[email protected]' -> UNION -> SELECT id -> FROM customers -> WHERE phone = '612-772-4311'; +-------+ | id | +-------+ | 11902 | +-------+ 1 row in set (0.00 sec)

The killer with my query with your data is the join condition ((a.email = b.email) OR (a.phone = b.phone)) Using the OR ... mysql> SELECT DISTINCT -> a.id -> FROM customers a -> JOIN ( -> SELECT c.id -> , c.email -> , c.phone -> FROM customers c JOIN quotes q ON q.customer_id = c.id AND q.purchased = 1 -> ) b ON ((a.email = b.email) OR (a.phone = b.phone)) -> AND a.id <> b.id -> ORDER BY a.id; +-------+ | id | +-------+ | 9 | | 13 | | 21 | | 36 | | 38 | | 41 | . . . | 11878 | | 11879 | | 11887 | | 11893 | | 11900 | | 11903 | +-------+ 2987 rows in set (1 min 29.25 sec) !!! Using a UNION to pull those matching on email then those matching on phone ... mysql> SELECT -> a1.id -> FROM customers a1 -> JOIN -> customers b1 ON (a1.email = b1.email) AND a1.id <> b1.id -> JOIN -> quotes q1 ON q1.customer_id = b1.id AND q1.purchased = 1 -> UNION -> SELECT -> a.id -> FROM customers a -> JOIN -> customers b ON (a.phone = b.phone) AND a.id <> b.id -> JOIN -> quotes q ON q.customer_id = b.id AND q.purchased = 1 -> ORDER BY id; +-------+ | id | +-------+ | 9 | | 13 | | 21 | | 36 | | 38 | | 41 | . . . | 11878 | | 11879 | | 11887 | | 11893 | | 11900 | | 11903 | +-------+ 2987 rows in set (1.33 sec)

Your query has "dependent subqueries" (to be avoided) which are ineffecient compared to joins (which mine uses) which is why I did my own 10000 record test. I couldn't believe yours was faster. Adding a dupe email on records 1 and 10,000 (2 rows returned) gave your time as 0.156 sec and mine as 0.015 (10x diff).

TEST 2 10,000 customers. IDs 890 and 8000 have same phone number. 890 has purchased = 1 mysql> select count(*) from customers; +----------+ | count(*) | +----------+ | 10000 | +----------+ Your query mysql> SELECT -> `a`.`id` AS `customer_id` -> FROM -> `customers` `a` -> WHERE -> ( -> SELECT -> `b`.`id` -> FROM -> `customers` `b` -> JOIN `quotes` ON `b`.`id` = `quotes`.`customer_id` -> WHERE -> ( (`a`.`email` = `b`.`email`) OR(`a`.`phone` = `b`.`phone`) ) -> AND(`a`.`id` <> `b`.`id`) -> AND (`quotes`.`purchased` = 1) -> GROUP BY -> `a`.`email` -> ); +-------------+ | customer_id | +-------------+ | 8000 | +-------------+ 1 row in set (0.13 sec) My query mysql> SELECT DISTINCT -> a.id -> FROM customers a -> JOIN -> customers b ON ((a.email = b.email) OR (a.phone = b.phone)) -> AND a.id <> b.id -> JOIN -> quotes qb ON qb.customer_id = b.id AND qb.purchased = 1 -> ORDER BY a.id; +------+ | id | +------+ | 8000 | +------+ 1 row in set (0.02 sec) 6.5x faster !?

I have added quotes to my data and queries. This is my test data.. My query mysql> SELECT DISTINCT -> a.id -> FROM customers a -> JOIN -> customers b ON ((a.email = b.email) OR (a.phone = b.phone)) -> AND a.id <> b.id -> JOIN -> quotes qa ON qa.customer_id = a.id AND qa.purchased = 1 -> JOIN -> quotes qb ON qb.customer_id = b.id AND qb.purchased = 1 -> ORDER BY a.id; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 9 | +----+ Your query (on the same data) gives mysql> SELECT -> `a`.`id` AS `customer_id` -> FROM -> `customers` `a` -> WHERE -> ( -> SELECT -> `b`.`id` -> FROM -> ( -> `customers` `b` -> JOIN `quotes` ON -> ( -> ( -> `b`.`id` = `quotes`.`customer_id` -> ) -> ) -> ) -> WHERE -> ( -> ( -> (`a`.`email` = `b`.`email`) OR(`a`.`phone` = `b`.`phone`) -> ) AND(`a`.`id` <> `b`.`id`) AND(`quotes`.`purchased` = 1) -> ) -> GROUP BY -> `a`.`email` -> ); +-------------+ | customer_id | +-------------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 9 | | 12 | | 15 | +-------------+

75 seconds seems excessively long! If you want to email me a dump of those two tables I'll see if can can speed it up for you.

Here's my version of your page, keeping the PHP separated from the HTML a far as is possible. <?php $res = $con->query("SELECT user_id , user_firstname , user_lastname , user_username , user_phone , GROUP_CONCAT(site_name SEPARATOR ', ') as site , user_title_id , user_role_id FROM users u LEFT JOIN site s ON u.user_id = s.site_manager_id GROUP BY user_id; "); $data = ''; foreach ($res as $row) { $data .= "<tr><td>" . join('</td><td>', $row) . "</td><td class='table-action'> <a href='#'><i class='align-middle' data-feather='edit-2'></i></a> <a href='#'><i class='align-middle' data-feather='trash'></i></a> </td> </tr>\n"; } ?> <!DOCTYPE html> <html> <head> <title>Example Multiple Locations</title> <meta http-equiv="content-language" content="en"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> </head> <body> <table id="datatables-column-search-select-inputs" class="table table-striped" style="width:100%"> <thead> <tr> <th>ID</th> <th>FirstName</th> <th>LastName</th> <th>Username</th> <th>Phone #</th> <th>Location</th> <th>Title</th> <th>Role</th> <th>Actions</th> </tr> </thead> <tbody> <?=$data?> </tbody> </table> </body> </html>

Don't run queries inside loops. Use a single with join/s to get all the data in one go. Don't use "SELECT * ". Specify the fields you need TEST DATA TABLE: users +---------+----------------+---------------+---------------+------------+---------------+--------------+ | user_id | user_firstname | user_lastname | user_username | user_phone | user_title_id | user_role_id | +---------+----------------+---------------+---------------+------------+---------------+--------------+ | 1 | Peter | Dowt | peterd | 1234 | 1 | 1 | | 2 | Laura | Norder | lauran | 2345 | 2 | 2 | | 3 | Tom | DiCanari | tomd | 3456 | 1 | 1 | | 4 | Scott | Chegg | cheggs | 4567 | 2 | 2 | | 5 | Polly | Vinyl | pollyv | 5678 | 3 | 1 | +---------+----------------+---------------+---------------+------------+---------------+--------------+ TABLE: site +---------+-----------+-----------------+ | site_id | site_name | site_manager_id | +---------+-----------+-----------------+ | 1 | Site A | 2 | | 2 | Site B | 2 | | 3 | Site C | 4 | | 4 | Site D | 4 | | 5 | Site E | 5 | +---------+-----------+-----------------+ QUERY SELECT user_id , user_firstname , user_lastname , user_username , user_phone , GROUP_CONCAT(site_name SEPARATOR ', ') as site , user_title_id , user_role_id FROM users u LEFT JOIN site s ON u.user_id = s.site_manager_id GROUP BY user_id; RESULTS +---------+----------------+---------------+---------------+------------+----------------+---------------+--------------+ | user_id | user_firstname | user_lastname | user_username | user_phone | site | user_title_id | user_role_id | +---------+----------------+---------------+---------------+------------+----------------+---------------+--------------+ | 1 | Peter | Dowt | peterd | 1234 | | 1 | 1 | | 2 | Laura | Norder | lauran | 2345 | Site B, Site A | 2 | 2 | | 3 | Tom | DiCanari | tomd | 3456 | | 1 | 1 | | 4 | Scott | Chegg | cheggs | 4567 | Site D, Site C | 2 | 2 | | 5 | Polly | Vinyl | pollyv | 5678 | Site E | 3 | 1 | +---------+----------------+---------------+---------------+------------+----------------+---------------+--------------+

That strikes me as a problem with the data, not the query.

foreach (array as $recno => $record) { echo "<h3>Record $recno</h3>"; foreach ($record as $field => $value) { echo "$field : $value<br>"; } }

I see emails and phone numbers customers +----+------+------------+-------+ | id | name | email | phone | +----+------+------------+-------+ | 1 | aaa | [email protected] | 123 | | 2 | bbbb | [email protected] | 124 | | 3 | ccc | [email protected] | 124 | | 4 | ddd | [email protected] | 123 | | 5 | eee | [email protected] | 125 | | 6 | fff | [email protected] | 126 | | 7 | ggg | [email protected] | 127 | | 8 | hhh | [email protected] | 128 | | 9 | iii | [email protected] | 125 | | 10 | jjj | [email protected] | 129 | | 11 | kkk | [email protected] | 130 | | 12 | lll | [email protected] | 127 | | 13 | mmm | [email protected] | 131 | | 14 | nnn | [email protected] | 132 | | 15 | ooo | [email protected] | 123 | | 16 | ppp | [email protected] | 133 | +----+------+------------+-------+ query SELECT DISTINCT a.id , a.email , a.phone FROM customers a JOIN customers b ON ((a.email = b.email) OR (a.phone = b.phone)) AND a.id <> b.id ORDER BY a.id; results +----+------------+-------+ | id | email | phone | +----+------------+-------+ | 1 | [email protected] | 123 | | 2 | [email protected] | 124 | | 3 | [email protected] | 124 | | 4 | [email protected] | 123 | | 5 | [email protected] | 125 | | 7 | [email protected] | 127 | | 9 | [email protected] | 125 | | 12 | [email protected] | 127 | | 15 | [email protected] | 123 | +----+------------+-------+

Use the successive keys. $value = $array[$recordNo][$fieldname]; EG $date = $array[0]['t_test_date']; // 2021-02-11

perhaps this, then SELECT a.id , a.email , a.phone FROM customers a JOIN customers b ON ((a.email = b.email) OR (a.phone = b.phone)) AND a.id <> b.id ORDER BY a.id;

OK, call me old fashioned, but why don't you just write a query instead of struggling to find the right incantations to get the db classes to do it for you?

posted in error

By "repeat customers" do you mean customers who have placed more then one order, or do you mean "duplicate" customers with more than 1 customer record? If it's the latter SELECT eamil , phone , GROUP_CONCAT(id SEPARATOR ', ') as duplicates FROM customers GROUP BY email, phone HAVING COUNT(*) > 0;

session.gc_maxlifetime = 86400

Such as what?

Looks like that code is giving ORDER BY l.buyout_price = 0 instead of the code I used... (which would explain the "weird ordering") ORDER BY l.buyout_price = 0, price (The ones that aren't zero need sorting by price)

Strange, because if I sort by that expression, I get mysql> SELECT id -> , name -> , price -> FROM test_a -> ORDER BY CASE price WHEN 0 THEN -0 ELSE price END; +----+------+-------+ | id | name | price | +----+------+-------+ | 4 | D | 0 | | 3 | C | 1 | | 5 | E | 5 | | 1 | A | 12 | | 2 | B | 200 | +----+------+-------+ However, on a similar tack, mysql> SELECT id -> , name -> , price -> FROM test_a -> ORDER BY CASE price WHEN 0 THEN 999999999 ELSE price END; +----+------+-------+ | id | name | price | +----+------+-------+ | 3 | C | 1 | | 5 | E | 5 | | 1 | A | 12 | | 2 | B | 200 | | 4 | D | 0 | +----+------+-------+ EDIT.... My apologies, I read it as a minus sign and not a bitwise inversion. Your version works fine. mysql> SELECT id -> , name -> , price -> FROM test_a -> ORDER BY CASE price WHEN 0 THEN ~0 ELSE price END; +----+------+-------+ | id | name | price | +----+------+-------+ | 3 | C | 1 | | 5 | E | 5 | | 1 | A | 12 | | 2 | B | 200 | | 4 | D | 0 | +----+------+-------+ ( ~0 evaluates to 18,446,744,073,709,551,615 )

I've no idea what that code of yours does. All I could do was show you how to use SQL TO order by a column ASC but with zero values last. How you accomplish that with your classes is down to you I'm afraid.

Do you mean something like this example? mysql> select * from test_a; +----+------+-------+ | id | name | price | +----+------+-------+ | 1 | A | 12 | | 2 | B | 200 | | 3 | C | 1 | | 4 | D | 0 | | 5 | E | 5 | +----+------+-------+ mysql> SELECT id -> , name -> , price -> FROM test_a -> ORDER BY price = 0, price; +----+------+-------+ | id | name | price | +----+------+-------+ | 3 | C | 1 | | 5 | E | 5 | | 1 | A | 12 | | 2 | B | 200 | | 4 | D | 0 | +----+------+-------+ (The value of "price = 0" will be either 1 or 0 depending on whether it is true or false)

The "WHERE ptsl_date = '2021-06-12' " is OK in the WHERE clause but you cannot put conditions on a LEFT JOINed subquery (or table) in a WHERE clause, they need to be in the join's ON clause.