Input and Display Records With Same Script?

[Bob Sanderson]

I am trying to create a form for a MySQL database similar to a spreadsheet.
The idea is to display a list of records, with the last line of the list
being an input form. When the user enters data in the form and hits the
submit button, the data is entered and the form is reloaded with the new
data displayed and another input form becomes the last line.

Example ---

Before entering new data

Record 1
Record 2
Record 3
Input form Submit button


After entering new data

Record 1
Record 2
Record 3
Record 4
Input form Submit button

It seems is what I think the approach should be:

1. Display all current records - the last line is an input form
2. User adds data to the form and hits the Submit button
3. The form action calls the same script
4. The new data is entered into the database
5. Back to step one

Does this make sense or is there a better way? How do I structure the
queries to accomplish this?

Thanks in advance.

[Paul Lautman]

Re: Input and Display Records With Same Script?

Bob Sanderson wrote:[color=blue]
> I am trying to create a form for a MySQL database similar to a
> spreadsheet. The idea is to display a list of records, with the last
> line of the list being an input form. When the user enters data in
> the form and hits the submit button, the data is entered and the form
> is reloaded with the new data displayed and another input form
> becomes the last line.
>
> Example ---
>
> Before entering new data
>
> Record 1
> Record 2
> Record 3
> Input form Submit button
>
>
> After entering new data
>
> Record 1
> Record 2
> Record 3
> Record 4
> Input form Submit button
>
> It seems is what I think the approach should be:
>
> 1. Display all current records - the last line is an input form
> 2. User adds data to the form and hits the Submit button
> 3. The form action calls the same script
> 4. The new data is entered into the database
> 5. Back to step one
>
> Does this make sense or is there a better way? How do I structure the
> queries to accomplish this?
>
> Thanks in advance.[/color]

Funny you should ask, I just did a similar thing, except my input was at the
top with the records displayed in descending order.

1) Check if form has been submitted
1a) if form has been submitted, INSERT data into database
2) Query database to get all records
3) Output all records
4) Output Form elements

That's it. You may want to edit-check the data and send an error message if
the data doesn't pass muster.

[Justin Koivisto]

Re: Input and Display Records With Same Script?

Bob Sanderson wrote:[color=blue]
> I am trying to create a form for a MySQL database similar to a spreadsheet.
> The idea is to display a list of records, with the last line of the list
> being an input form. When the user enters data in the form and hits the
> submit button, the data is entered and the form is reloaded with the new
> data displayed and another input form becomes the last line.
>
> It seems is what I think the approach should be:
>
> 1. Display all current records - the last line is an input form
> 2. User adds data to the form and hits the Submit button
> 3. The form action calls the same script
> 4. The new data is entered into the database
> 5. Back to step one
>
> Does this make sense or is there a better way? How do I structure the
> queries to accomplish this?[/color]

That would work fine:

<?php
if(isset($_POST['add_record'])){
// verify POST fields for type, etc.
$q='INSERT INTO table1 (field2, field3, field4) VALUES ('.
'\''.mysql_real _escape_string( $_POST['field2']).'\''.
'\''.mysql_real _escape_string( $_POST['field3']).'\''.
'\''.mysql_real _escape_string( $_POST['field4']).'\''.
')';
// where there is an auto_increment field for PK (field1)
// fields 2-4 are text or varchar in example

// execute query, send message, etc.
}

$q='SELECT field1, field2, field3 field4 FROM table1';

// execute query, loop, display list

// add form to end of list with a submit button at end like
// <input type="submit" name="add_recor d" value="Add Record' />
?>

If you want to be able to edit each record, make them all form fields
with the values filled in. Use fields like:
<?php
echo "<input type='text' name='field1[$recID]' value='$value' />";
?>

For the last row, have it be the same format, just use 0 as the id
value. That way, when you post, you'll have something like the following
as your POST array structure:

$_POST = array (
[field1] = array (
[0] = New Rec value
[1] = value1
[2] = value2
[24] = value24
)
[field2] = array (
[0] = New Rec value2
[1] = value1-2
[2] = value2-2
[24] = value24-2
)
)


If there wasn't anything new added to the form, the structure would be
the same, but the new values would be empty.

HTH

--
Justin Koivisto, ZCE

http://koivi.com/

[Justin Koivisto]

Re: Input and Display Records With Same Script?

Justin Koivisto wrote:[color=blue]
> Bob Sanderson wrote:[color=green]
>> I am trying to create a form for a MySQL database similar to a
>> spreadsheet. The idea is to display a list of records, with the last
>> line of the list being an input form. When the user enters data in the
>> form and hits the submit button, the data is entered and the form is
>> reloaded with the new data displayed and another input form becomes
>> the last line.
>>
>> It seems is what I think the approach should be:
>>
>> 1. Display all current records - the last line is an input form
>> 2. User adds data to the form and hits the Submit button
>> 3. The form action calls the same script 4. The new data is entered
>> into the database
>> 5. Back to step one
>>
>> Does this make sense or is there a better way? How do I structure the
>> queries to accomplish this?[/color]
>
> That would work fine:
>
> <?php
> if(isset($_POST['add_record'])){
> // verify POST fields for type, etc.
> $q='INSERT INTO table1 (field2, field3, field4) VALUES ('.
> '\''.mysql_real _escape_string( $_POST['field2']).'\''.
> '\''.mysql_real _escape_string( $_POST['field3']).'\''.
> '\''.mysql_real _escape_string( $_POST['field4']).'\''.
> ')';
> // where there is an auto_increment field for PK (field1)
> // fields 2-4 are text or varchar in example
>
> // execute query, send message, etc.
> }
>
> $q='SELECT field1, field2, field3 field4 FROM table1';
>
> // execute query, loop, display list
>
> // add form to end of list with a submit button at end like
> // <input type="submit" name="add_recor d" value="Add Record' />
> ?>
>
> If you want to be able to edit each record, make them all form fields
> with the values filled in. Use fields like:
> <?php
> echo "<input type='text' name='field1[$recID]' value='$value' />";
> ?>
>
> For the last row, have it be the same format, just use 0 as the id
> value. That way, when you post, you'll have something like the following
> as your POST array structure:
>
> $_POST = array (
> [field1] = array (
> [0] = New Rec value
> [1] = value1
> [2] = value2
> [24] = value24
> )
> [field2] = array (
> [0] = New Rec value2
> [1] = value1-2
> [2] = value2-2
> [24] = value24-2
> )
> )
>
>
> If there wasn't anything new added to the form, the structure would be
> the same, but the new values would be empty.[/color]

BTW - if you will have a lot of records, instead of having a huge form
like this, you will be better off having separate forms for editing via
a link in the row of the list. (I like using Edit and Delete links.)
Then in the code you'd have something more like:

<?php
if(isset($_POST['add_record'])){
// insert stuff
}else if(isset($_POST['edit_record'])){
// update stuff
}else if(isset($_POST['delete_record'])){
// delete stuff
}

if(isset($error s)){
// I store error messages in an array so I can output them all at once
// for the user to review. You may want to do other things here as well
}else{

if(isset($_GET['edit'])){
// show edit form. I like to use URIs like:
// page.php?edit=2 4
// when I want to edit record 24
}else if(isset($_GET['delete'])){
// show delete confirm form
// I use the same type of URI as the edit for this as well
}else{
// no action request, show list with links
}
}
?>