Help with inline editing mysql search results

jvance38 · February 28, 2011

Hello. I hope someone out there can help me with this as I have been trying all different ways to make this work but to no avail. I have a report.php page that allows a user to search 3 particular fields in a database to retrieve search results that displays itself on the same page. What I am looking to do is have both an EDIT and DELETE button/link for each queried result that is retrieved allowing the user to edit or delete the search result of their choice. Or, how to implement some inline editing on the results that are retrieved by the search query. Here is the code I have so far with the EDIT or DELETE options feature that I am wanting to utilize.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Budgets Report</title>

</head>
<body class="oneColFixCtr">

<div id="container">

<h2>Budget Report</h2>

<form name="search" method="post" style="background-color:#FFF" action="<?php $PHP_SELF?>">
<span id="search">Search for</span>:
<input type="text" name="find" /> in <Select NAME="field">
<Option VALUE="rundate">rundate</option>
<Option VALUE="section">section</option>
<Option VALUE="reporter">reporter</option> </Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>

<?php

// check to see if anything is posted
if (isset($_POST['find'])) {$find = $_POST['find'];}
if (isset($_POST['searching'])) {$searching = $_POST['searching'];}
if (isset($_POST['field'])) {$field = $_POST['field'];}

//This is only displayed if they have submitted the form
if (isset($searching) && $searching=="yes") {

echo "<h2>Results</h2><p>";

// If they did not enter a search term we give them an error
if (empty($find)) {
echo "<p>You forgot to enter a search term"; exit;
}

// Otherwise we connect to our Database
mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("budgets") or die(mysql_error());

// We preform a bit of filtering
$find = strtoupper($find);
$find = strip_tags($find); $find = trim ($find);

// Now we search for our search term, in the field the user specified
$data = mysql_query("SELECT * FROM daily_budget WHERE upper($field) LIKE'%$find%' ORDER BY section ASC");

// And we display the results
while($result = mysql_fetch_array( $data )) {
echo "<strong>Section:$nbsp</strong>"; echo " ";echo $result['section']; echo "<br>"; 
echo $result['slug']; echo " "; echo ":"; echo " "; echo $result['budgetInfo']; echo " "; echo "/"; echo " "; echo $result['reporter']; echo " ";  echo $result['notes']; echo " ";
echo $result['art_photos']; echo " ";  echo $result['artDesc']; echo " "; echo $result['multimedia']; echo " ";  echo $result['multimediaDesc']; echo "<br>";
echo "Pickup";  echo " "; echo ":"; echo " "; echo $result['pickup']; echo "<br>"; 
echo "Sidebar";  echo " "; echo ":"; echo " "; echo $result['sidebar']; echo "<br> ";
echo $result['sSlug']; echo " "; echo $result['sBudget']; echo " "; echo $result['sArt']; echo " ";
echo "EDIT"; echo " "; echo "| "; echo " ";echo "DELETE"; echo " ";
echo "<br>";
echo "<hr>";
}
//This counts the number or results - and if there wasn't any it gives them a little message explaining that
$anymatches=mysql_num_rows($data);

if ($anymatches == 0) {
echo "Sorry, but we can not find an entry to match your query<br><br>"; }

//And we remind them what they searched for
echo "<b>Searched For:</b> " .$find;
}

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

Muddy_Funster · February 28, 2011

post your code inside the php code tags, then I'll actualy look at it.

jvance38 · February 28, 2011

Sorry, i am new to this forum. How or what php code tags are required for posting code?

Muddy_Funster · February 28, 2011

if you are posting php, then use the php tags, general html/sql/css can live inside normal code tags. using the php tags highlights the code in a way that makes it much easier to read and debug then just posting the whole lot raw.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Budgets Report</title>

</head>
<body class="oneColFixCtr">

<div id="container">

<h2>Budget Report</h2>

<form name="search" method="post" style="background-color:#FFF" action="<?php $PHP_SELF?>">
<span id="search">Search for</span>:
<input type="text" name="find" /> in <Select NAME="field">
<Option VALUE="rundate">rundate</option>
<Option VALUE="section">section</option>
<Option VALUE="reporter">reporter</option> </Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>

<?php

// check to see if anything is posted
if (isset($_POST['find'])) {$find = $_POST['find'];}
if (isset($_POST['searching'])) {$searching = $_POST['searching'];}
if (isset($_POST['field'])) {$field = $_POST['field'];}

