Update query issue...

[ZafT]

Hi all - thanks in advance for any help.

I am trying to write a simple update script. In fact, I am re-using code
that I've used before (successfully) but I can't figure out why it is not
working. The script completes without any errors, but does not actually
update the database.

Also, the "check for same name" query works fine, and will return the proper
error if a duplicate is found.

Thanks again!
Shane

Here is the code - pretty simple...

<?PHP
require('header .php');
require('config .php');

mysql_connect($ dbserver,$usern ame,$password)
or die( "Unable to connect");
@mysql_select_d b($database)
or die( "Unable to select database");

$id=$_POST['id'];
$name=$_POST['name'];
$description=$_ POST['description'];
$show=$_POST['show'];

?>

<TABLE width=500 cellpadding="15 ">
<TR>
<TD>

<H1><font face="verdana"> Edit Project</H1>
<font face="verdana">

<?php

echo "$id <BR>";
echo "$name <BR>";
echo "$descripti on <BR>";
echo "$show <BR>";

$query="SELECT * FROM project WHERE name = '$name'";
$result=mysql_q uery($query);
$num=mysql_numr ows($result);

//echo $num;

//Check to see if there is already a project with the name
//we are trying to change to. Ignore if the id is the same
//since this just means there was no name change.

if($num 0){
$conflictid=mys ql_result($resu lt,0,"id");
if(!($id==$conf lictid)){
die("Error: There is already a project with that
name in the database. Please try again.");
}
else{

$query="UPDATE project SET name='$name',
description='$d escription',
show='$show'
WHERE id=$id";
mysql_query($qu ery);

echo "Record Updated";

}

}

else{

$query="UPDATE project SET name='$name',
description='$d escription',
show='$show'
WHERE id=$id";
mysql_query($qu ery);

echo "Record Updated";

}

mysql_close();
?>

</TD>
</TR>
</TABLE>
<?php
require('footer .php');
?>

[Joshua Ruppert]

Re: Update query issue...


ZafT wrote:

Hi all - thanks in advance for any help.
>
I am trying to write a simple update script. In fact, I am re-using code
that I've used before (successfully) but I can't figure out why it is not
working. The script completes without any errors, but does not actually
update the database.
>
Also, the "check for same name" query works fine, and will return the proper
error if a duplicate is found.
>
Thanks again!
Shane
>
Here is the code - pretty simple...
>
<?PHP
require('header .php');
require('config .php');
>
mysql_connect($ dbserver,$usern ame,$password)
or die( "Unable to connect");
@mysql_select_d b($database)
or die( "Unable to select database");
>
$id=$_POST['id'];
$name=$_POST['name'];
$description=$_ POST['description'];
$show=$_POST['show'];
>
?>
>
<TABLE width=500 cellpadding="15 ">
<TR>
<TD>
>
<H1><font face="verdana"> Edit Project</H1>
<font face="verdana">
>
<?php
>
echo "$id <BR>";
echo "$name <BR>";
echo "$descripti on <BR>";
echo "$show <BR>";
>
$query="SELECT * FROM project WHERE name = '$name'";
$result=mysql_q uery($query);
$num=mysql_numr ows($result);
>
//echo $num;
>
//Check to see if there is already a project with the name
//we are trying to change to. Ignore if the id is the same
//since this just means there was no name change.
>
if($num 0){
$conflictid=mys ql_result($resu lt,0,"id");
if(!($id==$conf lictid)){
die("Error: There is already a project with that
name in the database. Please try again.");
}
else{
>
$query="UPDATE project SET name='$name',
description='$d escription',
show='$show'
WHERE id=$id";
mysql_query($qu ery);
>
echo "Record Updated";
>
}
>
}
>
else{
>
$query="UPDATE project SET name='$name',
description='$d escription',
show='$show'
WHERE id=$id";
mysql_query($qu ery);
>
echo "Record Updated";
>
}
>
mysql_close();
?>
>
</TD>
</TR>
</TABLE>
<?php
require('footer .php');
?>

have you tried running mysql_error() after the update's. You may have a
single quote/apostrophy in $name, $description, or $show that needs to
be escaped before the string can be used in the query. I'm not sure
what the escape character is in MySQL, but in SQL Server you just have
to double the single quotes to have the dbms treat it as part of the
string instead of the end of the string.

Josh

[ZafT]

Re: Update query issue...

>
have you tried running mysql_error() after the update's. You may have a
single quote/apostrophy in $name, $description, or $show that needs to
be escaped before the string can be used in the query. I'm not sure
what the escape character is in MySQL, but in SQL Server you just have
to double the single quotes to have the dbms treat it as part of the
string instead of the end of the string.
>
Josh
>

I tried this:

$link = mysql_connect($ dbserver,$usern ame,$password)
or die( "Unable to connect");

<snip .....>

$query="UPDATE project SET name='$name',
description='$d escription',
show='$show'
WHERE id=$id";
mysql_query($qu ery);
mysql_error($li nk);

No errors. Is that what you meant?

Shane

[Rik]

Re: Update query issue...

ZafT wrote:

Hi all - thanks in advance for any help.
>
I am trying to write a simple update script. In fact, I am re-using
code that I've used before (successfully) but I can't figure out why
it is not working. The script completes without any errors, but does
not actually update the database.
>
Also, the "check for same name" query works fine, and will return the
proper error if a duplicate is found.
<?PHP
require('header .php');
require('config .php');
>
mysql_connect($ dbserver,$usern ame,$password)
or die( "Unable to connect");
@mysql_select_d b($database)
or die( "Unable to select database");
>
$id=$_POST['id'];
$name=$_POST['name'];
$description=$_ POST['description'];
$show=$_POST['show'];

$id = mysql_real_esca pe_string($_POS T['id']);//etc...

If that doesn't work: echo the built query, and any mysql_error();

Grtz,
--
Rik Wasmus

[Joshua Ruppert]

Re: Update query issue...


ZafT wrote:

have you tried running mysql_error() after the update's. You may have a
single quote/apostrophy in $name, $description, or $show that needs to
be escaped before the string can be used in the query. I'm not sure
what the escape character is in MySQL, but in SQL Server you just have
to double the single quotes to have the dbms treat it as part of the
string instead of the end of the string.

Josh

>
I tried this:
>
$link = mysql_connect($ dbserver,$usern ame,$password)
or die( "Unable to connect");
>
<snip .....>
>
$query="UPDATE project SET name='$name',
description='$d escription',
show='$show'
WHERE id=$id";
mysql_query($qu ery);
mysql_error($li nk);
>
No errors. Is that what you meant?
>
Shane

You're not actually capturing the mysql_error() output. Try either:

print(mysql_err or($link));

or this if you want to use the text in your code

$qryErr = mysql_error($li nk);

Josh

[ZafT]

Re: Update query issue...

>
You're not actually capturing the mysql_error() output. Try either:
>
print(mysql_err or($link));
>
or this if you want to use the text in your code
>
$qryErr = mysql_error($li nk);
>
Josh
>

Josh,

Thanks - I do apparently have an error in my syntax. I'll get on that and
try to fix it before bugging the group again. Your help is quite
appreciated.

Shane

[Joshua Ruppert]

Re: Update query issue...


ZafT wrote:

You're not actually capturing the mysql_error() output. Try either:

print(mysql_err or($link));

or this if you want to use the text in your code

$qryErr = mysql_error($li nk);

Josh

>
Josh,
>
Thanks - I do apparently have an error in my syntax. I'll get on that and
try to fix it before bugging the group again. Your help is quite
appreciated.
>
Shane

No problem. Just remember, there are no dumb questions just dumb
answers :o)

[ZafT]

Re: Update query issue...

Well, I'm back...

I have the script working on other update pages, and the only difference is
the $show variable. It is currently a smallint (I have tried changing to
varchar and int with no luck). The id field is an int, and the syntax '$id'
works on a sister script (but that script does not have the '$show'
variable. I have tried the mysql_real_esca pe_string() bit proposed by Rik,
but to no avail. The result is an error like:

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 'show=1 WHERE
id='3'' at line 3

Any more thoughts? This is killing me. It has to be something completely
rediculous.

Shane

[Rik]

Re: Update query issue...

ZafT wrote:

Well, I'm back...
>
I have the script working on other update pages, and the only
difference is the $show variable. It is currently a smallint (I have
tried changing to varchar and int with no luck). The id field is an
int, and the syntax '$id' works on a sister script (but that script
does not have the '$show' variable. I have tried the
mysql_real_esca pe_string() bit proposed by Rik, but to no avail. The
result is an error like:
>
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 'show=1 WHERE id='3'' at line 3
>
Any more thoughts? This is killing me. It has to be something
completely rediculous.

Aha, there's th error. Well, 'show' is a mysql function. If your field is
named 'show', use backticks like `show` to use it as a fieldname.

I had this problem quite often with naming fields in mysql when I just
started. In the beginning I chose different names, but now I just put
backticks around every fieldname, every query, all the time. Wether it
necessary or not, it might save some precious hours like these :-).

If you use PHPMyAdmin, it's always a good start when something goes wrong
with the query, to echo the query instead and try it in it's interface.
Thanks to syntax higlighting, errors are spotted easily.


Grtz,
--
Rik Wasmus

[Joshua Ruppert]

Re: Update query issue...


ZafT wrote:

Well, I'm back...
>
I have the script working on other update pages, and the only difference is
the $show variable. It is currently a smallint (I have tried changing to
varchar and int with no luck). The id field is an int, and the syntax '$id'
works on a sister script (but that script does not have the '$show'
variable. I have tried the mysql_real_esca pe_string() bit proposed by Rik,
but to no avail. The result is an error like:
>
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 'show=1 WHERE
id='3'' at line 3
>
Any more thoughts? This is killing me. It has to be something completely
rediculous.
>
Shane

It's the double single quotes after the 3 in id='3'' that is causing
that error message. Are you running the $id variable through the
mysql_real_esca pe_string() function? You only need that for
sring/varchar data.

[Joshua Ruppert]

Re: Update query issue...


ZafT wrote:

Well, I'm back...
>
I have the script working on other update pages, and the only difference is
the $show variable. It is currently a smallint (I have tried changing to
varchar and int with no luck). The id field is an int, and the syntax '$id'
works on a sister script (but that script does not have the '$show'
variable. I have tried the mysql_real_esca pe_string() bit proposed by Rik,
but to no avail. The result is an error like:
>
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 'show=1 WHERE
id='3'' at line 3
>
Any more thoughts? This is killing me. It has to be something completely
rediculous.
>
Shane

LOL I read the error message wrong. ZafT has it right.

[ZafT]

Re: Update query issue...

Aha, there's th error. Well, 'show' is a mysql function. If your field is

named 'show', use backticks like `show` to use it as a fieldname.
>

Oh my! Thanks to both of you for your help! That was it. I knew it had to
be somthing rediculous. Time to start naming my table fields
something_somet hing instead of stuff like "show" - anyway, you rock. Thanks
again.

Shane