homepage Welcome to WebmasterWorld Guest from 54.161.175.231
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
updating mysql via php form problem
adeibiza




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

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




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

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




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

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

adeibiza




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

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




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

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




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

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




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

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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved