[SOLVED] Update database using PHP/HTML form - not working

[rhiaro]

Fairly new to PHP and MySQL and I have knocked together the beginnings of a solution to what I need using various tutorials etc. 

 

What I need is a database, for which the entries can be updated using a HTML form  (ultimately, the form needs to be able to add/delete/update records, as well as upload images, but I'll worry about the rest later).

 

My database so far:

-- phpMyAdmin SQL Dump
-- version 2.11.9.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 08, 2009 at 05:38 PM
-- Server version: 5.0.67
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `rhiaroco_siren`
--

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

--
-- Table structure for table `showinfo`
--

CREATE TABLE IF NOT EXISTS `showinfo` (
  `id` mediumint(4) NOT NULL auto_increment,
  `Day` varchar(9) NOT NULL default '0',
  `StartTime` time NOT NULL default '00:00:00',
  `EndTime` time NOT NULL default '00:00:00',
  `ShowName` varchar(100) NOT NULL default '0',
  `DJ` varchar(100) NOT NULL default '0',
  `Image` blob NOT NULL,
  `Thumbnail` mediumblob NOT NULL,
  `ShowDesc` varchar(500) NOT NULL default '0',
  `DJInfo` varchar(500) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `showinfo`
--

INSERT INTO `showinfo` (`id`, `Day`, `StartTime`, `EndTime`, `ShowName`, `DJ`, `Image`, `Thumbnail`, `ShowDesc`, `DJInfo`) VALUES
(1, 'Monday', '21:00:00', '21:59:59', 'Silver Sounds', 'Calum Fuller', ' ', 'Calum, Calum, oh Calum', 'Your name it rhymes with Gallum??');
INSERT INTO `showinfo` (`id`, `Day`, `StartTime`, `EndTime`, `ShowName`, `DJ`, `Image`, `Thumbnail`, `ShowDesc`, `DJInfo`) VALUES
(2, '0', '11:00:00', '00:00:00', 'Dave Bussey Show', 'Dave Bussey', '', '', '0', '0');

 

A form to choose which record to update (this works fine):

http://siren.rhiaro.co.uk/choose_show_to_update.php.txt

 

The form to update the chosen record:

http://siren.rhiaro.co.uk/showupdate.php.txt

 

The postupdate.php file:

http://siren.rhiaro.co.uk/postupdate.php.txt

 

(I had to post links to the code as posting the code resulting in my post being too long )

 

As I said, when you change the info in the form, and submit, it says that the database has been updated, but actually nothing has changed at all.

 

All help much appreciated

 

Thanks in advance,

 

Amy

[phpdragon]

instead of using mysql_query($query); after the UPDATE try using $result = mysql_query($query);

 

[rhiaro]

Replacing that line made no difference.  Is there something else I need to change/add in as well? 

 

Thanks again

[uniflare]

If you can just post "postupdate.php", should be able to work from there .

 

Try changing:

<?php
//...

$query="UPDATE showinfo SET ".
"ShowName= \"".$formVars["ShowName"]."\",".
"DJ= \"".$formVars["DJ"]."\",".
"Day= \"".$formVars["Day"]."\",".
"StartTime= \"".$formVars["StartTime"]."\",".
"EndTime= \"".$formVars["EndTime"]."\",".
"ShowDesc= \"".$formVars["ShowDesc"]."\",".
"DJInfo= \"".$formVars["DJInfo"]."\",".

"\" WHERE id = \"".$formVars["id"]."\"";
mysql_query($query);
mysql_close($db1);

//...

 

So you can debug it like this:

<?php
// ...

$query="UPDATE showinfo SET ".
"ShowName= \"".$formVars["ShowName"]."\",".
"DJ= \"".$formVars["DJ"]."\",".
"Day= \"".$formVars["Day"]."\",".
"StartTime= \"".$formVars["StartTime"]."\",".
"EndTime= \"".$formVars["EndTime"]."\",".
"ShowDesc= \"".$formVars["ShowDesc"]."\",".
"DJInfo= \"".$formVars["DJInfo"]."\",".

"\" WHERE id = \"".$formVars["id"]."\"";
// First DEBUG, check out what the query looks like:
echo("<br />$query<br />");

mysql_query($query) or die ($query."<br />".mysql_error()."<br />"); // an OR DIE() is always useful to catch those simple mysql logic errors 
mysql_close($db1); // do not need this line unless you are connecting to different mysql host servers.

// ...

 

Tell us what it says, and we can work backwards from there .

[uniflare]

You will notice in the query it gives the "where id='' " should be something like, "where id='1' ".

 

So its missing the ID for the ROW to update, so it doesn't update any, but it doesn't error, so.

 

You need to add a "Hidden" type of input field: <input type="hidden"> with a value as the ID of the record that is being modified;

 

[] Add this line into showupdate.php

<?php /* This adds a "hidden" input field onto the form, (must be between <form> tags) */ ?>
<input type="hidden" name="id" value="<?php echo($formVars["id"]); ?>">

[rhiaro]

I added that line, as well as adding

$formVars["id"]=$row["id"];

to showupdate.php

 

which now gives me the message:

UPDATE showinfo SET ShowName= "Silver Sounds",DJ= "Calum Fuller",Day= "Monday",StartTime= "21:00:00",EndTime= "21:59:59",ShowDesc= "Calum, Calum, oh Calum",DJInfo= "This text is updated"," WHERE id = "1"

UPDATE showinfo SET ShowName= "Silver Sounds",DJ= "Calum Fuller",Day= "Monday",StartTime= "21:00:00",EndTime= "21:59:59",ShowDesc= "Calum, Calum, oh Calum",DJInfo= "This text is updated"," WHERE id = "1"

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

- So it now displayes id="1", but still doesn't actually update the record.  I can't figure out what this syntax error is, either...

 

Thanks again

[uniflare]

the error is here:

 

DJInfo= "This text is updated"," WHERE id = "1"

 

rid urself of the ,"

 

<?php

// Add this code, mysql_real_escape_string basically prevents SQL injection when working with insertion etc.
Foreach($formVars As $Key=>$Value){
      // So, this will loop each form variable, and temprorarily set the $key to the name of the variable, and $Value to the... value..
      
      // This basically sets variables from the formvars array, $formVars['name'] will become $name, and so on..
      $$Key = mysql_real_escape_string($Value);
}

// Change the query to a more standard structure. And remove the error mentioned above.
$query="UPDATE `showinfo` SET 
      `ShowName`= '$ShowName',
      `DJ`= '$DJ',
     ` Day`= '$Day',
     ` StartTime`= '$StartTime',
     ` EndTime`= '$EndTime',
     ` ShowDesc`= '$ShowDesc',
     ` DJInfo`= '$DJInfo'

      WHERE `id` = '$id'
";

?>

 

hope this helps

[rhiaro]

Thanks for the help everyone.  I still couldn't figure it out, however, so I started again, building it up bit by bit.  I'm just posting my solution in case anyone else has had the same problem:

 

The form page:

<?php
foreach($HTTP_POST_VARS as $varname => $value)
$formVars[$varname]=$value;


mysql_connect("localhost", "rhiaroco_siren", "password") or die("Connection Failed");

mysql_select_db("rhiaroco_siren")or die("Connection Failed");


$query="SELECT * FROM showinfo WHERE id = \"".$formVars["id"]."\"";
$result=mysql_query($query);
$row=mysql_fetch_array($result);
$formVars = array();
$formVars["ShowName"]=$row["ShowName"];
$formVars["DJ"]=$row["DJ"];
$formVars["Day"]=$row["Day"];
$formVars["StartTime"]=$row["StartTime"];
$formVars["EndTime"]=$row["EndTime"];
$formVars["ShowDesc"]=$row["ShowDesc"];
$formVars["DJInfo"]=$row["DJInfo"];

$formVars["id"]=$row["id"];


?>

<html>
<form method="post" name="update" action="update.php" />

Show Name: <input type="text" name="ShowName" value="<? echo ($formVars["ShowName"]); ?>" />
<br />
Presenter: <input type="text" name="DJ" value="<? echo ($formVars["DJ"]); ?>" />
<br />
Days: <input type="text" name="Day" value="<? echo ($formVars["Day"]); ?>" />
<br />
From: <input type="text" name="StartTime" value="<? echo ($formVars["StartTime"]); ?>" />
<br />
Until: <input type="text" name="EndTime" value="<? echo ($formVars["EndTime"]); ?>" />
<br />
Show Description: <input type="text" name="ShowDesc" value="<? echo ($formVars["ShowDesc"]); ?>" />
<br />
DJ Info: <input type="text" name="DJInfo" value="<? echo ($formVars["DJInfo"]); ?>" />

<input type="hidden" name="id" value="<? echo($formVars["id"]); ?>">

<input type="submit" name="Submit" value="update" />
</form>
</html>

 

The update page:

<?php
mysql_connect("localhost", "rhiaroco_siren", "s1ren2") or die("Connection Failed");

mysql_select_db("rhiaroco_siren")or die("Connection Failed");

$id = $_POST['id'];
$ShowName = $_POST['ShowName'];
$DJ = $_POST['DJ'];
$Day = $_POST['Day'];
$StartTime = $_POST['StartTime'];
$EndTime = $_POST['EndTime'];
$ShowDesc = $_POST['ShowDesc'];
$DJInfo = $_POST['DJInfo'];



$query = "UPDATE showinfo SET ".
"ShowName = '$ShowName'".",".
"DJ = '$DJ'".",".
"Day = '$Day'".",".
"StartTime = '$StartTime'".",".
"EndTime = '$EndTime'".",".
"ShowDesc = '$ShowDesc'".",".
"DJInfo = '$DJInfo'".

"WHERE id = '$id'";

if(mysql_query($query)){
echo "Information Updated.<br>Click <a href=\"choose_show_to_update.php\">here</a> to update another show.";}
else{
echo "Could not be updated";}
?>