PHP check if string is in table

Question

I've been trying to sort this issue out for a while but been unable to figure out if I have to do this through SQL side or the PHP side.

My code I have so far:

<?php
    $servername="";
    $username = "";
    $password = "";
    $dbname = "alphacre_kingsland";     


    $conn = mysqli_connect($servername, $username, $password, $dbname);
        if (!$conn) {
                die("Connection failed: " . mysqli_connect_error());
        }

        $sql = "SELECT * FROM `bm_player_bans` ORDER BY id";
        $result = mysqli_query($conn, $sql);

        if (mysqli_num_rows($result)>0){
            while($row = mysqli_fetch_assoc($result)){
                $playerId = bin2hex($row['player_id']);
                $storedname= getPlayerName($playerId);
                $sql = "INSERT INTO bm_onlinedata (uuid, playername) VALUES ('".$playerId."', '".$storedname."')";

                if ($conn->query($sql) === TRUE) {
                    echo "New record created successfully";
                    } else {
                        echo "Error: " . $sql . "<br>" . $conn->error;
                    }
                }
        }else{
            echo "0 results";
        }

                mysqli_close($conn);

        function getPlayerName($uuid){
            $json_response = file_get_contents("https://sessionserver.mojang.com/session/minecraft/profile/".$uuid);
            $data = json_decode($json_response);
            return $data->name;
        }

?>

So this stores "UUID" and playername into a separate table. But I want to check if the UUID is already in there before it adds a new one. Basically updating it. This is because playernames can change. And I don't want multiple rows of the same data. But that's ONLY if it is there of course.

Background information: I'm fetching the username from a user through Mojang's API, which only allows a single lookup of that UUID every minute. So I want to get the data every 5 minutes or so using a scheduled task running this script and store it in a table.

you need to add a WHERE clause to SELECT in order for num_rows to work. — Funk Forty Niner
– Funk Forty Niner, Commented Feb 12, 2017 at 20:02

ScaisEdge · Accepted Answer · 2017-02-12 20:58:53Z

1

You can easily check with select

$sql = "SELECT * FROM `bm_player_bans` ORDER BY id";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result)>0){
    while($row = mysqli_fetch_assoc($result)){
        $playerId = bin2hex($row['player_id']);
        $storedname= getPlayerName($playerId);
        $sql =  "SELECT * FROM `bm_onlinedata` where uuid = '" . $playerId ."';";
        $result = mysqli_query($link, $sql);
        $num_rows = mysqli_num_rows($result);
        if  ($num_rows> 0) { 
            $sql = "UPDATE bm_onlinedata 
                    set playername = '". $storedname."'
                    where uuid = '" .$playerId ."';";   )";
        } else {
         $sql = "INSERT INTO bm_onlinedata (uuid, playername) VALUES ('".$playerId."', '".$storedname."')";

            if ($conn->query($sql) === TRUE) {
                echo "New record created successfully";
                } else {
                    echo "Error: " . $sql . "<br>" . $conn->error;
                }
        }
    }
}else{
    echo "0 results";
}

edited Feb 12, 2017 at 20:58

answered Feb 12, 2017 at 20:14

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DarkEyeDragon Over a year ago

There seems to be something wrong with your usage of " and ' somewhere. I'll see if i can sort it out.

ScaisEdge Over a year ago

@DarkEyeDragon . i don't use and in the query .. because seems don't need

DarkEyeDragon Over a year ago

i ment the ' not the and xD Anyway. Still doesn't seems to work. it keeps adding a row instead of updating it.

ScaisEdge Over a year ago

Answer updated adding the check on the numbers of rows returned by query .. hope is useful

Collectives™ on Stack Overflow

PHP check if string is in table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related