What PHP should i use? PDO

[exceedinglife]

Is PDO one of the best phps to use these days?

For doing prepared statements how is this for my example of code below. Anything that i should do differently?

Can you all look at this.
How do you think this is for my prepared statements. Someone said PDO PHP is the way PHP should be used now adays? Is the correct.
Here is my prepared statement. Am I doing everything correctly or what should i change?

Here is for 1 row.
And what about :  mysqli_real_escape_string($connection, $_POST["name"]); I thought doing this was safe. but prepared is used over this? I dont really understand.
 

$sql = "SELECT * FROM users WHERE id = :id";
  // config for SQL Prepare
    if($stmt = $pdoConnect->prepare($sql)) {

        $stmt->bindParam(":id", $param_id);

        $param_id = trim($_GET["id"]);

        if($stmt->execute()) {
            if($stmt->rowCount() == 1) {

                $row = $stmt->fetch(PDO::FETCH_ASSOC);

                $id = $row["id"];
                $name = $row["name"];
                $language = $row["language"];
                $userdate = $row["date"];

//And here is multiple records

                       $sql = "SELECT * FROM users";

                        if($result = $pdoConnect->query($sql)) {

                            if($result->rowCount() > 0) {

                                    while ($row = $result->fetch()) {

 

[mac_gyver]

using the php PDO extension, using prepared queries when supplying external/unknown data to the sql query statement, using implicate binding by supplying an array of the input data to the execute() method call, and using exceptions to handle database statement errors will result in the least amount of code and the simplest sql query syntax.

the issue with the ...escape_string() functions is, if the character set that php is using for them isn't the same as the character set of your database table(s), sql special characters in the data can still break the sql query syntax, which is how sql injection is accomplished. by using a true prepared query (PDO has a mode that emulates prepared queries, which should be turned off in most cases), you are guaranteed that nothing in the data will ever been treated as sql syntax. by using a prepared query, you are also simplifying the code since any ...escape_string() functions you may have can be removed.

as to the code you have posted above -

1) any trimming of input data would occur before you validate the data and you would never get to the point of preparing/executing the query if the input data is not valid.

2) you should set the default fetch mode to assoc when you make the database connection so that you don't have to specify it in every fetch statement.

3) the rowCount() method is not universally available for SELECT queries for all database types and should be avoided.

4) if you just fetch the data into an appropriately named php variable, using the fetch() method for a single row, or the fetchAll() method for multiple rows, you can test the variable to determine if any row(s) were matched and if you need to get a count of the number of multiple rows, you can use php's count() function.

5) copying the fetched data into discrete variables is a waste of typing time and memory. the original variable you fetched the data into is perfectly fine to use in the rest of the code.

6) using a ? place-holder is shorter/simpler/less typing.

7) you should list out the columns you are selecting. this helps to document what you are doing and causes only the data you want to be retrieved.

using these suggestions, your two examples simply become -

$sql = "SELECT id, name, language, date FROM users WHERE id = ?";
$stmt = $pdoConnect->prepare($sql);
$stmt->execute([$param_id]);
$user_row = $stmt->fetch();

// if $user_row is a boolean true, the query matched a row. if it is a boolean false, the query did not match a row.
// access the id, name, language, and date elements in $user_row.

$sql = "SELECT id, name, language, date FROM users";
$stmt = $pdoConnect->query($sql);
$user_rows = $stmt->fetchAll();

// if $user_rows is a boolean true, the query matched at least one row. if it is a boolean false, the query did not match any row(s).
// you can use a foreach(){} loop to loop over $user_rows to reference each row of data

 

Edited January 29, 2019 by mac_gyver

[exceedinglife]

Thank you! That cleared up alot. So you would suggest that i use 

$sql = "SELECT id, name, language, date FROM users WHERE id = ?";

instead of 

$sql = "SELECT id, name, language, date FROM users WHERE id = :id";

I will look at how to set my default PDO with  fetch(PDO::FETCH_ASSOC);

What should I use instead of rowCount()

Thanks again. One other thing.

I am having a problem with my header() I execute my SQL create and delete they both work. But when I try to transfer to a new page my page just continually keeps loading. Here is my code

/Full DELETE
  if(isset($_POST["id"]) && !empty($_POST["id"])) {
        require_once "php/config.php";
      // Prepare a DELETE statement
      $sql = "DELETE FROM users WHERE id=:id";

      if($stmt = $pdoConnect->prepare($sql)) {
        $stmt->bindParam(":id", $param_id);
        $param_id = trim($_POST["id"]);
        if($stmt->execute()) {
          header("location: index.php");
          exit();
        } else {
              echo "Something went wrong with DELETE.";
        }
      }
      // Close $stmt statement
      unset($stmt);
      // Close connections
      unset($pdoConnect);
    } else {
        // Check existence of 'id' parameter
        if(empty(trim($_GET["id"]))) {
          //URL doesn't contain parameter send ERROR
          header("location: error.php");
          exit();
        }
    }
// Create php code
            $sql = "INSERT INTO users(name, language, date) VALUES " .
                   "(:name, :language, :date)";

            if($stmt = $pdoConnect->prepare($sql))
            { // Bind variables to prepared statement as parameters.
                $stmt->bindParam(":name", $para_name);
                $stmt->bindParam(":language", $para_lang);
                $stmt->bindParam(":date", $para_date);
              // Set parameters
                $para_name = $name;
                $para_lang = $language;
                $para_date = $currentDate;
              // Attempt to execute prepared statement
                if($stmt->execute())
                { // Determine if Success or Error
                    header("Location:index.php");
                    exit();
                } else {
                    echo "Something went wrong with INSERT, please try again later.";
                }

[ginerjm]

Personally I have chosen to use the named params in my prepared statements.  It avoids future problems when adjustments may be made to your query statement.  And it's easier to read.  Also I choose to use the array method of passing the arguments to those parms instead of the tedious 'bind' process. 

in pseudo-code this is what it resembles:

$q = 'select a,b,c from tablename where a=:valuea and id=:id';

$stmt = $pdo->prepare($q);

$parms = array( ':valuea'=>$a,  ':id'=>$id)

$stmt->execute($parms);

 

 

[ginerjm]

PS - as for your topic heading....

You are not choosing a type of PHP when you select the PDO database interface.  You are choosing a 'piece' of PHP that connects you to your site's database.  It could be the MySqlI extension/interface in many sites or the PDO extension/interface which is recommended very often. (And there may be others.)  In any case you would still be using the same version of php that your host has your server setup for. 

You have chosen wisely though.

[gizmola]

In regards to integration of PDO with MySQL, you want to make sure you are using the mysqlnd library.