[Resolved] Update query not updating?

elroy

Hello,

I have a database containg poems and I am building a page that would allow the logged in author to edit his/her poems.

Check it out at
http://www.jedworth.co.uk/poems.php
On top of the page (right corner), log in as user guest and pwd guest ... one of the poems is entitled 'test poem' and as you can see, you can edit the poem because it belongs to the logged in user ... so far so good 🙂

Clicking on the 'edit poem' link takes you to the edit poem page and loads the poem fields into the appropriate form fields where the author will then be able to change the values as needed and then press on the update poem button at the bottom of the page ... (btw, ignore the upload file as that doesn't work for now ... )
This will then action an update query to update the db fields ... although the message says its all good, the update does not actually take place 😕

why o why? ... everything seems to be in order ... here is the code:

include( 'php_scripts/dbinfo.php' );

				$db = mysql_pconnect( $dbhost, $dbuser, $dbpass ); 

			    	mysql_select_db($dbname,$db); 

			    	$sql = "UPDATE poems SET poemtitle=$poem_title, poemdate=$poem_datewritten, poemauthor=$poem_author, poemdescription=$poem_description, poemtext=$poem_poem WHERE poemID=$poemid"; 

				mysql_query($sql,$db);

				ECHO "Poem updated! <br>Details:<br>no file uploaded<br>";
		    		echo "poem title: $poem_title<br>\n"; 
		    		echo "poem date written: $poem_datewritten<br>\n"; 
		    		echo "poem author: $poem_author<br>\n"; 
		    		echo "poem description: $poem_description<br>\n"; 
		    		echo "poem: $poem_poem<br>\n";
		   		echo "<br>\n";

Can anybody see anything wrong with the above?

Thanks,

kindest regards,

Elroy.

tekky

include( 'php_scripts/dbinfo.php' );
//add this -- most likely your variables arent set...
echo '<pre>';
print_r($_POST);
echo '</pre><br>';
//you would fix this by either doing extract($_POST)
or  $varname = $_POST['postvarname'];
 					$db = mysql_pconnect( $dbhost, $dbuser, $dbpass ); 

			    	mysql_select_db($dbname,$db); 

			    	$sql = "UPDATE poems SET poemtitle=$poem_title, poemdate=$poem_datewritten, poemauthor=$poem_author, poemdescription=$poem_description, poemtext=$poem_poem WHERE poemID=$poemid"; 

				mysql_query($sql,$db);

				ECHO "Poem updated! <br>Details:<br>no file uploaded<br>";
		    		echo "poem title: $poem_title<br>\n"; 
		    		echo "poem date written: $poem_datewritten<br>\n"; 
		    		echo "poem author: $poem_author<br>\n"; 
		    		echo "poem description: $poem_description<br>\n"; 
		    		echo "poem: $poem_poem<br>\n";
		   		echo "<br>\n";

and use

 tags in the future, we love colors here ;)

elroy

Thanks tekky! ....

I copy/pasted your code in my page .. but it didn't work 🙁

you can see the new page and the changes as it displays the post entries ...

it's really strange, because that page is basically the same as the add poem page, except for the query that does an update instead of an insert ....

any ideas about what to do now?

thanks again for your help,

Elroy

ps: got it with the php tags 🙂

 echo "Thank you!"

tekky

what was the output of the print_r() ?

(that was kinda the point of mentioning your variables werent set but your POST data was...)

elroy

thanks again for your reply ...

the output was
Array
(
[submitted] => true
[poem_title] => test poem
[poem_author] => guest
[poem_datewritten] => 2027-03-20
[poem_description] => test description new
[poem_poem] => test poem
[language] => en
)

in my newbie eyes it looks all ok ... but it obviously isn't otherwise it would work :rolleyes:

thanks,

Elroy

tekky

then you need to either do

extract($_POST);
//or this (which is actually recommended)

$poem_title = $_POST['poem_title'], $poem_datewritten = $_POST['poem_datawritten'];
$poem_author = $_POST['poemauthor']; 
$poem_description = $_POST['poemdescription']; 
$poem_poem = $_POST['poemtext'];
$poemid = $_POST['poemID']

