[SOLVED] Storing data

hostfreak · July 13, 2007

I was wondering what would be the best way for one to store data (into a mysql database) like shown in

the attachment. This is for a work order system, so there will be multiple work orders added to the database.

The attachment is an example of what one work order input would look like.

As you can see there are 8 rows and 5 columns. Not all the time will all rows be completely filled out.

The totals wont need to be stored in the database, they will always be generated live based off the input.

I was thinking to create a field for each column, then store each row with a unique id and another id

that will link them together?

Another thing, sometimes I will need more rows, what would be a way to allow the user an options to

generate more fields before they add it to the database?

Any help is appreciated.

[attachment deleted by admin]

hostfreak · July 14, 2007

I have figured out how I will generate the rows based on the amount of rows needed to be added. Now I am still just trying to figure out how I will store the data in a database.

I think storing each row individually would be the best way, however, I am unsure how to do that.

hostfreak · July 14, 2007

Oh, and just to be clear, those won't be the only fields I am going to be adding. I have uploaded another attachment to show what I mean.

So far my database structure looks like:

  `equipment_number` int(255) NOT NULL default '0',
  `mileage` varchar(100) NOT NULL default '',
  `hours` varchar(100) NOT NULL default '',
  `operator` varchar(100) NOT NULL default '',
  `date_in` date NOT NULL default '0000-00-00',
  `plant` varchar(100) NOT NULL default '',
  `problem_description` text NOT NULL,
  `service_type` varchar(100) default NULL,
  `start_date` date NOT NULL default '0000-00-00',
  `complete_date` date NOT NULL default '0000-00-00',
  `initials` text NOT NULL,
  `work_code` text NOT NULL,
  `commercial_labor` text NOT NULL,
  `inventory_parts` text NOT NULL,
  `commercial_parts` text NOT NULL,
  `parts_used` text NOT NULL,
  `CAT_description` text NOT NULL,
  `mechanic_signature` varchar(100) NOT NULL default '',
  `supervisor_signature` varchar(100) NOT NULL default '',
  `id` int(255) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)

My code for the page is:

