Updating a Database

I’ve been working a modifying the CMS from the Novice to Ninja for my own uses, When trying to update an exsiting article in my database it is not. I click edit it populates the form when I modify an entry it returns me to the search page it doesn`t throw an exception, but the article is not updated on the database. Any thoughts? I’m not sure what I am missing

  <body>
    <h1><?php htmlout($pageTitle); ?></h1>
    <form action="?<?php htmlout($action); ?>" method="post">
      <div>
        <label for="headline">Type your headline here:</label></div>
		<div>
        <textarea id="headline" name="headline" rows="" cols="40">
			<?php htmlout($headline); ?></textarea>
	</div>
	<div>
			   <label for="content">Type your Article here:</label>
			   </div>
			   <div>
        <textarea id="content" name="content" rows="5" cols="40"><?php
            htmlout($content); ?></textarea>
      </div>
      <div>
        <label for="author">Author:</label>
        <select name="author" id="author">
          <option value="">Select one</option>
          <?php foreach ($authors as $author): ?>
            <option value="<?php htmlout($author['id']); ?>"<?php
                if ($author['id'] == $authorid)
                {
                  echo ' selected';
                }
                ?>><?php htmlout($author['name']); ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <fieldset>
        <legend>Categories:</legend>
        <?php foreach ($categories as $category): ?>
          <div>
		  <label for="category<?php htmlout($category['id']); ?>">
			
			  <input type="checkbox" name="categories[]"id="category<?php htmlout($category['id']); ?>"value="<?php htmlout($category['id']); ?>"<?php
              if ($category['selected'])
              {
                echo ' checked';
              }
              ?>><?php htmlout($category['name']); ?></label></div>
        <?php endforeach; ?>
      </fieldset>
      <div>
        <input type="hidden" name="id" value="<?php
            htmlout($contentid); ?>">
        <input type="submit" value="<?php htmlout($button); ?>">
      </div>
    </form>
	
<?php include($_SERVER['DOCUMENT_ROOT'] . "/footer.php"); ?>

	
if (isset($_GET['editform']))
{
  include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

  if ($_POST['author'] == '')
  {
    $error = 'You must choose an author for this article
        Click &lsquo;back&rsquo; and try again.';
    include 'error.html.php';
    exit();
  }

  try
  {
    $sql = 'UPDATE content SET
		headline = :headline,
        content = :content,
        authorid = :authorid
        WHERE id = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
	$s->bindValue(':headline', $_POST['headline']);
    $s->bindValue(':content', $_POST['content']);
    $s->bindValue(':authorid', $_POST['author']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error updating submitted Article.'. $e->getMessage();;
    include 'error.html.php';
    exit();
  }

Hi rabbit69ca, welcome to the forums.

Are all of the “include” files in place?

What exactly did you “modify”?

Thanks…

All of the includes are there…
Connects to the DB fine, I am able to Add, and delete entries…
I was just trying to simply change the base content, leaving categories, and authors the same… but when I try to modify an existing entry in the content table nothing happens… it acts like it works but then nothing is changed…

Sounds, to me, like there is something erroring in the process, and it redirects without displaying the error (common security practice, so anyone trying to hack it can’t see schema, etc.) Can you put some sort of error trapping in place? Something that will email you with details, whenever errors happen?

Are there logs that you can access? Web server logs, db logs, etc.

V/r,

:slight_smile:

Like I said, I’m new to most of this stuff… usually just stuck to static pages, but I like to keep myself entertained…

How would I go about doing that.

http://www.marxproshop.com/PHPtests
http://www.marxproshop.com/PHPtest/admin

Here is the server they are running on… Admin -> Manage Articles -> Blank Search will show you all of the current entries ( just 1 atm) then I try and edit it, and nothing, brings me back to the search without anything

For now you could try changing your code to

  catch (PDOException $e)
  {
// original code commented out for debugging only
//    $error = 'Error updating submitted Article.'. $e->getMessage();;
//    include 'error.html.php';
//    exit();
die($e->getMessage()); // new code for debugging only
  }

Hopefully you’ll see something helpful.

Try ->bindParam instead of ->bindValue

No luck, bindParam still works when adding a new line but still unable to edit…

This is very frustrating…

Are you sure $_GET[‘editform’] is part of $action ???

If you add this to top of page, are all expected values posted?

echo "<pre>";
print_r($_POST);
echo "</pre>";

I added it to all of the pages… I believe I am getting what I am looking for. but I am not 100% when I click edit the array is only showing the ID and the Action, not other variables… so I am not certain. I have it up on the server if you want to see…

Can you move that print_r($_POST); to the top of your code above where your processing code is? It’s currently within the BODY and the $GET condition so you don’t see what is being posted when you submit the form.

Viewing the source it looks like the id is missing.

        <input type="hidden" name="id" value="">
        <input type="submit" value="Update Article">

If I put the print_r with my controller page I get Warning: Cannot modify header information - headers already sent by (output started at /PHPtests/admin/content/index.php:223) in /PHPtests/admin/content/index.php on line 288

but I also get the following output

Array
(
    [headline] => 			This is a test		
    [content] => FROM PHPfff
    [author] => 1
    [categories] => Array
        (
            [0] => 1
            [1] => 2
        )

    [id] => 9
)

Which tells me the form is updating the variables, just not getting passed to my SQL statement… yay progress?

Well I am now seeing the id in the form. This is good. Did you change something to get id to show? Remove the print_r($_POST) so you don’t get that warning (which could stop processing code) and test.

I fixed some quotation mark issues… i’m not really sure… but it still isn’t working…

There are also a number of markup issues you need to fix. <body> before <head> tags and really I’m seeing three <body> tags and other <head> parts throughout the page. This really needs to be fixed.

The head portions are a part of formatting within the includes that make up the banner and the foot pages… I’ll work on cleaning them up.

This is where I am at as of now.

<?php
include_once $_SERVER['DOCUMENT_ROOT'] .
    '/includes/magic.inc.php';

if (isset($_GET['add']))
{
  $pageTitle = 'New Article';
  $action = 'addform';
  $text = '';
  $authorid = '';
  $contentid = '';
  $button = 'Add Article';

  include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

  // Build the list of authors
  try
  {
    $result = $pdo->query('SELECT id, name FROM author');
  }
  catch (PDOException $e)
  {
    $error = 'Error fetching list of authors.';
    include 'error.html.php';
    exit();
  }

  foreach ($result as $row)
  {
    $authors[] = array('id' => $row['id'], 'name' => $row['name']);
  }

  // Build the list of categories
  try
  {
    $result = $pdo->query('SELECT id, name FROM category');
  }
  catch (PDOException $e)
  {
    $error = 'Error fetching list of categories.';
    include 'error.html.php';
    exit();
  }

  foreach ($result as $row)
  {
    $categories[] = array(
        'id' => $row['id'],
        'name' => $row['name'],
        'selected' => FALSE);
  }

  include 'form.html.php';
  exit();
}

if (isset($_GET['addform']))
{
  include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

  if ($_POST['author'] == '')
  {
    $error = 'You must choose an author for this article.
        Click &lsquo;back&rsquo; and try again.';
    include 'error.html.php';
    exit();
  }

  try
  {
    $sql = 'INSERT INTO content SET
		headline = :headline,
        content = :content,
        cdate = CURDATE(),
        authorid = :authorid';
    $s = $pdo->prepare($sql);
    $s->bindParam (':headline', $_POST['headline']);
	$s->bindParam (':content', $_POST['content']);
    $s->bindParam (':authorid', $_POST['author']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    //$error = 'Error adding submitted article.';
	 $error = 'Failed to Add article  to mySQL DB' . $e->getMessage();

    include 'error.html.php';
    exit();
  }

  $contentid = $pdo->lastInsertId();

  if (isset($_POST['categories']))
  {
    try
    {
      $sql = 'INSERT INTO Ccategory SET
          contentid = :contentid,
          categoryid = :categoryid';
      $s = $pdo->prepare($sql);

      foreach ($_POST['categories'] as $categoryid)
      {
        $s->bindValue(':contentid', $contentid);
        $s->bindValue(':categoryid', $categoryid);
        $s->execute();
      }
    }
    catch (PDOException $e)
    {
      $error = 'Failed to Add Categories  to mySQL DB' . $e->getMessage();
      include 'error.html.php';
      exit();
    }
  }

  header('Location: .');
  exit();
}

if (isset($_POST['action']) and $_POST['action'] == 'Edit')
{
  include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

  try
  {
    $sql = 'SELECT id, headline, content, authorid FROM content WHERE id = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error fetching article details.';
    include 'error.html.php';
    exit();
  }
  $row = $s->fetch();

  $pageTitle = 'Edit article';
  $action = 'editform';
  $headline = $row['headline'];
  $content = $row['content'];
  $authorid = $row['authorid'];
  $id = $row['id'];
  $button = 'Update Article';

  // Build the list of authors
  try
  {
    $result = $pdo->query('SELECT id, name FROM author');
  }
  catch (PDOException $e)
  {
    $error = 'Error fetching list of authors.';
    include 'error.html.php';
    exit();
  }

  foreach ($result as $row)
  {
    $authors[] = array('id' => $row['id'], 'name' => $row['name']);
  }

  // Get list of categories containing this article
  try
  {
    $sql = 'SELECT categoryid FROM Ccategory WHERE contentid = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $id);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error fetching list of selected categories.';
    include 'error.html.php';
    exit();
  }

  foreach ($s as $row)
  {
    $selectedCategories[] = $row['categoryid'];
  }

  // Build the list of all categories
  try
  {
    $result = $pdo->query('SELECT id, name FROM category');
  }
  catch (PDOException $e)
  {
    $error = 'Error fetching list of categories.';
    include 'error.html.php';
    exit();
  }

  foreach ($result as $row)
  {
    $categories[] = array(
        'id' => $row['id'],
        'name' => $row['name'],
        'selected' => in_array($row['id'], (array)$selectedCategories));
  }

  include 'form.html.php';
  exit();
}

if (isset($_GET['editform']))
{
  include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

  if ($_POST['author'] == '')
  {
    $error = 'You must choose an author for this article
        Click &lsquo;back&rsquo; and try again.';
    include 'error.html.php';
    exit();
  }

  try
  {
    $sql = 'UPDATE content SET
		headline = :headline,
        content = :content,
        authorid = :authorid
        WHERE id = :id';
    $s = $pdo->prepare($sql);
    $s->bindParam(':id', $_POST['id']);
	$s->bindParam(':headline', $_POST['headline']);
    $s->bindParam(':content', $_POST['content']);
    $s->bindParam(':authorid', $_POST['author']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error updating submitted Article.'. $e->getMessage();;
    include 'error.html.php';
    exit();
  }


  try
  {
    $sql = 'DELETE FROM Ccategory WHERE contentid = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error removing obsolete joke category entries.';
    include 'error.html.php';
    exit();
  }


  if (isset($_POST['categories']))
  {
    try
    {
      $sql = 'INSERT INTO Ccategory SET
          contentid = :contentid,
          categoryid = :categoryid';
      $s = $pdo->prepare($sql);

      foreach ($_POST['categories'] as $categoryid)
      {
        $s->bindValue(':contentid', $_POST['id']);
        $s->bindValue(':categoryid', $categoryid);
        $s->execute();
      }
    }
    catch (PDOException $e)
    {
      $error = 'Error inserting article into selected categories.';
      include 'error.html.php';
      exit();
    }
  }

  header('Location: .');
  exit();
}

if (isset($_POST['action']) and $_POST['action'] == 'Delete')
{
  include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

  // Delete category assignments for this article
  try
  {
    $sql = 'DELETE FROM Ccategory WHERE contentid = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error removing article from categories.';
    include 'error.html.php';
    exit();
  }

  // Delete the article
  try
  {
    $sql = 'DELETE FROM content WHERE id = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error deleting article.';
    include 'error.html.php';
    exit();
  }

  header('Location: .');
  exit();
}

if (isset($_GET['action']) and $_GET['action'] == 'search')
{
  include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

  // The basic SELECT statement
  $select = 'SELECT id, headline, content';
  $from   = ' FROM content';
  $where  = ' WHERE TRUE';

  $placeholders = array();

  if ($_GET['author'] != '') // An author is selected
  {
    $where .= " AND authorid = :authorid";
    $placeholders[':authorid'] = $_GET['author'];
  }

  if ($_GET['category'] != '') // A category is selected
  {
    $from  .= ' INNER JOIN Ccategory ON id = contentid';
    $where .= " AND categoryid = :categoryid";
    $placeholders[':categoryid'] = $_GET['category'];
  }

  if ($_GET['text'] != '') // Some search text was specified
  {
    $where .= " AND headline, content LIKE :content";
    $placeholders[':content'] = '%' . $_GET['text'] . '%';
  }

  try
  {
    $sql = $select . $from . $where;
    $s = $pdo->prepare($sql);
    $s->execute($placeholders);
  }
  catch (PDOException $e)
  {
    $error = 'Error fetching articles.';
    include 'error.html.php';
    exit();
  }

  foreach ($s as $row)
  {
    $articles[] = array('id' => $row['id'], 'headline'=> $row['headline'], 'text' => $row['content']);
  }

  include 'content.html.php';
  exit();
}

// Display search form
include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

try
{
  $result = $pdo->query('SELECT id, name FROM author');
}
catch (PDOException $e)
{
  $error = 'Error fetching authors from database!';
  include 'error.html.php';
  exit();
}

foreach ($result as $row)
{
  $authors[] = array('id' => $row['id'], 'name' => $row['name']);
}

try
{
  $result = $pdo->query('SELECT id, name FROM category');
}
catch (PDOException $e)
{
  $error = 'Error fetching categories from database!';
  include 'error.html.php';
  exit();
}

foreach ($result as $row)
{
  $categories[] = array('id' => $row['id'], 'name' => $row['name']);
}

include 'searchform.html.php';
<?php include_once $_SERVER['DOCUMENT_ROOT'] .
    '/includes/helpers.inc.php'; ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Welcome To MarX ProShop <?php htmlout($pageTitle); ?></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
<link rel="stylesheet" type="text/css" href="http://www.marxproshop.com/css.css" />

</head>
<body bgcolor="a22727">
<?php include($_SERVER['DOCUMENT_ROOT'] . "/banner.php"); ?>
<div id="Content">

    <h1><?php htmlout($pageTitle); ?></h1>
    <form action="?<?php htmlout($action); ?>" method="post">
      <div>
        <label for="headline">Type your headline here:</label></div>
		<div>
        <textarea id="headline" name="headline" rows="" cols="40">
			<?php htmlout($headline); ?></textarea>
	</div>
	<div>
			   <label for="content">Type your Article here:</label>
			   </div>
			   <div>
        <textarea id="content" name="content" rows="5" cols="40"><?php
            htmlout($content); ?></textarea>
      </div>
      <div>
        <label for="author">Author:</label>
        <select name="author" id="author">
          <option value="">Select one</option>
          <?php foreach ($authors as $author): ?>
            <option value="<?php htmlout($author['id']); ?>"<?php
                if ($author['id'] == $authorid)
                {
                  echo ' selected';
                }
                ?>><?php htmlout($author['name']); ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <fieldset>
        <legend>Categories:</legend>
        <?php foreach ($categories as $category): ?>
          <div>
		  <label for="category<?php htmlout($category['id']); ?>">
			
			  <input type="checkbox" name="categories[]" id="category<?php htmlout($category['id']); ?> "value="<?php htmlout($category['id']); ?> "<?php
              if ($category['selected'])
              {
                echo ' checked';
              }
              ?>> <?php htmlout($category['name']); ?> </label> </div>
        <?php endforeach; ?>
      </fieldset>
      <div>
        <input type="hidden" name="id" value="<?php
            htmlout($contentid); ?>">
        <input type="submit" value="<?php htmlout($button); ?>">
      </div>
    </form>
	
<?php include($_SERVER['DOCUMENT_ROOT'] . "/footer.php"); ?>

	
	</div>	

	
</body>
</html>