?
Solved

Search mySQL Database, view result, and resubmit back to database with hidden fields

Posted on 2012-08-21
3
Medium Priority
?
526 Views
Last Modified: 2012-08-22
Hi All

I am trying to write a small script to search a small database, view the result on screen, then have a button to say "Checked", which submits the data back to the database, along with some hidden fields:

The database structure is:
Table: main_stock

Fields:
id
curr_timestamp
mastercategory
category
product_desc
newown
barcode
serial
stockcode
status

So first the user goes to a simple search page with one input box that searches the barcode field (each barcode is unique, so there will only be one result)
The following fields are then displayed on the page:
product_desc
mastercategory
category
barcode
serial

I have all the above working no problem, what i want to do then is have a button that says "Checked" which submits the info back into the database, along with the following hidden fields:

$curr_timestamp = strtotime("now"); 
$status = "CheckedOK"; 

Open in new window


What I have tried so far is the following but it doesnt find data from the array:

<?php
if(isset($_POST['submit']))
{

$url = 'http://172.16.0.250/stocktest/search.php'; // Where to redirect after form is processed.
$curr_timestamp = strtotime("now"); 
$status = "CheckedOK"; 


$sql = "INSERT INTO main_stock WHERE id = $id ( 
                  `id`, `curr_timestamp`, `mastercategory`, `category`, `product_desc`, `newown`, `barcode`, `serial`, `stockcode`, `status` 
               ) VALUES ( 
                  '{$id}', '{$curr_timestamp}', '{$mastercategory}', '{$category}', '{$product_desc}', '{$newown}', '{$barcode}', '{$serial}', '{$stockcode}', '{$status}' 
               )"; 
echo '<META HTTP-EQUIV=Refresh CONTENT="0; URL='.$url.'">';
}
?>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
      <input type="submit" name="submit" value="Checked OK"><br>
</form>

Open in new window



As there is only one result, and the fields pulled from the database dont change, I presume that i only need to submit the hidden fields back to the database, to the item in the search result, but am unsure on how to do this

Any help is greatly appreciated
Thanks J
0
Comment
Question by:Jon C
  • 2
3 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 38318264
If you define a column as the type TIMESTAMP MySQL will update the timestamp automatically when the row is updated.  Note that something actually has to change in the row -- simply issuing the UPDATE will not trigger an actual update if the data is the same as the content of the row.

I think I would add a column to the table.   Call it something like "checked" and make it varchar(3) DEFAULT 'No'.   Then you can have a hidden input like this:

<input name="checked" type="hidden" value="Yes" />

The general design of something like this would be to have a script that queries the data base and creates an HTML form prepopulated with the contents of the row of data.  The client would see the information in the form, change it (if appropriate) and submit the form.  The action= script would use the contents of the form to overwrite the row in the data base.

HTH, ~Ray
0
 

Author Comment

by:Jon C
ID: 38318371
Many thanks for your reply, That sounds like just what i need, i think I was trying to over complicate it.

Just one last thing, once the search is done and the HTML form is populated, I wouldn't want to give the client access to edit the fields, how could I display the result that wasn't editable but that would able me to submit it with the hidden field?

Thanks again
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38318950
You can mark some HTML form fields "readonly" but the only truly safe solution is to simply avoid updating the data base with any data items that are not things you want to change.  A hacker can (and eventually will) bypass your HTML form and post toxic information directly into your script.  So make sure that your script abides by the mantra, "Accept only known good values."
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question