TapeGun007

I took a stab at this, but the syntax is incorrect: SELECT c.name AS county_name , c.id AS county_id , c.population , group_concat(r.name separator '<br /> ') AS recruiter , group_concat(u.name separator '<br /> ') AS uname FROM counties c INNER JOIN states s ON c.state_id = s.id LEFT JOIN recruiters r ON r.county_id = c.id UNION RIGHT JOIN recruiters r ON r.county_id = c.id LEFT JOIN universities u ON r.county_id = u.county_id WHERE c.state_id = 5 GROUP BY c.name

Please read this first: http://forums.phpfreaks.com/topic/300283-join-tables-help/ I now want to add one more table with information: universities -- univ_id county_id name students state_id I read up fully on the differences in Inner Join vs Left Join so I understand what Barand did last time with the following: SELECT c.name AS county_name , c.id AS county_id , c.population , group_concat(r.name separator '<br /> ') AS recruiter FROM counties c INNER JOIN states s ON c.state_id = s.id LEFT JOIN recruiters r ON r.county_id = c.id WHERE c.state_id = $StateID GROUP BY c.name So I attempted first to try my own hand at this: SELECT c.name AS county_name , c.id AS county_id , c.population , group_concat(r.name separator '<br /> ') AS recruiter , group_concat(u.name separator '<br /> ') AS u_name FROM counties c INNER JOIN states s ON c.state_id = s.id LEFT JOIN recruiters r ON r.county_id = c.id LEFT JOIN universities u ON r.county_id = u.county_id WHERE c.state_id = $StateID GROUP BY c.name It works, but the university name appears twice. I ran the SQL directly into the database to see why and I got the following: Santa Clara // County Name 229 // County ID 1,894,605 // County Population Adecco<br /> Manpower // recruiter names Stanford<br /> Stanford // College name shows up twice, it should only be once So I added another recruiter as "test" and now "Stanford" appears 3 times. So for every recruiter there is, the university name duplicates itself. Theoretically, I presume the "recruiters" table would have to be a full outer join and then left join universities. I just read there is no full outer join and that you need to use a Union? Is this correct? I'm trying to learn, but just want to make sure I'm on the right track. I'm still testing various tries, but I'm not confident in my syntax.

Barand, Thank you again. I figured I would have to select from Counties instead of Recruiters and likely something would not be an Inner Join (in my limited knowledge). I had just started to read up on the different joins. I only had to modify the code above slightly so it worked with my PHP code already in place with SELECT c.name AS county_name , c.population , group_concat(r.name separator ', ') AS recruiter FROM counties c INNER JOIN states s ON c.state_id = s.id LEFT JOIN recruiters r ON r.county_id = c.id WHERE c.state_id = $StateID GROUP BY c.name But it works perfectly! Thank you so much! -Tape

I had some time off and other work that was a higher priority. The code that Barand gave me works, I had to slightly modify it to: SELECT c.name AS county_name, c.population, r.name AS recruiter FROM recruiters r INNER JOIN counties c ON r.county_id = c.id INNER JOIN states s ON c.state_id = s.id WHERE s.id = $StateID ORDER BY county_name I think I just had to change the state_id to s.id and it worked fine. At some point I should go back and rename the table fields so it makes more sense. In any case, I need every county to show regardless of whether or not it has a recruiter office there or not. Then list all the recruiters in one field as suggested above. Unfortunately the line "GROUP_CONCAT(r.name SEPARATOR ', ') as recruiters" caused nothing to return. Thanks!

Now that's the answer I was looking for... but didn't know how to ask. I do not use SELECT * except when just initially writing a query and then trim it down once I know exactly what fields I want to include. Bad habit from the looks of it. I didn't know you could temporarily rename them like that.... perfect! Thank you also Barand as you have helped me out of a jam many times.

Yeah, the issue with the database is that I didn't create it.... I'll have to go through and fix it, then try again with this code.

Didn't work, but I just realized this may be due to another issue: Some tables have the same names so using this: echo $row['name']."<br />"; May not work correctly.... How can you differentiate? I used the AND on another page and it works great. So, yes, recruiters are in a specific state and county only. I need the database to list the recruiters in each county and don't care about state level or nationwide.

I have 3 tables: States: id name capital Counties: id state_id name population Recruiters: id state_id county_id Name I have a drop down where you select the State, so the state name will be listed at the top. I want the information in the table to have the following: County - Population - Recruiter Name(s) I'm guessing that the SQL will go something like this, but I'm not super great at mySQL. $sql = "SELECT * FROM States s LEFT JOIN Counties c ON (id = state_id) LEFT JOIN Recruiter r ON (r.state_id = s.id AND r.county_id = c.id) WHERE state_id='$StateID' "; Any help would be appreciated.

I have looked at probably 50 different types of coding for this, but they all do not necessarily use a database. I have a field where the user selects a state. Once the state is selected I want the code to pull up the counties for that state. Again, I already have all the states in one table and the counties in another table. Each state has it's own ID that links to the counties that are in the that state. How can I do this with Javascript or Jquery?

Just curious, but lets say a sales person in my company enters in a clients address of say "123 Main St". But lets say that in my database there is already an entry that is "123 Main Street". Is there a way either in php or mySQL to check if there is something that is a close match? I'd like to be able to inform the sales person that this client was already entered into the database so they don't waste their time or create a duplicate entry. Or is there a better way to accomplish this? Thanks!

I literally just put that code in there and typed it up here without testing. The above code actually does what I wanted it to do. LMAO.

benanamen, It's quite simple (supposedly): I have a table called "Security" Security ---------- ID SalesID SecurityAnswer TimeRecord TempCode Attempts LoginSecurityID All I want to do now (regardless of what I posted previously) is check if the TimeRecord is less than 20 minutes. I need to also ensure that the TempCode matches, and the ID is correct. "SELECT * FROM LoginSecurity WHERE timestampdiff(minute, TimeRecord, CURRENT_TIMESTAMP()) < 20 AND SalesID = '$ID' AND TempCode ='$temppassword' I've tried CURRENT_TIMESTAMP, and I tried using Now()....it just doesn't seem to work and so I know it's a syntax thing. To test I'm running the following after the query: $rs=$con->query($sql); $rs->data_seek(0); $row = $rs->fetch_assoc(); if($row > 0){ echo "Record found"; }else{ echo "Record not found"; }

Benanamen, Yeah, my bad. That's because I have two different pages I'm working on at the same time. One will show a list of all (admin) and one will show just a single entry (user). For the users page I really need something like this (but it is incorrect): "SELECT * FROM LoginSecurity WHERE timestampdiff(minute, TimeRecord, Now()) < 20 AND SalesID = '$ID' AND TempCode ='$temppassword' ";

@benanamen, oh very nice. I'm not so good at SQL actually. Here was my original SQL: $sql = "SELECT * FROM LoginSecurity WHERE TempCode ='$temppassword' AND SalesID='$ID'"; I'm not sure how to implement your code with mine but I'll take a stab at it. I'm guessing: $sql = "SELECT * IF(timestampdiff(minute, TimeStamp, Now()) > 20, 'More than 20', 'Less Than 20') AS status FROM LoginSecurity WHERE TempCode ='$temppassword' AND SalesID='$ID'";

Ok, so then yes, it was a timestamp... of course, I now have made it a datetime field instead.

Hi Mel, I recently wrote a similar thing except I had a drop down with all the US States and it auto populated a drop down with all the counties in that state. I can tell you it's much easier and readable if you use multiple tables to accomplish this. And rather than use php, you'll really need some jquery or ajax. Here is a website that may answer this for you: http://www.php-dev-zone.com/2013/10/country-state-city-dropdown-using-ajax.html

@benanamen, Yes, I'm aware of that method. But I need the data for both the over 20 minutes and under 20 minutes, so that will not work. @hansford - doesn't work. It thinks the time has passed every time. The output for echo "$now - $past - $elapsed_minutes"; 1445456993 - 1445412904 - 734 @mac_gyver - I made a change today as I realized I don't need a timestamp field. I don't want the field to auto update. So I set it to datetime instead. This is in the mySQL db, but now you got me curious... how do you tell if it's UNIX or mySQL?

I seem to frequently beat my head against a wall when trying to compare anything to do with dates or times. This has me stumped. In my database I have a TimeStamp. I simply want to check if 20 minutes has passed. I have tried at least 20 different examples of code, none of which work. $Current = date("Y-m-d h:i:s"); $TimeStamp = $row['TimeStamp']; I presume I want to keep the date and time, but there is obviously something in the whole date and time functions that I'm not grasping. I know it has to be something very simple. If you have any links that explain comparisons like this, I would be more than happy to read.

Somehow I clicked submit on accident. I use a "code" because the code is unique to each sales person and is assigned by another database that I have no control over. So instead of using a SalesID, I use a SalesCode, ProspectCode, and TerritoryCode. So the SalesCode must match in the TerritoryCode. In some rare cases I may let a sales person keep a lead that is not in their territory. Here is how the table actually works:

Yeah, I realized when I first wrote that .... that it wasn't the ID that needed to match. I get confused sometimes because normally I would match an ID. But the Sales People are assigned a "ReferralCode" that is unique to them already. It really goes like this: Sales | Prospects | | Territories | -------------- ------------------ ----------------- SalesCode

Barand, I knew you had to be right, because so far you've ALWAYS been right. I started to debug... it wasn't working because it was ORDER BY p.ProspectBusinessName. Thank you so much!

Hi Barand, Just to clarify... in the table "Territories" there is either a Territory (County) AND State set, OR just a Zip code with no State or County set. I checked my "Prospects" table to ensure they all have a "ProspectLastContacted" date that is at least 2 - 4 weeks ago. I tried your code and it give me nothing in return.

I attempted this just to see if I could narrow it down. I realize I probably need to join based on State names and not counties. "SELECT * FROM Prospects p LEFT JOIN Territories t ON p.ProspectState = t.TerritoryState OR t.ProspectZip = t.TerritoryZip "; This yields nothing.

Here at least one of my attempts at this: SELECT * FROM Prospects p LEFT JOIN Territories t ON p.ProspectCounty = t.Territory WHERE (ProspectCounty = Territory AND ProspectState = TerritoryState) AND DATE(ProspectLastContacted) < DATE(NOW() - INTERVAL 14 DAY) ORDER BY p.BusinessName I won't use * once I get the right code, I realize that's not good. I just realized I forgot to put the OR in there.

Prospects --------- ProspectCode ProspectBusinessName ProspectLastContacted ProspectCounty ProspectZip ProspectState Territories ----------- TerritoryID TerritoryCode (Should always match ProspectCode above) Territory (should always be a county name) TerritoryState TerritoryZip I want to join these two tables under these circumstances: ( ProspectCounty = Territory County AND ProspectState = TerritoryState OR ProspectZip = TerritoryZip ) AND ProspectLastContacted was last contacted 2 weeks ago.