Editing MySQL Data with PHP

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »

50 Replies - 3440 Views - Last Post: 08 February 2009 - 09:40 PM Rate Topic: -----

#1 gregwhitworth  Icon User is offline

  • Tired.
  • member icon

Reputation: 219
  • View blog
  • Posts: 1,604
  • Joined: 20-January 09

Editing MySQL Data with PHP

Post icon  Posted 26 January 2009 - 09:49 PM

Ok, I am wanting my client to be able to edit information without inserting a new id. I am currently trying to just have the information re-inserted with the same id but that doesn't work (which I expected). I found a tutorial online - but it is really involved. Does anyone have a simple suggestion. Here is my php code for the submission:

<?php
	// --------------------
	// sqlConnection.php
	// November 6th, 2008
	// Greg Whitworth
	
	
	/**************************************************************************/
	// DOCUMENT NOTES
	//--------------------------------------------------------------------------
	
   
	// DESCRIPTION
	// -----------
	//   This script connects the application to the SQL Database
	
	// DEFINING DIAGRAM
	// ----------------
	// • INPUT
	// • PROCESS
	// • OUTPUT
	
	
	
	/**************************************************************************/
	// INCLUDES
	//--------------------------------------------------------------------------
	   
	   include "../store/sqlConnection.php";
	   
	/**************************************************************************/
	// VARIABLE DEFINITIONS
	//--------------------------------------------------------------------------

		$title = $_POST['title'];
		$description = $_POST['description'];
		$medium = $_POST['medium'];
		$price = $_POST['price'];
		$date = $_POST['date'];
		$edition = $_POST['edition'];
		$subject = $_POST['subject'];
		$orientation = $_POST['orientation'];
		$submit = $_POST['submit'];
		$id = $_POST['id'];
		$vars = array('$id', '$title', '$description', '$medium', '$price', '$date', '$edition', '$subject', '$orientation');
	
	/**************************************************************************/
	// PROCESS
	//--------------------------------------------------------------------------
	
		if(!isset($submit)) {
			print "You must submit the form before proceeding <a href=\"http://www.whitworthgallery.org\">Click here to return to Whitworth Gallery</a>";
			}
			
		else {
			mysql_query("UPDATE Art SET(id, title, description, medium, price, date, edition, subject, orientation) VALUES ('$id','$title','$description','$medium','$price','$date','$edition','$subject','$orientation')");
			header("Location:");}
			mysql_close();
	
	/**************************************************************************/
	// OUTPUT
	//--------------------------------------------------------------------------
	
	/**************************************************************************/
	// CHANGE LOG
	//--------------------------------------------------------------------------
	// GCW - added variables and created initial document [January 12, 2009]
	
?>


Please help me out if you can.

Thanks - Greg

Is This A Good Question/Topic? 0
  • +

Replies To: Editing MySQL Data with PHP

#2 no2pencil  Icon User is online

  • Dic Head
  • member icon

Reputation: 5166
  • View blog
  • Posts: 26,849
  • Joined: 10-May 07

Re: Editing MySQL Data with PHP

Posted 26 January 2009 - 09:55 PM

View Postgregwhitworth, on 26 Jan, 2009 - 10:49 PM, said:

mysql_query("UPDATE Art SET(id, title, description, medium, price, date, edition, subject, orientation) VALUES ('$id','$title','$description','$medium','$price','$date','$edition','$subject','$orientation')");



1st off, I would not throw (trust) the values from Post!

I would first validate them for malicious code (strip_tags ect).

This could be the reason why it's failing. Before the sql statement, print out the data, so you can visually see what you are inserting rather than throwing them in blind. Then if you see what's wrong, correct it. Empty fields, invalid data, that sort of thing.
Was This Post Helpful? 0
  • +
  • -

#3 gregwhitworth  Icon User is offline

  • Tired.
  • member icon

Reputation: 219
  • View blog
  • Posts: 1,604
  • Joined: 20-January 09

Re: Editing MySQL Data with PHP

Posted 26 January 2009 - 10:17 PM

Thanks for the reply no2pencil

Yeah I planned on doing that, seeing as how it isn't live to the world yet I didn't see the need to implement that now. But out of curiosity - how can that be the problem. I am having the form populated with the already current MySQL data and am changing one number, which isn't an issue when done in PHP MyAdmin. So logically, I don't see how throwing them in blind is the issue. You are shown what you updated by the browser refreshing and showing the updated information within the same form. Just curious I guess.

Other wise, is the Query update syntax correct in your eyes?

--
Greg
Was This Post Helpful? 0
  • +
  • -

#4 kiwi2  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 178
  • Joined: 16-September 08

Re: Editing MySQL Data with PHP

Posted 27 January 2009 - 12:32 AM

