Barand

As I stated, you are looping through your first query results, and, for each result, you are querying the users table. Running queries inside a loop is not the way to go. My query JOINs matching user records with the driver_route records so the whole thing is done with a single query. I set up a couple of test files with the relevant columns mysql> SELECT * FROM drive_routes; +----+--------+--------+-------+-------------+ | id | driver | price | costs | cargodamage | +----+--------+--------+-------+-------------+ | 1 | 1 | 25.00 | 5.00 | 2.00 | | 2 | 2 | 125.00 | 5.00 | 20.00 | | 3 | 1 | 75.00 | 5.00 | 2.00 | | 4 | 2 | 25.00 | 5.00 | 2.00 | | 5 | 1 | 250.00 | 35.00 | 20.00 | | 6 | 1 | 25.00 | 5.00 | 2.00 | | 7 | 2 | 125.00 | 15.00 | 12.00 | | 8 | 3 | 25.00 | 5.00 | 2.00 | | 9 | 1 | 225.00 | 50.00 | 2.00 | | 10 | 3 | 225.00 | 5.00 | 2.00 | +----+--------+--------+-------+-------------+ mysql> SELECT * FROM users; +-------+----------+ | id | username | +-------+----------+ | 1 | User 111 | | 2 | User 222 | | 3 | User 333 | +-------+----------+ Then ran my query SELECT u.username ,SUM(price) as price ,SUM(costs) as costs ,SUM(cargodamage) as cargodamage ,SUM(price - costs - cargodamage) as profit FROM drive_routes dr INNER JOIN users u ON u.id = dr.driver GROUP BY driver ORDER BY profit DESC; giving +----------+--------+--------+-------------+--------+ | username | price | costs | cargodamage | profit | +----------+--------+--------+-------------+--------+ | User 111 | 600.00 | 100.00 | 28.00 | 472.00 | | User 333 | 250.00 | 10.00 | 4.00 | 236.00 | | User 222 | 275.00 | 25.00 | 34.00 | 216.00 | +----------+--------+--------+-------------+--------+

Instead of an "items_completed" table you could just have a "date_completed" column (default NULL) in the items table and timestamp the item when completed.

SELECT order_id , SUM(price) as total_price , SUM(tax) as total_tax , SUM(price+tax) as total_product FROM order_product GROUP BY order_id

Sure, you can select constant values just as in any select statement INSERT INTO login_attempts ('identity', colA, colB, colC) SELECT email_address, 123, 'ABC', NOW() FROM users WHERE email_address = '[email protected]'

or $data = file_get_contents($uploaded_file); file_put_contents($current_file, $data, FILE_APPEND);

INSERT INTO login_attempts ('identity') SELECT email_address FROM users WHERE email_address = '[email protected]' If email doesn't exist in users table then no insert.

You are mixing statement syntaxes here. What do want to insert into the identity column? If it is the id of the user then INSERT INTO login_attempts ('identity') SELECT user_id FROM users WHERE email_address = '[email protected]'

I am going to guess there is no index on name or vehicle

Table aliases are normally optional are just a shorthand convenience. In this query we are joining twice to the quote_responses table so we need to able to distinguish between the one joined to quote_id and the one joined to ref_code, hence the aliases are required - think if it as two separate logical tables even though there is only one physical table. "<>" is the same as "!=" In the above query each select subquery is called for each record to find the matching records. So if you have 100 results you are calling an extra 200 "dependent" subqueries, a situation which should be avoided by using a more efficient join.

You need to check that data has been sent and that $_GET['name'] actually has a value before you try to use it if (isset($_GET['name'])) { // process } Use prepared statements instead of putting user provided values into your queries

Don't have your data to test but try this SELECT qr1.name as name , qr2.name as referral_name , r.id FROM referrals r INNER JOIN quote_responses qr1 ON qr1.id = r.quote_id AND qr1.purchased = FALSE AND qr1.name <> '' INNER JOIN quote_responses qr2 ON qr2.id = r.ref_code AND qr2.name <> '' WHERE r.sent = FALSE ORDER BY `referral_name`

You have mixture in the above lines and in other lines in your code. To use mysqli_ you must have a mysqli connection

You would be limited by available memory echo ini_get('memory_limit'); If you want to monitor usage memory_get_usage, memory_get_peak_usage

Alas, I can't. I have never read one, except manuals. There is good video tutorial on data normalization http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360 If you get the data right it can make life easier, at least where SQL is concerned.

You can't mix mysql_ functions and mysqli_ functions, Use one or the other and not mysql_.

I think the addition of the last line will do it SELECT User , COUNT(*) as `Count` , GROUP_CONCAT(datarichiesta,' (',result,')' ORDER BY datarichiesta SEPARATOR ' , ') as Dates FROM ( SELECT datarichiesta , @N := IF((codicepz<>@prevuser) OR (@prevres < 10) OR (risultato < 10) OR (datarichiesta > @startdate + INTERVAL 6 MONTH) , @N+1, @N) as groupno , @startdate := IF((codicepz<>@prevuser) OR (@prevres < 10) OR (risultato < 10) OR (datarichiesta > @startdate + INTERVAL 6 MONTH) , datarichiesta, @startdate) as startdate , @prevres:=risultato as result , @prevuser:=codicepz as user FROM mastertot JOIN ( SELECT @N:=0, @prevres:=NULL, @prevuser:=NULL ) as init WHERE descrprestaz = 'a1' ORDER BY codicepz, datarichiesta ) as detail GROUP BY user,groupno HAVING `Count` >= 5 AND MIN(datarichiesta) + INTERVAL 1 MONTH < MAX(datarichiesta);

So have I got this right? Currently if a test result date is more than 6 months from the start of the run then it is not included in the run and a new run starts. You now want that if a test date is less than month since the previous test then, again, it is not part of a run. This will mean that a run can be no longer than 6 tests and then only if they are exactly one month apart. Longer than a month apart the run will shorter. If too long then you have no results at all as there will be less than 5 in every run.

see the edit to my post

Store the prefix in its own column with an auto_incrementing numeric id. To select SELECT CONCAT(prefix, id) as custno, ..... edit: and make the id column INT(5) ZEROFILL

In the same way that the query tests for a time period of six months, a similar test could added in the same statement to check for a minimum period

Your original post required records for each person/date so you have decide what to do about those occasions where a patient has several tests for the same condition in a single day. At the moment it takes the MIN value but you could also have MAX or AVG. Whichever one you decide, you still will have a different count between the two tables because of this date repetition.

I think I know why there are missing records. Yesterday we had an instance where a patient had six tests on the same day. When we create mastertot2 we are grouping by CodicePz , DataRIchiesta so those six records are condensed to one.

It looks like you just need to run a simple query SELECT C FROM table1 WHERE A = $input1 AND B = $input2 Add an INDEX on (A,B) to the table

Have you any NULL results? SELECT COUNT(*) FROM mastertot WHERE risultato IS NULL;

One of the problems with storing descriptions instead of foreign keys in tables is they are prone to misspelling. One check you could do is SELECT DescrPrestaz , COUNT(*) as sampleTotal FROM mastertot GROUP BY DescrPrestaz; This will give the counts for each and will show if there are descriptions that are wrong