Help - Search - Members - Calendar
Full Version: mySQL check for match in database (array)
HTMLHelp Forums > Programming > Server-side Scripting
sanoj96
Hello,

So i have had this script going for a while, not noticing anything since it have given me an output. But yesterday i noticed that some useres had was missing on the list.

So i am trying to get all useres that has u_role 498147873003798539. The problem is that useres that has the role in the second place in the array like this : 123451232132131, 498147873003798539, 123154512555433 is getting displayed, but when it is in the 3rd or more position it wont display.
IPB Image

This is my code:

CODE

$array = array('498147873003798539'); //u_role to find in the array ('12345678910, 10987654321')
$sql = "SELECT * FROM userlist WHERE u_roles IN (".implode(',', $array).")"; //sql
$result = mysqli_query($db_conn, $sql); //sql
$array = mysqli_fetch_assoc($result); //sql

$exploded = explode(",", $array['u_roles']);
$gamers = [];
foreach ($exploded as $row){
    $query = "SELECT * FROM userlist WHERE u_roles = $row";

    $res = mysqli_query($db_conn, $query);
    $arr = mysqli_fetch_assoc($res);

    $gamers[] = $arr["u_roles"];
    while($test = mysqli_fetch_assoc($res)){
            if($test["u_nickname"] == "undefined"){
                echo "<p>Username: " .$test["u_name"]. "</p>";
                
            }else{
            //echo "id: " .$test["u_userid"]. "<br>";
            //echo "Name: " .$test["u_name"]. "<br>";
            echo "<p>Nickname: " .$test["u_nickname"]. "</p>";
            //echo "Role: " . $test["u_roles"];
            }
    }
}


it is proberly a easy fix, but for some reason i cant find the problem, any help will be appriciated.
CharlesEF
I will admit I don't use IN very much but from what I understand it's just another way of using multiple OR statements, nothing to do with arrays. I would suggest this:
CODE
-----$array = array('498147873003798539');----- REMOVE THIS LINE
$sql = "SELECT * FROM userlist WHERE LOCATE('498147873003798539',u_roles)>0"; //sql
Does this work?

A better DB design would be to make the u_roles a separate table, tied to each user. Then a simple SELECT with a INNER JOIN would have returned only users that match the u_role you want.
sanoj96
Thanks, Got it working when i did change the $query = to that! Didint know that there was a locate function in sql. But guess we are learning something new everyday!


would have set the database up better if it was suppoes to be a website with login and all that. But as of now it is just going to list all useres with x role from a dsicord server on the website.
CharlesEF
Glad you got it working. As far as I'm concerned there is no reason to NOT design a good database. Your u_roles column is not an array, it is a string. See here for a list of string commands.
sanoj96
Yea that is true, this is just a quick script, that i have to make a few tasks i have automatic. There is no login or anything in this database, and all it is going to do is to display the users with different roles in the correct area smile.gif But yes, it would be best practic to create a good database design!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2020 Invision Power Services, Inc.