I don't know if I understand what u r doing here but if you want to update by id, u have posted over te id so alter your code to read
if(!isset($_POST['id']) )
{
echo "missing id number";
}
else
{
go on with your code here.
the mysql statement must read:-



$q = "UPDATE Art SET title = '$title', description = '$description', medium = '$medium', ect, all your fields WHERE id = '$id'";
$result = mysql_query($q);
remember to close your if statement.
Was This Post Helpful? 0
  • +
  • -

#5 sam_benne  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 16
  • View blog
  • Posts: 732
  • Joined: 16-January 08

Re: Editing MySQL Data with PHP

Posted 27 January 2009 - 12:44 AM

As I have tried doing an update the same as you gregwhitworth (fields) VALUES (data) but the update doesn't work with that you have to do it one at a time spliting them of with a comma. If you are updating only the number in id why are you changing the others?
Was This Post Helpful? 0
  • +
  • -

#6 no2pencil  Icon User is online

  • Dic Head
  • member icon

Reputation: 5166
  • View blog
  • Posts: 26,849
  • Joined: 10-May 07

Re: Editing MySQL Data with PHP

Posted 27 January 2009 - 12:57 AM

View Postgregwhitworth, on 26 Jan, 2009 - 11:17 PM, said:

But out of curiosity - how can that be the problem.

I'm not sure what values that you are trying to use.

To visually verify for yourself, try the following :


$query="UPDATE Art SET(id, title, description, medium, price, date, edition, subject, orientation) VALUES ('$id','$title','$description','$medium','$price','$date','$edition','$subject','$orientation');";
echo "Going to try the following: ".$query."<br>";
mysql_query($query) or die(mysql_error());


Was This Post Helpful? 1

#7 kiwi2  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 178
  • Joined: 16-September 08

Re: Editing MySQL Data with PHP

Posted 27 January 2009 - 01:13 AM

add error checking to the code above

$result = mysql_query($q) or die ("ERROR IN QUERY: $q . " . mysql_error());
Was This Post Helpful? 0
  • +
  • -

#8 kiwi2  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 178
  • Joined: 16-September 08

Re: Editing MySQL Data with PHP

Posted 27 January 2009 - 02:22 AM

<?php
include "config.php";
$title = $_POST['title'];
$description = $_POST['description'];
$medium = $_POST['medium'];
$price = $_POST['price'];
$date = $_POST['date'];
$edition = $_POST['edition'];
$subject = $_POST['subject'];
$orientation = $_POST['orientation'];
$submit = $_POST['submit'];
$id = $_POST['id'];
$vars = array('$id', '$title', '$description', '$medium', '$price', '$date', '$edition', '$subject', '$orientation');
 
if ($vars['0'] == ' ' || $vars['1'] == ' ' || $vars['2'] == ' ' || $vars['3'] == ' ' || $vars['4'] == ' ' || $vars['5'] == ' ' || $vars['6'] == ' ' || $vars['7'] == ' ' || $vars['8'] == ' ') 
{ 
echo "You must submit the form before proceeding"; } else {

$q = "UPDATE Art SET title = '$title', description = '$description', medium = '$medium', price = '$price', date = '$date', edition = '$edition', subject = '$subject', orientation = '$orientation' WHERE id = '$id'";


$result = mysql_query($q) or die ("ERROR IN QUERY: $q . " . mysql_error()); 
} 
 



?>



** Edit ** :code:
Was This Post Helpful? 1
  • +
  • -

#9 no2pencil  Icon User is online

  • Dic Head
  • member icon

Reputation: 5166
  • View blog
  • Posts: 26,849
  • Joined: 10-May 07

Re: Editing MySQL Data with PHP

Posted 27 January 2009 - 02:40 AM

If you are going to go that route, I would do more than just check for blank fields.

Set the variable w/ strip_tags, I would do something similar to the following :

<?php
	include "config.php";
	$err=0; // check value
	if(isset($_POST['title'])) {
		$title = strip_tags($_POST['title']);
	}
	else {
		$err=1;
	}
	if(isset($_POST['description'])) {
		$description = strip_tags($_POST['description']);
	}
	else {
		$err=2;
	}
	if(isset($_POST['medium'])) {
		$medium = strip_tags($_POST['medium']);
	}
	else {
		$err=3;
	}
	if(isset($_POST['price'])) {
		$price = strip_tags($_POST['price']);
	}
	else {
		$err=4;
	}
	if(isset($_POST['date'])) {
		$date = strip_tags($_POST['date']);
	}
	else {
		$err=5;
	}
	if(isset($_POST['education'])) {
		$edition = strip_tags($_POST['edition']);
	}
	else {
		$err=6;
	}
	if(isset($_POST['subject'])) {
		$subject = strip_tags($_POST['subject']);
	}
	else {
		$err=7;
	}
	if(isset($_POST['orientation'])) {
		$orientation = strip_tags($_POST['orientation']);
	}
	else {
		$err=8;
	}
	$submit = $_POST['submit']; // Is this manditory?
	if(isset($_POST['id'])) {
		$id = strip_tags($_POST['id']);
	}
	else {
		$err=9;
	}
	
	if($err>0) {
		switch ($i) {
			case 1:
				$errtext="The title field must contain a value.";
				break;
			case 2:
				$errtext="The description field must contain a value.";
				break;
		}
	}
	if(isset($errtext)) {
		echo "<p>There was an error in the given field : <br>".$errtext;
	}
	else {
		$q = "UPDATE Art SET title = '$title', description = '$description', medium = '$medium', price = '$price', date = '$date', edition = '$edition', subject = '$subject', orientation = '$orientation' WHERE id = '$id'";
		$result = mysql_query($q) or die ("ERROR IN QUERY: $q . " . mysql_error());
	}
}
?>



