Edit MySQL Column & Update

**tomyknoker** · 03-29-2007, 07:30 AM

Hi all,

I am able to display the data from a table in my database. I have an Edit link, which you can see in the code. Obviously it's not active but want to when it is pressed to display the current user's data in textfield's that I can change and then click Update and it saves them... Anyone have any ideas?

PHP Code:


<?php



/* connect to the mysql database and use different queries for the count of members */



include 'configure.php';

include 'open.php';



//navigation

include("nav.php");



$info = mysql_query("SELECT * FROM tbladministrators");



echo '<table border="1" cellpadding="3" cellspacing="1">

  <tr valign="top">

    <td>First Name</td>

    <td>Last Name</td>

    <td>Username</td>

    <td>Password</td>

    <td>&nbsp;</td>

    <td>&nbsp;</td>

  </tr>';

    

if (mysql_num_rows($info) < 1) {

echo '<tr valign="top">

  <td colspan="4">There are no members that match the query. Please go back and try again</td>

    </tr>';

}



else {

   while ($qry = mysql_fetch_array($info)) {



//create the layout

?>

<link href="cs_style.css" rel="stylesheet" type="text/css" />



  <tr valign="top">

    <td><?php echo $qry['firstname']; ?></td>

    <td><?php echo $qry['lastname']; ?></td>

    <td><?php echo $qry['username']; ?></td>

    <td><?php echo $qry['password']; ?></td>

    <td>Edit</td>

    <td>Delete</td>

  </tr>

<?php

   }

}



echo '</table>';



include 'library/closedb.php';



?>

**Titan85** · 03-29-2007, 09:06 PM

Ok, this is how I would do it assuming you are using ids in your sql database, make another page called edit.php with code like this one:

PHP Code:


<?php

// Get the user id
$id = $_GET['id'];

// Get data from user with the specified id
$info = mysql_query("SELECT * FROM `tbladministrators` WHERE id = '$id'") or die ('Error Getting User Data! <br />' .mysql_error());
$chk = mysql_num_rows($info);
$u = mysql_fetch_array($info);

// If edit not hit
if (!$_POST['edit']) {

    // If user id returns no results
    if ($chk < 1) {
        echo 'The user with the id <b>'.$id.'</b> does not exist!';
    }
    else {
        // Edit Form
        ?>
    <!-- Edit Form -->
        <form method="post" action="">
            <table width="250">
                <tr>
                    <td>First Name:</td>
                    <td><input type="text" name="firstname" value="<?php echo $u['firstname'] ?>" /></td>
                </tr>
                <tr>
                    <td>Last Name:</td>
                    <td><input type="text" name="lastname" value="<?php echo $u['lastname'] ?>" /></td>
                </tr>
                <tr>
                    <td>Username:</td>
                    <td><input type="text" name="username" value="<?php echo $u['username'] ?>" /></td>
                </tr>
                <tr>
                    <td colspan="2"><input type="submit" name="edit" value="Edit" /></td>
                </tr>
            </table>
        </form>
    <!-- /Edit Form -->
        <?
}

// If edit was hit
if ($_POST['edit']) {
    $firstname = $_POST['firstname'];
    $lastname = $_POST['lastname'];
    $username = $_POST['username'];
    
        // Update data
        $update = mysql_query("UPDATE `tbladministrators` SET firstname = '$firstname', lastname = '$lastname', username = '$username' WHERE id = '$id'") or die ('Error Updating Data! <br />' .mysql_error());
        
        echo 'Update successfull';
}

?>

Now add this line to the end of your page displaying all the users:

PHP Code:


if ($_GET['act'] == 'edit') {
    require('edit.php');
}

And finally change the edit link to: <a href="?act=edit&id=<?php echo $qry['id'] ?>">Edit</a>

I also noticed that you have a delete function. You can delete things using: mysql_query("DELTE FROM `tbladministrators` WHERE id = '$id'");

Hope this helps

**tomyknoker** · 03-29-2007, 09:48 PM

Wow thanks for the help! It doesn't like the if statement spits out this error...

PHP Code:


Warning: main(edit.php): failed to open stream: No such file or directory in e:\test_one.php on line 54

Fatal error: main(): Failed opening required 'edit.php' (include_path='.;E:\php\pear') in e:\test_one.php on line 54
PHP Warning: main(edit.php): failed to open stream: No such file or directory in e:\test_one.php on line 54 PHP Fatal error: main(): Failed opening required 'edit.php' (include_path='.;E:\php\pear') in e:\test_one.php on line 54

**Titan85** · 03-29-2007, 09:54 PM

I believe the error is just that edit.php is not in that directory. Make sure it is uploaded and/or in the same directory as test_one.php. That should straiten it out. Let me know if it still gives the error.

**tomyknoker** · 03-30-2007, 02:17 AM

It says there is a parse error on line 52... strange!

**Titan85** · 03-30-2007, 03:16 AM

Originally Posted by tomyknoker

It says there is a parse error on line 52... strange!

Line 52 of the code I posted is the sql query to update. I would say make sure all the names are correct and match your database. If that isn't it, could you post the exact error so I can get more of what it is saying.

**thetestingsite** · 03-30-2007, 03:18 AM

Also, if you have added anything to the code, please post that as well so that we can see what is around line 52 that could be causing the error.

**tomyknoker** · 03-30-2007, 03:22 AM

Ok well this is the edit file code...

PHP Code:


<?php

/* connect to the mysql database and use different queries for the count of members */

include 'library/config.php';
include 'library/opendb.php';

//navigation
include("nav.php");

// Get the user id
$id = $_GET['id'];

// Get data from user with the specified id
$info = mysql_query("SELECT * FROM `tbladministrators` WHERE id = '$id'") or die ('Error Getting User Data! <br />' .mysql_error());
$chk = mysql_num_rows($info);
$u = mysql_fetch_array($info);

// If edit not hit
if (!$_POST['edit']) {

    // If user id returns no results
    if ($chk < 1) {
        echo 'The user with the id <b>'.$id.'</b> does not exist!';
    }
    else {
        // Edit Form
        ?>
    <!-- Edit Form -->
        <form method="post" action="">
            <table width="250">
                <tr>
                    <td>First Name:</td>
                    <td><input type="text" name="firstname" value="<?php echo $u['firstname'] ?>" /></td>
                </tr>
                <tr>
                    <td>Last Name:</td>
                    <td><input type="text" name="lastname" value="<?php echo $u['lastname'] ?>" /></td>
                </tr>
                <tr>
                    <td>Username:</td>
                    <td><input type="text" name="username" value="<?php echo $u['username'] ?>" /></td>
                </tr>
                <tr>
                    <td colspan="2"><input type="submit" name="edit" value="Edit" /></td>
                </tr>
            </table>
        </form>
    <!-- /Edit Form -->
        <?
}

// If edit was hit
if ($_POST['edit']) {
    $firstname = $_POST['firstname'];
    $lastname = $_POST['lastname'];
    $username = $_POST['username'];
    
        // Update data
        $update = mysql_query("UPDATE `tbladministrators` SET firstname = '$firstname', lastname = '$lastname', username = '$username' WHERE id = '$id'") or die ('Error Updating Data! <br />' .mysql_error());
        
        echo 'Update successfull';
}