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

QUERY AND UPDATE PROBLEM

P: n/a
Lag
Having a problem updating my database from a web page, through a
submission form. Can anyone help?

----THIS IS MY CODE IN update.php----(user, pass, and database are
typed in directly, I changed them here for securiy reasons :) )

<?php

/*---CONNECT TO DATABASE---*/

$conn = mysql_connect("localhost", "user", "pass") or
die(mysql_error());
mysql_select_db("database",$conn) or die(mysql_error());

$event = $_GET["id"];
$title=$_POST['title'];
$date=$_POST['datetime'];
$desc=$_POST['desc'];

//---UPDATE ENTRY IN DATABASE---
$query = "UPDATE 'calendar_events' SET 'event_title' = '$title',
'event_start' = '$date', 'event_shortdesc' = '$desc' WHERE 'id' =
$event";

//LIST ITEMS
echo "$event $title $date $desc";

mysql_query($query);
echo "Record Updated";
mysql_close();

?>

-----------------------THIS IS THE
OUTPUT-------------------------------------
TEST changed 2006-02-22 06:00:00 TESTING EDITINGRecord Updated
--------------------------------------------------------------------------------------------
The original information was "TEST 2006-02-22 06:00:00 TESTING EDITING"
I changed "TEST" to "TEST changed" in the submit form on a previous
page.

I included the echo to see if my information was truly getting passed,
it is. I passed the id, title, event_start, and event_shortdesc from
the previous page where the submission form is located. Why isn't my
database entry (row) updating? Please help.

Feb 23 '06 #1
Share this Question
Share on Google+
31 Replies


P: n/a
"Lag" <la*****@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
Having a problem updating my database from a web page, through a
submission form. Can anyone help?

----THIS IS MY CODE IN update.php----(user, pass, and database are
typed in directly, I changed them here for securiy reasons :) )

<?php

/*---CONNECT TO DATABASE---*/

$conn = mysql_connect("localhost", "user", "pass") or
die(mysql_error());
mysql_select_db("database",$conn) or die(mysql_error());

$event = $_GET["id"];
$title=$_POST['title'];
$date=$_POST['datetime'];
$desc=$_POST['desc'];

//---UPDATE ENTRY IN DATABASE---
$query = "UPDATE 'calendar_events' SET 'event_title' = '$title',
'event_start' = '$date', 'event_shortdesc' = '$desc' WHERE 'id' =
$event";

//LIST ITEMS
echo "$event $title $date $desc";

mysql_query($query)


put:
or die(mysql_error());
here and see what the problem is.

Debugging is a difficult skill to learn.

--
"En ole paha ihminen, mutta omenat ovat elinkeinoni." -Perttu Sirviö
sp**@outolempi.net | Gedoon-S @ IRCnet | rot13(xv***@bhgbyrzcv.arg)
Feb 23 '06 #2

P: n/a
Don't quote the table name or field names. Only quote the variables for
example '$title'.

Feb 23 '06 #3

P: n/a
Lag wrote:
Having a problem updating my database from a web page, through a
submission form. Can anyone help?

----THIS IS MY CODE IN update.php----(user, pass, and database are
typed in directly, I changed them here for securiy reasons :) )

<?php

/*---CONNECT TO DATABASE---*/

$conn = mysql_connect("localhost", "user", "pass") or
die(mysql_error());
mysql_select_db("database",$conn) or die(mysql_error());

$event = $_GET["id"];
$title=$_POST['title'];
$date=$_POST['datetime'];
$desc=$_POST['desc'];

//---UPDATE ENTRY IN DATABASE---
$query = "UPDATE 'calendar_events' SET 'event_title' = '$title',
'event_start' = '$date', 'event_shortdesc' = '$desc' WHERE 'id' =
$event";

//LIST ITEMS
echo "$event $title $date $desc";

mysql_query($query);
echo "Record Updated";
mysql_close();

?>


require_once ("DB.php"); // PEAR::DB
$db =& DB::connect(/*db params*/);

$fields = array ("event_title" => $title,
"event_start" => $date,
"event_shortdesc" => $desc);
$res = $db->autoExecute("calendar_events",$fields,
DB_AUTOQUERY_UPDATE,
"id = " . $event);
if (DB::isError($res)) die ($res->getMessage());
echo "Record updated";

With a small change you can also use the autoExecute for inserts with
just about identical code without repeating yourself.
Feb 23 '06 #4

