PHP - Advanced MYSQL While

Question

I have next situation.

1. From PHP form i get only COMPANY ID
2. I need array all usernames with that companny ID
3. I need array and export to table all logs with that usernames

My problem was, when i try next:

$sql2 = "SELECT vpnuserreg FROM users WHERE company='$company'";
$result2 = $database->query($sql2);
    while ($row2 = $database->fetch_array($result2)){
        $username = $row2['vpnuserreg'];
        $sql = "SELECT * FROM logs WHERE username='$username' ORDER BY radacctid DESC";
        $result = $database->query($sql);
        $row=$database->fetch_array($result);
    }

Problem was: In $sql2 query i have only 3 users and my log in $sql query will repat only 3 times. But i have more then 3 logs for thet user. How can i make sql query that my log export ALL row in table with only 3 usernames?

Answer 1

Instead of running two queries, do a simple JOIN instead. That means you can do everything in a single query.

$sql = "SELECT l.* 
        FROM users u 
        JOIN logs l ON u.vpnuserreg=l.username
        WHERE u.company='$company'
        ORDER BY l.radacctid DESC";
$result = $database->query($sql);

while ($row = $database->fetch_array($result)){
    /* Do whatever here */
}

---

It should be noted that this piece of code is vulnerable to SQL injection, as you use variables directly in the query. I recommend that you start using prepared statements with placeholders instead, which would look like this

$sql = "SELECT l.* 
        FROM users u 
        JOIN logs l ON u.vpnuserreg=l.username
        WHERE u.company=? 
        ORDER BY l.radacctid DESC";
$stmt = $database->prepare($sql);
$stmt->bind_param("s", $company);
$stmt->execute();
$result = $stmt->get_result();
$stmt->close();

while ($row = $database->fetch_array($result)){
    /* Do whatever here */
}

NOTE The above code is using get_result() and requires the mysqlnd driver to be installed. Otherwise, you have to use mysqli_stmt::bind_result() and mysqli_stmt::fetch() instead of mysqli_stmt::get_result()!

• mysqli::prepare()
• mysqli_stmt::bind_param()
• How can I prevent SQL injection in PHP?

Answer 2

You need another while loop for getting '$row's. If you're looking for one query and fetch all logs for this users, you need IF statement to check 'userid' and react to new userid.

Try this query:

 "SELECT * FROM logs WHERE username in " .
 " (SELECT vpnuserreg FROM users WHERE company='$company' ) " .
 " ORDER BY username asc , radacctid DESC"