1

I was using mysql to check if the user was logged in or not, but I received an email from my hosting company saying that I had exceeded the mysql queries limit and the cost to upgrade the mysql queries limit to 150000 per hour was $240/year.

Now, I need an alternative way to check if the user's logged in or not, like writing the user's timestamp in a file, but I don't know the best way to do it... And I don't even know which tags I put on this post...

So, could you please help me with this problem?

Sergio Toledo Piza
  • 793
  • 1
  • 6
  • 27

3 Answers3

5

Use $_SESSION. It's 1000 times more efficient and designed for just this purpose. Read about it here

Paul Dessert
  • 6,363
  • 8
  • 47
  • 74
  • 1
    Yeah, writing your own code to do this is a bit of overkill in my opinion. When the user log ins, set his username in the `$_SESSION` superglobal, and every page just start the session and check to see if username is set. If not, redirect to log in page. – thatidiotguy Oct 26 '12 at 17:10
  • When he uses more than 150k queries per hour, I whould suggest he would not be satisfied with $_SESSION – Ron Oct 26 '12 at 17:11
  • It depends on what you do with the session. A lot of IO, maybe heavy IO if the session is used to store more data than user-id. Than you should have a look how php does garbage collection on sessions. – Ron Oct 26 '12 at 17:16
  • @Ron - True, but in this case I think it's just a matter of logged in being true or false. – Paul Dessert Oct 26 '12 at 17:17
  • oooh Daniel's answer remembered me that I can't use sessions, because I don't want to make only the user know if he is logged in locally, but I need other users to know if the user is logged in, so I think sessions wouldn't work... :// – Sergio Toledo Piza Oct 26 '12 at 17:20
  • @relentless: Depending on whats the unique visitor / click ratio. If you have 150k session to store per hour, I would guess he run in trouble soon. – Ron Oct 26 '12 at 17:22
  • @Sergio Toledo Piza: This is an issue, you can handle a different way. Think about storing a "last visit timestamp" in the users-dataset. But the basic problem remains. – Ron Oct 26 '12 at 17:24
1

Upgrade (or from cost perspective downgrade) to a root-server and/or use better mechanisms to store sessions. ie memcache, couchdb or mongodb.

In every case, you have to handle semaphores by hand, if you leave PHP's session handler.

Ron
  • 1,336
  • 12
  • 20
  • 1
    I'd like to have my own host, but my computer's IP change every time, and I can't host anything on my PC... – Sergio Toledo Piza Oct 26 '12 at 17:15
  • Here (in germany) I get a full root server for 49€/m. Thats ~65$/m – Ron Oct 26 '12 at 17:17
  • 1
    but I'm only 14 and I can't afford paying 65$/m – Sergio Toledo Piza Oct 26 '12 at 17:22
  • Wtf are you doing there if you get 150k impressions per hour? :) – Ron Oct 26 '12 at 17:25
  • I only update the user's loginstamp in mysql (`mysql_query("UPDATE users SET loginstamp='".time()."' WHERE id='$user_id'");`) every 30 seconds... – Sergio Toledo Piza Oct 26 '12 at 17:29
  • 2 Problems: SQL-Injection-affective and mysql_query (which is deprecated and not maintained anymore) – Ron Oct 26 '12 at 17:31
  • well, mysql_query still work with me, so I don't think I really need to change do mysqli_query and why is it affective to sql injections? – Sergio Toledo Piza Oct 26 '12 at 17:35
  • Sql-Queries like yours highly depend on discipline and are easly vulnerable to human carelessness. You can use prepared statements with PDO or other technologies. They will enable you to write much more secure (and more performant) code. http://stackoverflow.com/questions/732561/why-is-using-a-mysql-prepared-statement-more-secure-than-using-the-common-escape - http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php – Ron Oct 26 '12 at 21:39
0

It is rather odd that your host has a mysql query limit, but back to your question;

I will take a guess that you are not just trying to see if a user is logged in locally, but rather, having another account see that the user is logged in, which would require a database call, not a $_SESSION (which is maintained on a per-user basis).

If, however, we make the assumption that you want to do this outside of the database, you can generate the end HTML result when a user logs in or out. Keep a table in the database with the status of the users, but whenever a user logs in or out, make ONE query to that table, and generate, say, your HTML list of logged in users. Save the output to a file, and when you need to render a page, include that file instead of making a database call.

It's not completely removing the MySQL database, but it is reasonably reliable, and should significantly reduce your number of database queries.

Daniel M.
  • 48
  • 2