//This is only displayed if they have submitted the form
if (isset($searching) && $searching=="yes") {

echo "<h2>Results</h2><p>";

// If they did not enter a search term we give them an error
if (empty($find)) {
echo "<p>You forgot to enter a search term"; exit;
}

// Otherwise we connect to our Database
mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("budgets") or die(mysql_error());

// We preform a bit of filtering
$find = strtoupper($find);
$find = strip_tags($find); $find = trim ($find);

// Now we search for our search term, in the field the user specified
$data = mysql_query("SELECT * FROM daily_budget WHERE upper($field) LIKE'%$find%' ORDER BY section ASC");

// And we display the results
while($result = mysql_fetch_array( $data )) {
echo '<strong>Section:$nbsp</strong>'.$result['section'].'<br>';
echo $result['slug'].' : '.$result['budgetInfo'].' / '.$result['reporter'].' '.$result['notes'].' '.$result['art_photos'].' '.$result['artDesc'].' '.$result['multimedia'].' '.$result['multimediaDesc'].'<br>';
echo 'Pickup : '.$result['pickup'].'<br>'; 
echo 'Sidebar : '.$result['sidebar'].'<br>';
echo $result['sSlug'].' '.$result['sBudget'].' '.$result['sArt'].' <a href="dbEdit.php"> EDIT</a> |  <a href="dbDelete.php">DELETE</a> <br>';
echo '<hr>';
}
//This counts the number or results - and if there wasn't any it gives them a little message explaining that
$anymatches=mysql_num_rows($data);

if ($anymatches == 0) {
echo "Sorry, but we can not find an entry to match your query<br><br>"; }

//And we remind them what they searched for
echo "<b>Searched For:</b> " .$find;
}

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

l;ooking through your code here (I tidied up your echo's a bit at the bottom) you are going to need to post your table structure so we can see what the primary key is.

jvance38 · February 28, 2011

Thank you for explaining the posting requirements. I was under the impression that only the php needed to be in php tags. Anyways, these are the fields that my table consists of with id being the primary key.

id, rundate, slug, reporter, budgetInfo, notes, `section`, deadline, sidebar, sBudget

Muddy_Funster · March 1, 2011

without posting two forms into each result line the easiest way that I can think of is to change the following line to look something like this :

 echo $result['sSlug'].' '.$result['sBudget'].' '.$result['sArt'].' <a href="dbEdit.php?record='.$result['id'].'"> EDIT</a> |  <a href="dbDelete.php?record='.$result['id'].'">DELETE</a> <br>';

then make up a new new page for each function: called dbEdit.php and dbDelete.php. At the start of each of these use the line

$id = $_GET['record'];  you can then code each page to do what you want it to do (either edit a record or delete it) by using the $id refference to the primary key field for the record.

Did that make sense? :shrug:

jvance38 · March 1, 2011

Thank you for your reply, it kind of makes sense but I want to be sure of what you are telling me. I already have the two pages developed for editing and deleting. When you say to use the line

$id = $_GET['record']; where does that get placed, in the following code?

<a href="dbEdit.php?record='.$result['id'].'"> EDIT</a> | <a href="dbDelete.php?record='.$result['id'].'">DELETE</a> <br>';

Muddy_Funster · March 1, 2011

it's not in that code, it's in the code of both the edit and delete pages, and it will need to be before you do anything with the data from the database, so the page knows what row it is to effect. You will need to change that line of code you have highlighted to point to your existing edit and delete pages (change the dbEdit.php and dbDelete.php to what your pages are actualy called)

jvance38 · March 1, 2011

Cool, thank you so much. Well, there is some progress!! I placed the <?php $id = $_GET['record']; ?> on the update_budget.php page but it is not populating the update form page fields. When I hover over the EDIT link, it is pointing to the correct id number for that record. I developed this update page by copying the Insert_budget.php page that users fill in and submit data to database and then adding an update record server behavior to that page. I am placing that code from the update page to this post to see if you can tell me where that particular $id=$_GET['record'] needs to be place.

<?php

$editFormAction = $_SERVER['PHP_SELF'];

if (isset($_SERVER['QUERY_STRING'])) {

$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

}

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {

$updateSQL = sprintf("UPDATE daily_budget SET rundate=%s, `section`=%s, slug=%s, reporter=%s, budgetInfo=%s, notes=%s, deadline=%s, pickup=%s, sidebar=%s, sBudget=%s WHERE id=%s",

GetSQLValueString($_POST['rundate'], "text"),

GetSQLValueString($_POST['section'], "text"),

GetSQLValueString($_POST['slug'], "text"),

GetSQLValueString($_POST['reporter'], "text"),

GetSQLValueString($_POST['budgetInfo'], "text"),

GetSQLValueString($_POST['notes'], "text"),

GetSQLValueString($_POST['deadline'], "text"),

GetSQLValueString($_POST['pickup'], "text"),

GetSQLValueString($_POST['sidebar'], "text"),

GetSQLValueString($_POST['sBudget'], "text"),

GetSQLValueString($_POST['id'], "int"));

mysql_select_db($database_connAdmin, $connAdmin);

$Result1 = mysql_query($updateSQL, $connAdmin) or die(mysql_error());

$updateGoTo = "manage_budgets.php";

if (isset($_SERVER['QUERY_STRING'])) {

$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";

$updateGoTo .= $_SERVER['QUERY_STRING'];

}

header(sprintf("Location: %s", $updateGoTo));

}

$colname_getBudget = "-1";

if (isset($_GET['id'])) {

$colname_getBudget = $_GET['id'];

}

mysql_select_db($database_connAdmin, $connAdmin);

$query_getBudget = sprintf("SELECT id, rundate, slug, reporter, budgetInfo, notes, `section`, deadline, sidebar, sBudget, pickup FROM daily_budget WHERE id = %s", GetSQLValueString($colname_getBudget, "int"));

$getBudget = mysql_query($query_getBudget, $connAdmin) or die(mysql_error());

$row_getBudget = mysql_fetch_assoc($getBudget);

$totalRows_getBudget = mysql_num_rows($getBudget);

?>

<!--

body {

font: 12px Verdana, Arial, Helvetica, sans-serif;

background: #DFFFF8;

margin: 0; /* it's good practice to zero the margin and padding of the body element to account for differing browser defaults */

padding: 0;

text-align: center; /* this centers the container in IE 5* browsers. The text is then set to the left aligned default in the #container selector */

color: #000000;

}

.oneColFixCtrHdr #container {

width: 780px; /* using 20px less than a full 800px width allows for browser chrome and avoids a horizontal scroll bar */

background: #FFFFFF;

margin: 0 auto; /* the auto margins (in conjunction with a width) center the page */

border: 1px solid #000000;

text-align: left; /* this overrides the text-align: center on the body element. */

}

