By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,852 Members | 955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,852 IT Pros & Developers. It's quick & easy.

Update query issue...

P: n/a
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,$username,$password)
or die( "Unable to connect");
@mysql_select_db($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 "$description <BR>";
echo "$show <BR>";

$query="SELECT * FROM project WHERE name = '$name'";
$result=mysql_query($query);
$num=mysql_numrows($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=mysql_result($result,0,"id");
if(!($id==$conflictid)){
die("Error: There is already a project with that
name in the database. Please try again.");
}
else{

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

echo "Record Updated";

}

}

else{

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

echo "Record Updated";

}

mysql_close();
?>

</TD>
</TR>
</TABLE>
<?php
require('footer.php');
?>
Aug 14 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a

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,$username,$password)
or die( "Unable to connect");
@mysql_select_db($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 "$description <BR>";
echo "$show <BR>";

$query="SELECT * FROM project WHERE name = '$name'";
$result=mysql_query($query);
$num=mysql_numrows($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=mysql_result($result,0,"id");
if(!($id==$conflictid)){
die("Error: There is already a project with that
name in the database. Please try again.");
}
else{

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

echo "Record Updated";

}

}

else{

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

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

Aug 14 '06 #2

P: n/a
>
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,$username,$password)
or die( "Unable to connect");

<snip .....>

$query="UPDATE project SET name='$name',
description='$description',
show='$show'
WHERE id=$id";
mysql_query($query);
mysql_error($link);

No errors. Is that what you meant?

Shane
Aug 14 '06 #3

P: n/a
Rik
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,$username,$password)
or die( "Unable to connect");
@mysql_select_db($database)
or die( "Unable to select database");

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

$id = mysql_real_escape_string($_POST['id']);//etc...

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

Grtz,
--
Rik Wasmus
Aug 14 '06 #4

P: n/a

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,$username,$password)
or die( "Unable to connect");

<snip .....>

$query="UPDATE project SET name='$name',
description='$description',
show='$show'
WHERE id=$id";
mysql_query($query);
mysql_error($link);

No errors. Is that what you meant?

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

print(mysql_error($link));

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

$qryErr = mysql_error($link);

Josh

Aug 14 '06 #5

P: n/a
>
You're not actually capturing the mysql_error() output. Try either:

print(mysql_error($link));

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

$qryErr = mysql_error($link);

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
Aug 14 '06 #6

P: n/a

ZafT wrote:

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

print(mysql_error($link));

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

$qryErr = mysql_error($link);

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)

Aug 14 '06 #7

P: n/a
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_escape_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
Aug 14 '06 #8

P: n/a
Rik
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_escape_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
Aug 14 '06 #9

P: n/a

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_escape_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_escape_string() function? You only need that for
sring/varchar data.

Aug 14 '06 #10

P: n/a

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_escape_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.

Aug 14 '06 #11

P: n/a
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_something instead of stuff like "show" - anyway, you rock. Thanks
again.

Shane
Aug 14 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.