Have an account? Sign in
Login  Register  Facebook
How to move pages in MySQL using PHP
Can anyone help with a little query I have.

I don't have the PHP code sorted yet but I'm hoping someone will have a solution with any code as an example.

Example of MySQL Tables:
Pages
Trash

etc

I want to create a delete page which will once the delete link is clicked the page is moved from the pages table into the trash table.

So the file is not completely deleted from the website but moved to another table (trash) and is not visible on the website.

What code would be used to move the page for example about_us.php from the Pages table into the Trash Table?

Thanks for your help
Started: September 22, 2011 Latest Activity: September 22, 2011 php mysql phpmysql
3 Answers
I've done this a few times. It usually involves creating a generic trash table with the following fields:
|------------|----------|------------|------|
| objectType | objectId | title      | data |
|------------|----------|------------|------|
| page       | 1        | Homepage   | ...  |
|------------|----------|------------|------|
Because you don't really want to create a trash table for each table in your database.

Basically, the objectType field contains the table name of the object that was deleted, along with its id in objectId. title contains a user-friendly description of the object, because when showing the contents of the trash to the user, you can't expect him to understand what "page 1" means. "Page: Homepage" is more explanatory.

The last field, data contains a serialized copy of the row from the page table. To undelete, you unserialize the row and re-insert.

Deleting a page involves fetching the page record, serializing it, inserting it into the trash table and then deleting it from the original page table.
The other solution is to have a flag in your page table that tells whether or not the record is trashed. However, this other solution involves leaving a bunch of deleted records in the original table (and in the index), and can slow down queries. It mostly depends on your traffic, number of records and expected response times.

Posted: Go
In: September 22, 2011

Thank you for your help.

THis is not what I was after but is sort of helpful if I wanted to create a visible or hidden page rather than moving pages from one table to another.

Let me rephrase my question with examples and code.

First table is for the pages

CREATE TABLE `pages` (
`pg_id` int(11) NOT NULL auto_increment,
`pg_link` varchar(150) default '',
`pg_title` varchar(255) default NULL,
`pg_cont` text,
`pg_nav` int(2) default NULL,
`pg_order` tinyint(3) default NULL,
PRIMARY KEY (`pg_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `pages`
--

INSERT INTO `pages` (`pg_id`, `pg_link`, `pg_title`, `pg_cont`, `pg_nav`, `pg_order`) VALUES
(1, 'Home', 'Welcome to our cms', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>', 2, 1),
(2, 'About us', 'About us', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>', 2, 2),
(3, 'Services', 'Services', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>', 2, 3),
(4, 'Products', 'Products', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>', 2, 4),
(5, 'Contact us', 'Contact us', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>', 2, 5),
(6, 'Help', 'Help', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>', 1, 1),
(7, 'Resources', 'Resources', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>', 1, 2),
(8, 'Terms & Conditions', 'Terms & Conditions', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>', 3, 1);

second table is for trash

CREATE TABLE `trash` (
`pg_id` int(11) NOT NULL auto_increment,
`pg_link` varchar(150) default '',
`pg_title` varchar(255) default NULL,
`pg_cont` text,
`pg_nav` int(2) default NULL,
`pg_order` tinyint(3) default NULL,
PRIMARY KEY (`pg_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

--
-- Dumping data for table `trash`
--

INSERT INTO `trash` (`pg_id`, `pg_link`, `pg_title`, `pg_cont`, `pg_nav`, `pg_order`) VALUES
(1, 'Home', 'Welcome to our cms', '<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>', 2, 1),

I'm not sure if this is correct for the trash table but i'm assuming I will need all the information from the table pages to pass into table trash in case I need to move the page back into the table pages.

This will be my code to delete the page but I want ot change it so it moves the file into the trash table rather than delete the page completely from the database.

<?php if (isset($_GET['id']) && $_GET['id'] != '') {

$pid = $_GET['id'];

}

?>

<?php require_once("inc/header.php")?>

<p class="pt">Remove Page</p>
<p> Are you sure you want ot remove page?<br />
<a href="pages_remove.php?id=<?php echo $pid; ?> ">Yes</a> | <a href="pages_list.php">No</a></p>

<?php require_once("inc/footer.php")?>

Than I can use the above delet code in the list of pages in the trash table to completly remove the page or similar code to move the page back into the pages table.

I'm after some ocde that will move the file into the trash table.

Is thier a MOVE command that can be used to move the page into the trash table and vise versa??

Posted: qakbar
In: September 22, 2011

If I understood correctly, your database will have at least two tables: \"Pages\" and \"Trash\". These tables will contain the name of certain files, that you want to move between \"Pages\" and \"Trash\".
The Table
If this is the case, I\'d advice you to do everything in one table, by using a field that would be a flag for weather the page is \"Trash\" or not. Let\'s call this table \"Pages\", and it would be something like this:
----------------------------
| PageName | isTrash |
|---------------------------|
| about.php |     1        |
| home.php |     0        |
In this case, the field isTrash indicates weather a page is trash or not, by using the values 1 and 0, respectively. The Code didn\'t quite understand if you were asking for PHP code or SQL, so here\'s both: First, you need to setup some DB information and make the connection, like this:
<?
    //Information needed to make the connection
    $user=\"DB_username\";
    $password=\"DB_password\";
    $database=\"DB_name\";
    //Same machine = localhost
    $host = \"localhost\";

    //Connect to the host
    mysql_connect(localhost,$user,$password);
    //Select the database
    @mysql_select_db($database) or die(\"Unable to select database\");
?>
You can find more information on this subject here Now that we have our database ready to accept queries, lets make the page \"home.php\" a trash page:
<?
    //Information needed to make the connection
    $user=\"DB_username\";
    $password=\"DB_password\";
    $database=\"DB_name\";
    //Same machine = localhost
    $host = \"localhost\";

    //Connect to the database
    mysql_connect(localhost,$user,$password);
    //Select the database
    @mysql_select_db($database) or die(\"Unable to select database\");

    //MySQL query to make \"home.php\" a trash page
    $query = \"UPDATE pages SET isTrash = 1 WHERE PageName = \'home.php\'\";
    //Execute the query
    mysql_query($query);
    //Close the connection
    mysql_close();
?>
Also, you can find more information about SQL here

Posted: MacOS
In: September 22, 2011

Your Answer

xDo you want to answer this question? Please login or create an account to post your answer