Page 1 of 1

editing the database problem - help please

Posted: Tue Sep 13, 2005 7:06 am
by umbra
What I want to do is to edit a record that is entered in the database before. My code is something like this (filename is edit.php):

Code: Select all

<?	


if($_POST) {
require_once ("login.php");
	$db_conn = @mysql_connect("localhost", "root", "") or die("<font color=red>Connection to MySQL could not be established!");
	$db_tabl = @mysql_select_db("pubman", $db_conn) or die("<font color=red>Connection to the database could not be established!");

	$table_name = $_POST['table_name'];						
	$entry_no = $_POST['entry_no'];				
	$author = @addslashes($_POST['author']);	
	$title = @addslashes($_POST['title']);		
	$journal = @addslashes($_POST['journal']);					
	$year = @addslashes($_POST['year']);		
	$volume = @addslashes($_POST['volume']);				
	$number = @addslashes($_POST['number']);	
	$pages = @addslashes($_POST['pages']);						
	$month = @addslashes($_POST['month']);		
	$note = @addslashes($_POST['note']);						
	$key = @addslashes($_POST['key']);			
	
	if ($table_name == "article") {
			$query = "UPDATE $table_name SET author='$author', title='$title', journal='$journal', year='$year',
			volume='$volume', number='$number', pages='$pages', month='$month', note='$note', key='$key' WHERE entry_no='$entry_no'";
		}
/* There are more if statements similar to this one here */

	$result = mysql_query($query);
	$deneme = mysql_fetch_assoc($result);          ---------LINE 151-----------
	
	if($result) {
		echo "The record has been updated. ";
		exit();
	} 
	else {
		echo "The record could not be updated!";
		exit();
	}
}
	else {
	require_once ("login.php");	

	$db_conn = @mysql_connect("localhost", "root", "") or die("<font color=red>Connection to MySQL could not be established!");
	$db_tabl = @mysql_select_db("pubman", $db_conn) or die("<font color=red>Connection to the database could not be established!");

//$table_name and $entry_no are passed to that file from url
	$table_name = @$_GET['table_name'];
	$entry_no = @$_GET['entry_no'];
	$query = "SELECT * FROM $table_name WHERE entry_no = $entry_no";
	$result = mysql_query ($query);
	$info = mysql_fetch_assoc($result);

}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Edit Records</title>
</head>
<body>
<?
	if ($table_name == "article") {
?> 
<h1><? echo "$table_name"; ?> entry</h1>
<form method='POST' action='edit.php'>
  <table>
  <strong>  Required fields <br /></strong>
  <input name="entry_no" type="hidden" value="<? echo $info["entry_no"];?>">
  <input name="table_name" type="hidden" value="<? echo "$table_name"; ?>">
  <tr>
     <td>Author:</td>
     <td><input type='text' name='author' value="<? echo $info['author']; ?>" ></td></tr>
   <tr>
     <td>Title:</td>
     <td><input type='text' name='title' value="<? echo $info['title']; ?>"></td></tr>
   <tr>
     <td>Journal:</td>
     <td><input type='text' name='journal' value="<? echo $info['journal']; ?>"></td></tr>
   <tr>
     <td>Year</td>
     <td><input type='text' name='year' value="<? echo $info['year']; ?>"></td></tr>
   <tr><br />
   <td colspan="2"><strong><br />Optional fields:</strong></td>
   <tr>
     <td>Volume:</td>
     <td><input type='text' name='volume' value="<? echo $info['volume']; ?>"></td></tr>
   <tr>
     <td>Number:</td>
     <td><input type='text' name='number' value="<? echo $info['number']; ?>"></td></tr>
   <tr>
     <td>Pages:</td>
     <td><input type='text' name='pages' value="<? echo $info['pages']; ?>"></td></tr>
   <tr>
     <td>Month:</td>
     <td><input type='text' name='month' value="<? echo $info['month']; ?>"></td></tr>
   <tr>
     <td>Note:</td>
     <td><input type='text' name='note' value="<? echo $info['note']; ?>"></td></tr>
   <tr>
     <td>Key:</td>
     <td><input type='text' name='key' value="<? echo $info['key']; ?>"></td></tr>
   <tr>
   <td colspan="2"><br /><input type="submit" value="Update"></td></tr>
<?
}
/* There are more if statements similar to this one here */

 ?>
 </body>
</html>
What i get from this code is:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in c:\program files\easyphp1-8\www\myproject\edit.php on line 151
The record could not be updated!

I know that $query is a valid query. So I really didn't understand what the problem is. The part that i get the variables with method GET works ok but when i push the update button (i send the variables with method POST) i get this warning message and my query does not work. I would really appreaciate if someone can solve this problem.

Posted: Tue Sep 13, 2005 7:13 am
by shiznatix
first off, what if $table_name != article? then your query will not work. maybe thats the problem but i doubt it

Posted: Tue Sep 13, 2005 7:23 am
by umbra
shiznatix wrote:first off, what if $table_name != article? then your query will not work. maybe thats the problem but i doubt it
Like i said there are more if statements that i didn't write because it would be too long. So $table_name must match with one of the if statements. Thanks anyway

Posted: Tue Sep 13, 2005 8:11 am
by feyd
even though you think the query is valid, I'm pretty sure it isn't... echo mysql_error();

Posted: Tue Sep 13, 2005 8:48 am
by umbra
feyd wrote:even though you think the query is valid, I'm pretty sure it isn't... echo mysql_error();
Now we are getting somewhere. As you said I have an error with the query. When I echoed mysql_error(), I had this message:

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 'key='' WHERE entry_no='2'' at line 2

I checked my query and it was: UPDATE article SET author='Ligthman, Alan', title='Time for Stars', journal='', year='1998', volume='2', number='', pages='130', month='april', note='', key='4' WHERE entry_no='2'

I didn't understand what the error message meant but i erased key from my query, it still gave the same error (Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in c:\program files\easyphp1-8\www\myproject\edit.php on line 152), however this time the record was updated.

So, why do i still have this warning message and why there is a problem with 'key' in my query.

P.S.: I got a key field in the table

Posted: Tue Sep 13, 2005 10:44 am
by umbra
any suggestions?

Posted: Tue Sep 13, 2005 10:59 am
by Skittlewidth
"key" is possibly a reserved word. Try putting backticks around it in the query i.e `key`= value

Haven't had enough time to read your enitre post, so I could be wrong...

Posted: Tue Sep 13, 2005 5:29 pm
by umbra
Skittlewidth wrote:"key" is possibly a reserved word. Try putting backticks around it in the query i.e `key`= value

Haven't had enough time to read your enitre post, so I could be wrong...
Unfortunately it didn't work. I have already used 'key' in other queries, I didn't understand why it became a problem now

Posted: Tue Sep 13, 2005 7:00 pm
by umbra
Yes, key is in the reserved words list of mysql. Does that mean that I cannot use key as my column name? I try to quote it but I still have the same problem

Posted: Tue Sep 13, 2005 9:25 pm
by feyd
quote it with backticks.. not quotes themselves:
` vs. ' vs "

the first one is for column, table, and database references, while the second and third are for strings.