CODEWITHSUNDEEP
phpmysql
user3579312
user3579312

Reputation:

Display all MySQL entries that a string doesn't contain a word

I am trying to create a mission system using MySQL and PHP. At the moment I can mark tasks as completed and display who completed them and when. What I am trying to do is show all missions that don't have the persons username in the string of completed people in $row['completed']. However the query returns all the missions that the user was marked to do even if they have already completed it.

The code:

<?php
$account = "LaughingQuoll"
// Create connection
$conn = new mysqli($host, $mysql_user, $mysql_pass, $db);
// Check connection
if ($conn->connect_error) {
die("Connection failed: ". $conn->connect_error);
} 
$sql = "SELECT * FROM  `tasks` WHERE `completed` NOT LIKE '$account' AND `members` LIKE '$account'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {

// output data of each row
while($row = $result->fetch_assoc()) {
echo $row['completed';
echo $row['taskname'];
echo $row['members'];
}
}
?>

The structure of the database: This is what is in it now. It is saying that the mission is set for LaughingQuoll and Bobjane and that LaughingQuoll and Bobjane have completed it.

|  taskname    |       details    |completion| startdate| members               | completed             | id|
| Create Class | Create the class | 2015-1-1 | 2014-1-1 | LaughingQuoll Bobjane | LaughingQuoll Bobjane | 1 |

What I want to do is search for a word (username) that is inside of the string in the database and show all entries that do not contain the word (username). How would I do this?

I also want to show all the missions the user has been marked with even if two people have been marked.

|  taskname    |       details    |completion| startdate| members               | completed | id|
| Create Class | Create the class | 2015-1-1 | 2014-1-1 | LaughingQuoll Bobjane |           | 1 |

I want to show all the tasks in the above table that contains 'LaughingQuoll' in the entry no matter where in the entry it is and if the entry is "LaughingQuoll Bobjane".

Any help would be greatly appreciated. Thanks in advance.

NOTE: The query echoes back fine. The username is in it properly.

Upvotes: 1

Views: 1477

Answers (1)

ob_start
ob_start

Reputation: 276

Change 

$sql = "SELECT * FROM  `tasks` WHERE `completed` 
NOT LIKE '$account' AND `members` LIKE '$account'";

too

$sql = "SELECT * FROM  `tasks` WHERE `completed` 
NOT LIKE '%$account%' AND `members` LIKE '%$account%'";

That should work. Maybe you have do add some like

(`members` LIKE '$account%' 
  OR`members` LIKE '%$account' 
  OR `members` LIKE '%$account%')

instead of

`members` LIKE '%$account%'

only, that for 'completed' too.

Anyway you have to use % when using LIKE in MYSQL

Hope helps.

Upvotes: 1

Related Questions