INSERT and SELECT

japetos

I'd like to add a field year in the Database.
So when I upload a new album I want to INSERT the year to. But i can't fint the INSERTion in the code.

As well as the INSERT I'd like to SELECT from the year too.

Can somebody help me?

trisweb

classes.php, the first class, called PersistentObject, takes care of all the database INSERTs and SELECTs so you don't have to worry about them.

Adding a field is as easy as adding it to the database table (album in your case) then adding two classes to classes.php (or class-album.php in the upcoming version...) to get and set the field. Check out the methods like getTitle and getDesc in the Album class to see how to do that.

japetos

Thank you for replying so quickly! I've now found a function:

/**
* Load the data array from the database, using the unique id set to get the unique record.
* @return false if the record already exists, true if a new record was created.
* The return value can be used to insert default data for new objects.
*/
function load() {
// Get the database record for this object.
$entry = query_single_row("SELECT * FROM " . prefix($this->table) .
getWhereClause($this->unique_set) . "year='2006'" . " LIMIT 1;");
if (!$entry) {
$this->save();
return true;
} else {
$this->data = $entry;
$this->id = $entry['id'];
return false;
}
}

Here I changed the SELECT by including year='2006'.

Is that OK?

Now the insertion:

/**
* Save the updates made to this object since the last update. Returns
* true if successful, false if not.
*/
function save() {
if ($this->id == null) {
// Create a new object and set the id from the one returned.
$insert_data = array_merge($this->unique_set, $this->updates);
$sql = "INSERT INTO " . prefix($this->table) . " (";
if (empty($insert_data)) { return true; }
$i = 0;
foreach(array_keys($insert_data) as $col) {
if ($i > 0) $sql .= ", ";
$sql .= "`$col`";
$i++;
}
$sql .= ") VALUES (";
$i = 0;
foreach(array_values($insert_data) as $value) {
if ($i > 0) $sql .= ", ";
$sql .= "'" . mysql_escape_string($value) . "'";
$i++;
}
$sql .= ");";
$success = query($sql);
if ($success == false || mysql_affected_rows() != 1) { return false; }
$this->id = mysql_insert_id();
$this->updates = array();

} else {
// Save the existing object (updates only) based on the existing id.
if (empty($this->updates)) {
return true;
} else {
$sql = "UPDATE " . prefix($this->table) . " SET";
$i = 0;
foreach ($this->updates as $col => $value) {
if ($i > 0) $sql .= ",";
$sql .= " `$col` = '". mysql_escape_string($value) . "'";
$this->data[$col] = $value;
$i++;
}
$sql .= " WHERE id=" . $this->id . ";";
$success = query($sql);
if ($success == false || mysql_affected_rows() != 1) { return false; }
$this->updates = array();
}
}
return true;
}

Where can I put the year='2006' tag? It may be static.
Thanks

Moritz

trisweb

Geez... much easier to put this in classes.php, like I was saying. You don't need to touch the SQL code, zenphoto is programmed right, and the database model is good, trust me. What you wrote above won't even work, so don't continue going that direction...

To select/insert based on your "key" you just need to modify one line to the album class:

Find:
`$new = parent::PersistentObject('albums', array('folder' => $this->name));`
Change to:
`$new = parent::PersistentObject('albums', array('folder' => $this->name, 'year' => '2006'));`

You're modifying the set of unique keys used to find records in the database. Just add an item to the array defining a new unique key. Note that all the albums you want to select/modify have to have the year as 2006. If you want to pull 'year' from the user and put it in there, it's easy too, just use a $_GET['year'] variable and pass it through the query string...

Look, zenphoto is clean, don't mess it up with SQL hacks, use the elegant (admittedly undocumented) structure already in place ;-)

japetos

Thanks, was just an example. I'm not going to hack the SQL.
Probably I'll just use the place field as year.

trisweb

But if you want to select by it, you'll still have to add it to the unique key set. :-) Just read over the code, it'll make sense.

Good luck! Come back if you need help. Wasn't trying to be demeaning, I just like clean code.