OOP for PHP/MySQL

[montyonthebonty]

Hi

I have the following code:

  protected function getSample($numLocations) {

    $statement = $this->connect()->prepare('
    SELECT * from tblRouteQuiz
    ORDER BY RAND()
    LIMIT 4');

    if (!$statement->execute()) {
      header('location: ../sqlerror.php');
      exit();
    } else {

      $results = $statement->fetchAll(PDO::FETCH_ASSOC);
      $statement = null;
      return($results);

    }

What do I need to do to use $numLocations as the Limit?

Thanks

Chris

[ginerjm]

Probably validate that it is numeric and then put it into your query?

[Barand]

You can...

$numLocations = 4;

$stmt = $pdo->prepare ("SELECT city_name
                        FROM city
                        ORDER BY city_name
                        LIMIT ?
                       ");
$stmt->execute([$numLocations]);
$data = $stmt->fetchAll();

 

[mac_gyver]

in your previous thread on this forum, you successfully built, prepared, and executed a prepared query with a ? place-holder in it. did you not learn anything by doing that? you are doing the same thing here, just with a different sql query statement.

some points about what you are showing us (applies to the previous thread's code too) -

don't make, then destroy a database connection inside of every method call. instead, your main code should make ONE connection for the whole application and use dependency injection to supply that connection to any class that needs it. once you do this, you would have a class property for the connection, that you would use when needed.

both the prepare() and execute() calls can fail with an error. however, for a SELECT query, these would be due to a programming mistake or a problem with the database server. both of these are fatal problems for a SELECT query. there's no good reason to have ANY code to handle an error for a SELECT query.

you should also use exceptions for ALL database statement errors. the PDO connection already always uses an exception for an error, you might as well use exceptions for query, prepare, and execute errors too. and in fact, in php8 the default error mode has been changed to use exceptions, so your current conditional logic won't do anything anymore and should be removed for a SELECT query.

the only database statement errors that the visitor to a site can do anything about are when inserting/updating duplicate or out of range user submitted data values. this is the only case where you should have database statement error handling, which when using exceptions for errors is in the form of try/catch logic. the catch logic would test the sql error number and if it is for anything that the user can recover from, setup a message telling the user exactly what was wrong with the data that they submitted, so that they can potentially submit a different value that will succeed.

when you make the connection, you should -

1. set the character set to match your database tables.
2. set the error mode to exceptions,
3. set emulated prepared queries to false.
4. set the default fetch mode to assoc so that you don' t need to specify it in each fetch statement. Keep It Simple (KISS.)

lastly, return is not a function. the () do nothing and are unnecessary typing.

once you do these things, the code simply becomes -

	protected function getSample($numLocations)
	{
		$stmt = $this->pdo->prepare('
		SELECT * from tblRouteQuiz
		ORDER BY RAND()
		LIMIT ?');
		$stmt->execute([$numLocations]);
		return $stmt->fetchAll();
	}

 

Edited July 9, 2022 by mac_gyver