Professional Web Applications Themes

Input and Display Records With Same Script? - MySQL

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 ...

  1. #1

    Default Input and Display Records With Same Script?

    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.

    Bob Sanderson Guest

  2. #2

    Default Re: Input and Display Records With Same Script?

    Bob Sanderson wrote:
    > 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.
    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.


    Paul Lautman Guest

  3. #3

    Default Re: Input and Display Records With Same Script?

    Bob Sanderson wrote:
    > 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?
    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_record" 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
    [url]http://koivi.com/[/url]
    Justin Koivisto Guest

  4. #4

    Default Re: Input and Display Records With Same Script?

    Justin Koivisto wrote:
    > Bob Sanderson wrote:
    >> 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?
    >
    > 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_record" 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.
    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($errors)){
    // 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=24
    // 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
    }
    }
    ?>
    Justin Koivisto Guest

Similar Threads

  1. display blocks of records
    By weblover in forum Coldfusion - Advanced Techniques
    Replies: 7
    Last Post: June 8th, 03:17 AM
  2. Records Display formatting
    By xcoldnet in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: May 25th, 11:00 PM
  3. Display all records on a page?
    By woodywyatt in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 16th, 08:48 AM
  4. Display Returned Records
    By Paulie579 in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: April 15th, 09:02 PM
  5. When records don't display that are there
    By Gerry in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 10th, 09:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139