Updating Database Using PHP & MySql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
RossDolan
Forum Commoner
Posts: 31
Joined: Tue Jul 19, 2011 5:44 am

Updating Database Using PHP & MySql

Post 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';
}
?>
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: Updating Database Using PHP & MySql

Post 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.
Last edited by tr0gd0rr on Tue Jul 31, 2012 10:18 am, edited 2 times in total.
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: Updating Database Using PHP & MySql

Post 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 =..."
RossDolan
Forum Commoner
Posts: 31
Joined: Tue Jul 19, 2011 5:44 am

Re: Updating Database Using PHP & MySql

Post 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.
RossDolan
Forum Commoner
Posts: 31
Joined: Tue Jul 19, 2011 5:44 am

Re: Updating Database Using PHP & MySql

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Updating Database Using PHP & MySql

Post 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.
RossDolan
Forum Commoner
Posts: 31
Joined: Tue Jul 19, 2011 5:44 am

Re: Updating Database Using PHP & MySql

Post 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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Updating Database Using PHP & MySql

Post 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.
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: Updating Database Using PHP & MySql

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Updating Database Using PHP & MySql

Post 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.
Post Reply