PHP SQL store results

[Itutorial]

Should create an array with the array created from the database info then return it.

$serverArrays = array();
        $sql = "SELECT * FROM servers WHERE id >= $startID AND id <= $endID";
       
        if ($conn->query($sql)) {
          do {
            // Store first result set
            if ($result = $conn->store_result()) {
              while ($row = $result->fetch_row()) {
                    $tmpArray = array();
                    $tmpArray[0] = $row['id'];
                    $tmpArray[1] = $row['name'];
                    $tmpArray[2] = $row['ip'];
                    $tmpArray[3] = $row['protocol'];
                    $tmpArray[4] = $row['port'];
                    $tmpArray[5] = $row['players'];
                    $tmpArray[6] = $row['maxplayers'];
                    $tmpArray[7] = $row['exprate'];
                    $tmpArray[8] = $row['website'];
                   
                    array_push($serverArrays, $tmpArray);
              }
             $result->free_result();
            }
             //Prepare next result set
          } while ($conn->next_result());
        }

Right now nothing happens so the $serverarrys isn't being populated clearly. Cant figure out why.

 

[Sarah Wesker]

I think it's fetch_assoc instead of fetch_row
since fetch_row returns an array with index 1,2,3,4,5, etc...
instead fetch_assoc returns an array with associative index, like ["id"], ["name"], etc...

 

[Itutorial]

I think it's fetch_assoc instead of fetch_row
since fetch_row returns an array with index 1,2,3,4,5, etc...
instead fetch_assoc returns an array with associative index, like ["id"], ["name"], etc...

I did try that, unfortunately no result still.

Post automatically merged: Feb 22, 2022

So this works but I feel like I should be storing the query instead of just executing it? maybe I am wrong.

if ($result = $conn->query($sql)) {
          do {
            // Store first result set
              while ($row = $result->fetch_assoc()) {
                    $tmpArray = array();
                    $tmpArray[0] = $row['id'];
                    $tmpArray[1] = $row['name'];
                    $tmpArray[2] = $row['ip'];
                    $tmpArray[3] = $row['protocol'];
                    $tmpArray[4] = $row['port'];
                    $tmpArray[5] = $row['players'];
                    $tmpArray[6] = $row['maxplayers'];
                    $tmpArray[7] = $row['exprate'];
                    $tmpArray[8] = $row['website'];
                    
                    array_push($serverArrays, $tmpArray);
              }
             $result->free_result();
             //Prepare next result set
          } while ($conn->next_result());
        }

 

[Boy67]

If you want a more secure PDO method, try something like this:

<?php

$host = '127.0.0.1';
$user = '';
$pass = '';
$db = '';
$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";

try {
    $pdo = new PDO($dsn, $user, $pass);
    
} catch (PDOException $e) {
    echo $e->getMessage();
}

$sth = $pdo->prepare("SELECT * FROM servers WHERE id >= :startID AND id <= :endID");
$sth->bindParam(':startID', $startID, PDO::PARAM_INT);
$sth->bindParam(':endID', $endID, PDO::PARAM_INT);
$sth->execute();
$results = $sth->fetchAll();

$serverArrays = array();
if($results){
    foreach($results as $row){
        $tmpArray = array();
        $tmpArray[0] = $row['id'];
        $tmpArray[1] = $row['name'];
        $tmpArray[2] = $row['ip'];
        $tmpArray[3] = $row['protocol'];
        $tmpArray[4] = $row['port'];
        $tmpArray[5] = $row['players'];
        $tmpArray[6] = $row['maxplayers'];
        $tmpArray[7] = $row['exprate'];
        $tmpArray[8] = $row['website'];
        
        $serverArrays[] = $tmpArray;
    }
}

?>

 

[Itutorial]

Next problem..

function createServer($name, $ip, $protocol, $port, $exprate, $website) {
        if (!$name or !$ip or !$protocol or !$port or !$exprate or !$website) {
            return false;
        }
        
        $nProtocol = intval($protocol);
        $nPort = intval($port);
        $nExprate = intval($exprate);

        $conn = connectDB();
        $sql = "INSERT INTO servers (name, ip, protocol, port, players, maxplayers, exprate, website) 
                            VALUES ('$name', '$ip', $nProtocol, $nPort, 0, 0, $nExprate, '$website')";
        
        if (!$conn->query($sql)) {
            return false;
        }
        $conn->close();
        return true;
    }

If you want a more secure PDO method, try something like this:

Not going to invest in a new system at this point. It's not really a big deal for security for what im doing. Not yet anyway.

 

[Boy67]

When making a project like this, it's better to implement early. Changing to PDO is hardly investing in a new system at this point.

I'm guessing reading the servers from the database is the only db I/O you've added so far, so why not make it safe now then having to change everything later on?

 

[Itutorial]

If PDO is the best why are others used? From what I read PDO is only better for flexibility for databases.

 

[Boy67]

Prepared statements, multi driver support (if you ever wanted to switch away from mySQL), binding params prevents SQL injection.