[SOLVED] My UPDATE query just isn't working, and I'm stumped.

[Dimwhit]

Maybe I just need some fresh eyes, but for the life of me, I can't figure out why I can't update some records using my update script. To start, I went into phpMyAdmin and did an UPDATE query directly to the database, and it worked. So I know I have proper permissions and that the db is updating fine. That means the problem has to be with one of my files. Here's what I have:

 

My form to edit the existing record (relevant script):

 

<?
include("wcdbinfo.inc.php");
$id=$_GET['id'];
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM faq WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

include("faq_calls.php");

?>


<form action="faq_edit.php" method="post">
  <table width="100%" border="1" cellpadding="5">
    <tr>
      <td valign="top" class="standardtext"><p class="standardtext">
        ID: <input name="ud_id" type="text" id="id" size="15" value="<? echo $id; ?>"><br>
        Order:  <input name="ud_order" type="text" id="order" size="4" value="<? echo $order; ?>">
        Category:  <input name="ud_category" type="text" id="category" size="10" value="<? echo $category; ?>">
        Contact (y/n):  <input name="ud_contact" type="text" id="contact" size="4" value="<? echo $contact; ?>">
        <br>
        Question:
        <input name="ud_question" type="text" id="question" size="50" value="<? echo $question; ?>"><br>
	<p>Answer<br>
	<textarea name="ud_answer" cols="80" rows="30" id="answer"><? echo $answer; ?></textarea><br />
  <p align="center"><input type="Submit"></p>
</td>
</tr>
</table>
</form>

 

And here's the faq_edit.php text:

 

<?
include("wcdbinfo.inc.php");

$ud_id=$_POST['ud_id'];
$ud_order=$_POST['ud_order'];
$ud_question=$_POST['ud_question'];
$ud_answer=$_POST['ud_answer'];
$ud_category=$_POST['ud_category'];
$ud_contact=$_POST['ud_contact'];

mysql_connect(localhost,$username,$password);


$query="UPDATE faq SET id='$ud_id', order='$ud_order', question='$ud_question', answer='$ud_answer', category='$ud_category', contact='$ud_contact' WHERE id='$ud_id'";
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
echo "<meta http-equiv=\"refresh\" content=\"1;URL=faq_view.php\">";
mysql_close();

?>

 

Now I should mention...I have other scripts for other db tables that update fine. I'm also able to INSERT into the faq table. So the wcdbinfo.inc.php file does have the correct db connection info in it.

 

I've looked over both these files, and I can't see it. I'm fully prepared for it to by a typo, but I hope not. Regardless, can anyone see a problem here?

[sasa]

1st try to echo $query before query database

2nd try to change line mysql_query($query); to mysql_query($query) or die(mysql_error());

[craygo]

add some error reporting and find out

 

$result=mysql_query($query) or die(mysql_error());

 

also you function for num rows is wrong

 

$num=mysql_num_rows($result);

 

mysql_query($query)or die(mysql_error());

 

also make sure your connecting to the db.

 

Let us know

 

Ray

[naveenbj]

Maybe I just need some fresh eyes, but for the life of me, I can't figure out why I can't update some records using my update script. To start, I went into phpMyAdmin and did an UPDATE query directly to the database, and it worked. So I know I have proper permissions and that the db is updating fine. That means the problem has to be with one of my files. Here's what I have:

 

My form to edit the existing record (relevant script):

 

<?
include("wcdbinfo.inc.php");
$id=$_GET['id'];
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM faq WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

include("faq_calls.php");

?>


<form action="faq_edit.php" method="post">
  <table width="100%" border="1" cellpadding="5">
    <tr>
      <td valign="top" class="standardtext"><p class="standardtext">
        ID: <input name="ud_id" type="text" id="id" size="15" value="<? echo $id; ?>"><br>
        Order:  <input name="ud_order" type="text" id="order" size="4" value="<? echo $order; ?>">
        Category:  <input name="ud_category" type="text" id="category" size="10" value="<? echo $category; ?>">
        Contact (y/n):  <input name="ud_contact" type="text" id="contact" size="4" value="<? echo $contact; ?>">
        <br>
        Question:
        <input name="ud_question" type="text" id="question" size="50" value="<? echo $question; ?>"><br>
	<p>Answer<br>
	<textarea name="ud_answer" cols="80" rows="30" id="answer"><? echo $answer; ?></textarea><br />
  <p align="center"><input type="Submit"></p>
</td>
</tr>
</table>
</form>

 

And here's the faq_edit.php text:

 

<?
include("wcdbinfo.inc.php");

$ud_id=$_POST['ud_id'];
$ud_order=$_POST['ud_order'];
$ud_question=$_POST['ud_question'];
$ud_answer=$_POST['ud_answer'];
$ud_category=$_POST['ud_category'];
$ud_contact=$_POST['ud_contact'];

mysql_connect(localhost,$username,$password);


$query="UPDATE faq SET id='$ud_id', order='$ud_order', question='$ud_question', answer='$ud_answer', category='$ud_category', contact='$ud_contact' WHERE id='$ud_id'";
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
echo "<meta http-equiv=\"refresh\" content=\"1;URL=faq_view.php\">";
mysql_close();

?>

 

Now I should mention...I have other scripts for other db tables that update fine. I'm also able to INSERT into the faq table. So the wcdbinfo.inc.php file does have the correct db connection info in it.

 

I've looked over both these files, and I can't see it. I'm fully prepared for it to by a typo, but I hope not. Regardless, can anyone see a problem here?

Hello

Try to write <?php instead of <?

Regards

Nj

[Dimwhit]

OK. I echoed the query and added the error reporting. This is what came back:

 

UPDATE faq SET id='20', order='20', question='I have a completed home study in our state of residence. The agency who completed my home study only serves our local counties. Would I have to complete a new home study to adopt a child from your state?', answer='XXXThat depends upon the state agency that completed your home study.', category='general', contact='y' WHERE id='20'

 

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 'order='20', question='I have a completed home study in our state of residence. T' at line 1

 

I'm not sure what syntax problem it's referring to. Particularly since I'm using the same in other scripts just fine. That make sense to anyone?

 

Edit: I also fixed the numrows problem and added 'PHP' to the <? code. Neither helped in this instance.

[revraz]

echo out $ud_id and see if it's just 20 or if it's 20'

[rcorlew]

You should try using the ` around your db column names. I have run into that same problem added the  'table.column' and it worked.

[revraz]

ORDER is a mysql reserved word, either change it or use backticks.  Changing it would be better.

[craygo]

you shouldn't set your id again especially if it is an auto increment field. also order is a mysql function, you would need to enclose it is ` to get it to work. You should also get you comment ready for mysql by using the mysql_real_escape_string function, because if someone uses a qingle or double quote you caould have problems inserting the data.

 

 

$ud_id=$_POST['ud_id'];
$ud_order=$_POST['ud_order'];
$ud_question=mysql_real_escape_string($_POST['ud_question']);
$ud_answer= mysql_real_escape_string($_POST['ud_answer']);
$ud_category=$_POST['ud_category'];
$ud_contact=$_POST['ud_contact'];

$query="UPDATE faq SET `order`='$ud_order', `question`='$ud_question', `answer`='$ud_answer', `category`='$ud_category', `contact`='$ud_contact' WHERE id='$ud_id'";

 

Ray

 

[Dimwhit]

ORDER is a mysql reserved word, either change it or use backticks.  Changing it would be better.

 

Bingo! That was the problem. I just changed the field name for that one. Works fine now. That thought didn't even occur to me.

 

Thanks for all the help, everyone!