[SOLVED] use html/php to mark mysql records for processing ?

[dinvinci]

I am a volunteer building a simple web application for a charity. It consists of a form that collects data and posts it to a mysql database (`new_referrals`). This is the form:

 

http://jwchendersonville.org/referral.php

 

The form results can then be read/printed by other volunteers on this page:

 

http://jwchendersonville.org/results.php

 

The default behavior is that after printing out all the new referrals, a volunteer will click a "process" button which will copy the records to a "processed referrals" database and delete all the existing records from the "new referrals" database. I figured out how to do that.

 

However sometimes a record may be a duplicate or otherwise have a problem -- I would like some kind of checkbox the volunteer can check to indicate that this record should be moved to the "problem" database rather than being processed. I can't figure out quite where to start with that.

 

To create the results page I am using code along these lines:

 

<?php
$sql = "SELECT * FROM new_referrals ORDER BY p_last";
$query = mysql_query($sql);
while($row = mysql_fetch_array($query)) {

echo "<tr>";
echo "<td>Record ".$row['index']." received <b>".$row['received']."</b> </td>";
echo "</tr>";
(et cetera for all the info displayed for each record)

 

I am very much a newbie FTR and only got this far by looking at other tutorials...if you can help with this please assume very little PHP knowledge on my part. Thanks!

[otuatail]

Why not kee the records in the same table. you could have an extra field "Proccess" This can have a value for completed or error. Then just look for error flag or show only outstandidng

 

Desmond.

 

[dinvinci]

I would like to keep all the records in one table and use a flag like you describe. I just don't quite know how to make it work. Currently my code displays all the records in the table. How would I tell it to loop through and display only the records marked "new"? And then what command would I use to turn off the flag on all records? Would this be a boolean field?

[otuatail]

If you have a field called status (small int). When the form is submitted this has a default value of 0. You should also have a unique ID in the table (Autonumber) and maybe a date of entry field. Then change query to

 

$sql = "SELECT * FROM new_referrals WHERE status = 0 ORDER BY p_last";

 

When processed succesfully.

$SQL = "UPDATE new_referrals SET status = 1 WHERE ID = $ID // $ID = the Autonumber.

 

If there is a problem then

$SQL = "UPDATE new_referrals SET status = 2 WHERE ID = $ID

 

To view the problem only records do this.

$sql = "SELECT * FROM new_referrals WHERE status = 1 ORDER BY p_last";

 

If you add a field for Date, this could be a string field and populated by date()

this is a 10 digit number representing total seconds from 1AM Jan 1970.

 

You can periodicaly delete old data by

$sql = "DELETE new_referrals WHERE satatus = 1 and date is < // you pick the date

 

Is this any help.

 

Desmond.

 

 

[otuatail]

Sorry I ment time() not date()

 

Desmond.

 

[dinvinci]

Thank you, that was very helpful! I created the autonumber and status fields and also put that first line of code you suggested into my results page:

 

$sql = "SELECT * FROM new_referrals WHERE status = 0 ORDER BY p_last";

 

As for the second and third lines, where you set status to 1 and 2 respectively, I understand the logic there but I don't quite know how to implement it. Going back to my idea of a checkbox on each record loop, my thinking is that "checked" records would get status of 2 and unchecked records would get the status of 1. How do I implement this from the html page my user sees?

 

 

[otuatail]

This is slightly harder if you created the page like

 

<input type="checkbox" name="<?=myrecords['ID']?>">

Each checkbox will have it's name set to the value of the table ID

 

The problem is the proccessing page will need to know these ID's. You will have to do the same query on that page to retreave them. It is not an easy task. doing one record at a time would be easy to code.

 

Desmond.

 

 

[atlanta]

Do you have AIM or LIVE messenger i have some free time on my hands i will help you code it if you want.

[otuatail]

This is a sample way the processing page could do what you sugested.

Desmond

 

<?
$sql = "SELECT * FROM new_referrals WHERE status = 0 ORDER BY p_last";
$query = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_array($rs))
{
  $rec = $row['ID'];
  if($_POST['$rec'] == 'CHECKED')
  {
    $sql_update = "UPDATE new_referrals SET status = " . 1 . " WHERE ID = " . $rec;
  }
}
?>

[dinvinci]

Okay, I'm sort of following that...

 

I added a line of code to make a checkbox:

 

echo "<tr><td align=\"center\"> <input name=\"ID\" value=\"".$row['autonumber']."\" type=\"checkbox\" /> </td>";

 

It produces this in the html:

 

<input name="ID" value="1" type="checkbox" />

 

...where the number in the value is equal to the autonumber field in my database.

[otuatail]

What I meant was make the name the ID. In teh code supplied above, you can get the status of the checkbox.

 

Desmond.

 

[dinvinci]

Would I put that code from reply #8 inside a form tag or...  ???

 

Here is my process so far:

 

• volunteer loads review.php - this presents a summary list of all the referrals with status = 0, meaning they are unprocessed -- it will look like this page: http://jwchendersonville.org/review.php
  
• volunteer checks the new referrals for any duplicates and marks them somehow (checkbox ?)
  
• volunteer clicks a button which changes duplicate records status to 2 and also moves the user to a new page showing only the "good" new records whose status is still equal to 0; this page would be a detailed view looking like this: http://jwchendersonville.org/results.php
  
• After printing out these records, the volunteer would click a button to change the status on all of these to 1, so they will no longer be displayed as "new" referrals
  

 

Just FYI, there would never be more than one person accessing/manipulating records at a time. There is only one computer at this location.

 

