Using PHP to Edit MySQL fields

silverf0x

Has anyone got any example code of how to edit MySQL fields using PHP?

I tried searching Google and found a tutorial by Spoono which doesn't even work. Even after removing the extra dollar in this line which the tutorial expert added, ($_POST["$submit"]) I still cant get it to work.

Has anyone got any examples that work of how to edit rows?

englishtom

$sql = "UPDATE table_name
			SET
				table_col1 = '".$_POST['col1']."',
				table_col2 = '".$col2."'
			WHERE  

				table_id = '".$_GET['id']."'
		";

$result = mysql_query($sql)
or die('Could not edit row '.mysql_error());

Something like that?

Tom

silverf0x

Thanks for posting back. I tried that but it still doesn't work. I don't get any error, however, the row wasn't updated.

If I turn on error reporting, I get:

Notice: Undefined index: submit in H:\xampplite\htdocs\pages\news\editNews.php on line 48

When I make my changes and click submit, I get:

Notice: Undefined index: cmd in H:\xampplite\htdocs\pages\news\editNews.php on line 21
Notice: Undefined index: id in H:\xampplite\htdocs\pages\news\editNews.php on line 62

I assume its to do with register_globals being turned off by default in later versions of PHP but I dont know how to solve it. Here's the code I used:

<?
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
   if (!isset($_POST["submit"]))
   {
      $id = $_GET["id"];
      $sql = "SELECT * FROM news WHERE id=$id";
      $result = mysql_query($sql);
      $myrow = mysql_fetch_array($result);
      ?>

  <form action="editNews.php" method="post">
  <input type=hidden name="id" value="<?php echo $myrow["id"] ?>">

  Name:<INPUT TYPE="TEXT" NAME="name"
  VALUE="<?php echo $myrow["name"] ?>" SIZE=30><br>
  Start_date:<INPUT TYPE="TEXT" NAME="start_date"
  VALUE="<?php echo $myrow["start_date"] ?>" SIZE=30><br>
  Headline:<INPUT TYPE="TEXT" NAME="headline"
  VALUE="<?php echo $myrow["headline"] ?>" SIZE=30><br>
  Article:<TEXTAREA NAME="article" ROWS=10 COLS=30>
  <? echo $myrow["article"] ?></TEXTAREA><br>
  <input type="hidden" name="cmd" value="edit">

  <input type="submit" name="submit" value="submit">

  </form>

   <? }?>

<?
   if ($_POST["submit"])
   {
      $name = $_POST["name"];
      $start_date = $_POST["start_date"];
      $headline = $_POST["headline"];
      $article = $_POST["article"];

$sql = "UPDATE news
            SET
                name = '".$_POST['name']."',
                start_date = '".$start_date."',
                headline = '".$headline."',
                article = '".$article."'
            WHERE
                id = '".$_GET['id']."'
        ";

$result = mysql_query($sql)
or die('Could not edit row '.mysql_error());

   }
}
?>

I hope you can help

silverf0x

If nobody can help, does anyone know of an alternative script I can use? I just want to be able to edit MySQL rows.

nemonoman

If you are saying you want to edit MySQL rows and don't want to learn how to do it, then download phpMyAdmin or similar programs. They are fairly simple to install. However, be warned that you will need to actually follow the installation instructions.

Many linux distributions and linux hosts have already installed phpMyAdmin.

Otherwise look here

silverf0x

I did try a few turtorials, one of which can be found here doesn't even work because of a PHP error which even I was able to spot. After correcting the error it still didn't work. The other turtorial I read also relies on register_globals being turned on which I dont want to do.

I'm using PHPMyAdmin but I really want to find out how to do it properly but nobody seems to know.

I read that I need to use the $GET and $POST super globals but I've been trying for days and days to work it out.

nemonoman

Based on the code you have shown, it appears you have not connected to the database before attempting the update. Register Globals should not be a factor, as you are using already $_POST["submit"], etc.

Before attempting any database action, connect to the database. The tutorial gives these commands:

//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","user","password");

//select which database you want to edit
mysql_select_db("spoono_news");

silverf0x

My edit php page already connects to the database using an include. I just posted the main part for easy reading. The whole script includes things such as header, sidebar, footer, CSS etc.

The first part works ok, the edit page does successfully retrieve all the fields from the database and displays them on the edit page. The problem is when I change any of the fields and click 'submit' , the changes aren't made to the database.

It gets the data ok but it cant post it. I get these errors:

Notice: Undefined index: cmd in H:\xampplite\htdocs\pages\news\editNews.php on line 21
Notice: Undefined index: id in H:\xampplite\htdocs\pages\news\editNews.php on line 62

Piranha

I think that you need to go back a couple of steps to learn the basics in PHP and debugging. The errors you get is not because of problems with the database, it is PHP errors. I suggest that you do something like the following, complete the first one before going on.

Go through w3schools tutorials for PHP to understand the basics.
Build a form with one text field. On the next page echo what was in the text field.
On the second page see if there is something in the variable. If there is not print an error message. Try to connect to the second page both directly and by pressing submit in the first page.
Now you are ready to look at database queries. Make a simple queryand print it before testing it out.

nemonoman

if($GET["cmd"]=="edit" || $POST["cmd"]=="edit")

;;;;

  $id = $_GET["id"];

The notices are telling you what your errors are.

PHP can't find a value for $GET["cmd"] ... or maybe for $POST["cmd"].

PHP can't find a value for $_GET["id"]

Without the $id value, your SQL will fail.

You need to make sure that you are passing the id value to the form in a GET variable. "http://someting.com/something.php?get=12345"

VIEW SOURCE of your resulting browser page: make sure
<input type=hidden name="id" value='12345'>

Add this line:
print_r($_POST) to see the values you are posting the form.

Change this
$result = mysql_query($sql)
or die('Could not edit row '.mysql_error());

$result = mysql_query($sql)
or die("the query $sql failed, returning error code: ".mysql_error());

To see how your query was populated, and the error associated with the failure.