Archive for category db
PHP code snippet – Reorder items in a table
Posted by vinoaj in db, development, howto, php on July 9, 2007
This snippet of code reorders items in a table based on the number of steps you want to move an item from its current position. It moves the item of interest to its new position and shifts all other items to their new shifted positions.
-
function moveItem ($n_steps) {
-
if ($n_steps == 0) {
-
return;
-
}
-
-
$cur_pos = $this->pos;
-
$new_pos = $cur_pos + $n_steps;
-
-
if ($n_steps <0) {
-
$min_pos = $cur_pos + $n_steps;
-
$max_pos = $cur_pos - 1;
-
$shift_sign = '+';
-
} elseif ($n_steps> 0) {
-
$min_pos = $cur_pos + 1;
-
$max_pos = $cur_pos + $n_steps;
-
$shift_sign = '-';
-
} else {
-
return;
-
}
-
-
$id = $this->id;
-
-
//Reorder existing items that will be existed by moving this item
-
$sql = "UPDATE positions
-
SET order_num = order_num $shift_sign 1
-
WHERE id = $id
-
AND position>= $min_pos
-
AND position <= $max_pos";
-
-
$this->db->query($sql);
-
-
//Now set new position for this item
-
$sql = "UPDATE postions SET position = $new_pos WHERE id = $id";
-
$this->db->query($sql);
-
}
PDO – initialising a MySQL connection
PHP Data Objects (PDO) is a PHP extension that provides a consistent interface for database connectivity. This means that it allows your PHP code to talk to a number of different database formats without you having to modify your code for each particular case. This is no different (on the face of it) to other PHP data-access abstraction layer mechanisms such as Pear's MDB2 package and Zend's DB package.
Note: PDO usually comes pre-installed with PHP 5.x.x, with the necessary drivers for sqllite already there. You will most likely want the MySQL drivers too. These can be installed using pecl. See the PHP PDO documentation for further details.
//Open our connection
$dbhandler = new PDO('mysql:host=localhost;dbname=yourdb', $db_user, $db_pass);
/*
... queries etc here { examples to come soon }; ....
*/
//Now that we are done with our connection, let's dispose of it
$dbhandler = null;