benanamen

!IMPORTANT: New data file attached After testing this is the response I got. If you don't understand it let me know and I will get clarification. barand-1-11-15.sql.txt

Close. This should do it. Not so sure about the AND without testing. Thinking a recruiter is tied to a county which is tied to a state so the AND would not be needed. Are there Recruiters that are only tied to a state and not a county? SELECT * FROM states s LEFT JOIN counties c ON ( c.id = s.state_id ) LEFT JOIN recruiter r ON ( r.state_id = s.id AND r.county_id = c.id ) WHERE state_id = '$StateID'

Thanks Barand! EDIT* !IMPORTANT: For some reason the first run of the query gives no results, then running again will give results. Doing an update that changes a count will show the change on a single run of the query after that first empty one. This was also tested by someone else outside my network. What are the x1 and x2 columns and results? Also, you posted a chart. What did you use for that output? I was going to use google charts but I like the display you did.

I apologize for the trouble with some data not matching the rules I provided. I am dealing with a fourth party to the third party and getting the right explanations is sometimes muddied. I have no problem making a donation to you for your help on this. I know it is time consuming and on the complicated side. Yes, your right, it SHOULD be 1,1,1 by the rules. What I was not told was that they were accounting for users currently being able to insert dates in the wrong order of the business rule flow. I will be building in restrictions in the app to force the correct flow. To get straight for our purpose here, just change the schedule.complete_date to 2015-01-08. That should give you a correct result of 1,1,1 per the rules. Ok, update on what dried in means. Doesn't change anything for our purpose though. A slab becomes a dried_in status when they put the roof on the house, therefore, the time for slab to dried_in could be same day, a week, or years and will be counted as a dried_in until the tile floor is finished which in our case means through the week of schedule.complete_date. As you discovered, there was going to need accounting going back to the prior year. I was trying to keep things as simple as possible initially. Just an FYI on the final requirement when all is good is they will have an option to select a start and end date range from a weekly drop-down select. I should have no problem with that once the query is right. Your help is greatly appreciated.

Yeah, there's no point doing it now since they can still make the same mistakes until everything gets updated. Will you be able to help me with the last part of the query?

Ahhh, ok, Just realized the live is still running phase one version. Phase two with all the changes has not gone live yet, so as of now, they can do many bad things that have been fixed or changed in phase two. Supposed to go live with phase two in a week or so and actually this Chart is the last part to finish Phase 2.

Yes, @Barand, You are correct. Those results are bad data. Thank you The reason it was able to get in there is that this app has been in continual development for over a year and at the time those records were added, there were no business rules or code in place to keep that from happening. At present, it is just as I told you, impossible through the app for that to happen now. Fortunately, it is someone else job to "Fix" the data. So back to the dried_count, did you understand the explanation? It is really the same as the slab count except for considering conditions related to the scheduling/completion of tile flooring. Let me know if you need anything else. You have been a tremendous help. This app is the most complex app I have ever built and I have learned quite a bit doing it.

I see a few problems. Line one isnt what you think it is. Missing curly braces SELECT * FROM PI WHERE PIID={$_GET[PIID]} Is $_GET[PIID] a number or something else? Do not EVER use $_SERVER['PHP_SELF']. It is vulnerable to SQL Injection. Use $_SERVER['SCRIPT_NAME'] Why in the world did you go back to using deprecated code when you where using Mysqli? And stop mixing your case all over the place. Always use lower case.

You cant go by the test data as far as that goes. Minimal records are in there for testing. If you looked over the actual live data I gave you in pm you should see it it not so. If you see whatever problem you are referring to is in the live full database, let me know as it should not be so and would indicate a problem in the application. The test data has actually been manipulated directly on the DB and not through the app. If your interested I could PM you the url and superadmin login to the app on the dev server.

It will never happen that way. The order of the process is forced in the app. It is impossible for a user to do it in the wrong order. Process First status=lot Second=slab Third=dried_in Fourth= Frame Fith and final=Drywall

What have you tried? Where is your code?

Ok, plain version A lot is started to be counted as a dried in every week starting from the date in lot.dried_in_date The next thing that happens is that same lot is scheduled to have tile flooring installed in the house. (lot_id and work_type_id=1 is inserted in to schedule table). Nothing changes. We are still counting the lot as a dried in every week. It has only been scheduled at this point Now the change: The final step is the tile flooring is installed and the job has been completed (schedule.complete_date is is not null for that lot with a schedule.work_type_id of 1 which is tile). This is the last week we count it as dried in. Yes, I saw #7. It is showing the start end weeks. No problem there. There are no double not nots. The example is a union query, to display a week at a time with the exact conditions just different week start/end dates Just to double check, I downloaded the data from that post #8 in a new db, copy/pasted the posted query from post#8 and get 0,1,1 as it should.

