My update function is creating a new row. Baffled!

I made an update function & a form to update a page, but it is inserting a new row into the db instead of updating it. I can’t for the life of me figure out where I’ve gone wrong. Can anyone spot my mistake? Thanks :slight_smile:

url looks like this: update.php?id=12


function update_content($params)
	{
	  $id = $_GET['id'];
	  $connection = db_connect();		
	  $query = sprintf("UPDATE content SET page = '%s',
                                                page_id = '%s',
                                                title = '%s',
                                                text = '%s'
											WHERE id = $id;", 
							mysql_real_escape_string($params['page']),
                            mysql_real_escape_string($params['page_id']),
                            mysql_real_escape_string($params['title']),
							mysql_real_escape_string($params['text'])
                            );		
	  $result = mysql_query($query);
		if (!$result)
		{
		  return false;
		}
		else
		{
		  return true;
		}		
	}
	
	// Grab the content to echo into the title & text fields
function show_content()
	{
		$id = $_GET['id'];
		$connection = db_connect();			
	    $query = sprintf("select * from content where id = $id"); 
			
			$result = mysql_query($query);				
			$number_of_posts = mysql_num_rows($result);
			if ($number_of_posts == 0) 
			{
			  return false;	
			}			
			$row = mysql_fetch_array($result);			
			return $row;			
	}
 if(isset($_POST['text']))
	{
	$result = update_content($_POST);
        }
?>

And the form…


<form name="content" id="content" method="post" action="add-content.php">
	  <p><br />
	        <b>Assign to page</b><br />
          <input name="page" id="page" size="60" maxlength="500" value="<?php 
	                                                                     // Get the name from the url and put the space back
		                                                             $page = show_content ();
                                                                             echo $page['page'];	
	                                                                     ?>" />
	    
          <br />
          <br />
          <b>Page id</b>
          <input  name="page_id" id="page_id" size="4" maxlength="4" value="<?php 
	                                                                   // Get the id from the url
		                                                              $page_id = show_content ();
									      echo $page_id['page_id'];		
	                                                                   ?>
                                                                      " />
          <br />
          <br />                                                
	    
	    <b>Title</b>
           <span class="smalltext">(Just a short name this piece of content)</span><br />
          <input name="title" id="title" size="60" maxlength="200" value="<?php 
	                                                                             // Get the name from the url and put the space back
		                                                                         $title = show_content ();	
											 $page = str_replace('-', ' ',$page);
                                                                                         echo $page['title'];	
	                                                                             ?>" />
          <br />
          <br />
          <br />
	      <strong>Content</strong> <span class="smalltext">(paste html in here)</span><br />
	      <textarea name="text" id="text" cols="75" rows="15"><?php $id = $_GET['id'];
								  $text = show_content ();
                                                                  echo $text['text'];
								  ?></textarea>
	      <?php //turn the text area into CK Editor  echo $ckeditor_ini; ?>
          <br />
          <input type="image" src="../images/button_submit.gif" alt="submit" name="submit" value="submit" />
	 &nbsp;&nbsp;<a href="index.php"><img src="../images/button_cancel.gif" alt="Cancel" width="120" height="26" border="0" /></a></p>
    </form>

Honestly? No.

That UPDATE statement cannot INSERT a record, AFAIK. :slight_smile:

If you print out your SQL statement you’ll likely find that the id value is not getting there.

Use %d in the sprintf string for a decimal value.


$query = sprintf("... WHERE id = %d",
    ...,
    $id
);

The value is there pmw57.

BuddyH’s SQL string, $query, is double-quoted so the $id is evaluated and substituted.

I don’t understand how any of the code posted can cause an INSERT though. :confused:

My thinking is that using %d would help to guarantee that it’s always a number, in situations where there is no id value being submitted.

Do you think it would help if he showed us the value of $query ?

print_r($query);

Which implies that some other part of the code is incorrectly directing things to a separate insert section, instead of the expected update one.

:shifty:

