473,226 Members | 1,569 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

Update query issue...

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
11 2251

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

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

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

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

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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Karaoke Prince | last post by:
Hi There, I have an update statement to update a field of a table (~15,000,000 records). It took me around 3 hours to finish 2 weeks ago. After that no one touched the server and no...
6
by: David Shorthouse | last post by:
Hello folks, I have a problem with an update query on an asp not updating the table in an Access db. The code runs, I have no errors, but when I examine the table, nothing was updated. The query...
2
by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An...
1
by: Kunal | last post by:
Hi, I need some help on writing an update query to update "UnitsSold" field in Products Table whenever I save a transaction. The transaction may contain several "Subtransactions", one for each...
3
by: Megan | last post by:
hi everybody- it seems like my update query should work. the sql view seems logical. but that might be up to discussion...lol...i'm a newbie! UPDATE , Issue SET .IssueID = . WHERE ((.=.));
9
by: James Butler | last post by:
Our setup: Online db: MySQL Inhouse db: MS Access 97 with MySQL tables linked via ODBC Our issue: Almost every field updates successfully, except one. A scenario: Information is written to...
6
by: Sparticus | last post by:
I have a database that isn't very big. It has about 2400 rows in it. I try and do an update like this one below (it looks big, but it's really not if you look at it) : UPDATE jobs SET...
3
by: Greg Strong | last post by:
Hello All, Is the only solution to an update query which requires the records in a certain order to dump the records into a temp table in the required order, then do the update query? I've...
5
by: Alastair Anderson | last post by:
I have created a very simple form with which I would like to update a single value in a single row of a database as a proof of concept. The relevant parts of the form are a DBWebTextBox (which...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.