Jashun Jashun - 11 months ago 48
MySQL Question

Need to count a row in a column based on the logged in username

Alright so I'm not sure if anyone is going to get this but I need to count from a column in my database based on a row equal to the username logged in

$this->setParams('username', $users->getInfo($_SESSION['user']['id'], 'username'));

I'm not sure if I've been doing the code all wrong but I just need it to count how many times a username is repeated in that table.

<?php echo number_format(mysql_num_rows(mysql_query("SELECT from login_logs WHERE username = '".$_SESSION['user']['id'] ."username"))); ?>

It coming up as 0 even though I see 4 values in the database.

Answer Source

First off, like everyone who sees mysql_query says, use PDO. However, for the purposes of learning, mysql_query is just fine but totally unacceptable on a production server. Mainly because mysql_query is very insecure. It is prone to stuff like sql injections that can really mess up the server or even give access to hackers. Once you get comfortable using PHP and MySQL, read up on sql injection and securing your code.

Back to the real question, you are not seeing anything because your select query is all out of whack. Let's break it down. I only indented your query so we can talk about it in detail.

from login_logs 
WHERE username = '".$_SESSION['user']['id'] ."username"

There are couple issues with this query.

  1. You are not defining what you want to select. SELECT * will return every column. However, if you are just counting and don't care about retrieving the data, you can use an indexed column like the id field SELECT id. This improves performance. Perhaps something you want to read later on.
  2. Your where condition is pretty much rendered useless by appending "username" to it. Let's say that I am the logged in user. My username is khuderm so $_SESSION['user']['id'] would return khuderm. So if you substitute the variable with my username, it become WHERE username = 'khudermusername when it should just be looking for khuderm. So removing that will fix the problem partially. Also, you are missing a closing quote surrounding the username value. So instead of appending the literal word "username" to the actual logged in user's username, you should be appending "'" to close out that quote in the beginning WHERE username = '"

If you can follow these steps, you should be able to fix your problem. An easy way to debug MySQL queries in PHP is to echo it to get the final query with variables and such filled in. So anywhere in your code, echoing "SELECT from login_logs WHERE username = '".$_SESSION['user']['id'] ."username" would show you exactly what is sent to the db for querying. Once you have that, run the result in phpmyadmin or workbench or whatever you are using to look at the db and it should tell you what is wrong with the query.

Don't forget to read up on sql injections, PDO, and prepared statements. Hope this was insightful and happy coding!