0

I have a database where I'm getting a random row from a table depending on a few variables. I'm trying to make it so it adds their username in this format (Admin, Jerry, Ben) once they view the advertisement (My project - clients view ads for Bitcoin). I want to make it so that once they view it , it adds their name in to a row called users_viewed_add (example is above). I need it to only get random rows based on if their username does not exist in the row users_viewed_add atoll so they can't view the ad more than once.

At the moment, I'm trying to do:

$query_second = mysqli_query($con, "SELECT * FROM business_advertisments WHERE ($users_credits >= amount_per_click) AND (users_viewed_add) NOT LIKE '$username' AND users_name != '$username' ORDER BY RAND() LIMIT 1");

As you can see, the AND (users_viewed_add) NOT LIKE '$username' is not working for me as it's reading the text as a whole word. The problem is that I'm using comma's to seperate the usernames of the clients who have viewed the ad. Is there any work around for this ? I know the method to check a block of text but it wouldn't work in a SQL statement I'm pretty sure.

Ben Za
  • 21
  • 1
  • 9

3 Answers3

0

You can do:

AND CONCAT(',', users_viewed_add, ',') NOT LIKE '%,$username,%'

But you probably want to make a separated many to many table, and use user id instead of user name.

HTMHell
  • 5,761
  • 5
  • 37
  • 79
  • this doesn't seem to be working. It's displaying rows where my username 'Admin' is in the users_viewed_add – Ben Za Dec 10 '17 at 10:44
  • Are you sure `$username` = Admin? Maybe you have spaces between commas? – HTMHell Dec 10 '17 at 10:46
  • oh yes I do , hold on. I will remove the spaces and try again. – Ben Za Dec 10 '17 at 10:48
  • yep, it's not working. https://gyazo.com/8aec8e9f0da8317a0ad8d157726b3fd5 https://gyazo.com/2ab7dfd242bde19be423e94b5c7064ae The bottom row is the one in the first image. – Ben Za Dec 10 '17 at 10:52
  • @BenZa It is working, take a look here: http://sqlfiddle.com/#!9/7fcfd9/3. Play the id in the SELECT query so you can see that it's working. – HTMHell Dec 10 '17 at 11:06
0

If you want to use multiple values in your LIKE comparison, you can use:

AND users_viewed_add NOT LIKE ALL ('$username')

This will check that none of the $username values exists in the users_viewed_add field. You may need to format your variable properly so the SQL works.

ravioli
  • 3,749
  • 3
  • 14
  • 28
0

I found the answer from this post: How do I add multiple "NOT LIKE '%?%' in the WHERE clause of sqlite3 in python code?

The correct SQL syntax to check a word doesn't exist in a row is:

AND users_viewed_add NOT REGEXP '[$username]'
Ben Za
  • 21
  • 1
  • 9