Help with php / mysql database update script

[adammc]

Hi,
I am trying to create an update script for my mysql database but cant get the thing to work

Heres the update script (update.php):

<?
// Connect to the database
$db = mysql_connect("localhost","user","pass");
mysql_select_db ("epidemic_test5");

// Ask the database for the information from the links table
$query="SELECT * FROM links WHERE id='$id'";
$result = mysql_query("SELECT * FROM links");
$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {
$date=mysql_result($result,$i,"date");
$venue=mysql_result($result,$i,"venue");
$info=mysql_result($result,$i,"info");

?>

<form action="updated.php">
<input type="hidden" name="ud_id" value="<? echo "$id";?>">
Date: <input type="text" name="ud_date" value="<? echo "$date"?>"><br>
Venue: <input type="text" name="ud_venue" value="<? echo "$venue"?>"><br>
Info: <input type="text" name="ud_info" value="<? echo "$info"?>"><br>

<input type="Submit" value="Update">
</form>

<?
++$i;
}
?>

-------------------------

Heres updated.php

<?php

$hostname="localhost";
$username="user";
$password="pass";
$database="epidemic_test5";

// Connect to the database
MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect");
@MYSQL_SELECT_DB("$database") OR DIE("Unable to select database");

// Ask the database for the information from the links table
$query="UPDATE links SET date='$ud_date', venue='$ud_venue', info='$ud_info' WHERE id='$ud_id'";
@mysql_select_db($database) or die( "Unable to select database");

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

-------------------------

I keep getting a blank page with "record updated" after pressing 'update' on update.php

Only problem is. Its not updating the database :(

I am only knew to PHP, therefore not really sure if my code is even in the right ballpark

Can anyone help?

[edited by: jatar_k at 5:48 am (utc) on Sep. 1, 2004]
[edit reason] generalized user and pass [/edit]

[figment88]

When a query doesn't work like you think it should, the best approach is generally to echo it (e.g. echo $query), so you can see if it is well formed.

In this case, I think you would find that your variables are not being populated (i.e. they are blank).

try:
$query="UPDATE links SET date=\"$_GET[ud_date]\", venue=\"$_GET[ud_venue]\", info=\"$_GET[ud_info]\" WHERE id=\"$_GET[ud_id]\"";

[adammc]

Hi figment88,

Thaks for your advice :)
However, I did as you suggested, am still getting the 'record updated' message but no changes made to the database :(

Any ideas?

[jatar_k]

Welcome to WebmasterWorld adammc,

try changing this bit

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

to

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

that should get you the error message from mysql and give us some more info.

[adammc]

Hi,
I changed that code as you suggested, all I got was a blank page with no error message?

[jatar_k]

hmmm, I would make a couple changes

change your form tag from
<form action="updated.php">

to
<form action="updated.php" method="post">

this way we can be sure that the data we are looking for will be in the $_POST superglobal array

then your script could be

<?php
$hostname="localhost";
$username="user";
$password="pass";
$database="epidemic_test5";

// Connect to the database
mysql_connect($hostname, $username, $password) OR DIE("Unable to connect");
mysql_select_db("$database") or die("Unable to select database");

$query="UPDATE links SET date='" . $_POST['ud_date'] . "', venue='" . $_POST['ud_venue'] . "', info='" . $_POST['ud_info'] . "' WHERE id='" . $_POST['ud_id'] . "'";
$checkresult = mysql_query($query);
if ($checkresult) echo 'update query succeeded';
else echo 'update query failed';

mysql_close();
?>

I removed some redundant lines
I removed the @ symbol which supresses errors
I changed the query itself
I grabbed the return value from mysql_query and tested it for success

[adammc]

Jatar,
Thanks for your help

Recieved a "update query succeeded " message
Its still no updating the database :(

Here's what I have at the moment :

Update.php
--------------

<?
// Connect to the database
$db = mysql_connect("localhost","user","pass");
mysql_select_db ("epidemic_test5");

// Ask the database for the information from the links table
$query="SELECT * FROM links WHERE id='$id'";
$result = mysql_query("SELECT * FROM links");
$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {
$date=mysql_result($result,$i,"date");
$venue=mysql_result($result,$i,"venue");
$info=mysql_result($result,$i,"info");

?>

<form action="updated.php" method="post">
<input type="hidden" name="ud_id" value="<? echo "$id";?>">
Date: <input type="text" name="ud_date" value="<? echo "$date"?>"><br>
Venue: <input type="text" name="ud_venue" value="<? echo "$venue"?>"><br>
Info: <input type="text" name="ud_info" value="<? echo "$info"?>"><br>

<input type="Submit" value="Update">
</form>

<?
++$i;
}
?>

Updated.php
--------------

<?php
$hostname="localhost";
$username="user";
$password="pass";
$database="epidemic_test5";

// Connect to the database
mysql_connect($hostname, $username, $password) OR DIE("Unable to connect");
mysql_select_db("$database") or die("Unable to select database");

$query="UPDATE links SET date='" . $_POST['ud_date'] . "', venue='" . $_POST['ud_venue'] . "', info='" . $_POST['ud_info'] . "' WHERE id='" . $_POST['ud_id'] . "'";
$checkresult = mysql_query($query);
if ($checkresult) echo 'update query succeeded';
else echo 'update query failed';

mysql_close();
?>

[jatar_k]

after this

$query="UPDATE links SET date='" . $_POST['ud_date'] . "', venue='" . $_POST['ud_venue'] . "', info='" . $_POST['ud_info'] . "' WHERE id='" . $_POST['ud_id'] . "'";

add
echo $query;

then take that output and try it directly in mysql using the commandline or something like phpmyadmin and see what mysql tells you.

[adammc]

Yep.. recieved an error :

Error

SQL-query :

UPDATE links SET date = '12th Sept',
venue = 'Heat',
info = 'testtesttesttets' WHERE id = '' UPDATE query succeeded

MySQL said:

#1064 - 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 'UPDATE query succeeded' at line 3

[jatar_k]

the id is blank so you will need to check why that is

also you might want to add this in the script.

replace these 2 lines

if ($checkresult) echo 'update query succeeded';
else echo 'update query failed';

with

if ($checkresult) echo '<p>update query succeeded';
else echo '<p>update query failed';

I think the <p> will help too ;)

The problem must be coming from the select on the original page and the id's aren't getting set in the hidden field. Take a look at the source of the original update.php page and you can confirm.

[adammc]

Yes the id's are blank :

<input type="hidden" name="ud_id" value="">

[Knowles]

Where does $id come from? You reference it twice, once to make the database query and second in the hidden tag. We have established that the hidden tag is showing up as blank. Is this being passed from a url or something of that nature? If so try adding at the top of the page "$id = $_GET['id'];" at each reference of $id change it to $_GET['id']