<?php
if ($session->logged_in)
{
?>

<script type="text/javascript">
function add_fields()
{
   URL = location.href
if (URL.search("rows") != -1)
{
   URL = URL.substring(0,URL.search("rows")-1)
}
if (document.getElementById('rows').options[document.getElementById('rows').selectedIndex].value != -1)
   URL = URL+"&rows="+document.getElementById('rows').options[document.getElementById('rows').selectedIndex].value
   location.href = URL
}
</script>

<?php
    function loop($amount)
    {
        for ($i = 1; $i <= $amount; $i++)
        {
            echo "<option value='$i'>$i</option>";
        }
    }

    $rows = $_GET['rows'];
    function generate_rows($field_name, $times, $onchange, $onchange_letter)
    {   
        for($i = 1; $i <= $times; $i++)
        {
            if (($onchange != "1") && (!$onchange_letter))
            {
                echo "<input type=\"text\" name=\"$field_name$i\" maxlength=\"3\" class=\"basic_input\"><div class=\"spacer\"></div>";
            }
            else
            {
                echo "<input type=\"text\" name=\"$field_name$i\" onChange=\"calculate_$onchange_letter()\" class=\"price_input\"><div class=\"spacer\"></div>";
            }
        }
    }

    if (isset($_POST['submit']))
    {

        //Set variable values, run query - part im having trouble trying to figure how to indvidually add the rows

        $result = mysql_query($query) OR die(mysql_error());
        if ($result)
        {
            echo 'The Work Order has been added!';
        }
        else
        {
            echo 'There was an error adding the Work Order! If the problem persist, please contact the administrator.';
        }
    }
?>

<html>
    <head>
        <link rel="stylesheet" type="text/css" href="equipment/work_orders/css.css" />
        <script type="text/javascript" src="equipment/work_orders/js.js"></script>
    </head>
    <body onload="calculate_a(); calculate_b(); calculate_c();">

        <form name="work_order" method="POST" action="">




            <table>
                <tr>
                    <td>
                        <p align="center">
                            <div id="title">Company Name - Work Orders</div>
                            <div id="instructions">
                                All sections must be completed<br>
                                Mileage and or hours must be shown <br>
                            </div>
                        </p>
                    </td>
                </tr>
            </table>




            <div class="spacer"></div>




            <table>
                <tr>
                    <td><p class="column_title" align="right">Unit Number:</p></td>
                    <td> <input type="text" name="equipment_number" size="23" class="basic_input"></td>
                    <td><p class="column_title" align="right">Mileage / Hours:</p></td>
                    <td> <input type="text" name="mileage" size="8" class="basic_input"> / <input type="text" name="hours" size="8" class="basic_input"></td>
                </tr>

                <tr>
                    <td><p class="column_title" align="right">Operator:</p></td>
                    <td> <input type="text" name="operator" size="23" class="basic_input"></td>
                    <td><p class="column_title" align="right">Date In:</p></td>
                    <td>
                         <select name="date_in_M" class="basic_select">
                            <option value=''>M</option>
                    <?php
                            //Using the "loop function" set amount of months to 12 for months of the year
                            loop(12);
                    ?>
                        </select>
                         <select name="date_in_D" class="basic_select">
                            <option value=''>D</option>
                    <?php
                            //Using the "loop function" set amount of days to 31 for days of the month
                            loop(31);
                    ?>
                        </select>
                         <input type="text" name="date_in_Y" size="5" class="basic_input">
                    </td>
                </tr>

                <tr>
                    <td><p class="column_title" align="right">Plant:</p></td>
                    <td> <input type="text" name="plant" size="23" class="basic_input"></td>
                    <td></td>
                    <td></td>
                </tr>
            </table>




            <table>
                <tr>
                    <td><p class="column_title" align="right">Explain Problem:</p></td>
                    <td> <textarea rows="10" name="problem_description" style="width: 454;" class="basic_input"></textarea></td>
                </tr>
            </table>




            <div class="spacer"></div>




            <table>
                <tr>
                    <td><p class="column_title" align="center">Please check one of the following:</p></td>
                </tr>
                <tr>
                    <td>
                        <table>
                            <tr>
                                <td>
                                    <table>
                                        <tr>
                                            <td><p align="right">Scheduled:</p></td>
                                            <td>
                                                <table>
                                                    <tr>
                                                        <td> </td>
                                                        <td><p align="right"><input type="radio" value="Scheduled" name="service_type"></p></td>
                                                        <td> </td>
                                                    </tr>
                                                </table>
                                            </td>
                                            <td><p align="right">Unscheduled:</p></td>
                                            <td>
                                                <table>
                                                    <tr>
                                                        <td> </td>
                                                        <td><p align="center"><input type="radio" value="Unscheduled" name="service_type"></p></td>
                                                        <td> </td>
                                                    </tr>
                                                </table>
                                            </td>
                                            <td><p align="right">Road Service:</p></td>
                                            <td>
                                                <table>
                                                    <tr>
                                                        <td> </td>
                                                        <td><p align="center"><input type="radio" value="Road Service" name="service_type"></p></td>
                                                        <td> </td>
                                                    </tr>
                                                </table>
                                            </td>
                                        </tr>
                                    </table>
                                </td>
                            </tr>
                        </table>
                    </td>
                </tr>
            </table>




            <div class="spacer"></div>




            <table>
                <tr>
                    <td><p class="column_title">Start Date:</p></td>
                    <td>
                         <select name="start_date_M" class="basic_select">
                            <option value=''>M</option>
                        <?php
                            //Using the "loop function" set amount of months to 12 for months of the year
                            loop(12);
                        ?>
                        </select>
                         <select name="start_date_D" class="basic_select">
                            <option value=''>D</option>
                        <?php
                            //Using the "loop function" set amount of days to 31 for days of the month
                            loop(31);
                        ?>
                        </select>
                         <input type="text" name="start_date_Y" size="5" class="basic_input">
                    </td>
                    <td><p class="column_title">Complete Date:</p></td>
                    <td>
                         <select name="complete_date_M" class="basic_select">
                            <option value=''>M</option>
                        <?php
                            //Using the "loop function" set amount of months to 12 for months of the year
                            loop(12);
                        ?>
                        </select>
                         <select name="complete_date_D" class="basic_select">
                            <option value=''>D</option>
                        <?php
                            //Using the "loop function" set amount of days to 31 for days of the month
                            loop(31);
                        ?>
                        </select>
                         <input type="text" name="complete_date_Y" size="5" class="basic_input">
                    </td>
                </tr>
            </table>




            <div class="spacer"></div>




            <table>
                <tr>
                    <td><p class="column_title" align="center">Initials</p></td>

                    <td><p class="column_title" align="center">Work Code</p></td>

                    <td><p class="column_title" align="center">Commercial Labor</p></td>

                    <td><p class="column_title" align="center">Inventory Parts</p></td>

                    <td><p class="column_title" align="center">Commercial Parts</p></td>
                </tr>

                <tr>
<?php
if ($rows)
{
?>
                    <!---- Initials ---->
                    <td valign="top">
                    <?php
                        generate_rows("initials_row", $rows, 0, "");
                    ?>
                    </td>

                    <!---- Work Code ---->
                    <td valign="top">
                    <?php
                        generate_rows("work_code_row", $rows, 0, "");
                    ?>
                    <input type="text" name="" value="Totals:" class="basic_input" READONLY />
                    </td>

                    <!---- Commercial Labor ---->
                    <td valign="top">
                    <?php
                        generate_rows("number_a_row", $rows, 1, "a");
                    ?>
                        <input type="text" name="totals_a" class="basic_input" READONLY />
                    </td>


                    <!---- Inventory Parts ---->
                    <td valign="top">
                    <?php
                        generate_rows("number_b_row", $rows, 1, "b");
                    ?>
                        <input type="text" name="totals_b" class="basic_input" READONLY />
                    </td>

                    <!---- Commercial Parts ---->
                    <td valign="top">
                    <?php
                        generate_rows("number_c_row", $rows, 1, "c");
                    ?>
                        <input type="text" name="totals_c" class="basic_input" READONLY />
                    </td>

                </tr>

            </table>
<?php
}
else
{
?>
            <table>
                <tr>
                    <td>
                        Please select the number of rows required: 
                        <select name='rows' id='rows' onchange='add_fields();'>
                        <option value='-1' selected='selected'>
<?php
    if ($rows == "")
    {
        echo "Choose Number";
    }
    else
    {
        echo "$rows";
    }
    echo "</option>";
    loop(20);
    echo "</select>";
}
?>
                    </td>
                </tr>
            </table>




        </form>

    </body>
</html>

<?php
}
else
{
    echo 'You are not authorized to view this page!';
}
?>

