Page 1 of 1

Move table records with the click of a link?

Posted: Thu Jun 25, 2009 10:25 am
by pkunzipula
Hello Experts!

I have a CMS dashboard that lists all current and old news.

I have links that let me EDIT and DELETE any story by taking me to another page.

What I need now is a third link to ARCHIVE STORY that simply lets me move a current news story into the old news table without leaving the page, but I don't know A) how to code the anchor tag for this, or B) if there is MOVE sql statement or if it's simply INSERT and then DELETE.

Here is code that works from my DELETE page.

Code: Select all

  $stmt = $conn->stmt_init();
  if ($stmt->prepare($sql)) {
  
    // bind the query parameters
    $stmt->bind_param('i', $_GET['storyID']);
    // bind the result to variables
    $stmt->bind_result($storyID, $headline, $created);
    // execute the query, and fetch the result
    $OK = $stmt->execute();
    $stmt->fetch();
    }
  }
// if confirm deletion button has been clicked, delete record
if (array_key_exists('delete', $_POST)) {
  $sql = 'DELETE FROM news WHERE storyID = ?';
  $stmt = $conn->stmt_init();
  if ($stmt->prepare($sql)) {
    $stmt->bind_param('i', $_POST['storyID']);
    $deleted = $stmt->execute();
    }
  }
And here are the three links (third link shouldn't leave the page, just execute the MOVE)

Code: Select all

<td><a href="updateNews.php?storyID=<?php echo $row['storyID']; ?>">EDIT</a></td>
<td><a href="deleteNews.php?storyID=<?php echo $row['storyID']; ?>">DELETE</a></td>
<td><a click here to>MOVE TO ARCHIVES</a></td>
This has been troubling me for a couple of days now. I appreciate anyone's effort to solve this dastardly dilemma.

Arlen

Re: Move table records with the click of a link?

Posted: Thu Jun 25, 2009 2:08 pm
by McInfo
If both the "news" and "old_news" tables have exactly the same structure (field names and data types), you can initiate a move by executing an INSERT...SELECT followed by a DELETE.

This will move story 99 to the archive. The LIMIT clause is not entirely necessary if storyID is the primary key, but it still provides peace-of-mind.

Code: Select all

INSERT INTO `old_news` SELECT * FROM `news` WHERE `storyID` = 99 LIMIT 1;
DELETE FROM `news` WHERE `storyID` = 99 LIMIT 1;
It looks like updateNews.php and deleteNews.php act as confirmation pages. The flow of the application may be easier to follow if you keep that convention when performing a move operation.

Whether you want to immediately return to the first page after clicking the link or display a confirmation page, you can use a hyperlink similar to your Edit and Delete links.

Code: Select all

<a href="archiveNews.php?storyID=99">MOVE TO ARCHIVE</a>
On archiveNews.php, display content only if the move was not completed (due to an error or because you need to confirm the operation). If the move succeeds, there should be nothing displayed. This allows the script to return to index.php (or whatever page).

Code: Select all

<?php
if (array_key_exists('storyID', $_GET)) {
   
    // Run the INSERT and DELETE queries here.
   
    // Don't use rand(). It is here to simulate success/failure.
    // Simulation succeeds twice for every three tries.
    if (rand(0, 2) == 0) {
        // If the move failed, display an error.
        echo 'Move operation failed.';
    }
}
// If the headers have not been sent, there was no error.
if (!headers_sent()) {
    // Redirect to the previous page (assumed to be index.php).
    header('Location: index.php');
    // Exit because header() does not exit for you.
    exit;
}
?>
Edit: This post was recovered from search engine cache.

Re: Move table records with the click of a link?

Posted: Thu Jun 25, 2009 3:19 pm
by pkunzipula
McInfo,

Thank you for your response.

Okay, please let me walk through this with you.

Here is what I did with your code.

Code: Select all

<?php
if (array_key_exists('storyID', $_GET)) {
    
    // Run the INSERT and DELETE queries here.
    INSERT INTO 'info' SELECT * FROM 'news' WHERE 'storyID' = '?' LIMIT 1;
    DELETE FROM 'news' WHERE 'storyID' = '?' LIMIT 1;
    // Don't use rand(). It is here to simulate success/failure.
    // Simulation succeeds twice for every three tries.
    if (rand(0, 2) == 0) {
        // If the move failed, display an error.
        echo 'Move operation failed.';
    }
}
// If the headers have not been sent, there was no error.
if (!headers_sent()) {
    // Redirect to the previous page (assumed to be index.php).
    header('Location: storyList.php');
    // Exit because header() does not exit for you.
    exit;
}
?>
 
And I'm getting this message:
Parse error: syntax error, unexpected T_STRING in C:\xampp\htdocs\Cougars\moveToInfo.php on line 5.

I'm trying different combinations of quotes and no quotes. Also, what should I have in place of rand()?

I'm sorry this is so fundamental, but I'm brand spanking new and very shaky with this logic.

Thanks again,

Arlen

Re: Move table records with the click of a link?

Posted: Thu Jun 25, 2009 3:58 pm
by McInfo
Queries don't run by themselves. They are just strings. Study the updateNews.php and deleteNews.php scripts to learn how to initialize a database connection and execute a query. It looks like those two scripts are using a MySQLi object to make the connection.
pkunzipula wrote:Also, what should I have in place of rand()?
You should confirm that the queries changed some rows by checking if mysqli->affected_rows is greater than zero or check for errors with mysqli->errno or mysqli->error.

Also, make sure you copy the queries correctly. Backticks (`) are not single quotes ('). Backticks are used to identify names of tables, fields, etc. Single quotes are used to identify strings.

Incorrect:

Code: Select all

INSERT INTO 'info' SELECT * FROM 'news' WHERE 'storyID' = '?' LIMIT 1;
Correct:

Code: Select all

INSERT INTO `info` SELECT * FROM `news` WHERE `storyID` = '?' LIMIT 1;
Edit: This post was recovered from search engine cache.