and then remove the 3 lines I had you add earlier (the PRE tags and the print_r() lines)

the reason you page isnt working is because newer releases of PHP have register_globals turned OFF by default so you cant access a post variable as $varname w/o first assinging it to $varname from $_POST['varname'];

elroy

🙁 ... still no luck ....

I can't thank you enough though for providing me with all this help ...
I might try to change the whole approach ... see if I might have a wee bit more luck ....

if you are still willing to help ... here is the whole code again ...

$poem_title = $_POST['poem_title'];
			$poem_datewritten = $_POST['poem_datawritten']; 
			$poem_author = $_POST['poem_author'];  

			$poem_description = $_POST['poem_description'];  

			$poem_poem = $_POST['poem_poem']; 
			$poemid = $_POST['poemID'];

                $db = mysql_pconnect( $dbhost, $dbuser, $dbpass );  

                mysql_select_db($dbname,$db);  

                $sql = "UPDATE poems SET poemtitle=$poem_title, poemdate=$poem_datewritten, poemauthor=$poem_author, poemdescription=$poem_description, poemtext=$poem_poem WHERE poemID=$poemid";  

                mysql_query($sql,$db); 
                echo '<pre>'; 
		print_r($_POST); 
		echo '</pre><br>';

                ECHO "Poem updated! <br>Details:<br>no file uploaded<br>"; 
                ECHO "poem title: $poem_title<br>\n";  
                ECHO "poem date written: $poem_datewritten<br>\n";  
                ECHO "poem author: $poem_author<br>\n";  
                ECHO "poem description: $poem_description<br>\n";  
                ECHO "poem: $poem_poem<br>\n"; 
                ECHO "<br>\n";

(notice the php tags? 🆒 ... )

also, the initial load of the page (when submitted = false) is as follows:

<?php
$codeid=$HTTP_GET_VARS['poemid'];
?><?php
$conn = mysql_connect( $dbhost, $dbuser, $dbpass );
mysql_select_db($dbname);
$sql = 'SELECT poemtitle,poemdate,poemdescription,poemauthor,poemtext,poempictureurl,poemID FROM poems where poemID =' . $codeid;
//  . ' AND poemauthor=' .$_SESSION['username']
$poemResult = mysql_query( $sql );
$poemRow = mysql_fetch_row( $poemResult );



	ECHO '<input type="hidden" name="poemID" value="' .$poemRow[6] .'">';
	ECHO 'Poem title<br>';
	ECHO '<input type="Text" name="poem_title" size="33" value="' .$poemRow[0] .'"><br>';
	ECHO '<input type="hidden" name="poem_author" size="25" value="' . $_SESSION["username"] . '">';
	ECHO 'Date written<br>';
	ECHO '<input type="Text" name="poem_datewritten" size="25" value="' . $poemRow[1] . '"><br>';
	ECHO 'Upload this file (optional):<br>';
	ECHO '<input name="' . $upload_file_name . '" type="file" value="' . $poemRow[5] . '"><br>';
	ECHO 'Description<br>';
	ECHO '<TEXTAREA name="poem_description" rows="8" cols="60">' . $poemRow[2] . '</TEXTAREA><br>';
	ECHO 'Poem<br>';
	ECHO '<TEXTAREA name="poem_poem" rows="24" cols="60">' . $poemRow[4] . '</TEXTAREA><br>';

mysql_close ($conn);							
?>

if you (or anyone else) can see anything wrong with the above, I would be very grateful if you could help me!

thanks again,

Elroy.

elroy

right ...

I created a new php page ... all blank .. and then added the follwing code

		<?php

	include( 'php_scripts/dbinfo.php' );

	       $db = mysql_pconnect( $dbhost, $dbuser, $dbpass );  

                mysql_select_db($dbname,$db);  

                $sql = "UPDATE poems SET poemtitle='new test', poemdate='27/03/2004', poemauthor='guest', poemdescription='new descr', poemtext='my poem' WHERE poemID=97";  

                mysql_query($sql,$db); 

	?>

and that works fine!!! 😕

why o why does the other code above not work then?? it does exactly the same except that the values are not hardcoded but come from the form??

any help would be great,