[attachment deleted by admin]

hostfreak · July 15, 2007

Sorry to bump this, just hoping someone can help or at least has some suggestions. I am definitely open to them. Thanks.

MadTechie · July 15, 2007

Long yet Vague question...

Whats the "Problem"

hostfreak · July 15, 2007

There isn't necessarily a problem yet, as I haven't figured out how to do what I want. So the question is geared towards receiving a suggestion.

AndyB · July 15, 2007

I was thinking to create a field for each column, then store each row with a unique id and another id
that will link them together?

That seems eminently sensible.

Another thing, sometimes I will need more rows, what would be a way to allow the user an options to
generate more fields before they add it to the database?

I'd let them add one row at a time with the option (after adding it) to add another row. If they wanted to add another row (to the same w.o.), I'd probably display what they already have in the db for that work order.

hostfreak · July 15, 2007

Ok, yeah that makes sense, and is doable. The other way, I would somehow need to loop through the rows and add them to the database.

So I guess I need to make two tables in the database. One to hold the main work order information and another for the other information;

Table One:

  `equipment_number` int(255) NOT NULL default '0',
  `mileage` varchar(100) NOT NULL default '',
  `hours` varchar(100) NOT NULL default '',
  `operator` varchar(100) NOT NULL default '',
  `date_in` date NOT NULL default '0000-00-00',
  `plant` varchar(100) NOT NULL default '',
  `problem_description` text NOT NULL,
  `service_type` varchar(100) default NULL,
  `start_date` date NOT NULL default '0000-00-00',
  `complete_date` date NOT NULL default '0000-00-00',
  `parts_used` text NOT NULL,
  `CAT_description` text NOT NULL,
  `mechanic_signature` varchar(100) NOT NULL default '',
  `supervisor_signature` varchar(100) NOT NULL default '',
  `id` int(255) NOT NULL auto_increment,

