Php-mysql Course

When you perform a SELECT SQL query with PDO prepare() - execute() statements, the values of the columns in the result set are string type. Even if the columns in MySQL table contain numeric type values: integer (INT), or float (FLOAT /DECIMAL /DOUBLE), the values in the result set returned by PDO are string.
- The following example shows the data type and values of a SELECT query executed with PDO prepare() and execute().
<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'root';
$passdb = 'pass';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Define the SQL statement that will be applied in prepare()
  $sql = "SELECT `id`, `name` FROM `users` WHERE `name`= :name LIMIT 1";
  $sqlprep = $conn->prepare($sql);        // Prepares and stores the SQL statement in $sqlprep

  $name = array('name'=>'admin');             // the value for ":name"
  // If the prepared SQL is succesfully executed, store the result set into a variable
  if($sqlprep->execute($name)) {
    $row = $sqlprep->fetchAll(PDO::FETCH_ASSOC);
  }
}
catch(PDOException $e) {
  $row = $e->getMessage();
}

var_dump($row);          // debug /show the result
?>
Debugged with var_dump(), shows this result:
array(1) { [0]=> array(2) { ["id"]=> string(1) "5" ["name"]=> string(5) "admin" } }
- As you can notice, the "id" value is returned as a string ("5"), but in the MySQL table, the "id" column is INT().

This conversion (numeric to string) in the result set can cause problems if we want to perform some PHP instructions according to exact value and data type of the "id" (when the equality is checked with three equal signs "===").
Example:
if($row[0]['id'] === 5) echo 'ID correct';
else echo 'The ID - '.$row[0]['id'].' not equal to 5';
- Because the value of the if($row[0]['id'] is "5" (not numeric 5), the code above will output:
The ID - 5 not equal to 5

If the equality is checked using two equal signs (==), it result: ID correct , because the "==" checks only the value, not data type.
But sometimes we must check data using the "===", especially when checks 0 against empty or null value.
So, the correct way is to have the numbers stored in numeric data type variables.

• Here's how you can have the numbers in the PDO result set returned as numeric type.
The following function (executeSQL()) receives a PDO object with the connection to MySQL database ($conn), and the SQL command ($sql). Stores each row into an array. Uses a foreach() loop, and the is_numeric() function to check the value of each column, if it's a number (integer, or float) adds 0, this will convert the string value into an integer, or float, without to modify the value.
This function returns the result set with the correct data type.
<?php
// execute SQL command and return the result set ( https://coursesweb.net )
function executeSQL($conn, $sql) {
  // prepare and execute the $sql query
  try {
    $reout = array();
    $sqlprep = $conn->prepare($sql);

    if($sqlprep->execute()) {
      // if fetch() returns at least one row (not false), adds the rows in $reout for return
      if(($row = $sqlprep->fetch(PDO::FETCH_ASSOC)) !== false){
        do {
          // check each column if it has numeric value, to cenvert it from "string"
          foreach($row AS $k=>$v) {
            if(is_numeric($v)) $row[$k] = $v + 0;
          }
          $reout[] = $row;
        }
        while($row = $sqlprep->fetch(PDO::FETCH_ASSOC));
      }
    } else {
      $eror = $conn->errorInfo();
      $reout[] = 'Error: '. $eror[2];
    }
  }
  catch(PDOException $e) {
    $reout[] = $e->getMessage();
  }

  return $reout;
}

// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'root';
$passdb = 'pass';

// Connect and create the PDO object
try {
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8
}
catch(PDOException $e) {
  $row = $e->getMessage();
}
// define the Select query, and calls the executeSQL() to get the selected rows
$sql = "SELECT `id`, `name` FROM `users` WHERE `name`='admin' LIMIT 1";
$rows = executeSQL($conn, $sql);

var_dump($rows);              // debug /show the result
?>
Result:
array(1) { [0]=> array(2) { ["id"]=> int(5) ["name"]=> string(5) "admin" } }
- As you can notice, this time the "id" is stored as integer (5).

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which type of <input> creates a date input control, such as a pop-up calendar?
type="text" type="date" type="button"
<input type="date" name="set_date" value="2012-10-15" />
Which CSS property adds shadow effects to the text of an element?
font-style color text-shadow
h2 {
  text-shadow: 2px 3px 3px #a0a1fe;
}
Click on the function that adds new elements to the end of an array.
pop() shift() push()
var pags = ["lessons", "courses"];
pags.push("download", "tutorials");
alert(pags[2]);            // download
Which function sorts an array by key, in ascending order, maintaining key to data correlations?
asort() ksort() sort()
$lang =[10=>"PHP", 20=>"JavaScript", "site"=>"coursesweb.net");
ksort($lang);
var_export($lang);     // array ("site"=>"coursesweb.net", 10=>"PHP", 20=>"JavaScript")

S H A R E

Sharing Twitter

Last accessed pages

  1. Select the Content of HTML Element (4515)
  2. jqPlot Charts (6281)
  3. Keep data in form fields after submitting the form (12405)
  4. Output or Force Download MP3 with PHP (5821)
  5. Create simple Website with PHP (44123)

Popular pages this month

  1. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (180)
  2. The Mastery of Love (19)
  3. PHP-MySQL free course, online tutorials PHP MySQL code (18)
  4. CSS cursor property - Custom Cursors (16)
  5. Read Excel file data in PHP - PhpExcelReader (13)