thanks,

Elroy.

elroy

aargh!! :mad:

I reallydon't understand! ... I've cleaned up the page and removed lots of code and all that was not strictly necessary but it still doesn't work!

Can anybody see anything wrong with the code below?

<?php

require 'php_scripts/db_connect.php';

// require our database connection
// which also contains the check_login.php
// script. We have $logged_in for use.

if ($logged_in == 0) {
	die('Sorry you are not logged in, this area is restricted to registered members. <a href="login.php">Click here</a> to log in.');
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<META http-equiv="content-type" content="text/html;charset=iso-8859-1">
<META name="generator" content="Adobe GoLive 6">
<TITLE>Jedworth.co.uk - Edit your poem</TITLE>
<link rel="stylesheet" href="/style.css" type="text/css">
</HEAD>

<BODY BGCOLOR=#FFFFFF LEFTMARGIN=0 TOPMARGIN=0 MARGINWIDTH=0 MARGINHEIGHT=0>
	<TABLE WIDTH=800 BORDER=0 CELLPADDING=0 CELLSPACING=0>
		<TR>
			<TD COLSPAN=2>[code=php]<?php

include( 'http://www.jedworth.co.uk/includes/header.php');
?>[/code]</TD>
</TR>
<TR>
<TD COLSPAN=2>
<HR>
</TD>
</TR>
<TR>
<TD valign="top">

<?php
include( 'http://www.jedworth.co.uk/includes/leftmenu.php');
?>

</TD>
<TD align="left" valign="top" width="674">
<H1>Poems</H1>
<H2>Edit your poem</H2>
<p></p>
<P>Here you will have the opportunity to add a poem with some additional information regarding that poem. You will also have the opportunity to upload or specify a picture to show when that poem is displayed on the web site.</P>
<P></P>

<?php

include( 'php_scripts/dbinfo.php' );

if (isset($_REQUEST['submitted'])) {

		$poem_title = $_POST['poem_title'];
		$poem_datewritten = $_POST['poem_datawritten']; 
		$poem_author = $_POST['poem_author'];  
		$poem_description = $_POST['poem_description'];  
		$poem_poem = $_POST['poem_poem']; 
		$poemid = $_POST['poemID'];

                $db = mysql_pconnect( $dbhost, $dbuser, $dbpass );  

                mysql_select_db($dbname,$db);  

                $sql = "UPDATE poems SET poemtitle='$poem_title', poemdate='$poem_datewritten', poemauthor='$poem_author', poemdescription='$poem_description', poemtext='$poem_poem' WHERE poemID=$poemid";  
                // $sql = "UPDATE poems SET poemtitle='$poem_title', poemdate=$poem_datewritten, poemauthor=$poem_author, poemdescription=$poem_description, poemtext=$poem_poem WHERE poemID=$poemid";  

                mysql_query($sql,$db); 
                echo '<pre>'; 
		print_r($_POST); 
		echo '</pre><br>';

                ECHO "Poem updated! <br>Details:<br>no file uploaded<br>"; 
                ECHO "poem title: $poem_title<br>\n";  
                ECHO "poem date written: $poem_datewritten<br>\n";  
                ECHO "poem author: $poem_author<br>\n";  
                ECHO "poem description: $poem_description<br>\n";  
                ECHO "poem: $poem_poem<br>\n"; 
                ECHO "<br>\n";
}

else
{
#--------------------------------#
# HTML FORM
#--------------------------------#
?>

<form enctype="multipart/form-data" action="<?= $_SERVER['PHP_SELF']; ?>" method="POST">

<input type="hidden" name="submitted" value="true">
[code=php]<?php
	$codeid=$HTTP_GET_VARS['poemid'];
?>
<?php
	$conn = mysql_connect( $dbhost, $dbuser, $dbpass );
	mysql_select_db($dbname);
	$sql = 'SELECT poemtitle,poemdate,poemdescription,poemauthor,poemtext,poemID FROM poems where poemID =' . $codeid;
	//  . ' AND poemauthor=' .$_SESSION['username']
	$poemResult = mysql_query( $sql );
	$poemRow = mysql_fetch_row( $poemResult );

	ECHO '<input type="hidden" name="poemID" value="' .$poemRow[6] .'">';
	ECHO 'Poem title<br>';
	ECHO '<input type="Text" name="poem_title" size="33" value="' .$poemRow[0] .'"><br>';
	ECHO '<input type="hidden" name="poem_author" size="25" value="' . $_SESSION["username"] . '">';
	ECHO 'Date written<br>';
	ECHO '<input type="Text" name="poem_datewritten" size="25" value="' . $poemRow[1] . '"><br>';
	// ECHO 'Upload this file (optional):<br>';
	// ECHO '<input name="' . $upload_file_name . '" type="file" value="' . $poemRow[5] . '"><br>';
	ECHO 'Description<br>';
	ECHO '<TEXTAREA name="poem_description" rows="8" cols="60">' . $poemRow[2] . '</TEXTAREA><br>';
	ECHO 'Poem<br>';
	ECHO '<TEXTAREA name="poem_poem" rows="24" cols="60">' . $poemRow[4] . '</TEXTAREA><br>';

	mysql_close ($conn);

?>
[/code]
<br>

<input type="submit" value="Update Poem"></P>

</form>
<hr>

<?php
}
?>

<?php
include( 'http://www.jedworth.co.uk/includes/footer.php');
?>

</BODY>
</HTML>

thanks,

Elroy

LordShryku

I was going to say because you're not quoting your text, but you should use [man]mysql_error[/man] and let it tell you what's wrong...

elroy

thanks for your tip LordShryku

I inserted that statement and it returned the following:
1065: Query was empty

how can it be empty? .. 😕
it does look like I am pssing values to the query or not?

i modified my code slightly since last post as I wanted to make sure that old values were not held somewhere ... here is again


if (isset($_REQUEST['submitted'])) {

		$u_poem_title = $_POST['frmpoem_title'];
		$u_poem_datewritten = $_POST['frmpoem_datawritten']; 
		$u_poem_author = $_POST['frmpoem_author'];  
		$u_poem_description = $_POST['frmpoem_description'];  
		$u_poem_poem = $_POST['frmpoem_poem']; 
		$u_poemid = $_POST['frmpoemID'];

                $u_db = mysql_pconnect( $dbhost, $dbuser, $dbpass );  

                mysql_select_db($dbname,$u_db);  

		// $u_sql = "UPDATE poems  WHERE poemID=$poemid SET poemtitle='$poem_title' poemdate='$poem_datewritten' poemauthor='$poem_author' poemdescription='$poem_description' poemtext='$poem_poem'";  

                $sql = "UPDATE poems SET poemtitle='$u_poem_title', poemdate='$u_poem_datewritten', poemauthor='$u_poem_author', poemdescription='$u_poem_description', poemtext='$u_poem_poem' WHERE poemID=$u_poemid";  
                // $sql = "UPDATE poems SET poemtitle='$poem_title', poemdate=$poem_datewritten, poemauthor=$poem_author, poemdescription=$poem_description, poemtext=$poem_poem WHERE poemID=$poemid";  

                mysql_query($u_sql,$u_db); 
                echo '<pre>'; 
		print_r($_POST); 
		echo '</pre><br>';

                ECHO "Poem updated! <br>Details:<br>no file uploaded<br>"; 
                ECHO "poem title: $poem_title<br>\n";  
                ECHO "poem date written: $poem_datewritten<br>\n";  
                ECHO "poem author: $poem_author<br>\n";  
                ECHO "poem description: $poem_description<br>\n";  
                ECHO "poem: $poem_poem<br>\n"; 
                ECHO "<br>\n";

                echo mysql_errno($u_db) . ": " . mysql_error($u_db). "\n";
                mysql_close();

please that the last ECHOs return empty values because those variables are now not specified (I was trying to prove something to myself ... :rolleyes: )

Anyway, anybody who can explain this one to me ... I would be most grateful,

Kindest regards,

Elroy.

elroy

weehee!! it works now .. using the mysql_error function allowed me to narrow down to the various errors (from additional quote, wrong $sql string call, etc ... )

thank you very much for all the help ... .

Kindest regards,

Elroy.