Table Two:

  `initials` text NOT NULL,
  `work_code` text NOT NULL,
  `commercial_labor` text NOT NULL,
  `inventory_parts` text NOT NULL,
  `commercial_parts` text NOT NULL,
  `key_id` int(255) NOT NULL auto_increment,
  `id` int(255) NOT NULL auto_increment,

With the 'key_id' being the 'id' of the relative entry in "Table One" to link the two together.

Does that seem right?

AndyB · July 15, 2007

I'd say you're on track with this. Some of your field sizes look waaay too large, e.g varchar 100 for mileage and hours (those would be pretty big numbers), etc.

hostfreak · July 15, 2007

Yes, I will definitely fix those. Thanks for the help Andy.

hostfreak · July 15, 2007

Oh, one more thing.

How will I code the script to predict/gather what the 'id' of the entry into "table one" will be, to be able to enter it into the 'key_id' for the entry into "table_two". That is of course running both queries back-to-back?

    if (isset($_POST['submit']))
    {   
        //Query One
        $equipment_number = $_POST['equipment_number'];
        $mileage = $_POST['mileage'];
        $hours = $_POST['hours'];
        $operator = $_POST['operator'];
        $date_in_Y = $_POST['date_in_Y'];
        $date_in_M = $_POST['date_in_M'];
        $date_in_D = $_POST['date_in_D'];
        $plant = $_POST['plant'];
        $problem_description = $_POST['problem_description'];
        $service_type = $_POST['service_type'];
        $start_date_Y = $_POST['start_date_Y'];
        $start_date_M = $_POST['start_date_M'];
        $start_date_D = $_POST['start_date_D'];
        $complete_date_Y = $_POST['complete_date_Y'];
        $complete_date_M = $_POST['complete_date_M'];
        $complete_date_D = $_POST['complete_date_D'];

        $query_one = "INSERT INTO work_orders_one(";
        $query_one .= "equipment_number , mileage , hours , operator , date_in , plant , problem_description , service_type , start_date , complete_date";
        $query_one .= ") ";
        $query_one .= "VALUES(";
        $query_one .= "'$equipment_number','$mileage','$hours','$operator','$date_in_Y-$date_in_M-$date_in_D','$plant','$problem_description','$service_type',";
        $query_one .= "'$start_date_Y-$start_date_M-$start_date_D','$complete_date_Y-$complete_date_M-$complete_date_D'";
        $query_one .= ")";

        $result_one = mysql_query($query_one) OR die(mysql_error());


        //Query Two
        $initials = $_POST['initials'];
        $work_code = $_POST['work_code'];
        $commercial_labor = $_POST['commercial_labor'];
        $inventory_parts = $_POST['inventory_parts'];
        $commercial_parts = $_POST['commercial_parts'];

        $query_two = "INSERT INTO work_orders_two(";
        $query_two .= "initials , work_code , commercial_labor ,  inventory_parts , commercial_parts , key_id";
        $query_two .= ") ";
        $query_two .= "VALUES(";
        $query_two .= "'$initals',";
        $query_two .= "'$work_code',";
        $query_two .= "'$commercial_labor',";
        $query_two .= "'$inventory_parts',";
        $query_two .= "'$commercial_parts',";
        $query_two .= "'$key_id',"; //Needs to be the 'id' of the previous query
        $query_two .= ")";

        $result_two = mysql_query($query_two) OR die(mysql_error());


        if ($result_one && $result_two)
        {
            echo 'The Work Order has been added!';
        }
        else
        {
            echo 'There was an error adding the Work Order! If the problem persist, please contact the administrator.';
        }
    }

hostfreak · July 15, 2007

Edit- I think I have solved it.

I created a counter, that will create the 'key_id'. Then just store the 'key_id' in both tables, to link the entries.

Sign In

[SOLVED] Storing data

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information