All Employment Journal Notes Programming & Coding

NOTES: 2021-04-26 | Notes on LinkedIn Learning PHP Videos

These notes are continued from this post.

How to Retrieve Data from a MySQL database with PHP (with mysqli)

Best practice: Always free result and connection when done.

db_connect_guide.php

// Credentials
$dbhost = 'localhost';
$dbuser = 'webuser';
$dbpass = 'secret';
$ dbname = 'globe_bank';

// 1. Create DB Connection
$conn = mysqli_connect(
   $dbhost, $dbuser, $dbpass, $dbname
);

// 2. Perform Query
$query = "SELECT * FROM subjects";
$result_set = mysqli_query($conn, $query);

// 3. User returned data
while ($subj = mysqli_fetch_assoc($result_set); {
   echo $subject["menu_name"] . "<br/>";
}

// 4. Release returned data
mysqli_free_result($result_set);

// 5. Close connection
mysqli_close($conn);
get_declared_classes()
class_exists()
get_class()
is_a()
get_class_vars($string)
get_object_vars($object) //current ?
property_exists($mixed, $string)
header('Content-Type: text/csv');
header('Content-Disposition: attachment,
   filename=rates.csv');
$file->fpassthru();
exit;

// #2
->SetCSVControl("\t");

SplTempFileObject()
// Handles memory
// Clears up resources automatically
require_once
   ('db_credentials.php');

function db_conn() {
   $conn = mysqli_connect(
      DB_SERVER, DB_USER,
      DB_PASS, DB_NAME);

   return $conn;
}

function db_disconn($conn) {
   if(isset($conn)) {
      mysqli_close($conn);
   }
}
$result = find_all_subjects();

$count = mysqli_num_rows($result);

// The following are TWO OPTIONS for displaying the query results

// FOR LOOP
for ($i=0; i<$count; $i++) {
   $subject = mysqli_fetch_assoc($result);

   echo $subject['menu_name'];
}

// WHILE LOOP
While ($subj = mysqli_fetch_assoc($result)) {
   echo $subject['menu_name'];
}
// Convert CSV to multidimensional array

<?php
$csvFile = new SplFileObject('common/data/cars.csv');
$csvFile->setFlags(SplFileObject::READ_CSV);
foreach ($csvFile as $line) {
   $cars[] = $line;
}

echo '<pre>';
print_r($cars);
echo '</pre>';
->OpenFile('\t')
->setFlags() // SKIP_EMPTY , READ_AHEAD
->seek(3)
->current()
->next()
->eof() // End of file
->fwrite
mysql_fetch_row()
// returns standard array

mysql_fetch_assoc() // best
// - associative array
// - keys are columnar
// - slightly slower, but worth it (?)

mysql_fetch_array()
// - does boon (?)

PHP: 3 APIs

  1. mysql (gone in PHP 7)
  2. mysqli (most common)
  3. PDO (object-oriented; works with any rdbms)

Prepared Statements (good security)

#GOTCHA: On Windows: GlobIterator requires a direct/absolute path (no relative paths)

$files = new GlobIterator(__DIR__, '/path/*.jpg');

foreach ($files as $file) {
   echo $file->getFilename() . '<br>';
}
RegexIterator($files, '/\');
mysqli_connect_errno()
// last errror number

mysqli_connect_error()
// string description of last error

Date Published: 04/26/21