Jump to content

[SOLVED] Storing data

hostfreak

By hostfreak
in PHP Coding Help

 Share

Recommended Posts

hostfreak Regular Member

hostfreak

Posted
Posted

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]

Link to comment
https://forums.phpfreaks.com/topic/59796-solved-storing-data/
Share on other sites
hostfreak Regular Member

hostfreak

Posted
  • Author
Posted

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.

Link to comment
https://forums.phpfreaks.com/topic/59796-solved-storing-data/#findComment-298052
Share on other sites
hostfreak Regular Member

hostfreak

Posted
  • Author
Posted

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]

Link to comment
https://forums.phpfreaks.com/topic/59796-solved-storing-data/#findComment-298090
Share on other sites
AndyB Regular Member

AndyB

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

Link to comment
https://forums.phpfreaks.com/topic/59796-solved-storing-data/#findComment-298839
Share on other sites
hostfreak Regular Member

hostfreak

Posted
  • Author
Posted

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?

Link to comment
https://forums.phpfreaks.com/topic/59796-solved-storing-data/#findComment-298990
Share on other sites
hostfreak Regular Member

hostfreak

Posted
  • Author
Posted

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.';
        }
    }

Link to comment
https://forums.phpfreaks.com/topic/59796-solved-storing-data/#findComment-299014
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.