I feel you’re leading me somewhere, but I’m too obtuse to figure it out. :stuck_out_tongue:

Perhaps :slight_smile:

If indeed it’s impossible for UPDATE to perform an INSERT, we have ruled out the impossible and can now focus on the implausible instead.

Any other parts of his code that performs an INSERT need to be examined, and the conditional logic leading to it needs to be microscopically analyzed with Sherlock Holmes-like determination.

Ah ha!

The form posts to add-content.php, not update.php, BuddyH needs to add code to determine whether this entry is an addition or update.

Please tell me this is what you were hinting at.

Thanks Paul!

[ot]A-ha!
You two are the reincarnations of Sherlock Holmes and John Watson![/ot]


A-ha! Thank you!!!
:tup: :tup: :tup:

…and there’s me thinking I could just duplicate add-content.php, change the insert to an update and save myself a whole heap of time… :lol:

Hmm oh well, now that’s corrected, I can’t get the update to work anyway lol. I’ve tried a few different methods, but no joy.

Here’s my code & form. I’m still baffled why it doesn’t work! :lol:

url is update-content.php?id=12


<?php
	include("../include/session.php");
	  
	  if(!$session->logged_in)
	  {   
          header('Location: ../login.php');
		  die; 
      } 
	require_once('../include/functions.php');
	


	function content($id)
	{    
	    $id = $_GET['id'];
		$connection = db_connect();			
	    $query = sprintf("select * from content where id = '$id'",									 
									 mysql_real_escape_string($id)								
									 ); 
			
			$result = mysql_query($query);				
			$number_of_posts = mysql_num_rows($result);
			if ($number_of_posts == 0) 
			{
			  return false;	
			}			
			$row = mysql_fetch_array($result);			
			return $row;			
	}

function update($param)
{
     // Get the content id from url to output into the editor	
     $id = $_GET['id'];
     $connect = db_connect();
	
			$page = mysql_real_escape_string($param['page']);
			$page_id = mysql_real_escape_string($param['page_id']);
			$title = mysql_real_escape_string($param['title']);
			$text = mysql_real_escape_string($param['text']);	
					
			$query = ("UPDATE content SET
			                                    page = '$page'
												page_id = '$page_id'
												title = '$title'
										        text = '$text'
											 WHERE id = '$id'
											 ");
 		    $result = mysql_query($query); 
    
		    if (!$result) {
				return false;
    		} else {
				return true;
    		}                           
}

?>

<?php
		// Send form data to mysql	
		if(isset($_POST['text']))
		{
			$result = update($_POST);
			if($result === true)
			{
				echo 'Success!';
				die();
			}
		}
		
?>


   <form form name="editor" id="editor" method="post"  action="update-content.php">
	  <p><br />
	        <b>Assign to page</b><br />
          <input name="page" id="page" size="60" maxlength="500" value="<?php $page = content($id);  echo stripslashes($page['page']); ?>" />
	    
          <br />
          <br />
          <b>Page id</b>
          <input  name="page_id" id="page_id" size="4" maxlength="4" value="<?php $page_id = content($id); echo stripslashes($page_id['page_id']); ?>" />
          <br />
          <br />                                                
	    
	    <b>Title</b>
           <span class="smalltext">(Just a short name this piece of content)</span><br />
          <input name="title" id="title" size="60" maxlength="200" value="<?php $title = content($id);  echo stripslashes($title['title']); ?>" />
          <br />
          <br />
          <br />
	      <strong>Content</strong> <span class="smalltext">(paste html in here)</span><br />
	      <textarea name="text" id="text" cols="75" rows="15"><?php $text = content($id); echo stripslashes($text['text']); ?></textarea>
	      <?php //turn the text area into CK Editor  echo $ckeditor_ini; ?>
          <br />
          <input type="image" src="../images/button_submit.gif" alt="submit" name="submit" value="submit" />
	 &nbsp;&nbsp;<a href="index.php"><img src="../images/button_cancel.gif" alt="Cancel" width="120" height="26" b   
    </form>