P: n/a
Lag wrote:
Having a problem updating my database from a web page, through a
submission form. Can anyone help?

----THIS IS MY CODE IN update.php----(user, pass, and database are
typed in directly, I changed them here for securiy reasons :) )

<?php

/*---CONNECT TO DATABASE---*/

$conn = mysql_connect("localhost", "user", "pass") or
die(mysql_error());
mysql_select_db("database",$conn) or die(mysql_error());

$event = $_GET["id"];
$title=$_POST['title'];
$date=$_POST['datetime'];
$desc=$_POST['desc'];

//---UPDATE ENTRY IN DATABASE---
$query = "UPDATE 'calendar_events' SET 'event_title' = '$title',
'event_start' = '$date', 'event_shortdesc' = '$desc' WHERE 'id' =
$event";

//LIST ITEMS
echo "$event $title $date $desc";

mysql_query($query);
echo "Record Updated";
mysql_close();

?>

-----------------------THIS IS THE
OUTPUT-------------------------------------
TEST changed 2006-02-22 06:00:00 TESTING EDITINGRecord Updated
--------------------------------------------------------------------------------------------
The original information was "TEST 2006-02-22 06:00:00 TESTING EDITING"
I changed "TEST" to "TEST changed" in the submit form on a previous
page.

I included the echo to see if my information was truly getting passed,
it is. I passed the id, title, event_start, and event_shortdesc from
the previous page where the submission form is located. Why isn't my
database entry (row) updating? Please help.


Why are you quoting the table names. This is VERY POOR programming
style and the database name (depending on database engine) Will
interpret tablea and 'tablea' as two different tables. Database entity
names are case insensitive.

As someone that has been around databases for a very long time, do not
quote anything but text columns in the database - and then you must in
order for it to work :).
Feb 23 '06 #5

P: n/a
Lag
Thanks for the help......will try these things to get it to work.
Haven't been doing this long, php mysql programming, so yeah my code is
going to be bad and messy.......LOL.

Feb 23 '06 #6

P: n/a
Lag
I removed the quotes and the same thing still happens......the
variables are passed but the database doesn't update.
Any other help you can give would be appreciated.

CODE: $query = "UPDATE calendar_events SET event_title = $title,
event_start = $date, event_shortdesc = $desc WHERE id = $event" or
die(mysql_error());

OUTPUT: test 2006-02-23 01:00:00 testingRecord Updated.
(original information: test 2006-02-23 01:00:00 testRecord Updated.)

Feb 24 '06 #7

P: n/a
Lag wrote:
I removed the quotes and the same thing still happens......the
variables are passed but the database doesn't update.
Any other help you can give would be appreciated.

CODE:
(modified for readability)

$query = "UPDATE calendar_events SET event_title = '" . $title . "',";
$query .= "event_start = " . $date . ", event_shortdesc = '".$desc."'";
$query .= " WHERE id = ". $event;

text should be enclosed in single quotes.
($title and $desc are text fields)

Can you give me the output of "show fields from calendar_events;"

You may also need to enclose the $date field....

or
die(mysql_error());
OUTPUT: test 2006-02-23 01:00:00 testingRecord Updated.
(original information: test 2006-02-23 01:00:00 testRecord Updated.)

Feb 24 '06 #8

P: n/a
Like i said in my earlier post, quote the variables in the query.

$query = "UPDATE calendar_events SET event_title = '$title',
event_start = '$date', event_shortdesc = '$desc' WHERE id = '$event'";

This part, "or die(mysql_error())" is NOT part of the query. It should
be used like this:
mysql_query($query) or die(mysql_error());

Feb 24 '06 #9

P: n/a
Lag
I tried and it still passes the variables but does not update.....I
even tried using the root user and password, thinking I didn't update
the user privileges in MySQL server. But that didn't work either.
Thanks for the help, any other ideas?

$event = $_GET["id"];
$title=$_POST['title'];
$date=$_POST['datetime'];
$desc=$_POST['desc'];

//---DELETE ENTRY FROM DATABASE---
$query = "UPDATE calendar_events SET event_title = '$title',
event_start = '$date', event_shortdesc = '$desc' WHERE id = '$event'";

//LIST ITEMS
echo "$event $title $date $desc";

@mysql_query($query) or die(mysql_error());
echo "Record Updated";
mysql_close();

Feb 24 '06 #10

P: n/a
Lag
//---DELETE ENTRY FROM DATABASE---

should be

//---UPDATE ENTRY IN DATABASE---

