updating mysql via php form problem - PHP Server Side Scripting forum at WebmasterWorld - WebmasterWorld

Forum Moderators: coopster

Message Too Old, No Replies

updating mysql via php form problem

adeibiza

5:23 pm on Sep 3, 2010 (gmt 0)

10+ Year Member

i'm going round and round in circles on this one

should be pretty self-explanetory but i've got a bunch of simple records, the edit-date.php pulls in the details of the relevant record fine (ie edit-date.phpID=17) but no matter what i try i cant get the form to update the mysql DB - i know the variables are being passed as i wrote a simple show.php

i'm sure i've got some stupid glaring error - but i just cant see it

edit-date.php:

<?php
include '../config_cal.php';
include '../opendb.php';

$result = mysql_query("SELECT ID,djs,photo,comments,date FROM test WHERE ID='" . $_GET['ID'] . "'", $connection) or die("error querying database");
$i = 0;
while($result_ar = mysql_fetch_assoc($result)){

?>
<form method="post" action="update.php" >
<input name="ID" type="text" value="<?php echo $result_ar['ID']; ?>" size="4" />
<br>
<input name="date" type="text" value="<?php echo $result_ar['date']; ?>" size="20" /><br />
<input name="djs" type="text" value="<?php echo $result_ar['djs']; ?>" size="30" />
<br />
<input name="photo" type="text" value="<?php echo $result_ar['photo']; ?>" size="30" /><br>
<textarea rows="6" cols="35" name="comments"><?php echo $result_ar['comments']; ?></textarea>

<?php
$i+=1;
}
?>

<br/>
<input TYPE="Submit" >
</form>

the update.php file:

<?
if($_POST['Submit']){

$id=$_POST['ID'];
$djs=$_POST['djs'];
$date=$_POST['date'];
$photo=$_POST['photo'];
$coments=$_POST['comments'];
$db="test";
$link = mysql_connect("localhost", "", "");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());
mysql_query(" UPDATE test SET djs='$djs' , date='$date' , photo='$photo' , comments='$comments' WHERE ID='$id'");
echo "Record Updated";
mysql_close($link);
}
?>

Matthew1980

5:42 pm on Sep 3, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Hi there adeibiza,

Firstly:

Full tags for php please! (<?php ?>) Not all servers support short style tags, something as simple as this can stop a script dead - saves headaches later too.

Secondly:

<?php
if(isset($_POST['Submit'])){

$id = strip_tags($_POST['ID']);
$djs = strip_tags($_POST['djs']);
$date = strip_tags($_POST['date']);
$photo = strip_tags($_POST['photo']);
$coments = strip_tags($_POST['comments']);

Sanitise the data; Never let/use/trust the data that is user submitted, this can have devastating effects on your DB if you don't protect it.

Strip_tags() and mysql_real_escape_string() are about the two best functions that spring to mind.

Thirdly:

mysql_query("UPDATE `test` SET `djs` = '".$djs."', `date` = '".$date."', `photo` = '".$photo."', `comments` = '".$comments."' WHERE `ID` = '".$id."' ");

That should make the update work, but generally build the statement up outside the mysql_query() so that it is easier to debug (which you may need to do later on anyway) Back ticks are not mandatory but I find that it helps with sensitive column names, you can have reserved words & spaces in them using back ticks, not that I encourage this practise of using reserved words, but I prefer to put them in instead of retro fitting them after...

Have a play with that, I may have missed something, but I can smell my pizza cooking and the beer is chilling in the fridge :)

Cheers,
MRb

adeibiza

5:53 pm on Sep 3, 2010 (gmt 0)

10+ Year Member

cheers! will give it a go later as the footy is on :)

adeibiza

9:54 pm on Sep 3, 2010 (gmt 0)

10+ Year Member

nope - that didnt work :( - i cant see what would be wrong ive checked privileges, i dont get any connection errors...

this is the new version of update.php

<html><head><title></title></head>
<body>
<?php

if(isset($_POST['Submit'])){

$id = strip_tags($_POST['ID']);
$djs = strip_tags($_POST['djs']);
$date = strip_tags($_POST['date']);
$photo = strip_tags($_POST['photo']);
$coments = strip_tags($_POST['comments']);

$db="dbname";
$link = mysql_connect("localhost", "", "");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());
mysql_query("UPDATE `test` SET `djs` = '".$djs."', `date` = '".$date."', `photo` = '".$photo."', `comments` = '".$comments."' WHERE `ID` = '".$id."' ");
echo "Record Updated";
mysql_close($link);
}
?>
</body>
</html>

fyi when i change the link to this page it shows the variables passing ok and displays the "changed" details from the form

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<?php echo $_POST["ID"]; ?><br />
<?php echo $_POST["date"]; ?><br />
<?php echo $_POST["djs"]; ?><br />
<?php echo $_POST["photo"]; ?><br />
<?php echo $_POST["comments"]; ?>

</body>
</html>

adeibiza

10:18 pm on Sep 3, 2010 (gmt 0)

10+ Year Member

maybe this helps? - if i run :

UPDATE `test` SET `djs` = 'stuff' WHERE `ID` = 17

in phpmyadmin as a sql query it updates

but if i do it manually in the php file i get nada

<html><head><title></title></head>
<body>
<?php

if(isset($_POST['Submit'])){

$id = strip_tags($_POST['ID']);
$djs = strip_tags($_POST['djs']);
$date = strip_tags($_POST['date']);
$photo = strip_tags($_POST['photo']);
$coments = strip_tags($_POST['comments']);

$db="dbname";
$link = mysql_connect("localhost", "", "");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());
//mysql_query("UPDATE `test` SET `djs` = '".$djs."', `date` = '".$date."', `photo` = '".$photo."', `comments` = '".$comments."' WHERE `ID` = '".$id."' ");

mysql_query("UPDATE `test` SET `djs` = 'stuff' WHERE `ID` = 17 ");

echo "Record Updated";
mysql_close($link);
}
?>
</body>
</html>

its just plain weird to me

Matthew1980

11:04 pm on Sep 3, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Hi there adeibiza,

Firstly are you certain that there is a valid connection handle going? Because:-

$link = mysql_connect("localhost", "", "");
if (! $link)
die("Couldn't connect to MySQL");

looks like it's missing something, I know that you can leave out the braces ({}) but I always leave them in just because I think as it's easier to read.

But that little bit can be condensed into:-
(I'm assuming as you have a username & password set up for this, and that you have left the values blank on purpose here :-p)

$link = mysql_connect("localhost", "", "") or die(mysql_error());

but just be aware to remove it once you go live - because if you leave it it, it is a security risk, for development its fine though :)

As for the query, try something like this:-

$sqlQuery = "UPDATE `test` SET `djs` = 'stuff' WHERE `ID` = 17 ";
$querySent = mysql_query($sqlQuery, $link) or die(mysql_error());

if($querySent){
echo "Query sent successfully";
}
else{
echo "Something went wrong";
}

You get the idea there. Also, once you know you have a connection link going, you can actually leave that parameter blank because the subsequent calls inherit the last used connection, so you can explicitly define it (using multiple connections) or leave it blank, you'll see what I mean the more you use it :)

And yes, echoing the globals like that (so long as they have been set) will echo to screen, otherwise you will get a bunch of undefined index error's.

Hope that helps a bit anyway,

Cheers,
MRb

adeibiza

10:26 am on Sep 4, 2010 (gmt 0)

10+ Year Member

i've sussed it - i started from scratch and built it from a simple basic query that would update and then added bits in but i noticed 2 things - $comments =strip_tags was spelt $coments ;)

also i changed the the submit button from

<input type="Submit"> to
<input type="Submit" name="Submit" title="Submit">

anyway for anyone else here's the update page

<?php
if(isset($_POST['Submit'])){

$id = strip_tags($_POST['ID']);
$djs = strip_tags($_POST['djs']);
$date = strip_tags($_POST['date']);
$photo = strip_tags($_POST['photo']);
$comments = strip_tags($_POST['comments']);

// connect to the database
$con = mysql_connect('localhost','user','password')
or die('Could not connect to the server!');

// select a database:
mysql_select_db('name-of-db')
or die('Could not select a database.');

$sql = "UPDATE test SET `djs` = '".$djs."', `date` = '".$date."', `photo` = '".$photo."', `comments` = '".$comments."' WHERE ID='".$id."'";

// execute query:
$result = mysql_query($sql) or die('A error occured: ' . mysql_error());

echo "Record Updated - <a href='link.php'>link to something else</a>";
}

?>

Matthew1980

11:21 am on Sep 4, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

^^^
Doh! Spelink catchis us all owt occasionally! (that sounds like I have a cold though ;-P)

Also for extra security:-

$id = mysql_real_escape_string(strip_tags($_POST['ID']));
$djs = mysql_real_escape_string(strip_tags($_POST['djs']));
$date = mysql_real_escape_string(strip_tags($_POST['date']));
$photo = mysql_real_escape_string(strip_tags($_POST['photo']));
$comments = mysql_real_escape_string(strip_tags($_POST['comments']));

Then at least you can protect your information that little bit more now, glad you have sussed it out anyway.. Don't forget though that ANY posted data is interpreted as a string once sent to the receiver/processing script, if you want to do any numerical checking, you would need to typecast - though that's another lesson another day...

Cheers,
MRb