Annoying PHP Error

MagSafe · 14 Apr 2006 at 13:07

Hi folks,

I noticed a really weird problem this morning, when I go to update a record in a database. Whats happening is I have 3 records, and when I update the 2nd record it then records these newly submitted details whilst over writing the 3rd villa, with the new details from the 2nd one. Basically so whenever I edit record 2, it becomes 2 & 3 ... and I can't figure out why!

This is the code I've code for it, and I really can't see anything wrong with it...was hoping someone on here could maybe have a look?

Thanks.

Edit: Could it possibly be because I am using brackets in a name? , as that's when the problem started

Code:

<strong>Edit Villa</strong>

<br />
<br />

<?php

	// Connect to the database server
	$dbcnx = @mysql_connect('*****', '*****', '*****');
	
	if(!$dbcnx)
	{
		exit('<p>Unable to connect to the database server at this time.</p>');
	}
	
	// Select the villa database
	if(!@mysql_select_db('*****'))
	{
		exit('<p>Unable to locate the villa database at this time.</p>');
	}
	
	if(isset($_POST['name'])):
	// The villa's details have been updated
	
	$thumbnail = $_POST['thumbnail'];
	$picture1 = $_POST['picture1'];
	$picture2 = $_POST['picture2'];
	$picture3 = $_POST['picture3'];
	$categoryid = $_POST['categoryid'];
	$name = $_POST['name'];
	$description = $_POST['description'];
	$pricing = $_POST['pricing'];
	$contact = $_POST['contact'];
	
	$sql = "UPDATE villas SET
			thumbnail='$thumbnail',
			picture1='$picture1',
			picture2='$picture2',
			picture3='$picture3',
			categoryid='$categoryid',
			name='$name',
			description='$description',
			pricing='$pricing',
			contact='$contact'
			WHERE villaid='$villaid'";
			
	if(@mysql_query($sql))
	{
		echo '<p>Villa details updated.</p>';
	}
	else
	{
		echo '<p>Error updating villa details: ' . mysql_error() . '</p>';
	}

?>
	
<?php
	
	else: // Allow the user to edit the villa
	
	$villaid = $_GET['villaid'];
	$villa = @mysql_query("SELECT thumbnail, picture1, picture2, picture3, categoryid, name, description, pricing, contact FROM villas WHERE villaid='$villaid'");
	
	if(!$villa)
	{
		exit('<p>Error fetching villa details: ' . mysql_error() . '</p>');
	}
	
	$villa = mysql_fetch_array($villa);
	
	$thumbnail = $villa['thumbnail'];
	$picture1 = $villa['picture1'];
	$picture2 = $villa['picture2'];
	$picture3 = $villa['picture3'];
	$categoryid = $villa['categoryid'];
	$name = $villa['name'];
	$description = $villa['description'];
	$pricing = $villa['pricing'];
	$contact = $villa['contact'];
	
	// Convert special characters for safe use as HTML attributes.
	$thumbnail = htmlspecialchars($thumbnail);
	$picture1 = htmlspecialchars($picture1);
	$picture2 = htmlspecialchars($picture2);
	$picture3 = htmlspecialchars($picture3);
	$categoryid = htmlspecialchars($categoryid);
	$name = htmlspecialchars($name);
	$description = htmlspecialchars($description);
	$pricing = htmlspecialchars($pricing);
	$contact = htmlspecialchars($contact);
	
?>

<form action="" method="post">	
	<table>
		<tr>
			<td>Bedrooms:</td>
			<td><input type="text" name="categoryid" value="<?php echo $categoryid; ?>" /></td>
		</tr>
		
		<tr>
			<td>Villa name:</td>
			<td><input type="text" name="name" value="<?php echo $name; ?>" /></td>
		</tr>

		<tr>
			<td>Thumbnail:</td>
			<td><input type="text" name="thumbnail" value="<?php echo $thumbnail; ?>" /> <em>(property/villaname/thumbnail.jpg)</em></td>
		</tr>
		
		<tr>
			<td>Picture 1:</td>
			<td><input type="text" name="picture1" value="<?php echo $picture1; ?>" /> <em>(property/villaname/image1.jpg)</em></td>
		</tr>

		<tr>
			<td>Picture 2:</td>
			<td><input type="text" name="picture2" value="<?php echo $picture2; ?>" /> <em>(property/villaname/image2.jpg)</em></td>
		</tr>

		<tr>
			<td>Picture 3:</td>
			<td><input type="text" name="picture3" value="<?php echo $picture3; ?>" /> <em>(property/villaname/image3.jpg)</em></td>
		</tr>
		
		<tr>
			<td>Description:</td>
			<td><textarea name="description" rows="10" cols="40"><?php echo $description; ?></textarea></td>
		</tr>
		
		<tr>
			<td>Pricing:</td>
			<td><input type="text" name="pricing" value="<?php echo $pricing; ?>" /></td>
		</tr>
		
		<tr>
			<td>Contact:</td>
			<td><input type="text" name="contact" value="<?php echo $contact; ?>" /></td>
		</tr>
		
		<tr>
			<td></td>
			<td><input type="submit" value="Update Villa" /></td>
		</tr>
	</table>
</form>

<?php endif; ?>

paulsheff · 14 Apr 2006 at 13:30

There's no $villaid = $_GET['villaid']; in the if(isset($_POST['name'])):

MagSafe · 14 Apr 2006 at 16:31

Hi Paul,

What do you mean exactly?

Beansprout · 14 Apr 2006 at 17:43

An UPDATE query will by default affect all rows in the table unless it's constrained by a WHERE clause. ie, you use the WHERE clause to specify a unique row, in this case by the villa ID.

However you haven't set $villaid so you need to add:

$villaid = $_POST['villaid'];

The rest of this script is horrendously insecure.