.oneColFixCtrHdr #header {

background: #FFF;

padding: 0 10px 0 20px; /* this padding matches the left alignment of the elements in the divs that appear beneath it. If an image is used in the #header instead of text, you may want to remove the padding. */

text-align: right;

}

.oneColFixCtrHdr #header h1 {

margin: 0; /* zeroing the margin of the last element in the #header div will avoid margin collapse - an unexplainable space between divs. If the div has a border around it, this is not necessary as that also avoids the margin collapse */

padding: 10px 0; /* using padding instead of margin will allow you to keep the element away from the edges of the div */

}

.oneColFixCtrHdr #mainContent {

background: #DFFFF8;

width: 780px;

padding-top: 0;

padding-right: 20px;

padding-bottom: 0;

padding-left: 20px;

}

.oneColFixCtrHdr #footer {

padding: 0 10px; /* this padding matches the left alignment of the elements in the divs that appear above it. */

background:#DDDDDD;

}

.oneColFixCtrHdr #footer p {

margin: 0; /* zeroing the margins of the first element in the footer will avoid the possibility of margin collapse - a space between divs */

padding: 10px 0; /* padding on this element will create space, just as the the margin would have, without the margin collapse issue */

}

.oneColFixCtrHdr #container #header table tr .logout strong .logout {

text-align: right;

}

.oneColFixCtrHdr #container #header table tr .logout strong {

text-align: right;

}

.oneColFixCtrHdr #container #header table tr td p strong a {

text-align: left;

}

.oneColFixCtrHdr #container #header table tr td h2 {

}

.lftfloat {

text-align: left;

float: left;

}

.oneColFixCtrHdr #container #header table tr .logout {

float: right;

}

.oneColFixCtrHdr #container #header table tr .logout strong {

float: right;

}

.oneColFixCtrHdr #container #header table tr .logout .logout strong {

float: right;

}

.oneColFixCtrHdr #container #header table tr td p strong {

float: left;

}

.oneColFixCtrHdr #container #header table tr td h2 {

float: left;

}

.oneColFixCtrHdr #container #header table tr td strong {

float: left;

}

-->

</style>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Budget Record</title>

</head>

<body>

<tr>

<th scope="row"><p><a href="/new_budget/forms/Welcome.php">Return to Welcome Screen</a></p></th>

<th scope="row"><p><a href="/new_budget/forms/report2.php">View Budget Report</a></p></th>

<th scope="row"><p><a href="/new_budget/admin/manage_budgets.php">Edit budgets</a></p></th>

</table></center><BR/>

<center><h2>Insert Daily Budget </h2></center>

<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">

<td width="167" align="right" nowrap="nowrap"><strong>Rundate:</strong></td>

</tr>

<td nowrap="nowrap" align="right"><strong>Section:</strong></td>

</tr>

</tr>

<td nowrap="nowrap" align="right"><strong>Reporter:</strong></td>

</tr>

<td nowrap="nowrap" align="right"><strong>Budget Information:</strong></td>

</tr>

<td nowrap="nowrap" align="right"><strong>Notes:</strong></td>

</tr>

<td nowrap="nowrap" align="right"><strong>Deadline:</strong></td>

</tr>

<tr>

Factbox</td>

</tr>

<tr>

Graphic</td>

</tr>

<tr>

Photo</td>

</tr>

<tr>

Video</td>

</tr>

</table></td>

</tr>

<td nowrap="nowrap" align="right"><strong>Sidebar:</strong></td>

<tr>

Yes</td>

</tr>

<tr>

No</td>

</tr>

</table></td>

</tr>

<td nowrap="nowrap" align="right"><strong>Sidebar Budget:</strong></td>

</tr>

</tr>

</table>

</form>

?>

jvance38 · March 1, 2011

Cool, thank you so much. I got it to work by changing the record='.$result['id'].' to id='.$result['id'].' and adding the $id = $_GET['record']; to the Update and Delete pages. You rock!!! Once again, many thanks to you.

Sign In

Help with inline editing mysql search results

Recommended Posts

jvance38

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

jvance38

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

jvance38

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

jvance38

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

jvance38

Link to comment

Share on other sites

jvance38

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information