Edit a specific mysql row using php

@themonkey40May 28.2009

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.

to post a comment

PHP

13 Comments(s) _↴

@Blizzard84May 28.2009 — #You need to create 2 links for each row you're displaying

[code=php]echo "<td><a href="edit.php?id=$row['id']">Edit</a></td><td><a href="delete.php?id=$row['id']">Delete</a></td>";[/code]

Now in your edit.php, you will need to do something similar

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

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.

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

I hope this helps. I haven't tested the code but you're going to be doing something similar for edit and delete.

@themonkey40authorMay 28.2009 — #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

[code=php]<?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();
 ?>[/code]

Here is the code I have in the edit.php

[code=php]<?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>[/code]

And here is the code for the delete.php

[code=php]<?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"); 
 
 ?>[/code]

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.

@Blizzard84May 28.2009 — #Hey,

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

One quick thing i see is

[code=php]<td width="10%"><a href="edit.php?id=$row['id']">Edit</a></td><td><a href="delete.php?id=$row['id']">Delete</a></td>[/code]

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

[code=php]<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>[/code]

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.

@themonkey40authorMay 28.2009 — #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.

@MindzaiMay 28.2009 — #Can you post your code again after the changes?

@themonkey40authorMay 28.2009 — #Here is the rsvp.php page that shows the results:

[code=php]<?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($vegetarian, 0)
 or die(mysql_error());
 
 $guests = mysql_query( "SELECT SUM(total_guests) FROM guests" ); 

 $total_guests = mysql_result($guests, 0) 
 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>[/code]

Here is the edit.php page:

[code=php]<?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>[/code]

And lastly here is the delete.php:

[code=php]<?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"); 
 
 ?>[/code]

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.

@themonkey40authorMay 29.2009 — #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.

@Blizzard84May 29.2009 — #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.

@felixvahJun 29.2009 — #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.[/QUOTE]

OK, can you help me what id you are referring to. I have the same problem you've mentioned on your code.

@themonkey40authorJun 29.2009 — #in your sql table, you need to have a column "id" that auto-increments.

@felixvahJun 29.2009 — #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>

@Brad555Nov 10.2012 — #<?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

@Brad555Dec 08.2012 — #in your sql table, you need to have a column "id" that auto-increments.[/QUOTE]

I totally agree with you.

[url=http://www.tkdumps.com/70-640.html]mcitp 70-640[/url]

http://www.leatherstock.co.uk/

Also in #PHP _↴

currency conversion Connecting To A Database time stopped between scripts

Success!

Help @themonkey40 spread the word by sharing this article on Twitter...

Tweet This

Edit a specific mysql row using php

13 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Edit a specific mysql row using php

13 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

13 Comments(s) _↴

Also in #PHP _↴