Rows echoed from MySQL. How to add an edit button next to each?

[JsteRmX]

Hi there, I currently have a script that allows you to insert a record to the database, and then there is a page that displays all the records in an html table.

Now, I am wondering how to add a link "edit" next to each row, so that when clicked, the user will be able to edit that record in the database?

Any help would be greatly appreciated.

Thanks a ton!

<?
$DBhost = "xxxxxx";
$DBuser = "xxxxxx";
$DBpass = "xxxxxx";
$DBName = "xxxxxx";
$table = "2007";
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select database $DBName");

$result = mysql_query("SELECT * FROM $table ORDER BY name ASC");

echo "<table border='0' p class ='style15' align='center' cellpadding='5'>
<tr>
<th>#</th>
<th>Name</th>
<th>Speech</th>
</tr>";

//set counter variable 
$counter = 1; 

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr>"; 
  echo "<td>" . $counter . "</td>"; 
  echo "<td>" . $row['name'] . "</td>"; 
  echo "<td>" . $row['speech'] . "</td>"; 
  echo "</tr>"; 
  $counter++; //increment counter by 1 on every pass 
  } 
echo "</table>";  

  
  //mysql_close($con);
?>

 

[Peter]

it is not simply a case of placing a button or link beside the entry, you will of course have to create a page that is used to actually do the edit.

You will need to have a unique identifier for each record (presumably each record has an id, if that is he case you can use that).

I take it you have absolutely no knowledge of programming?

 

[JsteRmX]

lol i have some, but its been awhile since i have worked with php and mysql.
I set up my database so that each record does have a unique ID, and I know i need to create a page to edit the information.

So far, I have figured out how to add a link to edit the record to each row, but I am not sure how to start off in the edit.php file. How can I make it so that when "edit" is clicked, only that record will be updated?

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr>"; 
  echo "<td>" . $counter . "</td>"; 
  echo "<td>" . $row['name'] . "</td>"; 
  echo "<td>" . $row['speech'] . "</td>"; 
  echo "<td><a href='http://www.example.com/edit.php'>edit</a></td>"; //THIS IS THE LINE I ADDED
  echo "</tr>"; 
  $counter++; //increment counter by 1 on every pass 
  } 
echo "</table>";

 

[psalzmann]

How can I make it so that when "edit" is clicked, only that record will be updated?

Like this:

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr>"; 
  echo "<td>" . $counter . "</td>"; 
  echo "<td>" . $row['name'] . "</td>"; 
  echo "<td>" . $row['speech'] . "</td>"; 
  echo "<td><a href='http://www.example.com/edit.php?id=" . $row['id'] . "'>edit</a></td>"; //THIS IS THE LINE I ADDED
  echo "</tr>"; 
  $counter++; //increment counter by 1 on every pass 
  } 
echo "</table>";

Then in edit.php:

if (isset($_GET['id']) AND $_GET['id'] > 0)
{
     // call db, and present form with fields for user..
}

Then most likely a save form:

save.php:

if (isset($_GET['id']) AND $_GET['id'] > 0)
{
     // save fields for db record #
}

 

[JsteRmX]

Hey, thanks alot for that response. I think it put me in the right direction.

I made all the pages, but it is not working correctly. I've played around with it quite a bit and am not able to figure out what it doesnt actually update the record.

EDIT.PHP

<?
if (isset($_GET['ID']) AND $_GET['ID'] > 0) 
{ 
// call db, and present form with fields for user.. 
$DBhost = "xxxxxxxx";
$DBuser = "xxxxx";
$DBpass = "xxxxxx!";
$DBName = "xxxxxxx";
$table = "2007";
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select database $DBName");

//edit forms below...

}

?>
      </p>
      <form id="form1" name="form1" method="post" action="save.php">
      <input type="hidden" name="id" value="null">
        <p><strong>EDIT TITLE: </strong>To <span class="style23">EDIT</span> your title, enter the new speech title below and click update:</p>
        <p>
          <input name="speech" type="text" id="speech" size="75" />
        </p>
        <p>
          <input type="submit" name="button" id="button" value="Update" />
        </p>
            </form>

SAVE.PHP

 <?
$DBhost = "xxxxxxxxx";
$DBuser = "xxxxxxx";
$DBpass = "xxxxxxx!";
$DBName = "xxxxxxxx";
$table = "2007";
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select database $DBName");

 
 if (isset($_GET['ID']) AND $_GET['ID'] > 0) 
{ 


// save fields for db record # 

$sqlquery = "UPDATE '$table' SET 'speech' = ('$speech')";


$results = mysql_query($sqlquery);

mysql_close();

print "<html><body>";
print "<p class ='style20'>Your record has been updated to:<p>";
print "<p class ='style15'>Name : $name<p>";
print "<p class ='style15'>Speech Title: $speech<p>";
print "<p class ='style20'>Click <a href='http://www.example.com/fallmidterm2007list.php'>here</a> to view all</p>";
print "</body></html>";

	 
} 

?>

What am i doing wrong?

 

[Eric]

Instead of using $name and $speech, you should be using the appropriate $_POST..

$_POST['name']
$_POST['speech']

etc..

And, you shouldn't be quoting your mysql table names / fields.


Also, you should clean/sanitize the INPUT before adding / updating the data.

Eg:

$speech = trim(strip_tags($_POST['speech']));

if (empty($speech))
{
    // some kind of error here...
}
else
{
    $result = mysql_query("UPDATE $table SET speech='" . mysql_real_escape_string($speech) . "'");
}

 

[JsteRmX]

great! thanks. I almost got it now....

its updating the records, but ALL of them, and not just the one with the ID.

I'll be busy tonight figuring that one out. hehe

thanks again for all the help!

 

[Eric]

Then something along the lines of:

<?php

if (isset($_GET['id']) AND intval($_GET['id']) > 0)
{
	$speech = trim(strip_tags($_POST['speech']));

	if (empty($speech))
	{
		// some kind of error here...
	}
	else
	{
		$result = mysql_query("
			UPDATE $table
			SET speech='" . mysql_real_escape_string($speech) . "'
			WHERE id=" . intval($_GET['id']) . "
		");
	}
}

?>

 

[JsteRmX]

im still having trouble. not sure what i am missing.

would anyone be willing to take a look at all my pages and correct what is wrong? im low on time and need to get this done and could use some of the teaching.

i'll give you 5 NP$. =)

Thanks

 

[labrocca]

I like $_REQUEST instead of GET or POST now.

 

[JsteRmX]

well if you can code this simple thing for me, ill send you some NP. like i said, i just need this done now. i dont have much time to figure it out now, as much as i would like to learn myself. I am still trying to find out and learn how to do it, buti really just need it done. can you help?