How do you count the number of times any given $variable appears in a column in a MySql table

Question

How do you count the number of times any given $variable appears in a MySQL Table

The database name is STUDENTS The table is called PROJECTS The column is called ESSAYS The variable for each essay is what im trying to count. It is called &essay and inserts into PROJECTS

Answer 1

Are you trying to count the number of rows that contain that variable? Or how many times in total it appears in the table (counting multiple occurences in one row as well)?

If you're just trying to count how many rows include that variable, then you could do something like:

SELECT * FROM students.projects WHERE essays LIKE '%$variable%';

As your query.

Obviously this would have to be called from your PHP and not the command-line, since it's using the variable you declared and set in the PHP. And if this variable is going to be from some user input, you definitely need to sanitize it using mysql_real_escape_string() before using it.

Answer 2

Using what BraedenP has said, you can get the rows which contain relevant varibales. then for each row, get the value in essays field and use a regular expression function to get number of occurrences for the value in your $variable.

Answer 3

I think you may be looking for:

SELECT `ESSAYS`, COUNT(*) AS `COUNT` FROM `PROJECTS` GROUP BY `ESSAYS`

or

SELECT `ESSAYS`, COUNT(*) AS `COUNT` FROM `PROJECTS` WHERE `ESSAYS` LIKE '%$safe_variable%' GROUP BY `ESSAYS`

or

SELECT COUNT(*) AS `COUNT` FROM `PROJECTS` WHERE `ESSAYS` LIKE '%$safe_variable%'

Don't forget to escape $variable:

$safe_variable = mysql_real_escape_string($variable);