Back

PDO statements best practices

Large images INSERT

$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("insert into images (id, contenttype, imagedata) values (?, ?, ?)");
$id = get_new_id(); // some function to allocate a new ID

// assume that we are running as part of a file upload form
// You can find more information in the PHP documentation

$fp = fopen($_FILES['file']['tmp_name'], 'rb');

$stmt->bindParam(1, $id);
$stmt->bindParam(2, $_FILES['file']['type']);
$stmt->bindParam(3, $fp, PDO::PARAM_LOB);

$stmt->beginTransaction();
$stmt->execute();
$stmt->commit();

Read More →

Running queries. PDO::query()

$stmt = $pdo->query('SELECT name FROM users');
while ($row = $stmt->fetch())
{
    echo $row['name'] . "\n";
}

$sql = "SELECT * FROM hostplan where id = ?";
		$q = $pdo->prepare($sql);
		$q->execute(array($id));
		$data = $q->fetch(PDO::FETCH_ASSOC);

		$planid = $data['planid'];
        $durations  = $data['durations'];
        $notes   = $data['notes'];
        $plan   = $data['plan'];
		$costs = $data['costs'];
			Database::disconnect();
-then- on each indexed column

Read More →

Prepared statements.

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ? AND status=?');
$stmt->execute([$email, $status]);
$user = $stmt->fetch();
// or
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND status=:status');
$stmt->execute(['email' => $email, 'status' => $status]);
$user = $stmt->fetch();

Read More →

SELECT

$stmt = $pdo->query('SELECT name FROM users');
foreach ($stmt as $row)
{
    echo $row['name'] . "\n";
}
----------------------------------------------
--------- while loop for multiple rows -------
  $statement = $pdo->query('SELECT * FROM esell_fields ORDER BY `id` DESC LIMIT 50');
    while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
print($row['id']);

Read More →

DELETE

$stmt = $pdo->prepare("DELETE FROM goods WHERE category = ?");
$stmt->execute([$cat]);
$deleted = $stmt->fetchColumn();

Read More →

UPDATE

$sql = "UPDATE users SET name = ? WHERE id = ?";
$pdo->prepare($sql)->execute([$name, $id]);

Read More →

fetchColumn()

returns value of the singe field

// Getting the name based on id
$stmt = $pdo->prepare("SELECT name FROM table WHERE id=?");
$stmt->execute([$id]);
$name = $stmt->fetchColumn();

// getting number of rows in the table utilizing method chaining
$count = $pdo->query("SELECT count(*) FROM table")->fetchColumn();
===also===
$statement = $connection->prepare('Select * FROM users WHERE name = :name');
$results = $connection->execute([
    ':name' => $name
]);

Read More →

LIKE search

$search = "%$search%";
$stmt  = $pdo->prepare("SELECT * FROM table WHERE name LIKE ?");
$stmt->execute([$search]);
$data = $stmt->fetchAll();

Read More →

IN clause

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($arr);
$data = $stm->fetchAll();

Read More →

Sanitize filters

FILTER_SANITIZE_NUMBER_INT "number_int" 	Remove all characters except digits, plus and minus sign.
FILTER_SANITIZE_EMAIL 	"email" 	  	Remove all characters except letters, digits and !#$%&'*+-=?^_`{|}~@.[].
FILTER_SANITIZE_STRING 	"string" Strip tags, optionally strip or encode special characters.
FILTER_SANITIZE_URL 	"url" 	  	Remove all characters except letters, digits and $-_.+!*'(),{}|\\^~[]`<>#%";/?:@&=.
FILTER_SANITIZE_SPECIAL_CHARS "special_chars" HTML-escape '"<>& and characters with ASCII value less than 32, optionally strip or encode other special characters.
FILTER_SANITIZE_FULL_SPECIAL_CHARS 	"full_special_chars" 	Equivalent to calling htmlspecialchars() with ENT_QUOTES set. Encoding quotes can be disabled by setting
 

Read More →

Insert and get last id

$query = "INSERT INTO song (artist, track, link) VALUES (:artist, :track, :link)";
$stmt = $pdo->prepare($query);

$params = array(
    "artist" => $artist,
    "track" => $track,
    "link" => $link,
);

$data = $stmt->execute($params);

$insert_id = $pdo->lastInsertId();

Read More →

Select from two tables

SELECT c . * , p . *
FROM tbl_categories c, tbl_products p
WHERE c.cat_id = p.cat_id

Read More →

Export MySQL table to csv

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=file.csv");
header("Pragma: no-cache");
header("Expires: 0");

$dbo = new PDO('mysql:host=localhost;dbname=db1', $username, $password);
$sql = "SELECT * FROM table1";
$qry = $dbo->prepare($sql);

// Execute the statement
$qry->execute();
var_dump($qry->fetch(PDO::FETCH_ASSOC));
$data = fopen('/tmp/db_user_export_".time().".csv', 'w');
while ($row = $qry->fetch(PDO::FETCH_ASSOC))
{
    echo "Success";
    // Export every row to a file
    fputcsv($data, $row);
}
--------------------------------------------------------
$sql = "SELECT * FROM patient";
$stmt = $conn->prepare($sql);

$stmt->execute();

$data = fopen('backup.csv', 'w');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    // Export every row to a file
    fputcsv($data, $row);
}
fclose($data);                 // <- close file

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=reports.csv");
header("Pragma: no-cache");
header("Expires: 0");
readfile('backup.csv');        // <- new line
----------------------------------------------------------

Read More →

Export MySQL table to JSON format

$row=$pdo->prepare('select * from `'.$table_name.'`');
$row->execute();//execute the query
$json_data=array();//create the array
foreach($row as $rec)//foreach loop
{
$json_array['id']=$rec['id'];
    $json_array['bill']=$rec['bill'];
    $json_array['amnt']=$rec['amnt'];
    $json_array['dtdue']=$rec['dtdue'];
        $json_array['dtpaid']=$rec['dtpaid'];
    $json_array['notes']=$rec['notes'];
    $json_array['more']=$rec['more'];
//here pushing the values in to an array
    array_push($json_data,$json_array);

}

//built in PHP function to encode the data in to JSON format
echo json_encode($json_data);

Read More →

Fetch

PDO::FETCH_ASSOC: returns an array indexed by column name. That is, in our previous example, you need to use $row['id'] to get the id.
PDO::FETCH_NUM: returns an array indexed by column number. In our previous example, we’d get the id column by using $row[0] because it’s the first column.
PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set. For example, $row->id would hold the value of the id column.

// This works: ($a = '12345';)
 echo "qwe{$a}rty"; // qwe12345rty, using braces
 echo "qwe" . $a . "rty"; // qwe12345rty, concatenation used

Read More →