Feb 24 '06 #11

P: n/a
Lag
This is the ouput for......

$query = "UPDATE calendar_events SET event_title = '" . $title . "',";

$query .= "event_start = " . $date . ", event_shortdesc = '".$desc."'";

$query .= " WHERE id = ". $event;
test2 2006-02-23 01:00:00 testYou have an error in your SQL syntax.
Check the manual that corresponds to your MySQL server version for the
right syntax to use near '01:00:00, event_shortdesc = 'test' WHERE id
=' at line 1

Feb 24 '06 #12

P: n/a
Lag
Versions of software being used:

MySQL v3.23.49
PHP v4.4.1

Feb 24 '06 #13

P: n/a
Lag
I added a count ($count) to see if or how many rows were being
affected.............even though I am tagging them by id, which auto
increments for each new event. I am not selecting any row, even though
I know the id is correct. I went into the database to make sure.

This is the code and the result of running the code.

//---UPDATE ENTRY IN DATABASE---
$result = mysql_query("SELECT * FROM calendar_events WHERE
id='.$event'",$conn);
$count = mysql_num_rows($result);
$query = "UPDATE calendar_events SET event_title = '$title',
event_start = '$date', event_shortdesc = '$desc' WHERE id = '$result'";
mysql_query($query) or die(mysql_error());

//LIST ITEMS
echo "$event $title $date $desc";

echo "<br>Record Updated! Rows Affected: $count";
mysql_close();

?>

This is the result....

test2 2006-02-23 01:00:00 test
Record Updated! Rows Affected: 0

Feb 24 '06 #14

P: n/a
Lag wrote:
This is the ouput for......

$query = "UPDATE calendar_events SET event_title = '" . $title . "',";

$query .= "event_start = " . $date . ", event_shortdesc = '".$desc."'";

$query .= " WHERE id = ". $event;
test2 2006-02-23 01:00:00 testYou have an error in your SQL syntax.
Check the manual that corresponds to your MySQL server version for the
right syntax to use near '01:00:00, event_shortdesc = 'test' WHERE id
=' at line 1


It's telling you the same thing everyone else has been saying - although
not so clearly.

ALL non-numeric data MUST be within single quotes. And 01:00:00 is not
numeric data.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Feb 24 '06 #15

P: n/a
Lag
Sorry, I completely overlooked that..........I changed it and now am
getting this...............

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 '' at
line 1.

Thanks for the help.

Feb 24 '06 #16

P: n/a
Lag wrote:
I added a count ($count) to see if or how many rows were being
affected.............even though I am tagging them by id, which auto
increments for each new event. I am not selecting any row, even though
I know the id is correct. I went into the database to make sure.

This is the code and the result of running the code.

//---UPDATE ENTRY IN DATABASE---
$result = mysql_query("SELECT * FROM calendar_events WHERE
id='.$event'",$conn);
$count = mysql_num_rows($result);
$query = "UPDATE calendar_events SET event_title = '$title',
event_start = '$date', event_shortdesc = '$desc' WHERE id = '$result'";
mysql_query($query) or die(mysql_error());

//LIST ITEMS
echo "$event $title $date $desc";

echo "<br>Record Updated! Rows Affected: $count";
mysql_close();

?>

This is the result....

test2 2006-02-23 01:00:00 test
Record Updated! Rows Affected: 0


What is the type of column `id`? If it's numeric, then it should NOT be
in single quotes.

Also in your update statement $result is a result set, not an id. You
need to use "UPDATE calendar_events..... WHERE id= '$event'".

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Feb 24 '06 #17

P: n/a
Lag wrote:
Sorry, I completely overlooked that..........I changed it and now am
getting this...............

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 '' at
line 1.

Thanks for the help.


Echo your query before you run it to see if it's what you think it is.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Feb 24 '06 #18

P: n/a
Lag wrote:
This is the ouput for......

$query = "UPDATE calendar_events SET event_title = '" . $title . "',";

$query .= "event_start = " . $date . ", event_shortdesc = '".$desc."'";

$query .= " WHERE id = ". $event;
test2 2006-02-23 01:00:00 testYou have an error in your SQL syntax.
Check the manual that corresponds to your MySQL server version for the
right syntax to use near '01:00:00, event_shortdesc = 'test' WHERE id
=' at line 1


okay.. show us the entire script - remove any passwords, usernames etc...

add:

echo $query."<br>;

so we can see the query as the database sees it...

in
MySQL> show fields from calendar_events;

In your code, I do not see where you are selecting the data to see if it
changed or not (or are you doing this in MySql -- is so, copy/paste the
select statement and the result so we can see what you are doing.

Basic troubleshooting techniques -- make sure what you are contatenating
is what you think it should be...

Feb 24 '06 #19

P: n/a
What do you get when you run this code?

$query1 = "select * from calendar_events where id = '$event'";
$result = mysql_query($query1);
$data = mysql_fetch_assoc($result);
print "<pre>";
print_r($data);
print "</pre>";

Feb 24 '06 #20

P: n/a
Lag
<?php

/*---CONNECT TO DATABASE---*/

$conn = mysql_connect("localhost", "******", "******") or
die(mysql_error());
mysql_select_db("******",$conn) or die(mysql_error());

$event = $_GET["id"];
$title=$_POST['title'];
$date=$_POST['datetime'];
$desc=$_POST['desc'];

//---UPDATE ENTRY IN DATABASE---
$result = mysql_query("SELECT * FROM calendar_events WHERE
id='.$event'",$conn);
$count = mysql_num_rows($result);
$query = "UPDATE calendar_events SET event_title = '" . $title . "',
event_start = '" . $date . "', event_shortdesc = '".$desc."' WHERE id =
$event";
mysql_query($query) or die(mysql_error());

//LIST ITEMS
echo "$event $title $date $desc";

echo "<br>Record Updated! Rows Affected: $count";
mysql_close();

?>

Feb 27 '06 #21

P: n/a
Lag wrote:
<?php

/*---CONNECT TO DATABASE---*/

$conn = mysql_connect("localhost", "******", "******") or
die(mysql_error());
mysql_select_db("******",$conn) or die(mysql_error());

$event = $_GET["id"];
$title=$_POST['title'];
$date=$_POST['datetime'];
$desc=$_POST['desc'];

//---UPDATE ENTRY IN DATABASE---
$result = mysql_query("SELECT * FROM calendar_events WHERE
id='.$event'",$conn);
$count = mysql_num_rows($result);
$query = "UPDATE calendar_events SET event_title = '" . $title . "',
event_start = '" . $date . "', event_shortdesc = '".$desc."' WHERE id =
$event";
mysql_query($query) or die(mysql_error());

//LIST ITEMS
echo "$event $title $date $desc";

echo "<br>Record Updated! Rows Affected: $count";
mysql_close();

?>

Why the double query? Why not:

//---UPDATE ENTRY IN DATABASE---
$query = "UPDATE calendar_events SET event_title = '$title',
event_start = '$date', event_shortdesc = '$desc' WHERE id = $event";
mysql_query($query) or die(mysql_error());
$count = mysql_affected_rows($result);
....

or, to make it more readable/maintainable:
//---UPDATE ENTRY IN DATABASE---
$query = <<<SQL
UPDATE calendar_events
SET
event_title = '$title',
event_start = '$date',
event_shortdesc = '$desc'
WHERE id = $event
SQL;
mysql_query($query) or die(mysql_error());
$count = mysql_affected_rows($result);

or if you don't like the use of <<<
//---UPDATE ENTRY IN DATABASE---
$sql = "UPDATE "
."calendar_events "
."SET "
."event_title = '$title', "
."event_start = '$date', "
."event_shortdesc = '$desc' "
."WHERE id = $event ";
mysql_query($query) or die(mysql_error());
$count = mysql_affected_rows($result);

Either of these last two methods allows you to add/subtract/edit the SQL
without having to walk a huge line of code.

-david-

Feb 27 '06 #22

P: n/a
David Haynes wrote:

or if you don't like the use of <<<
//---UPDATE ENTRY IN DATABASE---
$sql = "UPDATE "
."calendar_events "
."SET "
."event_title = '$title', "
."event_start = '$date', "
."event_shortdesc = '$desc' "
."WHERE id = $event ";
mysql_query($query) or die(mysql_error());
$count = mysql_affected_rows($result);

Either of these last two methods allows you to add/subtract/edit the SQL
without having to walk a huge line of code.

-david-


.... of course the query should be
mysql_query($sql) or die(mysql_error());

-david-

Feb 27 '06 #23

P: n/a
You are making this so difficult when it doesn't have to be.
Run this line of code and post the results! Don't include your own
code.

<?php
$event = $_GET['id'];
print "id for query is: $event<br />";
mysql_connect("localhost", "******", "******") or die("can't connect");
mysql_select_db("******") or die("can't select database");
$query = "select * from calendar_events where id = '$event'";
$result = mysql_query($query);
$data = mysql_fetch_assoc($result);
print "<pre>";
print_r($data);
print "</pre>";
?>

Feb 27 '06 #24

P: n/a
Lag
Sorry............thanks for the help.

This is the output...

id for query is:

Feb 27 '06 #25

P: n/a
Lag
I'm trying to pass the id via a button.........this is the code.....
<td colspan="2"><p><br>
<a href="update.php?id=<? echo $id; ?>">Update
Event?</a>
<input name="update" type="submit" id="update"
value="Update Event">
</p>
</td>

Using a text link, so I can see the id in the status bar.......I get
the result.
109
Record Updated! Rows Affected: 0

Where 109 is the id.

Feb 27 '06 #26

P: n/a
Were you opening my script from your form submission page?

And one more thing i just noticed in your code is this:
$event = $_GET["id"];
$title=$_POST['title'];
$date=$_POST['datetime'];
$desc=$_POST['desc'];
Why do you have $_GET["id"] and not $_POST["id"] ???
Is your from method set to "post" or set to "get"? This could be the
source of all your troubles.

Feb 27 '06 #27

P: n/a
Think about this. If you click on your link
"<a href="update.php?id=<? echo $id; ?>">Update Event?</a>", the only
thing passed to update.php will be the id. You can't do an update with
an id alone. The same thing goes for the form. The values "title",
"datetime", and "desc" are passed to update.php but id isn't unless you
specify a hidden field in your form with the value of id.

<input type="hidden" name="id" value="<? echo $id; ?>" />

Feb 27 '06 #28

P: n/a
Lag
Thanks for the example.....that makes sense.....if I use a form how do
I get the data, like this? The form is listed first!

<td><form name="edit" method="post" action="updated.php?id=<?php echo
$id; ?>&title=title&datetime=datetime&desc=desc">
<table width="300" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="100" align="left" valign="top">Event Title</td>
<td width="298"><input name="title" type="text" id="title"
value="<? echo $title; ?>" size="30"></td>
</tr>
<tr>
<td width="100">Date &amp; Time</td>
<td><input name="datetime" type="text" id="datetime"
value="<? echo $date; ?>" size="30"></td>
</tr>
<tr>
<td width="100">Description</td>
<td><textarea name="desc" cols="30" rows="5" wrap="VIRTUAL"
id="desc"><? echo $desc; ?></textarea></td>
</tr>
<tr>
<td colspan="2">
<p>&nbsp;</p><p>
<input name="update" type="submit" id="update"
value="Update Event">
</p>
</td>
</tr>
</table>
</form></td>

---------------------updated.php----------------------------

//id, title, datetime, desc are all names of the text fields in the
form on the previous page

$ud_id=$_POST['id'];
$ud_title=$_POST['title'];
$ud_date=$_POST['datetime'];
$ud_desc=$_POST['desc'];
//$ud_web=$_POST['ud_web'];

$query="UPDATE calendar_events SET event_title='$ud_title',
event_start='$ud_date', event_shortdesc='$ud_desc' WHERE id='$ud_id'";
mysql_query($query);
echo "Record Updated";
mysql_close();
?>

Feb 27 '06 #29

P: n/a
Lag
or is the data actually pulled with _POST?

Feb 27 '06 #30

P: n/a
Is there some reason you can't just do it like this?

<form method="post" action="updated.php">
ID <input name="id" type="text" value="<?php echo $id; ?>" /><br />
Title <input name="title" type="text" value="<?php echo $title; ?>"
/><br />
Date <input name="datetime" type="text" value="<?php echo $date; ?>"
/><br />
Desc <textarea name="desc" cols="30" rows="5" wrap="VIRTUAL"><?php echo
$desc; ?></textarea>
<input name="update" type="submit" value="Update Event">
</form>

$ud_id=$_POST['id'];
$ud_title=$_POST['title'];
$ud_date=$_POST['datetime'];
$ud_desc=$_POST['desc'];

Feb 27 '06 #31

P: n/a
Lag
No........that's the way I was doing it.........

with
$ud_id=$_POST['id'];
$ud_title=$_POST['title'];
$ud_date=$_POST['datetime'];
$ud_desc=$_POST['desc'];
on updated.php

it still doesn't update though...............any suggestions?

Feb 27 '06 #32

This discussion thread is closed

Replies have been disabled for this discussion.