www.php.net/mysql_real_escape_string

www.php.net/intval

MagSafe · 14 Apr 2006 at 21:34

Hi again, still not entirely sure what you mean I'm afraid!

I sort of know what you mean, but not sure on where about to put it in the coding, I put it in just after the if statement starts, but now it won't update any new information at all.

And yeah I know the script isn't perfect ... or secure - I've been learning from a book for a few months so fairly new with combining php with mysql. I'm learning as I go so will definetly improve it when I get to grips with the language more.

Thanks again,

Steven.

Edit: Shouldn't I be saying "GET villaid" , instead of POST?

Code:

<?php

	// Connect to the database server
	$dbcnx = @mysql_connect('*****', '******', '******');
	
	if(!$dbcnx)
	{
		exit('<p>Unable to connect to the database server at this time.</p>');
	}
	
	// Select the villa database
	if(!@mysql_select_db('*****'))
	{
		exit('<p>Unable to locate the villa database at this time.</p>');
	}
	
	if(isset($_POST['name'])):
	// The villa's details have been updated
	
	$villaid = $_POST['villaid'];  // <-- Added it in here
	$thumbnail = $_POST['thumbnail'];
	$picture1 = $_POST['picture1'];
	$picture2 = $_POST['picture2'];
	$picture3 = $_POST['picture3'];
	$categoryid = $_POST['categoryid'];
	$name = $_POST['name'];
	$description = $_POST['description'];
	$pricing = $_POST['pricing'];
	$contact = $_POST['contact'];
	
	$sql = "UPDATE villas SET
			thumbnail='$thumbnail',
			picture1='$picture1',
			picture2='$picture2',
			picture3='$picture3',
			categoryid='$categoryid',
			name='$name',
			description='$description',
			pricing='$pricing',
			contact='$contact'
			WHERE villaid='$villaid'";
			
	if(@mysql_query($sql))
	{
		echo '<p>Villa details updated.</p>';
	}
	else
	{
		echo '<p>Error updating villa details: ' . mysql_error() . '</p>';
	}

?>
	
<?php
	
	else: // Allow the user to edit the villa
	
	$villaid = $_GET['villaid'];
	$villa = @mysql_query("SELECT thumbnail, picture1, picture2, picture3, categoryid, name, description, pricing, contact FROM villas WHERE villaid='$villaid'");
	
	if(!$villa)
	{
		exit('<p>Error fetching villa details: ' . mysql_error() . '</p>');
	}
	
	$villa = mysql_fetch_array($villa);
	
	$thumbnail = $villa['thumbnail'];
	$picture1 = $villa['picture1'];
	$picture2 = $villa['picture2'];
	$picture3 = $villa['picture3'];
	$categoryid = $villa['categoryid'];
	$name = $villa['name'];
	$description = $villa['description'];
	$pricing = $villa['pricing'];
	$contact = $villa['contact'];
	
	// Convert special characters for safe use as HTML attributes.
	$thumbnail = htmlspecialchars($thumbnail);
	$picture1 = htmlspecialchars($picture1);
	$picture2 = htmlspecialchars($picture2);
	$picture3 = htmlspecialchars($picture3);
	$categoryid = htmlspecialchars($categoryid);
	$name = htmlspecialchars($name);
	$description = htmlspecialchars($description);
	$pricing = htmlspecialchars($pricing);
	$contact = htmlspecialchars($contact);
	
?>

<form action="" method="post">	
	<table>
		<tr>
			<td>Bedrooms:</td>
			<td><input type="text" name="categoryid" value="<?php echo $categoryid; ?>" /></td>
		</tr>
		
		<tr>
			<td>Villa name:</td>
			<td><input type="text" name="name" value="<?php echo $name; ?>" /></td>
		</tr>

		<tr>
			<td>Thumbnail:</td>
			<td><input type="text" name="thumbnail" value="<?php echo $thumbnail; ?>" /> <em>(property/villaname/thumbnail.jpg)</em></td>
		</tr>
		
		<tr>
			<td>Picture 1:</td>
			<td><input type="text" name="picture1" value="<?php echo $picture1; ?>" /> <em>(property/villaname/image1.jpg)</em></td>
		</tr>

		<tr>
			<td>Picture 2:</td>
			<td><input type="text" name="picture2" value="<?php echo $picture2; ?>" /> <em>(property/villaname/image2.jpg)</em></td>
		</tr>

		<tr>
			<td>Picture 3:</td>
			<td><input type="text" name="picture3" value="<?php echo $picture3; ?>" /> <em>(property/villaname/image3.jpg)</em></td>
		</tr>
		
		<tr>
			<td>Description:</td>
			<td><textarea name="description" rows="10" cols="40"><?php echo $description; ?></textarea></td>
		</tr>
		
		<tr>
			<td>Pricing:</td>
			<td><input type="text" name="pricing" value="<?php echo $pricing; ?>" /></td>
		</tr>
		
		<tr>
			<td>Contact:</td>
			<td><input type="text" name="contact" value="<?php echo $contact; ?>" /></td>
		</tr>
		
		<tr>
			<td></td>
			<td><input type="submit" value="Update Villa" /></td>
		</tr>
	</table>
</form>

<?php endif; ?>

paulsheff · 14 Apr 2006 at 23:03

Where you've put the $villaid = $_POST['villaid']; is fine, but you'll need to put

Code:

<input name="villaid" type="hidden" id="villaid" value="<?php echo $villaid; ?>">

in your form to pass it over.

The only reason I suggested using $villaid = $_GET['villaid']; is because you're posting back to the same page, so $villaid = $_GET['villaid']; should still be available, if as Beansprout said, a touch insecure.

MagSafe · 14 Apr 2006 at 23:50

Working perfectly now

Thanks again for the help

, much appreciated