UPDATE SQL error

[JsusSalv]

Hello Everyone:

 

MySQL client version: 5.0.32

Server version: 5.0.67

 

I am in need of great help.  I've Google'd my SQL problem but I can't seem to find the answer so I've come to the community.  I'm attempting to update a table with many rows. What I've done is set up the page so that every row is returned and wrapped in a form for updating purposes. When I click on the submit button I get an SQL syntax error.  Here's my SQL code:

(Sorry fenway but I need the PHP variables in there otherwise I'm afraid of confusing others).

// Prepare the first SQL query.
"UPDATE $webpage_table SET element_name = '$element_name', sefurl = '$sefurl', content = '$content' ORDER BY element_id DESC";

 

 

Here are the errors I'm receiving:

 

From Google Chrome:

error on line 22 at column 261: Extra content at the end of the document

 

mysql_error()

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET element_name = 'main content', sefurl = 'test-test', content = 'This is some' at line 1

 

 

Here's my table structure for this particular problem (I have several other tables that will have the same code)

-- Database: `databasename`
--

-- --------------------------------------------------------

--
-- Table structure for table `example3`
--

CREATE TABLE `example3` (
  `element_id` int(10) unsigned NOT NULL auto_increment,
  `element_name` varchar(30) default NULL,
  `sefurl` varchar(30) default NULL,
  `content` varchar(30) default NULL,
  `datecreated` datetime NOT NULL default '0000-00-00 00:00:00',
  `updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`element_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `example3`
--

INSERT INTO `example3` (`element_id`, `element_name`, `sefurl`, `content`, `datecreated`, `updated`) VALUES
(5, 'main content', 'test-test', 'This is some content', '2008-11-15 00:04:17', '2008-11-17 00:07:21'),
(1, 'comment box', 'test', 'This is more content', '2008-11-14 03:19:40', '2008-11-17 00:07:21'),
(2, 'advertisements', 'test', 'up and down and up', '2008-11-14 03:20:10', '2008-11-17 00:07:21'),
(4, 'articles', '12345', 'three two one', '2008-11-14 03:47:44', '2008-11-17 00:07:21'),
(6, 'flyer', 'test', 'At the beginning...', '2008-11-15 00:28:33', '2008-11-17 00:07:21');

 

 

I've tried modifying the SQL so include a WHERE clause but I'd like for ALL of the rows within the table to be updated (if they're 'dirty').  According to the MySQL documentation: "With no WHERE clause, all rows are updated." (http://dev.mysql.com/doc/refman/5.0/en/update.html)

At the bottom of the page at the link I've provided there is a statement that I thought may have something to do with my dilemma but I don't believe I am running a SELECT while UPDATE-ing.

"Currently, you cannot update a table and select from the same table in a subquery. "

 

If you need to see the full code I'd be glad to post it, just ask.

All I want to do is update the database table but I cannot figure out what I'm doing incorrectly.  Please help. Thanks!

[JsusSalv]

I am posting my entire code.  I thought about it and I'm not sure I am making myself clear based on what I wrote in the first post.  Forgive me if this is a bit long but I really do need help and am eager to figure out what I'm doing incorrectly.  I'm hoping a fresh pair of eyes can shed light on this dilemma:

 

// Get details of selected record.
if ($_GET && !$_POST) {
if (isset($_GET['webpage_table'])) {
	$webpage_table = (string) $_GET['webpage_table'];
	$webpage_table = trim($webpage_table);
	$webpage_table = htmlentities($webpage_table, ENT_QUOTES);
	$webpage_table = mysql_real_escape_string($webpage_table);
	$webpage_table = stripslashes($webpage_table);
	$webpage_table = strip_tags($webpage_table);
}

    else {
  $webpage_table = NULL;
}

    if ($webpage_table) {
    $sql = "SELECT * FROM $webpage_table";
	$result = mysql_query($sql) or die (mysql_error());
}
}

// Redirect the page if cancel button clicked.
if (array_key_exists('cancel_update', $_POST))  {
  header('Location: ./webpage-tables.php');
  exit;
}


// If form has been submitted, update record.
if (array_key_exists('update', $_POST)) {

//$webpage_table = (string) $_POST['webpage_table'];

// Prepare an array of expected items.
$expected = array('element_name', 'sefurl', 'content', 'element_id');

// prepare expected items for insertion in to database
    foreach ($_POST as $key => $value) {
	if (in_array($key, $expected)) {
	    ${$key} = mysql_real_escape_string($value);
	    }
	}

// Abandon the process if primary key is invalid.
if (is_numeric($webpage_table) && !is_numeric($element_id)) {
	die('Invalid request');
}

    // Prepare the first SQL query.
    $sql = "UPDATE $webpage_table SET element_name = '$element_name', sefurl = '$sefurl', content = '$content' ORDER BY element_id DESC";
  
// Submit the query and redirect if successful.
$done = mysql_query($sql) or die(mysql_error());
}

// Redirect page on either success or failure.
if ($done || !isset($webpage_table)) {
header('Location: ./webpage-tables.php');
exit;
}
?>
<div id="cmsContainer">
    <h3><strong>CURRENTLY UPDATING WEBPAGE: <?php echo strtoupper(htmlentities($webpage_table, ENT_QUOTES)); ?></strong></h3><br />
<div class="whiteBG">
    <?php if (mysql_num_rows($result) == 0) { ?>
	    <p class="warning">Invalid request: this webpage has no content.</p>
	<?php
		}
	    else {
    		while($element = @ mysql_fetch_array($result))
			{
				// Assign shorter variable names.
				$element_name = str_ireplace(' ', '', htmlentities($element['element_name'], ENT_QUOTES)); // No whitespace.
					$element_name_ws = htmlentities($element['element_name'], ENT_QUOTES); // Whitespace is ok.
				$sefurl = str_ireplace(' ', '', htmlentities($element['sefurl'], ENT_QUOTES));
				$content = nl2br(htmlentities($element['content'], ENT_QUOTES))							;
				$element_id = $element['element_id'];

				echo '<form id="form1" name="form1" method="post" action="">
						<div class="'.$element_name.'">
					        <p>
					            <label for="element_name"><strong><small>ELEMENT NAME: </small></strong></label>
					            <input type="text" name="element_name" id="element_name" value="'.$element_name_ws.'" />
					        </p>
							<br />
					        <p>
					            <label for="sefurl"><strong><small>S.E.F. URL:</small> </strong></label>
					            <input type="text" name="sefurl" id="sefurl" value="'.$sefurl.'" />
					        </p>
		  		    	    <br />
							<label for="content"><strong><small>CONTENT:</small> </strong></label>
							<textarea name="content" id="content" cols="85" rows="">'
								.$content.
							'</textarea><br /><br />
							<input type="submit" name="update" value="UPDATE PAGE" />
				    		<input name="element_id" type="hidden" value="'.$element_id.'" />
					        <input type="submit" name="cancel_create" id="cancel_update" value="CANCEL" />
						</div>
					  </form>
					 ';
			}
		}
	?>
</div> <!-- end.whiteBG -->
</div>  <!-- end.cmsContainer -->

[revraz]

The problem appears to be in $webpage_table, what does it contain?

 

Echo $sql to verify it.

[JsusSalv]

$webpage_table contains the name of the table from which the records are being pulled from.  What I'm trying to do is setup the system to create new webpages for the site by creating tables instead of a record within a table.

 

When I echo the $sql I get the appropriate table being selected and parsed into the SQL query.

[JsusSalv]

OH NO!!!!!  I had run the $sql in the wrong place.  I've echo''ed the $sql at the top of the page and it does NOT show up.  Here's there error I get:

 

UPDATE SET element_name = 'main content', sefurl = 'test-test', content = 'This is some content' ORDER BY element_id DESCYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET element_name = 'main content', sefurl = 'test-test', content = 'This is some' at line 1

 

If you notice, between the commands UPDATE and SET the $webpage_table variable is missing.  What can I do?  Should I use a hidden input field with the $webpage_table variable?

[JsusSalv]

I added a hidden input with the $webpage_table variable but it didn't help.  I'm still not able to produce the $webpage_table within the SQL query when updating.  Can anyone help?

[revraz]

I'm confused on what you are trying to do.  Why do you set it to NULL if it's missing?

 

else {

  $webpage_table = NULL;

 

If it's missing, your query will fail.  Verify that it is set and if it isn't, provide a error message and go back to the form so they user can put it in.

[JsusSalv]

I got it to work!!!!  The hidden input did it, I had just forgotten to add it to the $expected array.

 

To answer your question, the following:

 

else {
     $webpage_table = NULL;

 

is part of the SELECT query and is just a redundant check in my system.  I was having issues with the UPDATE query.

 

 

Now, because I don't have a WHERE clause in the SQL everything is getting updated.  How would I update only those records which have been updated or modified?