PHP and SQLite hierarchical data handling
Normally with projects I am restricted to MySQL or SQLite, where I would work with MySQL, but now I tend to work more with the lightweight SQLite.
(keep in mind, I only work with SQLite through PDO)
The scenario is a simple, it’s a CRUD situation but don’t let that confuse you, this is not about forms or scaffolding as far as I know, there is the difference that we are not looking at the interface but instead only handle the database–>server side bit.
I have 2 related tables, and I need to achieve:
A. Selecting data from the 2 tables in a meaningful manner.
B. Inserting the data in both tables consistently.
C. Updating the data in both tables consistently.
D. Deleting the data in both tables where required.
Lets take a regular office example.
The 2 tables are:
contacts
CREATE TABLE contacts (id INT AUTOINCREMENT PRIMARY KEY, name VARCHAR);
| id | name |
-------------
| 0 | Paul |
| 1 | Fred |
| 2 | Jack |
and
phones
CREATE TABLE phones (id INT AUTOINCREMENT PRIMARY KEY, contact INT, phone VARCHAR, FOREIGN KEY(contact) REFERENCES contacts(id));
| id | contact | phone |
----------------------------
| 0 | 0 | 952390487 |
| 1 | 0 | 500932345 |
| 2 | 2 | 213994470 |
you can see that in the example, Paul has 2 phones, Jack has 1, and Fred has no phones at all, the problem stems from the fact that it is actually a tree structure that we are getting from the 2 tables.
There are several ways of working with tree structures in a SQL environment as seen in this models for hierarchical data presentation.
(examples are made up as I type)
A. Selecting data
The ways by which we can do this are:
- Do several selects, selecting each bit of information separately.
example where everything is selected and put into an Associative Array:
function contactsList(){
$stmt = $pdo->prepare('SELECT id, name FROM contacts');
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
$contacts = array();
foreach($data as $value){
//populate each fetched contact with phone numbers
$stmt = $pdo->prepare('SELECT phone FROM phones WHERE contact=?');
$stmt->execute(array($value['id']));
$value['phones'] = array();
//add each phone to array
while($fetch = $stmt->fetch(PDO::FETCH_ASSOC)){
$value['phones'][] = $fetch['phone'];
}
$contacts[] = $value;
}
return $contacts;
}
Now it is relatively easy to present turn this associative array into a presentation, although I could have used object oriented programming for this, but I have had too much trouble with object relational mismatch to do it relatively efficiently.
- Both tables are joined resulting in semi-duplicate rows.
//select everything
$stmt = $pdo->prepare('SELECT contacts.id, name,phone FROM contacts
OUTER LEFT JOIN phones ON phones.contact = contacts.id');
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
//function to format the semi-duplicate rows into a associative array
function format($id,$mrows,$data){
$xdata = array();
foreach($data as $key=>$value) {
$xid = $value[$id];
if(!isset($xdata[$xid])){
//set values
$xdata[$xid] = $value;
}
foreach($mrows as $mrow){
if(!is_array($xdata[$xid][$mrow])){
//prepare return data
$xdata[$xid][$mrow] = array();
}
if(isset($value[$mrow])){
//add value to multi row
$xdata[$xid][$mrow][] = $value[$mrow];
}
}
}
return $xdata;
}
$contacts = format('id',array('phone'),$data);
- Tables are merged into a single view.
which is the same as 2. but instead we use a view as the joined select.
This is just the SELECTING part and it’s already unnecessarily complicated, but I know of no alternative.
So I’m not going to post the full php examples for B,C or D
Although (D) deleting can be achieved pretty easily using triggers and views (even though triggers have their own problems).
The inserting and updating remains too complicated in my opinion.
If I could somehow get the associative array directly from the sql fetch (or with a php function) and then Ideally just use the same associative array to save the data with some other handy function.
But I think it to be handled completely differently than I expect.
How do you handle these CRUD(dy) issues with even the most simple hierarchical data from even two simple related tables?
Especially the selecting, inserting and updating?