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.

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.