0

Using PHP, PDO to query my games database, to retrieve a game that is available to join. If I use Fetch it will bring back the last row that meets the criteria. I want the first row, so I'm currently using FetchAll then looking at the first row retrieved. Is there a way to use Fetch and tell it to just get the first row. As using FetchAll seems like it is gathering a lot more data than I require.

I'm very new to all of this (animator by trade), but having got my head around old mysql I've now moved over to using PDO which seems great. Slowly building a working asynchronous game server.

Here's my code, which works but I wonder if it could be more efficient, if I did'nt use FetchAll.

       //Set variables to use when searching for random game in table
       $player2_id = 0; //if 0 then player 2 slot has not been filled.
       $whose_turn = 2;  //if 2 then its player twos go...so good to join.


        //IS THERE A RANDOM GAME TO JOIN 
        $current_game = $db->prepare("SELECT * FROM games_database WHERE player2_id=? AND whose_turn=?");
        $current_game->execute(array($player_2_id, $whose_turn));

        //Fetch All the results
        $random_game_to_use = $current_game->fetchAll(PDO::FETCH_ASSOC);

        //Get the first of all the results
        $firstAvailableGame = $random_game_to_use[0];
        $rand_game_name = $firstAvailableGame['game_name'];

I hope that makes sense. Any help or pearls of wisdom would be gratefully received. Also just want to say Stackoverflow has been an incredibly helpful website in my learning of coding PHP and PDO database queries. I've probably tried to learn to code about 4 times in my life but finally its starting to stick nicely. This is my first question... thanks Jon.

Parzival
  • 29
  • 8

2 Answers2

0

Since you have no ORDER BY clause in your query, the result set is unsorted by definition. It might have a sorting, but this is not guaranteed.

Since you obviously want the "first" and not the "last" row, you want to sort by something and therefore you should specify your desired sorting so that the database actually sorts your result set.

As soon as you have appended the ORDER BY something clause to your query, you can further append ASC or DESC to control the direction of the sorting and to push the liked row to the top (for example ORDER BY something DESC). (This makes "last row the first" or "first row the last".)

Then you can (and should) LIMIT your result set to one row, since you only want one row. So append LIMIT 1 to your query, to retrieve exactly the row you want.

You can now use fetch() to request the first (and only) row, which is the one you want.

Ulrich Thomas Gabor
  • 6,584
  • 4
  • 27
  • 41
  • Thanks will try using LIMIT 1 this evening... as well as the other idea of using ORDER... really appreciate you taking the time to help. – Parzival Mar 24 '14 at 14:57
-1

Selecting only one record with fetch()

/core/model.php

@select values from table

@access public

@param string $table The name of the table

@param string $fieldname

@param string $id

@return the specific row as an object on success or throw PDOException on failure

@warn : Select only unique values from a column

public function dbSelectOne($table, $fieldname=null, $id=null)
{
$this->conn();
$sql = "SELECT * FROM `$table` WHERE `$fieldname`=:id";
$stmt = $this->db->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->execute();
return $stmt->fetch(PDO::FETCH_OBJ);
}

Example:

$client = $this->pdo->dbSelectOne('clients', 'id_client', $id );
echo $client->name;

Notice: You will have to make a validation in case the record is not found.

The try{} catch{} are useless for empty object, because pdo is not throwing any error. 

if(empty($client)){ /* do something */ }

However, you can catch any error if the table or field names are not valid.

PM1625637
  • 102
  • 10