-1

I am working on a project where I need to maintain user specific content.

One user can view one content only one time in 24 hours. After 24 hours it will be made available to the same user again.

So when a user successfully views a content I store the username of the user in the database in a comma separated value.

For example: when the user user1 successfully views a content his username will stored in user_statusas user1 along with other users likes user1,user2,user3,.......,etc.

Now what I want is to check the content if the username user1 exists in the database or not.

If it exists then content will not be made available to him for next 24 hours.

But for some reasons I cannot check a particular user from the group of comma separated usernames using if...else.

I can do it with if...else and explode but that does not satisfies my need. I want to know that can I check for one username in the WHERE clause of SELECT statement?

For ex. something like SELECT * FROM user_data WHERE user_status != '".$something."'.

Is it possible by using explode first then checking it in WHERE clause or just by some other method? If yes, then how? Please help me php experts.

Is it possible something like this?

$uname = $_SESSION['username'];//username of the logged in user

$ths = "SELECT * FROM user_data";
        $its = mysql_query($ths) or die(mysql_error());
        $uhs = mysql_fetch_array($its);

                $user_status = explode(',', $uhs['user_status']);// first explode the comma separated usernames

        $ths = "SELECT * FROM user_data WHERE user_status != '".$uname."'";// then check if the logged in username does not exists in the database
        $its = mysql_query($ths) or die(mysql_error());

Can the exploded list of usernames be checked in the WHERE clause? Is it possible? Or is there any method to do this? If yes then how?

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Laptop Medico
  • 22
  • 2
  • 10
  • 4
    [Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Jay Blanchard Nov 11 '15 at 13:50
  • 3
    If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Nov 11 '15 at 13:50
  • 1
    You could probably use `LIKE` but it would make a lot more sense to normalize your database. – Steve Nov 11 '15 at 13:53
  • 1
    Please show the table structure and a data sample. – Kostas Mitsarakis Nov 11 '15 at 13:56
  • Is this question significantly different from your other question at http://stackoverflow.com/questions/33629455/php-implode-explode-comma-separated-values-for-use-in-if-else-statement ? – Isaac Bennetch Nov 12 '15 at 05:54
  • yes.. its completely different... its both contains question on implode explode but the query is completely different.. – Laptop Medico Nov 12 '15 at 11:36

2 Answers2

6

I think you are looking at it all wrong. That is a lot of maintenance for a very simple task. You could just have another table, where you keep user id & data id pairs along side with a timestamp.

For example, for user data 1 and user 1 you will save:

uid | did | ts
--------------------
  1 |   1 | TIMESTAMP

To check if a user watched an item, just select from that table using the user id and data id. If no row is found, or the the current timestamp minus the row timestamp is more than 24 hours, allow the user to view the data, otherwise - don't allow it.

Ron Dadon
  • 2,666
  • 1
  • 13
  • 27
0

You may use LIKE to search some string for existence of another string:

SELECT * FROM `user_data` WHERE `user_status` LIKE '%user1%';

See: https://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html

feeela
  • 29,399
  • 7
  • 59
  • 71
  • you did not get my question.. I m not searching for a particular user as there can be 100s of users and I need to see if the current logged in username exists or not.. and yes... I cannot use the LIKE as I want to get the data if the USERNAME DOES NOT EXISTS. Is there something like NOT LIKE..? – Laptop Medico Nov 11 '15 at 14:09
  • @LaptopMedico erh, yes: `NOT LIKE` – see the documenation link above. But `NOT LIKE '%user1%` is false if there is a `user11` or `user101`. You really should normalize your database to get rid of such pain… – feeela Nov 11 '15 at 14:10
  • you are right.. the LIKE and NOT LIKE statements will return the result if even a single item matches with the one in the list. I can't use LIKE or NOT LIKE then.. any other solution? – Laptop Medico Nov 11 '15 at 14:25