The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> mySQL check for match in database (array)
sanoj96
post Jul 27 2020, 12:56 PM
Post #1


Advanced Member
****

Group: Members
Posts: 114
Joined: 18-September 12
Member No.: 17,803



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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Jul 27 2020, 09:37 PM
Post #2


Programming Fanatic
********

Group: Members
Posts: 1,787
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



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.

This post has been edited by CharlesEF: Jul 27 2020, 09:56 PM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
sanoj96
post Jul 28 2020, 10:49 AM
Post #3


Advanced Member
****

Group: Members
Posts: 114
Joined: 18-September 12
Member No.: 17,803



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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Jul 28 2020, 07:16 PM
Post #4


Programming Fanatic
********

Group: Members
Posts: 1,787
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
sanoj96
post Jul 31 2020, 04:12 PM
Post #5


Advanced Member
****

Group: Members
Posts: 114
Joined: 18-September 12
Member No.: 17,803



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!
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
2 User(s) are reading this topic (2 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 30th October 2020 - 12:16 PM