the most basic way to edit a record in a mySql db

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
coder4life
Forum Newbie
Posts: 10
Joined: Sat May 22, 2010 6:59 pm

the most basic way to edit a record in a mySql db

Post by coder4life »

lets say i am looking at a list of records and i want to revise one. maybe i could then see the record with the ID of 5 has a price field that needs changing, so then i search for that record, pull it out of the db, highlight it, so to speak, where it shows all its fields, and then i can go to the field i want to change, revise it, and leave the rest alone.

the code i was working with is below, but all i get is a blank page:

Code: Select all

<?php
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM data1 WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_num_rows($result); 
mysql_close();

$i=0;
while ($i < $num) {
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$street=mysql_result($result,$i,"street");
$city=mysql_result($result,$i,"city");
$state=mysql_result($result,$i,"state");
$zip=mysql_result($result,$i,"zip");
$fee_rcpt=mysql_result($result,$i,"fee_rcpt");
$date_rcd=mysql_result($result,$i,"date_rcd");
$opnd_by=mysql_result($result,$i,"opnd_by");
$check_encl=mysql_result($result,$i,"check_encl");
?>

<form action="updated.php">
<input type="hidden" name="id" value="<?php echo "$id"; ?>">
First Name: <input type="text" name="first" value="<?php echo "$first"?>"><br>
Last Name: <input type="text" name="last" value="<?php echo "$last"?>"><br>
Street: <input type="text" name="street" value="<?php echo "$street"?>"><br>
City: <input type="text" name="city" value="<?php echo "$city"?>"><br>
State: <input type="text" name="state" value="<?php echo "$state"?>"><br>
Zip: <input type="text" name="zip" value="<?php echo "$zip"?>"><br>
Fee Rcd (Y/N): <input type="text" name="fee_rcpt" value="<?php echo "$fee_rcpt"?>"><br>
Date Rcd: <input type="text" name="date_rcd" value="<?php echo "$date_rcd"?>"><br>
Opened By: <input type="text" name="opnd_by" value="<?php echo "$opnd_by"?>"><br>
Check Encl (Y/N): <input type="text" name="check_encl" value="<?php echo "$check_encl"?>"><br>

<input type="Submit" value="Update">
</form>

<?php
++$i;
} 
?>
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: the most basic way to edit a record in a mySql db

Post by flying_circus »

You can use mysql_fetch_assoc() to retrieve 1 row from the result set in an array, like this"

Code: Select all

<?php
  # Includes
    include("dbinfo.inc.php");
    
  # Connect to the Database
    mysql_connect(localhost,$username,$password);
    // The '@' operator supresses errors.  Do not suppress errors... HANDLE them.
    mysql_select_db($database) or die( "Unable to select database");
    
  # Execute Query
    $query = "SELECT * FROM data1 WHERE id='" . mysql_real_escape_string($id) . "'";
    
  # Fetch Results
    $result = mysql_query($query);
    
  # Use Results
    while($row = mysql_fetch_assoc($result)) {
?>
      <form action="updated.php">
        <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
        First Name: <input type="text" name="first" value="<?php echo $row['first'];?>"><br>
        Last Name: <input type="text" name="last" value="<?php echo $row['last'];?>"><br>
        Street: <input type="text" name="street" value="<?php echo $row['street'];?>"><br>
        City: <input type="text" name="city" value="<?php echo $row['city'];?>"><br>
        State: <input type="text" name="state" value="<?php echo $row['state'];?>"><br>
        Zip: <input type="text" name="zip" value="<?php echo $row['zip'];?>"><br>
        Fee Rcd (Y/N): <input type="text" name="fee_rcpt" value="<?php echo $row['fee_rcpt'];?>"><br>
        Date Rcd: <input type="text" name="date_rcd" value="<?php echo $row['date_rcd'];?>"><br>
        Opened By: <input type="text" name="opnd_by" value="<?php echo $row['opnd_by'];?>"><br>
        Check Encl (Y/N): <input type="text" name="check_encl" value="<?php echo $row['check_encl'];?>"><br>
        <input type="Submit" value="Update">
      </form>
<?php
    }
    
  # Close Database Connection
mysql_close();
?>
To update a product, there is no need to "highlight" a row, simply run a mysql "UPDATE" query.

