www.webdeveloper.com
Results 1 to 14 of 14

Thread: Edit a specific mysql row using php

  1. #1
    Join Date
    Apr 2009
    Posts
    107

    Edit a specific mysql row using php

    I am creating a RSVP management system for a site. I have already created a page that displays all the results of people who have already registered. I want to be able to create a link on each row that allows the information to be brought up in another page and allows me to edit or delete the information. My thought is that I could but all the information in corresponding text boxes and make the values the original information. I could then change the values and use a update php script.

    Could really use some help on this, as I am still a beginner at PHP and MYSQL. Will post code if it will make i easier, but really looking for the code that I would add in the loop on the display page and then the code for an edit.php page that will get the information from only the row selected.

  2. #2
    Join Date
    Nov 2007
    Posts
    67
    You need to create 2 links for each row you're displaying

    PHP Code:
    echo "<td><a href=\"edit.php?id=$row['id']\">Edit</a></td><td><a href=\"delete.php?id=$row['id']\">Delete</a></td>"
    Now in your edit.php, you will need to do something similar

    PHP Code:
    $member_id $_GET['id'];
    $results mysql_query("select * from member where id = $member_id");
    $row mysql_fetch_assoc($results); 
    Now all your information is in the $row variable. You may get eg) $row['name'] etc. Place the information in a form and submit to something similar to your register page.

    In delete.php you could do this.

    PHP Code:
    header("Location: index.php");  // bring back to original page
    $member_id $_GET['id'];
    mysql_query("delete from member where id = $member_id limit 1"); 
    I hope this helps. I haven't tested the code but you're going to be doing something similar for edit and delete.

  3. #3
    Join Date
    Apr 2009
    Posts
    107

    not sure if I have this right

    Thanks for the quick reply. Here is partial code where I inserted the code you gave me on the page that views all the rows
    PHP Code:
    <?php

    // Retrieve data from database 
    $sql="SELECT * FROM $tbl_name";

    $result=mysql_query($sql);

    // Start looping rows in mysql database.
    while($rows=mysql_fetch_array($result)){
    ?>
    <div align="center">
    <table width="80%" border="0" cellspacing="0" cellpadding="5">
        <tr>
            <td width="10%"><a href="edit.php?id=$row['id']">Edit</a></td><td><a href="delete.php?id=$row['id']">Delete</a></td>
            <td width="20%" valign="top" class="accomodations">
                1. <? echo $rows['guest_1']; ?><br>
                2. <? echo $rows['guest_2']; ?><br>
                3. <? echo $rows['guest_3']; ?><br>
                4. <? echo $rows['guest_4']; ?><br>
                5. <? echo $rows['guest_5']; ?></td>
            <td width="10%" valign="top" align="center" class="accomodations"><? echo $rows['total_guests']; ?></td>
            <td width="10%" valign="top" align="center" class="accomodations"><? echo $rows['vegetarian']; ?></td>
            <td width="22%" valign="top" class="accomodations"><? echo $rows['email']; ?></td>
            <td width="18%" valign="top" class="accomodations"><? echo $rows['accomodations']; ?></td>
        </tr>
        <hr width="80%" align="center">
    </table>
    </div>
    <?
    // close while loop 
    }

    // close connection 
    mysql_close();
    ?>
    Here is the code I have in the edit.php
    PHP Code:
    <?php
    session_start
    ();
    if (@
    $_SESSION['auth'] !="yes")
    {
        
    header("Location: login_form.php");
        exit();
    }
    ?>
    <?php 
    $host
    ="localhost"
    $username="username"
    $password="password"
    $db_name="db_namer"
    $tbl_name="table";

    // Connect to server and select database.
    mysql_connect("$host""$username""$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");

    $member_id $_GET['id']; 
    $results mysql_query("select * from member where id = $member_id"); 
    $row mysql_fetch_assoc($results); 

    ?>
    <html>
    <head>
        <link href="style.css" type="text/css" rel="stylesheet">
    </head>
    <body>

    <div align="center">
    <table width="80%" cellpadding="0" cellspacing="0" border="0">
        <tr>
            <td>Guest 1 <input type="text" value="<? echo $row['guest_1']; ?>" name="guest_1"></td>
        </tr>

    </div>
    </body>
    </html>
    And here is the code for the delete.php
    PHP Code:
    <?php
    $host
    ="localhost"
    $username="username"
    $password="password"
    $db_name="db_namer"
    $tbl_name="table";

    // Connect to server and select database.
    mysql_connect("$host""$username""$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");

    header("Location: rsvp_view.php");  // bring back to original page 
    $member_id $_GET['id']; 
    mysql_query("delete from member where id = $member_id limit 1"); 

    ?>

    When I click edit, I don't get anything in the guest 1 text box, and I get this error "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/content/m/a/r/markandmichell/html/manager/edit.php on line 22
    "

    When I click delete, the browser processing, but nothing is deleted on the screen. Not sure what I am doing wrong, but some help would be much appreciated.

  4. #4
    Join Date
    Nov 2007
    Posts
    67
    Hey,

    I'm at work right now, so I can't go through all the code lol.

    One quick thing i see is

    PHP Code:
    <td width="10%"><a href="edit.php?id=$row['id']">Edit</a></td><td><a href="delete.php?id=$row['id']">Delete</a></td
    It's outside of the php code, so it won't know what $row variable is. Also, your while variable is called "$rows"

    Try this

    PHP Code:
    <td width="10%"><a href="edit.php?id=<?php echo $rows['id']; ?>">Edit</a></td><td><a href="delete.php?id=<?php echo $rows['id']; ?>">Delete</a></td>
    Make sure after you click the edit link you see the url edit.php?id=3 some integer. That's when you know you're passing it correctly.

  5. #5
    Join Date
    Apr 2009
    Posts
    107

    Still doesn't work...

    Thanks for looking at the code, even though you are at work. I changed the link to incorporate the your changes. I still for some reason though get a url without a row identification (edit.php?=). So still not sure what isn't working. I am hoping that you will have some time after work to go through it all. Let me know if you want me to post anymore of the code.

  6. #6
    Join Date
    Nov 2008
    Posts
    2,477
    Can you post your code again after the changes?

  7. #7
    Join Date
    Apr 2009
    Posts
    107
    Here is the rsvp.php page that shows the results:
    PHP Code:
    <?php
    session_start
    ();
    if (@
    $_SESSION['auth'] !="yes")
    {
        
    header("Location: login_form.php");
        exit();
    }
    ?>

    <html>

    <head>
        <link href="style.css" type="text/css" rel="stylesheet">
    </head>
    <body>

    <div align="center">
    <table width="100%" cellpadding="0" cellspacing="0" border="0">
        <tr>
            <td height="30"></td>
        </tr>
        <tr>
            <td class="header" align="center">RSVP Results</td>
        </tr>
        <tr>
            <td height="20"></td>
        </tr>
        <tr>
            <td align="center" class="accomodations">
                <form name="form" action="search.php" method="get">
                  Search Registrants <input type="text" name="q" />
                  <input type="submit" name="Submit" value="Search" />
                </form>
            </td>
        </tr>
    </table>
    </div>

    <?php 
    $host
    ="localhost"
    $username="usernamer"
    $password="password"
    $db_name="db_name"
    $tbl_name="table";

    // Connect to server and select database.
    mysql_connect("$host""$username""$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");

    $total_result mysql_query"SELECT guest_1, guest_2, guest_3, guest_4, guest_5, total_guests, email, vegetarian, accomodations FROM guests" )  
        or die(
    "SELECT Error: ".mysql_error());  

    $num_rows mysql_num_rows($total_result);  

    $vegetarian mysql_query"SELECT SUM(vegetarian) FROM guests");
    $total_vegetarian mysql_result($vegetarian0)
        or die(
    mysql_error());

    $guests mysql_query"SELECT SUM(total_guests) FROM guests" );  
    $total_guests mysql_result($guests0
        or die(
    mysql_error()); 

    $email mysql_query("SELECT email FROM guests");

    ?>

    <div align="center">
    <table width="80%" border="0" cellspacing="0" cellpadding="5">
        <tr>
            <td height="20"></td>
        </tr>
        <tr>
            <td class="accomodations" width="33%"><b>Total Replies:</b> <? echo $num_rows?></td>
            <td class="accomodations" width="33%"><b>Total Number of Guests:</b> <? echo $total_guests?></td>
            <td class="accomodations" width="33%" align="center"><b>Total # of Vegetarian Meals:</b> <? echo $total_vegetarian?></td>
        </tr>
        <tr>
            <td height="20"></td>
        </tr>
    </table>

    <table width="80%" border="0" cellspacing="0" cellpadding="5">
        <tr>
            <td width="10%"></td>
            <td width="20%" class="accomodations"><b>Guests</b></td>
            <td width="10%" align="center" class="accomodations"><b>Total Guests</b></td>
            <td width="10%" align="center" class="accomodations"><b>Veg. Meals</b></td>
            <td width="22%" class="accomodations"><b>Email</b></td>
            <td width="18%" class="accomodations"><b>Accomodations</b></td>
        </tr>
    </table>
    </div>

    <?php

    // Retrieve data from database 
    $sql="SELECT * FROM $tbl_name";

    $result=mysql_query($sql);

    // Start looping rows in mysql database.
    while($rows=mysql_fetch_array($result)){
    ?>
    <div align="center">
    <table width="80%" border="0" cellspacing="0" cellpadding="5">
        <tr>
            <td width="10%"><a href="edit.php?id=<?php echo $rows['id']; ?>">Edit</a></td><td><a href="delete.php?id=<?php echo $rows['id']; ?>">Delete</a></td>
            <td width="20%" valign="top" class="accomodations">
                1. <? echo $rows['guest_1']; ?><br>
                2. <? echo $rows['guest_2']; ?><br>
                3. <? echo $rows['guest_3']; ?><br>
                4. <? echo $rows['guest_4']; ?><br>
                5. <? echo $rows['guest_5']; ?></td>
            <td width="10%" valign="top" align="center" class="accomodations"><? echo $rows['total_guests']; ?></td>
            <td width="10%" valign="top" align="center" class="accomodations"><? echo $rows['vegetarian']; ?></td>
            <td width="22%" valign="top" class="accomodations"><? echo $rows['email']; ?></td>
            <td width="18%" valign="top" class="accomodations"><? echo $rows['accomodations']; ?></td>
        </tr>
        <hr width="80%" align="center">
    </table>
    </div>
    <?
    // close while loop 
    }

    // close connection 
    mysql_close();
    ?>
    </body>
    </html>
    Here is the edit.php page:
    PHP Code:
    <?php
    session_start
    ();
    if (@
    $_SESSION['auth'] !="yes")
    {
        
    header("Location: login_form.php");
        exit();
    }
    ?>
    <?php 
    $host
    ="localhost"
    $username="usernamer"
    $password="password"
    $db_name="db_name"
    $tbl_name="table";

    // Connect to server and select database.
    mysql_connect("$host""$username""$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");

    $member_id $_GET['id']; 
    $results mysql_query("select * from member where id = $member_id"); 
    $row mysql_fetch_assoc($results); 

    ?>
    <html>
    <head>
        <link href="style.css" type="text/css" rel="stylesheet">
    </head>
    <body>

    <div align="center">
    <table width="80%" cellpadding="0" cellspacing="0" border="0">
        <tr>
            <td>Guest 1 <input type="text" value="<? echo $row['guest_1']; ?>" name="guest_1"></td>
        </tr>

    </div>
    </body>
    </html>

    And lastly here is the delete.php:
    PHP Code:
    <?php
    $host
    ="localhost"
    $username="usernamer"
    $password="password"
    $db_name="db_name"
    $tbl_name="table";

    // Connect to server and select database.
    mysql_connect("$host""$username""$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");

    header("Location: rsvp_view.php");  // bring back to original page 
    $member_id $_GET['id']; 
    mysql_query("delete from member where id = $member_id limit 1"); 

    ?>

    So here is all the code. Again, I want to be able to open a specific row in the edit page and makes changes and then execute an update script. I also want to be able to just delete an entire row all together as well. Any advice would be a great help.

  8. #8
    Join Date
    Apr 2009
    Posts
    107

    Figured it out

    Ok I figured out what was going wrong. My SQL table did not have an 'id' column. Once I added one, presto everything worked. Thanks for the help.

  9. #9
    Join Date
    Nov 2007
    Posts
    67
    Hey!

    Sorry I took so long to get back to you. I'm glad you found the error!

    I was going to mention about making sure you had a column "id" too haha.

  10. #10
    Join Date
    Jun 2009
    Posts
    2

    php edit form

    Quote Originally Posted by themonkey40 View Post
    Ok I figured out what was going wrong. My SQL table did not have an 'id' column. Once I added one, presto everything worked. Thanks for the help.
    OK, can you help me what id you are referring to. I have the same problem you've mentioned on your code.

  11. #11
    Join Date
    Apr 2009
    Posts
    107
    in your sql table, you need to have a column "id" that auto-increments.

  12. #12
    Join Date
    Jun 2009
    Posts
    2

    php edit form

    OK,

    I have an id as increment, but I still have a problem. I was able to delete when click on the delete link, but when I click on the update, I've got the error. here's my code:

    <?php
    $hostname = "localhost";
    $username = "root";
    $password = "Passw0rd";

    $conn = mysql_connect($hostname, $username, $password) or die ("mysql error");
    $selected = mysql_select_db("first_test",$conn) or die ("database error");

    // delete record

    $delete_record = $_GET['delete'];
    if (isset($_GET['delete']))
    {
    mysql_query("delete from people where id = '$delete_record'");
    echo $delete_record . ' was deleted!<p>';
    }


    // display result
    $result = mysql_query("select * from people order by id");
    echo "<table border = '1' align='center' width='70%'>
    <tr>
    <th>ID</th>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Phone Number</th>
    <th>Delete Record</th>
    <th>Update Record</th>
    </tr>";

    while ($row = mysql_fetch_assoc($result))
    {
    echo '<tr>
    <td> '.$row['id']. '</td>
    <td> '.$row['first_name']. '</td>
    <td> '.$row['last_name']. '</td>
    <td> '.$row['ph_no']. '</td>
    <td><a href="delete.php?delete= ' .$row['id']. '">Delete</td>
    <td><a href="update-form.php?= ' .$row['id']. '">Edit</td>
    </tr>';
    }

    echo '</table>';
    echo "<p><a href='test.php'>View Record</a>";
    mysql_close($conn);

    ?>

    // Here's the update form..

    <?php
    $hostname = "localhost";
    $username = "root";
    $password = "Passw0rd";

    $db = mysql_connect($hostname, $username, $password) or die ("not able to connected to mysql");
    // connect to database

    $select = mysql_select_db("first_test",$db) or die (" not able connected to db");

    $update = $_GET['id'];

    $result = mysql_query("select * from people where id = $update");
    $row = mysql_fetch_assoc($result);
    mysql_close($db);
    ?>

    <form action="update.php" method="post">
    ID: <input type="text" name="id" value="<?php echo $row["id"] ?>" />
    FirstName: <input type="text" name="first_name" value="<?php echo $row["first_name"]?>" />
    <br />
    LastName: <input type="text" name="last_name" value="<?php echo $row["last_name"] ?>" />
    <br />
    Phone#: <input type="text" name="ph_no" value="<?php echo $row["ph_no"] ?>" />
    <br />
    <br />
    <input type="submit" name="submit" />
    </form>

  13. #13
    Join Date
    Nov 2012
    Posts
    2
    <?php
    $ud_ID = $_REQUEST["ID"];
    $ud_firstname = $_POST["ud_firstname"];
    $ud_surname = $_POST["ud_surname"];
    $ud_FBID = $_POST["ud_FBID"];
    $ud_IMG = $_POST["ud_IMG"];

    mysql_connect('localhost', 'admin', 'passw0rd') or die(mysql_error());
    echo "MySQL Connection Established! <br>";

    mysql_select_db("students") or die(mysql_error());
    echo "Database Found! <br>";

    $query = "UPDATE stokesley_students SET firstname = '$ud_firstname', surname = '$ud_surname',
    FBID = '$ud_FBID' WHERE ID = '$ud_ID'";

    $res = mysql_query($query);

    ______________________
    Brad555

  14. #14
    Join Date
    Nov 2012
    Posts
    2
    Quote Originally Posted by themonkey40 View Post
    in your sql table, you need to have a column "id" that auto-increments.
    I totally agree with you.

    mcitp 70-640
    http://www.leatherstock.co.uk/

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles