0

I have table students containing data like given below :

id       name       activity
1        susan      1,2
2        denny      1,2,3
3        ken        3

I want to choose all the data like name and id from this table having activity equal to 2. I have done a sql query using like but it will not work.

$sqll = mysqli_query($con,"SELECT id,studentname FROM students WHERE activity LIKE '%$activity'");

If another row contains data like id : 4 name:ben activity : 22 it will show this row also. I want the rows containing activity 2 only.

Can anyone suggest a solution for this ?

ADyson
  • 57,178
  • 14
  • 51
  • 63
nnnnnn
  • 123
  • 2
  • 2
  • 9
  • $sqll = mysqli_query($con,"SELECT id,studentname FROM students WHERE activity LIKE '%$activity'"); – nnnnnn Mar 02 '18 at 09:53
  • Is there any other method without using LIKE – nnnnnn Mar 02 '18 at 09:54
  • if another row containing data like id : 4 name:ben activity : 22. It will show also this row.I want the rows containing activity 2 only – nnnnnn Mar 02 '18 at 09:55
  • 3
    This data is denormalised, which is the reason you're struggling. If you had properly normalised data where the activity lists are in a sub-table with a foreign key back to the students table, this would not even be an issue. Storing multiple values in a single field is an anti-pattern. Learn about entity-relationship design and database normalisation to understand how to properly structure your data. Then your queries will be much simpler as well. This is a classic X-Y problem - you're trying to work round the symptom rather than address the real cause. – ADyson Mar 02 '18 at 09:55
  • You can use `activity = $activity` but it must be an exact match – Syntax Error Mar 02 '18 at 09:55
  • 3
    Don't store data this way ([why?](https://stackoverflow.com/q/3653462/335858)). – Sergey Kalinichenko Mar 02 '18 at 09:56
  • Don't use variables directly in SQL query strings. http://bobby-tables.com/ – GordonM Mar 02 '18 at 09:58
  • @nnnnnn take a look at [this](https://stackoverflow.com/a/49019193/8469069) – ishegg Mar 02 '18 at 10:03
  • Any solution to this anyone ? – nnnnnn Mar 02 '18 at 10:10
  • "Any solution" ...yes don't do it like this, like everyone is saying. It's the old joke...a man is lost and asks a farmer "how do I get to X?" and the farmer replies "well, I wouldn't start from here". Mark's answer will work but doesn't fix the underlying issue. There are still other queries you can't do using this data structure without a lot of mess - e.g. you can't properly count how many students are associated with each activity type in the database, just for one example. And the SQL injection is still an issue until you use parameterised queries. – ADyson Mar 02 '18 at 11:05
  • 1
    Whoever stored the data in this form did you a disservice. In the long run, you will do better by restructuring the data into two tables, as others have suggested. I know this is a real pain. That is why we keep urging people to learn database design before building databases for other people. – Walter Mitty Mar 02 '18 at 11:47

2 Answers2

0

I agree with the comments so far about normalising the data but to answer your question with the table structure as it is you can do this:

$sqll = mysqli_query($con,"SELECT id,studentname FROM students WHERE CONCAT(',',activity,',') LIKE '%,$activity,%'");

Note, you concatenate a comma either side of the field data and the specific activity you are looking for. Also note, I added a % on the right of the like as well so that "denny" will be caught

Mark
  • 1,006
  • 1
  • 6
  • 6
  • IF I give $activity it will cause sql injection. How to prevent this ? – nnnnnn Mar 02 '18 at 10:31
  • 1
    If activity is coming from a GET or POST request then use php filter_input function and validate as integer then check is not false, otherwise just check is_numeric before running and if not a number throw error or some such response – Mark Mar 02 '18 at 10:53
0
SELECT  id, studentname
    FROM  students
    WHERE  FIND_IN_SET("$activity", activity);
Rick James
  • 135,179
  • 13
  • 127
  • 222