SQL | Get all results from query not just the first-CodePudding

Hello i am doing an request to an SQL Database (in php) witch looks like this...

SELECT firstname FROM users WHERE job = '$jobL'

Lets assume in the Database are three users with the $jobL namend: Mike, Steve & Danny.

Expected Output: array("mike", "steve", "danny")

Given Output: array("mike")

So mike is the first result in the users table. SQL only gets me the first result, i want to get all results matching my query, not only the first one.

Thanks

EDIT: Its an php function...

function GetJobMembers($jobL) {     //Function to get all Members of an Job in an Array
    global $db;
 
    $AllWithJobSQL = $db->query("SELECT firstname FROM users WHERE job = '$jobL'");  
    $AllUsersWithJob = $AllWithJobSQL->fetch_assoc();


    return $AllUsersWithJob;
}

i tested it with $jobL = groove //GTA RP Server stuff

my db manual serach:

CodePudding user response：

Ok i fixxed it... was related to how php works with sql.

You just have to set fetch_all(MYSQLI_ASSOC);

Like this:

function GetJobMembers($jobL) {     //Function to get all Members of an Job in an Array
    global $db;
 
    $AllWithJobSQL = $db->query("SELECT firstname FROM users WHERE job = '$jobL'");  
    $AllUsersWithJob = $AllWithJobSQL->fetch_all(MYSQLI_ASSOC);

    return $AllUsersWithJob;
}

CodePudding user response：

fetch() is opened as a pointer ready to step through the data one by one (usually in a while loop).

while($row= $AllWithJobSQL->fetch_assoc()) {
   echo $row["job"].PHP_EOL;;
}

In your case, you should use fetchAll(). It fetches all the data at once, without opening any pointer, storing it in an array. It is recommended when you do not expect too many results that could cause memory problems when you want to store thousands or millions of rows from a SELECT into an array at once.

$AllUsersWithJob = $AllWithJobSQL-> fetch_all(MYSQLI_ASSOC);
return $AllUsersWithJob;

In this case $AllUsersWithJob is an associative array with all the query data. If you want to read the rows in it, you can implement a loop that loops the array:

$resultado= GetJobMembers("doctor");
foreach ($resultado as $row){
    echo $row["job"].PHP_EOL;
}

CodePudding user response：

Use fetch_all() to fetch all rows as a 2-dimensional array. Then you can use array_column() to extract the firstname index from each row.

You should also use a prepared statement to prevent SQL injection.

function GetJobMembers($jobL) {     //Function to get all Members of an Job in an Array
    global $db;
    $stmt = $db->prepare("SELECT firstname FROM users WHERE job = ?");  
    $stmt->bind_param("s", $jobL);
    $stmt->execute();
    $result = $stmt->get_result();
    $AllUsersWithJob = $result->fetch_all(MYSQLI_ASSOC);

    return array_column($AllUsersWithJob, 'firstname');
}

CodePudding user response：

I think you are using wrong fetch method.Fetch, returning matched first row.

Your code should be like that:

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);

For more information you can check here

If you are using mysqli your code should be like that.

$AllWithJobSQL = $mysqli->query("SELECT firstname FROM users WHERE job = '$jobL'");

$AllUsersWithJob = $AllWithJobSQL ->fetch_all(MYSQLI_ASSOC);

return $AllUsersWithJob;