It's (just?) Data
Zvi Boshernitzan
Robocommerce, LLC
Simple Data Structures are Powerful.
- The only thing that's important is applications.
- The main thing applications (should) care about is data.
- Simple is good. PHP arrays are simple.
- SQL is declarative, and lets you think in terms of your data.
- PHP is procedural, but has really nice array data structures.
[any material that should appear in print but not on the slide]
Pet Peeve: Sequences vs Dictionaries
- sequence: array(1,3,4);
- dictionary: array('name' => 'Zvi')
- They are both arrays, but SEQUENCES have ONLY numeric keys starting with zero.
- array_values($arr) of any array always returns a sequence.
- This is all you need for 90% of applications!
(A) Creating and Altering Data
- db::insert($table_name, $dictionary)
- db::update($table_name, $dictionary, $where_spec)
- db::delete($table_name, $where_spec)
- db::update_or_insert($table_name, $dictionary, $where_spec)
The 'where' spec.
- 'where specs', are just associative arrays that can be used to create sql.
- db::where(array("author_id" => 1, "last_name" => "O'Reilly"))
author_id = 1 and last_name = 'O\'Reilly'
- db::where(3)
id = 3
(A) Creating and Altering Data
create table authors (
id integer primary key auto_increment,
last_name varchar(200) not null unique
);
create table books (
id integer primary key auto_increment,
author_id integer not null references authors,
title varchar(200) not null unique
);
So lets insert some data..
$author_id = db::insert('authors',array('last_name' => 'Rosenthal'));
db::update('authors', array('last_name' => 'Boshernitzan'), $id);
$books = array('title1','title2');
foreach ($books as $title) {
db::insert('books', compact('author_id','title'));
}
- $author_id now holds '1'.
- there are 2 books and one author in the database.
(B) Queries: What do you want?
- no columns (just a condition)
- one column (a list of values, or a single value)
- more than one column (a list of objects, "sequences", or "dictionaries")
Queries with no columns
- parameters: $table_name, $where_spec
- db::count
- db::exists
Queries with no columns: Examples
- Are there any books with author_id = 1?
- select count(1) from books where author_id = 1
- ...
$author_id = 1;
$n_books = db::exists('books',compact('author_id'));
...
- n_books now holds 1 (which is greater than zero)
Queries with one column
- parameters: a) $sql OR b) $table_name, $column_name, $where_spec
- db::string
- db::strings
- db::sum
Queries with one column: examples
- What is the last name of the author with id = 1?
- $name = db::string('select last_name from authors where id = 1');
- $name = db::string('authors','last_name',1);
- if you name your primary keys 'id'; otherwise:
- $name = db::string('authors','last_name',array('id' => 1));
- So $name is 'Boshernitzan', a string.
Queries with one column: examples
- What book_titles did author 1 write?
- db::strings('select title from books where author_id = 1');
- ...
$author_id = 1;
$titles = db::strings('books','title',compact('author_id'));
...
- $titles now holds array('title1', 'title2');
- useful for a list of ids.
Queries with more than one column
- parameters: a) $sql OR b) $table_name, $where_spec
- db::objects, db::assocs, db::rows correspond to mysql_fetch_...
- db::object, db::assoc, db::row just return the first one
Queries with more than one column: examples
- parameters: a) $sql OR b) $table_name, $where_spec
- db::assocs('authors',1))
array( array('id' => 1, 'last_name' => 'Boshernitzan'), ... );
- db::rows('authors',array('id' => 1))
array( array(1,'Boshernitzan'), ... );
- etc; this is a simple way to 'just fetch a record' from the database.