PHP Classes
elePHPant
Icontem

File: test.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Gonzalo Chumillas  >  Ses Query  >  test.php  >  Download  
File: test.php
Role: Example script
Content type: text/plain
Description: Several examples
Class: Ses Query
Manipulate records retrieved with select queries
Author: By
Last change:
Date: 1 year ago
Size: 4,768 bytes
 

Contents

Class file image Download
<?php
/**
 * This script illustrates the most common uses of the DBQuery class:
 *
 * 1. Selecting a single record.
 * 2. Selecting multiple records.
 * 3. Accessing columns.
 * 4. Saving records.
 * 5. Refreshing records.
 * 6. Deleting records.
 */
header("Content-Type: text/plain; charset=UTF-8");

require_once
"classes/database/database.php";
require_once
"classes/database/db-query.php";

/*
 * Change the username and password to connecto to your database.
 */
$username = "<possibly root>";
$password = "<your password here>";
$database = "library_test";

try {
   
$db = new Database($database, $username, $password);
} catch (
DBException $e) {
    echo
"Error: " . $e->getMessage() . "\n";
    echo
"Make sure your username and password are correct and exists the $database database .\n";
    echo
"You may need to run the 'create-database.php' script to create the database.";
    die();
}

/*
 * Selects a single row.
 * Note that you do not need to create an instance of DBQuery. The 'query' function does this for you.
 */
$row = $db->query("
select
    b.id,
    b.title,
    a.name
from books as b
inner join authors as a on a.id = b.author_id
and b.id = 4"
);
echo
"Selecting a single row...\n";
echo
"Title : " . $row->title . "\n";
echo
"Author: " . $row->name . "\n\n\n";

// Selects multiple rows.
// Note that you can use the same function 'query' to get a single or multiple rows.
$rows = $db->query("
select
    b.id,
    g.name as genre,
    ifnull(a.name, '<Unknown>') as author,
    b.title
from books_by_genre as bg
inner join genres as g on g.id = bg.genre_id
inner join books as b on b.id = bg.book_id
left join authors as a on a.id = b.author_id
order by g.name, b.title"
);

echo
"Selecting multiple rows...\n";
echo
str_pad("Book Id", 10) . str_pad("Genre", 10) . str_pad("Author", 20) . "Title\n";
echo
str_repeat("-", 46) . "\n";
foreach (
$rows as $row) {
    echo
str_pad($row->id, 10) . str_pad($row->genre, 10) . str_pad($row->author, 20) . $row->title . "\n";
}
echo
"[ Number of rows: " . count($rows) . " ]\n\n\n";

/*
 * You can access the columns by three ways:
 *
 * 1. Through the column name. For example:
 * $row->column
 *
 * 2. Through the array access. For example:
 * $row['column'] or
 * $row['table.column']
 *
 * 3. Through the index. For example:
 * $row[3] // Index from 0 onwards.
 */
$row = $db->query("
select
    b.id,
    b.title,
    group_concat(g.name) as genres,
    a.id,
    a.name
from books as b
left join books_by_genre as bg on bg.book_id = b.id
left join genres as g on g.id = bg.genre_id
left join authors as a on a.id = b.author_id
where b.id = 8
group by b.id
order by b.title"
);

echo
"Accessing columns...\n";
// accessing columns by array
echo "Book id : " . $row['b.id'] . "\n";
echo
"Book title : " . $row['title'] . "\n";
// accessing columns by name
echo "Genres : " . $row->genres . "\n";
// accessing columns by index
echo "Author id : " . $row[3] . "\n";
echo
"Author name: " . $row[4] . "\n\n\n";

/*
 * Saving records.
 * Note that primary keys are present in the query.
 * This is because the class needs to know what records must be updated.
 *
 * The 'save' method not only updates records, but also inserts new records when needed.
 * In the example below, the selected book doesn't have author, so the system creates a new author record.
 *
 * If the fields 'updated_on' and 'created_on' are present in the table, they are updated automatically.
 */
 
// creates the record if it does not exist
$row = $db->query("select count(*) from books where id = 11");
if (!
$row[0]) {
   
$db->query("insert into books(id, title, created_on, updated_on) values(11, %1, now(), now())", array("Code Complete"));
}

// gets the record and saves the changes
$row = $db->query("
select
    b.id as book_id,
    b.title,
    b.created_on,
    b.updated_on,
    a.id as author_id,
    a.name as author
from books as b
left join authors as a on a.id = b.author_id
where b.id = 11"
);

echo
"Saving records ...\n";
$row->setDebugMode(TRUE); // This line lets us to know what happens behind the scene :)
$row->title = "Code Complete 2";
$row->author = "Steve McConnell";
$row->save();
echo
"done\n\n\n";

/*
 * Refreshing records.
 * Refreshes the previous row.
 */
echo "Refreshing records ...\n";
$row->setDebugMode(FALSE);
$row->refresh();
echo
"Book id : " . $row->book_id . "\n";
echo
"Book title: " . $row->title . "\n";
echo
"Created on: " . $row->created_on . "\n";
echo
"Updated on: " . $row->updated_on . "\n";
echo
"Author : " . $row->author . "\n\n\n";

/*
 * Deleting records.
 * Deletes the previous book.
 */
echo "Deleting records ...\n";
$row->setDebugMode(TRUE); // let me see what is happening
$row->delete(TRUE);
echo
"done\n";

$db->close();