can't find multiple values in SQL

Question

I have a DB full of players and I am trying to create a page to register them into a specific tournament. The tournament director will search for players by username (which is only "firstname.lastname").

The problem I'm experiencing is that when I run loop to echo each $players[$x] it only gives the ID for the first matching DB record, and repeats the number once for each match. Rather than returning ID 7, 11, 26 it will return 7, 7, 7. Please can someone explain why?

I have written the following code in a .php file:

session_start();
if (isset($_POST['newsearch']) === true && empty($_POST['newsearch'] === false)){
require 'dbconnect.php';
$term = $_POST['newsearch'];
$terms = "%" . $term . "%";

$query = ("SELECT PlayerID FROM players WHERE Username LIKE '$terms'");
$run_query = mysqli_query($dbcon, $query);
$search = mysqli_fetch_assoc($run_query);
$players = array ();

    do{
    //Get data from players table
    $PlayerID = $search['PlayerID'];
array_push ($players, $PlayerID);        
    }
        while ($dbsearch = mysqli_fetch_assoc($run_query));}

score 2 · Accepted Answer · answered Aug 25 '15 at 07:52

2

You have more that one fetch for the same query, replace the code after $run_query = mysqli_query($dbcon, $query); with this code:

 $players = array ();
 while ($dbsearch = mysqli_fetch_assoc($run_query))
     {
         array_push($players, $dbsearch['PlayerID']);;
     }

answered Aug 25 '15 at 07:52

Luthando Ntsekwa

4,192
6
23
52

@Tunna182 if this has solved your question please mark it as accepted by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself – Luthando Ntsekwa Aug 25 '15 at 07:59
Thanks mate. I tried to accept straight away but it said I had to wait a few minutes. You've helped heaps (it's accepted now). – Tunna182 Aug 25 '15 at 08:04

score 1 · Answer 2 · answered Aug 25 '15 at 07:51

Your while loop is wrong

$query = ("SELECT PlayerID FROM players WHERE Username LIKE '$terms'");
$run_query = mysqli_query($dbcon, $query);
$search = mysqli_fetch_assoc($run_query);
$players = array ();

do{
    //Get data from players table
    $PlayerID = $search['PlayerID'];
    array_push ($players, $PlayerID);        
    }
    while ($search = mysqli_fetch_assoc($run_query));
}

Put $search instead of $dbsearch

$search = mysqli_fetch_assoc($run_query))

Note : your query is vulnerable to SQLI

can't find multiple values in SQL

2 Answers2