CODEWITHSUNDEEP
phpmysqlarrayssearchvarchar
mmsarquis
mmsarquis

Reputation: 190

php code to search multiple words in varchar

From my application, I send a list of users that I want to search the group they are subscribed:

$selectedUsers = ["John", "Carlos", "Anna", "Julia"]

I have in my database many different groups with many different users in each of them:

$football = ["**John**" ,"**Carlos**" ,"Daniel" ,"Rob" ,"Frank" ,"Bob"] 

$cooking = ["**John**" , "**Anna**" , "**Julia**" , "Claudia" , "Rob" , "Adriana"]

$startups = ["**John**", "**Carlos**", "**Anna**", "**Julia**", "Rob", "Adriana"]

The output I want to have is the sorted list of groups with the amount of the selectedUsers in it: 

$returnArray[0] = $startups //4 users inside group
$returnArray[1] = $cooking //3 users inside group
$returnArray[2] = $football //2 users inside group

Here is the code I have so far, but the loop I'm using is based on the group_id I've stored and I want to change that:

<?php
    //fetch groups with users

    $returnValue = array();
    $groupUsersNumber = 0;

    $selectedUsers = htmlentities($_REQUEST["selectedUsers"]);
    $lastGroupID = htmlentities($_REQUEST["lastGroupID"]); //remove

    if($lastGroupID == ""){
        $lastGroupID = getLastGroupID(); 
        $lastGroupID = $lastGroupID + 1;
    }

    if($selectedUsers == ""){
        //return all groups ordered by ID desc

        $group = getGroupWithID($lastGroupID); 

    } else{

        $usersArray = explode(', ', $selectedUsers);
        $foundGroup = false;
        while($foundGroup == false){

            $group = getGroupWithID($lastGroupID);

            $fetchedGroupUsers = explode(', ', $group["users"]);
            for($i = 0; $i < count($usersArray); $i++){ 
                if(in_array($usersArray[$i], $fetchedGroupUsers)){
                    $foundGroup = true;
                    break;
                } else{
                    $lastGroupID = $group["group_id"];
                }
            }
        }
    }

    for($i = 0; $i < count($usersArray); $i++){
        if(in_array($usersArray[$i], $fetchedGroupUsers)){
            $groupUsersNumber = $groupUsersNumber + 1;
        }
    }

    if(empty($group))
    {
        $returnValue["status"]="403";
        $returnValue["message"]="No more groups with that users.";
        echo json_encode($returnValue);
        return;
    } else{

        $returnValue=$group;
        $returnValue["groupUsersNumber"]=$groupUsersNumber;
    }

    echo json_encode($returnValue);
?>

Is there any other way to have a better/ more efficient way to search into my database? Appreciated!

Upvotes: 1

Views: 173

Answers (2)

Kickstart
Kickstart

Reputation: 21513

You appear to be mixing up php and mysql, and it would be better to redesign your database.

However as a basic idea you can do roughly what you want in MySQL. It is not nice, and not efficient but something like this:-

SELECT a.group_description ,
        GROUP_CONCAT(b.wanted_name)
FROM some_table a
LEFT OUTER JOIN
(
    SELECT "John" AS wanted_name UNION SELECT "Carlos" UNION SELECT "Anna" UNION SELECT "Julia"
) b
ON FIND_IN_SET(b.wanted_name, a.group_users)
GROUP BY a. group_description

Upvotes: 1

Marcel
Marcel

Reputation: 5119

It seems that your database is not normalized. A normalized database may be the more efficient way. Do not store users in a describing varchar. Instead establish a many to many relation.

Beside that the FilterIterator class of PHP is something for you. It is reusable and a bit more efficient at iterating over arrays.

Here 's a short example.

class NameFilterIterator extends FilterIterator {

    protected $filter = null;

    public function __construct(Iterator $iterator, $filter) {
        parent::__construct($iterator);
        $this->filter = $filter;
    }

    public function accept() {
        $current = $this->getInnerIterator()->current();

        if (strpos($current, $this->filter) !== false) {
            return true;
        }

        return false;
    }
}

// Usage
$aUsers = [ 'John', 'Carlos', 'Anna', 'Julia' ];
$oFootball = new ArrayIterator(["**John**" ,"**Carlos**" ,"Daniel" ,"Rob" ,"Frank" ,"Bob"]);

foreach ($aUsers as $sUser) {
    $oFilter = new NameFilterIterator($oFootball, $sUser);
    foreach ($oFilter as $sName) {
        var_dump($sName); // outputs: John, Carlos
    }
}

The internal memory usage of the FilterIterator object is way more efficient.

Upvotes: 1

Related Questions