So for instance, on your page above, on submit, your "updated.php" page would look similar in that you will create the database connection and then when you run the query, it will not be a SELECT statement, but something like:
mysql_query("UPDATE `data1` SET `price`='$escaped_new_price' WHERE `id`='$escaped_id';");
coder4life
Forum Newbie
Posts: 10
Joined: Sat May 22, 2010 6:59 pm

Re: the most basic way to edit a record in a mySql db

Post by coder4life »

hi

thanks for the reply....

well, the page still shows up blank, i dont see the form.

when i delete this part of the code then the form shows up:

Code: Select all

  // Fetch Results
   $result = mysql_query($query);
   
  // Use Results
   while($row = mysql_fetch_assoc($result)) {

}

not sure what the issue is ...
coder4life
Forum Newbie
Posts: 10
Joined: Sat May 22, 2010 6:59 pm

Re: the most basic way to edit a record in a mySql db

Post by coder4life »

ok, i used this

Code: Select all

<?php
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

mysql_query("UPDATE data1 SET last = 'jackson'
WHERE id = '5'");

mysql_close();
?> 

and i was able to make the change, i guess 'manually'

ideally i would like to search the DB, have it pull up the field data for the requested record, and infill the form with that data, and then re-submit the same data in the fields not changed, and of course with the revisions made to those fields changed ...

any ideas?

thanks again!
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: the most basic way to edit a record in a mySql db

Post by mikosiko »

Just a few suggestions for you.... please read the code that is given to you and try to understand it... flying_circus gave you a clear example of code and you simply change it because "was not working" without trying to find up why was not working.

In another thread that you posted early you were provided with another example and you did exactly the same and in addition you make in this post the same coding mistake for which you receive corrections before...

what I suggest you to do now is :
- read what was suggested to you in your previous thread, specially how to use the die(mysql_error()) function.
- take the code provided for flying_circus and make the appropriated changes to make it work... is relative simple to figure out why it is not working if you read your previous thread.

check it... and if still with doubts ask again... we will help.
coder4life
Forum Newbie
Posts: 10
Joined: Sat May 22, 2010 6:59 pm

Re: the most basic way to edit a record in a mySql db

Post by coder4life »

simple for someone who does this all the time ... but thanks for ur insights
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: the most basic way to edit a record in a mySql db

Post by mikosiko »

... we all started in some point ... so... try again... write this code exactly in this way to start and let us know what do you get (don't change nothing yet):

Code: Select all

<?php
  # Includes
   include("dbinfo.inc.php");
    
  # Connect to the Database
   mysql_connect(localhost,$username,$password) or die("Unable to connect");

    mysql_select_db($database) or die( "Unable to select database");
    
   /* LETS USE A FIXED ID FOR NOW
  # Execute Query
   $query = "SELECT * FROM data1 WHERE id=5";
    
  # Fetch Results
   $result = mysql_query($query);
    
  # Use Results
   while($row = mysql_fetch_assoc($result)) {
?>
      <form action="updated.php">
        <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
        First Name: <input type="text" name="first" value="<?php echo $row['first'];?>"><br>
        Last Name: <input type="text" name="last" value="<?php echo $row['last'];?>"><br>
        Street: <input type="text" name="street" value="<?php echo $row['street'];?>"><br>
        City: <input type="text" name="city" value="<?php echo $row['city'];?>"><br>
        State: <input type="text" name="state" value="<?php echo $row['state'];?>"><br>
        Zip: <input type="text" name="zip" value="<?php echo $row['zip'];?>"><br>
        Fee Rcd (Y/N): <input type="text" name="fee_rcpt" value="<?php echo $row['fee_rcpt'];?>"><br>
        Date Rcd: <input type="text" name="date_rcd" value="<?php echo $row['date_rcd'];?>"><br>
        Opened By: <input type="text" name="opnd_by" value="<?php echo $row['opnd_by'];?>"><br>
        Check Encl (Y/N): <input type="text" name="check_encl" value="<?php echo $row['check_encl'];?>"><br>
        <input type="Submit" value="Update">
      </form>
<?php
    }
    
  # Close Database Connection
mysql_close();
?>
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: the most basic way to edit a record in a mySql db

Post by Eran »

simple for someone who does this all the time ... but thanks for ur insights
Not everything in life is simple, least of all software development. If it doesn't look like you're making an effort to solve problems yourself it really discourages people from trying to help you.
Post Reply