TapeGun007

Maybe I am going about this the wrong way, so any advice on how to do this BETTER would be greatly appreciated. So when a sales person logs in the code is simply: <?php if($_SESSION['SalesCRMA'] !== "Admin" && $_SESSION['SalesCRMA'] !== "Owner" && $_SESSION['SalesCRMA'] !== "Moderator" && $_SESSION['SalesCRMA'] !== "Sales"){ header('Location: http://www.mysite.com/logout.php'); } ?> I send them to the logout.php because it clears any cookies that have to do with the website. I have an ADMIN button at the top that only shows up if an actual admin logged in. On all the Admin pages I have similar code: <?php if($_SESSION['SalesCRMA'] !== "Admin" && $_SESSION['SalesCRMA'] !== "Owner" && $_SESSION['SalesCRMA'] !== "Moderator"){ header('Location: http://www.mysite.com/logout.php'); } ?> For some reason, when I click the ADMIN button (which just goes to admin.php), I get sent back to the login screen. However, the 2nd time I click on the ADMIN button, everything works fine for the entire day until the next morning when I have to log in twice again. What is causing this to occur? BTW, admin.php is in a sub folder if that makes any difference.

Thanks. After reading their documentation and modifying their code to work with my database, I now have it working with a minor bug.

I'm sure there has been several posts about this and I've done quite a bit of reading on the internet, but I've never been able to get an example to actually work. For now, all I want is a simple form where I can select a state from a drop down box, and the drop down box with all the "counties" will auto update. I have all the states and counties stored in mySQL already. I'm more than happy to read if you have a good link that would teach me how to accomplish this. Thanks

Yes, that works perfectly. I will go and study exactly what you did there. Thank you very much. Do you recommend any books to better learn mySQL?

This is mySQL 5.5.42 (If that helps) and I get the following error message: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RIGHT JOIN recruiters r ON r.county_id = c.id LEFT JOIN ' at line 10

Right, I wasn't sure so I tested that as well. It works better than anything else I've tried because it now lists Universities where no recruiter exists (before it didn't), but it still repeats the name of the college for every recruiter in that county. Output: county_name | county_id | population | recruiter | u_name

Sorry, I should've pointed to what line was getting the syntax error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RIGHT JOIN recruiters r ON r.county_id = c.id LEFT JOIN ' at line 10 I thought maybe it's because I have "recruiters r" twice? So I removed "recruiters", same error.

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.