Maybe I have not planned the workflow in the optimal way. I am open to suggestion on a better way. 

 

Thanks!

 

atlanta, thanks for your offer, I'm not sure how to best take you up on it, but I appreciate it!

[otuatail]

Ok first off. How is the date stored? is it a time stamp time(). or Date.

Either way it could be reversed to be dd-mm-yyyy

Do you need Hours minutes seconds?

 

If you mean that duplicate records are the errors you mentioned then YEs. SET to 2

 

I would have a hidden processing page with only proccess.php. no html. This would update the database and do a

header('Location: review.php'); back to the page.

As this page only shows status 0 the volunteer will just see an imediate change

on the page. All errors know removed.

 

He could then make more changes if needed. removing more errors.

You can have 2 submit buttons on a the page like this.

<input type="submit" name="submit" value="remove duplicates">

<input type="submit" name="submit" value="Show results">

 

on the hidden page proccess.php you can do this.

$submit = $_POST['submit'];

if($submit == "Show results")

header('Location: results.php');

// else update the database setting errors then

header('Location: index.php');

 

so hitting remove duplicates allows multipul changes before proceeding to results.php

 

However the reviews page you show is for a single parent. What you have after changing the duplicates to errors is only to remove them. the list is now a list of all parents.

do you intend to have muliple tables of each parent on the same web page as in results.php. not just the one

you have shown.

 

Desmond.

 

Hope it makes sense. need to do a flow control on pages.

 

 

[otuatail]

Sorry. One last thing. If you go to the results page BECAUSE your happy !

 

You could

 

$sql = UPDATE table SET status = 1 WHERE status = 0;

 

So next time the volunteer starts with a new sheet.

 

Desmond.

 

[otuatail]

I have done a lot of this kind of thing. I sugest a few other things

If you store the date entered in the table as a unix time stamp time()

you can display it in any way you want using the date function. The volunteer

may not need to see hour , mins, seconds.

 

An Admin page password protected

where you can see all the data regardless of status.

Change the status ih he/she has made a mistake

Remove old data

Statistics revews etc.

 

Desmond.

 

[dinvinci]

Ok first off. How is the date stored? is it a time stamp time(). or Date.

Either way it could be reversed to be dd-mm-yyyy

Do you need Hours minutes seconds?

 

I don't need the hours minutes seconds. I would prefer mm-dd-yyyy but I am using the mysql date-time stamp and I don't know how to format it differently.It doesn't matter what time of day a referral comes in but sometimes we get asked for the date a particular child was referred so we need to be able to easily find that.

 

on the hidden page proccess.php you can do this.

$submit = $_POST['submit'];

if($submit == "Show results")

header('Location: results.php');

// else update the database setting errors then

header('Location: index.php');

 

so hitting remove duplicates allows multipul changes before proceeding to results.php

 

Okay I understand this bit and I agree, thanks.

 

However the reviews page you show is for a single parent. What you have after changing the duplicates to errors is only to remove them. the list is now a list of all parents.

do you intend to have muliple tables of each parent on the same web page as in results.php. not just the one

you have shown.

 

The review.php page currently should show 6 rows. Each row is a different parent and two children. The volunteer just needs to check for any duplicates on the parent's name or otherwise bad records. Once they've cleared the bad records, they can go to the results.php page which is an expanded view of the same 6 records, showing more fields and in an easier to read format.

[otuatail]

Ok thats fine. you don't need the

<input type="submit" name="submit" value="remove duplicates">

Just the

<input type="submit" name="submit" value="Show results">

 

If you are using time() for the date entry you can use the following

Assume $stamp = 1191532312 // equal to 04-10-2007 22:11:52

$show = $date = date('D d-M-Y' , $stamp ) // Thu 04-10-2007

 

[dinvinci]

I'm making progress...sorry for being so dim but thanks for all the help!

 

On my review page I have a form tag like this:

 

<form action="process.php" method="get">

 

I also have two submit buttons that say "Remove duplicates" and "Show results". The process.php page has just this code and nothing else:

<?php
$submit = $_POST['submit'];
if($submit == "Remove duplicates") {
header("Location: review2.php");
} elseif ($submit == "Show results") {
header("Location: results.php");
}
?>

 

But when I click one of the buttons, I just go to a blank page with the URL:

 

http://jwchendersonville.org/process.php?submit=Show+results

 

Shouldn't it take me to the results.php page instead?

[dinvinci]

I figured out that the method needs to be post, not get.

 

The code on my review page is producing this in the html:

 

<input name="checkbox[8]" id="checkbox[8]" value="8" type="checkbox" />

 

On my process.php page I have this code:

 

$checkbox = $_POST['checkbox'];

foreach($checkbox as $key => $value){
$autonumber= $value;
$sql = "UPDATE referral SET status = '2' WHERE autonumber='$autonumber'";

 

I'm not getting any errors, but the status of the records is not getting updated  ???  I know I'm missing something...

 

 

[dinvinci]

Hold your breath...I figured it out...

 

<?php
$submit = $_POST['submit'];
if($submit == "Show results") {

echo "<meta http-equiv=\"refresh\" content=\"0;URL=results.php\">";

} elseif ($submit == "Remove duplicates") {

$checkbox = $_POST['checkbox'];

foreach($checkbox as $key => $value) {
$autonumber= $value;
$sql = "UPDATE referral SET status = 2 WHERE autonumber='$autonumber'";
mysql_query($sql);

echo "<meta http-equiv=\"refresh\" content=\"0;URL=review2.php\">";

}
mysql_close();	

}
?>

[dinvinci]

I am all set with this project...thanks for all the advice!