Just remember that $err & $errtext will only contain the last value. So if there are multiple errors, the user will have to reenter each of them. I'm sure that this could be made better with loops :)
Was This Post Helpful? 0
  • +
  • -

#10 no2pencil  Icon User is online

  • Dic Head
  • member icon

Reputation: 5166
  • View blog
  • Posts: 26,849
  • Joined: 10-May 07

Re: Editing MySQL Data with PHP

Posted 27 January 2009 - 02:55 AM

This is also good, because then in each check you add more strict or less strict checking.

For example, digits on a phone number, domain on an email, that sort of thing.
Was This Post Helpful? 0
  • +
  • -

#11 kiwi2  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 178
  • Joined: 16-September 08

Re: Editing MySQL Data with PHP

Posted 27 January 2009 - 07:02 AM

v nice no2pencil a loop would be good for testing, but your code is better, there is an error in mine, should read
f ($vars['0'] == '' || $¢vars['1' =='' ect

This post has been edited by kiwi2: 27 January 2009 - 07:07 AM

Was This Post Helpful? 1
  • +
  • -

#12 gregwhitworth  Icon User is offline

  • Tired.
  • member icon

Reputation: 219
  • View blog
  • Posts: 1,604
  • Joined: 20-January 09

Re: Editing MySQL Data with PHP

Posted 27 January 2009 - 09:01 AM

WOW - everyone thanks for the knowledge. I have just began to delve into the world of PHP so I greatly appreciate the info.

@sam_benne

I don't plan on updating only 1 field (sorry for writing that, I should have explained it better), but for testing purposes I was updating only 1 field.

@no2pencil

I really appreciate your insite into this. I understand that you're saying, "That if x field comes in blank set $err = y." But then you do the following:

 if($err>0) {
		switch ($i) {
			case 1:
				$errtext="The title field must contain a value.";
				break;
			case 2:
				$errtext="The description field must contain a value.";
				break;
		}
	}



Would you be willing to explain how the switch and case segments work?

Once again thanks for the help - I'll launch the site soon and show it on dreamincode.

--
Greg
Was This Post Helpful? 0
  • +
  • -

#13 no2pencil  Icon User is online

  • Dic Head
  • member icon

Reputation: 5166
  • View blog
  • Posts: 26,849
  • Joined: 10-May 07

Re: Editing MySQL Data with PHP

Posted 27 January 2009 - 08:32 PM

View Postgregwhitworth, on 27 Jan, 2009 - 10:01 AM, said:

Would you be willing to explain how the switch and case segments work?

Since the variable $err is set to zero at the start of your code, if all is successful, it will still be zero at the end. So I run it against a switch case to check it's value. Like I said before, this will only yield the most resent error though. The way it's used in my example allows you to setup customized errors based on what the error was. Rather than "You dumby, it isn't correct fix it" & prompt them again, you can say "this filed was blank or invalid" based on your validation.
Was This Post Helpful? 0
  • +
  • -

#14 gregwhitworth  Icon User is offline

  • Tired.
  • member icon

Reputation: 219
  • View blog
  • Posts: 1,604
  • Joined: 20-January 09

Re: Editing MySQL Data with PHP

Posted 27 January 2009 - 08:45 PM

Thanks no2pencil [also - great name]

I will work on this and let you know where I get - thanks to everyone!

--
Greg
Was This Post Helpful? 0
  • +
  • -

#15 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2889
  • View blog
  • Posts: 10,003
  • Joined: 08-August 08

Re: Editing MySQL Data with PHP

Posted 27 January 2009 - 08:46 PM

If you wanted to get really fancy, you could expand on no2pencil's code to display a list of errors:

$posterrs = array();
	if(isset($_POST['title'])) {
		$title = strip_tags($_POST['title']);
	}
	else {
$posterrs['title'] = " is empty!";
	}
	if(isset($_POST['description'])) {
		$description = strip_tags($_POST['description']);
	}
	else {
$posterrs['description'] = " is empty!";
	}
	if(isset($_POST['medium'])) {
		$medium = strip_tags($_POST['medium']);
	}
	else {
$posterrs['medium'] = " is empty!";
	}
	if(isset($_POST['price'])) {
		$price = strip_tags($_POST['price']);
	}
	else {
$posterrs['price'] = " is empty!";
	}
	if(isset($_POST['date'])) {
		$date = strip_tags($_POST['date']);
	}
	else {
$posterrs['date'] = " is empty!";
	}
	if(isset($_POST['education'])) {
		$edition = strip_tags($_POST['edition']);
	}
	else {
$posterrs['edition'] = " is empty!";
	}
foreach($posterrs as $key => $value) {
echo $key.$value."<br>";
}



I can't test this code right now, so it probably contains errors!
Was This Post Helpful? 0
  • +
  • -

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »