Page 1 of 1

Updating Database Using PHP & MySql

Posted: Mon Jul 30, 2012 10:50 am
by RossDolan
Hello,

I am currently trying to make a page where the user can update the content themselves. I have managed to get one of the pages working however the second one is not and for the sake of me I cannot figure out what it is. I have attached the code below. All of the querys seem to be working fine, except the $editwww query. I you notice anything that would help that would be great, cheers.

Code: Select all

<?php
if ($_SESSION['uid'])
{
include 'dbconnect.php';

$now = time();
$a = $_GET['a'];
$wwwuid = $_POST['wwwid'];
$wwwtext = $_POST['wwwtext'];
//$wwwheader = $_POST['wwwheader'];

if ($a == 'edit')
	{
		$editwww = mysql_query("UPDATE www SET Text = '$wwwtext' WHERE ID = '$wwwuid'");
		echo $wwwtext;
	}
	
?>

<table border="1">
<tr>
<th>ID</th>
<th>Text</th>
<th></th>

</tr>

<?php

$wquery = mysql_query ('SELECT * FROM www ORDER BY ID DESC LIMIT 5');

if (!$wquery)
{
	die ('<p> Error performing query: ' .mysql_error() . '</p>');
}


while($wwwq = mysql_fetch_array($wquery))
{
	$wwwid = $wwwq['ID'];
	$wwwtext = $wwwq['Text'];
	//$wwwheader = $wwwq ['Header'];

echo ("<tr>\n<td>" . $wwwid . "</td>\n");

?>

<form method="post" action="AED.php?aedp=aedwww1&a=edit">

<input name="wwwid" type="hidden" value="<?php echo $wwwid ?>"  />
<td>
<textarea name="wwwtext" cols="50" rows="20"><?php echo $wwwtext ?></textarea>
</td>
<td>
<input name="Submit" type="submit" value="Submit Changes" />
</td>

<?php
}
?>
</form>
</table>

<?php
}
else 
{
	echo 'Unathorized Access';
}
?>

Re: Updating Database Using PHP & MySql

Posted: Mon Jul 30, 2012 1:59 pm
by tr0gd0rr
If `$wwwtext` has an apostrophe in it, it will fail. You must run mysql_real_escape_string to escape such characters.

Actually it is absolutely critical to run mysql_real_escape_string on every value that you put into a query. It will protect you from SQL injection hacking attempts. Here is a oversimplified function you can use to make using mysql_real_escape_string a no-brainer.

Code: Select all

function mysql_bound_query($sql, $values = array()) {
	foreach ($values as $variable => $value) {
		$escapedValue = "'" . mysql_real_escape_string($value) . "'";
		$sql = str_replace(":$variable", $escapedValue, $sql);
	}
	return mysql_query($sql);
}
// Usage
$editwww = mysql_bound_query("UPDATE www SET Text = :wwwtext WHERE ID = :wwwid", $_POST);
Also note that if you use PDO, there are built in options for binding instead of using mysql_real_escape_string.

Re: Updating Database Using PHP & MySql

Posted: Mon Jul 30, 2012 2:01 pm
by tr0gd0rr
Also, you can run mysql_error() to find out what the error is. It might be that you need to quote `Text` with back ticks in "UPDATE www SET Text =..."

Re: Updating Database Using PHP & MySql

Posted: Tue Jul 31, 2012 7:29 am
by RossDolan
Thanks for that tr0gd0rr, I'll try out both of them. Still trying to get to grips with PHP and mySQL at the moment.

Re: Updating Database Using PHP & MySql

Posted: Wed Aug 01, 2012 7:56 am
by RossDolan
I tried echoing mysql_error() out directly after the $editwww query but no errors are returned. Its strange because it pulls the data from the database no problem but won't allow it to update. Can you let me know what back ticks are please? And I can try that out. Cheers

Re: Updating Database Using PHP & MySql

Posted: Wed Aug 01, 2012 4:04 pm
by califdon
Have you carefully checked to insure that the field name in the table is actually Text? Not text? Remember, both PHP and MySQL are very case sensitive.

Re: Updating Database Using PHP & MySql

Posted: Fri Aug 03, 2012 3:20 am
by RossDolan
Yeah I have checked all the fields match correctly. I have done a bit a debugging using the echo function to see if anything is being picked up and it appears that the problem is with: $wwwtext = $_POST['wwwtext']; as it doesn't seem to be picking up what is posted in the textarea specified. Have any of you heard of this happening before?

Re: Updating Database Using PHP & MySql

Posted: Fri Aug 03, 2012 12:37 pm
by califdon
RossDolan wrote:ICan you let me know what back ticks are please?
Back ticks are the punctuation mark that looks like a backwards apostrophe, it is the key just above the Tab key on most keyboards. It is this: `

Although usually you don't have to use back ticks around table names and column names, technically, they should be there. At first I thought "Text" might be a SQL reserved word, but it is not in the list of reserved words. Still, it's worth trying:

Code: Select all

$editwww = mysql_bound_query("UPDATE `www` SET `Text` = :wwwtext WHERE `ID` = :wwwid", $_POST);
You know, I just realized that you are using colons (:) and not using quotes around strings like wwwtext and wwwid. I don't recognize what the colon is doing, and assuming that these are literal strings, you must enclose them in single quotes. That's probably your problem.

Re: Updating Database Using PHP & MySql

Posted: Fri Aug 03, 2012 2:12 pm
by tr0gd0rr
The mysql_bound_query() code was my example above. The colons give a way to have named parameter binding a la Oracle. They get replaced in the mysql_bound_query() function

Re: Updating Database Using PHP & MySql

Posted: Fri Aug 03, 2012 3:44 pm
by califdon
tr0gd0rr wrote:The mysql_bound_query() code was my example above. The colons give a way to have named parameter binding a la Oracle. They get replaced in the mysql_bound_query() function
Thanks for that explanation. I've never used that technique. So my closing comment in the previous post doesn't apply.