1. I assume you used the latest sql dump I posted, otherwise it wont work. 2. I posted a standalone query for the dried_in only. Using the latest DB dump and running the standalone query you will get the results of 0,1,1 Please verify you are at this point while I work on getting the correct non technical explanation. * Your previous post has no consideration for conditions with the schedule table which is why you are not getting the correct result as you would from my dried_in query. In a nutshell, For a dried_in lot we need to know if tile flooring has been scheduled to be installed or not and if it has been scheduled, has the job been completed. This will determine if it is counted or not. Plain detailed explanation coming shortly.

Isnt it great how php will tell you exactly what is wrong when you ask it to? My guess is that you took old mysql and just added an "i" to the end which will not work. Go look up a basic mysqli connection example and you will see what you are missing. You really shouldn't need us to tell you the problem at this point. http://www.w3schools.com/php/func_mysqli_query.asp

FYI Experts, slade has run up a thread with 158 posts on basically the same matter on another forum frustrating every expert programmer there and has apparently decided to come here. Fasten your seatbelts for this ride.

First thing, you are using deprecated code that will not work at all in the latest version of Php. You need to use PDO or MYsqli with prepared statements. That should be your first order of business. Next, use HTML5. You are incorrectly using the word "Rank". Rank means something else to every programmer. What you are referring to is a role, or access level. Also, for that column name you used upper case. ALWAYS use lower case for column names and variable names. It will save you much trouble. Regarding blocking ip's, @Jaques1 has already gone into detail on these forums about that. Read his posts. Finally,you dont need to manually close the connection. Php does it automatically when the script finishes running.

You are missing the post method from your form tag. method="post"

Thanks @Barand, We are looking good on the slab_count side. The following is a tested query for the dried in count. Notable changes are the join of the schedule table and conditions regarding that table. These only apply to the dried in count. The following should be used to update the dried_in result of the previous query you posted. !IMPORTANT Also note the updated SQL attachment. The slab_count should still be the same as it was before. Expected dried_in results are week1=0, week2=1, week3=1 (Slab should still be counts of 2,3,4). Using the live data I sent you in PM I am seeing some negative counts for some slab records, not sure why. ie: -xxx. The only other addition I am looking for which you did in private chat query is the display of the dates equal to the week numbers. Optimally a concat of the week_start - week_end dates for the display shown in the previous chart image, but at minimum the start date of each week starting on Sundays. Thank you so much. It isn't often I get stumped so that says a lot about your skills. SELECT Count(l.lot_id) AS dried_in_count, WEEK ('2015-01-04') AS week_number FROM lot l LEFT JOIN `schedule` AS s ON s.lot_id = l.lot_id LEFT JOIN block AS b ON l.block_id = b.block_id LEFT JOIN community AS c ON b.community_id = c.community_id WHERE l.dried_in_date <= '2015-01-10' AND (c.contract_type_id = 1 OR c.contract_type_id= 3) AND NOT ( s.work_type_id IN (1) AND s.complete_date IS NOT NULL ) OR l.dried_in_date <= '2015-01-10' AND (c.contract_type_id = 1 OR c.contract_type_id= 3) AND (s.work_type_id=1 AND s.complete_date >= '2015-01-04') UNION SELECT Count(l.lot_id) AS dried_in_count, WEEK ('2015-01-11') AS week_number FROM lot l LEFT JOIN `schedule` AS s ON s.lot_id = l.lot_id LEFT JOIN block AS b ON l.block_id = b.block_id LEFT JOIN community AS c ON b.community_id = c.community_id WHERE l.dried_in_date <= '2015-01-17' AND (c.contract_type_id = 1 OR c.contract_type_id= 3) AND NOT ( s.work_type_id IN (1) AND s.complete_date IS NOT NULL ) OR l.dried_in_date <= '2015-01-17' AND (c.contract_type_id = 1 OR c.contract_type_id= 3) AND (s.work_type_id=1 AND s.complete_date >= '2015-01-11') UNION SELECT Count(l.lot_id) AS dried_in_count, WEEK ('2015-01-18') AS week_number FROM lot l LEFT JOIN `schedule` AS s ON s.lot_id = l.lot_id LEFT JOIN block AS b ON l.block_id = b.block_id LEFT JOIN community AS c ON b.community_id = c.community_id WHERE l.dried_in_date <= '2015-01-24' AND (c.contract_type_id = 1 OR c.contract_type_id= 3) AND NOT ( s.work_type_id IN (1) AND s.complete_date IS NOT NULL ) OR l.dried_in_date <= '2015-01-24' AND (c.contract_type_id = 1 OR c.contract_type_id= 3) AND (s.work_type_id=1 AND s.complete_date >= '2015-01-18') new_data.sql.txt

Just got some clarification Say week one and two we have a slab with no dried in date during those two weeks, the count is 1 for each week. Week 3 now has a dried_in date for that week, so we dont count it as a slab anymore (Next phase of this we will be doing dried_in counts but not yet.) Say week one and two we have a slab with no dried in date during those two weeks, the count is 1 for each week. Week 3 now has a dried_in date for that week, so this will be the last week it is counted as a slab. So all three weeks will have a count of 1 but week 4 and on, it will no longer be counted as a slab. Part2: That same lot is now going to be started to be counted as a dried_in in part2 coming up. (Week 3 lot is a slab AND a dried in status)

My Bad, made error on desired results count image. Corrected image attached Slab count should be 2, 3, and 4 as in the current.jpg. With the posted data and query, those results are the correct counts. Ok, the rules are fairly simple once you get a grasp of this. For clarification we are talking about housing lots where a house will eventually get built. A 'Slab' is the date when they pour the drive way cement. dried_in is the date when the cement is dried enough to continue work. The "rules" are in the where condition but can be confusing. A 'slab' should be counted as a slab every week that it is NOT a dried_in in the same week. Say week one and two we have a slab with no dried in date during those two weeks, the count is 1 for each week. Week 3 now has a dried_in date for that week, so we dont count it as a slab anymore (Next phase of this we will be doing dried_in counts but not yet.) So basically for each individual week, they are wanting to know how many lots were a slab status. (Final query will actually be getting a weekly count of the dried_in statuses per week. See chart image for example) * Week Starts Sunday.

Your not going to get anywhere without posting your code for us to review.

Where is this array coming from?

We dont have crystal balls. Tell us what problems your having. What is the result? What is the expected result?

I have the following UNION query that works. I need this same thing so that I don't have to UNION fifty two times to get the weekly results for a year. Attached is the DB Import Dump, Current Query Result, The desired output, and a chart graphic of what I am looking to end up with. Current data is only going to give three results. Should just have zero count for all other weeks with no data. SELECT Count(l.lot_id) AS slab_count, WEEK ('2015-01-04') AS week_number FROM lot l LEFT JOIN block AS b ON l.block_id = b.block_id LEFT JOIN community AS c ON b.community_id = c.community_id WHERE l.dried_in_date IS NULL AND l.slab_date <= '2015-01-10' AND (c.contract_type_id = 1 OR c.contract_type_id= 3) OR l.slab_date <= '2015-01-10' AND l.dried_in_date >= '2015-01-04' AND (c.contract_type_id = 1 OR c.contract_type_id= 3) UNION /* WEEK 2 */ SELECT Count(l.lot_id) AS slab_count, WEEK ('2015-01-11') AS week_number FROM lot l LEFT JOIN block AS b ON l.block_id = b.block_id LEFT JOIN community AS c ON b.community_id = c.community_id WHERE l.dried_in_date IS NULL AND l.slab_date <= '2015-01-17' AND (c.contract_type_id = 1 OR c.contract_type_id= 3) OR l.slab_date <= '2015-01-17' AND l.dried_in_date >= '2015-01-11' AND (c.contract_type_id = 1 OR c.contract_type_id= 3) UNION /* WEEK 3 */ SELECT Count(l.lot_id) AS slab_count, WEEK ('2015-01-18') AS week_number FROM lot l LEFT JOIN block AS b ON l.block_id = b.block_id LEFT JOIN community AS c ON b.community_id = c.community_id WHERE l.dried_in_date IS NULL AND l.slab_date <= '2015-01-24' AND (c.contract_type_id = 1 OR c.contract_type_id= 3) OR l.slab_date <= '2015-01-24' AND l.dried_in_date >= '2015-01-18' data.sql.txt

Try this: if (!defined('ABSPATH')) {exit;}