editing the database problem - help please

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
umbra
Forum Newbie
Posts: 21
Joined: Tue Sep 13, 2005 4:20 am

editing the database problem - help please

Post 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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

first off, what if $table_name != article? then your query will not work. maybe thats the problem but i doubt it
umbra
Forum Newbie
Posts: 21
Joined: Tue Sep 13, 2005 4:20 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

even though you think the query is valid, I'm pretty sure it isn't... echo mysql_error();
umbra
Forum Newbie
Posts: 21
Joined: Tue Sep 13, 2005 4:20 am

Post 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
umbra
Forum Newbie
Posts: 21
Joined: Tue Sep 13, 2005 4:20 am

Post by umbra »

any suggestions?
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post 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...
umbra
Forum Newbie
Posts: 21
Joined: Tue Sep 13, 2005 4:20 am

Post 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
umbra
Forum Newbie
Posts: 21
Joined: Tue Sep 13, 2005 4:20 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply