Page 2 of 3

Posted: Sun Dec 16, 2007 6:18 pm
by sharpstyx
ok i have 5 tables, do you want all five?

Posted: Sun Dec 16, 2007 6:20 pm
by John Cartwright
Relevant table(s), yes.

Posted: Sun Dec 16, 2007 10:10 pm
by sharpstyx
ok i figured it out, by figured it out i mean i figured out how to post the structure :D

Code: Select all

mysql> describe casings;
+---------------------------------+-------------+------+-----+------------------
-+-------+
| Field                           | Type        | Null | Key | Default
 | Extra |
+---------------------------------+-------------+------+-----+------------------
-+-------+
| timestamp                       | timestamp   | NO   | PRI | CURRENT_TIMESTAMP
 |       |
| weekending                      | date        | NO   |     |
 |       |
| date                            | date        | NO   |     |
 |       |
| start_time                      | time        | NO   |     |
 |       |
| stop_time                       | time        | NO   |     |
 |       |
| Mechancial_Downtime_Total_mins  | varchar(10) | NO   |     |
 |       |
| Operational_Downtime_Total_mins | varchar(10) | NO   |     |
 |       |
| Gangtime                        | varchar(10) | NO   |     |
 |       |
| Gang_Run_Hours                  | varchar(10) | NO   |     |
 |       |
| Sets_to_the_room                | varchar(10) | NO   |     |
 |       |
| sets_saved                      | varchar(10) | NO   |     |
 |       |
| Percent_of_Kill                 | varchar(10) | NO   |     |
 |       |
| Bundles                         | varchar(10) | NO   |     |
 |       |
| Actual_Runners                  | varchar(10) | NO   |     |
 |       |
| Percent_of_Sets                 | varchar(10) | NO   |     |
 |       |
| Bundle_Runner_Count             | varchar(10) | NO   |     |
 |       |
| Yield                           | varchar(10) | NO   |     |
 |       |
| Avg_Runner_Length               | varchar(10) | NO   |     |
 |       |
| Bundle_Length                   | varchar(10) | NO   |     |
 |       |
| Salt_Usage_Bags                 | varchar(10) | NO   |     |
 |       |
| Pet_food_slurry_lbs             | varchar(10) | NO   |     |
 |       |
+---------------------------------+-------------+------+-----+------------------
-+-------+
21 rows in set (0.00 sec)

mysql> describe head_table;
+---------------------------------+-------------+------+-----+------------------
-+-------+
| Field                           | Type        | Null | Key | Default
 | Extra |
+---------------------------------+-------------+------+-----+------------------
-+-------+
| timestamp                       | timestamp   | NO   | PRI | CURRENT_TIMESTAMP
 |       |
| weekending                      | date        | NO   |     |
 |       |
| date                            | date        | NO   |     |
 |       |
| start_time                      | time        | NO   |     |
 |       |
| stop_time                       | time        | NO   |     |
 |       |
| Mechancial_Downtime_Total_mins  | varchar(10) | NO   |     |
 |       |
| Operational_Downtime_Total_mins | varchar(10) | NO   |     |
 |       |
| Gangtime                        | varchar(10) | NO   |     |
 |       |
| Gang_Run_Hours                  | varchar(10) | NO   |     |
 |       |
| Tanked_Product_Pounds           | varchar(10) | NO   |     |
 |       |
+---------------------------------+-------------+------+-----+------------------
-+-------+
10 rows in set (0.00 sec)

mysql> describe kfyields;
+---------------------+-------------+------+-----+-------------------+-------+
| Field               | Type        | Null | Key | Default           | Extra |
+---------------------+-------------+------+-----+-------------------+-------+
| timestamp           | timestamp   | NO   | PRI | CURRENT_TIMESTAMP |       |
| weekending          | date        | NO   |     |                   |       |
| date                | date        | NO   |     |                   |       |
| SALIVARY_GLANDS     | varchar(10) | NO   |     |                   |       |
| CHEEKMEAT           | varchar(10) | NO   |     |                   |       |
| TONGUES             | varchar(10) | NO   |     |                   |       |
| TONGUES_BASE        | varchar(10) | NO   |     |                   |       |
| PATE_MEAT           | varchar(10) | NO   |     |                   |       |
| EARS                | varchar(10) | NO   |     |                   |       |
| SNOUTS              | varchar(10) | NO   |     |                   |       |
| TEMPLE              | varchar(10) | NO   |     |                   |       |
| SNOUT_LEAN          | varchar(10) | NO   |     |                   |       |
| HEADBACK            | varchar(10) | NO   |     |                   |       |
| SCALD_STOMACH       | varchar(10) | NO   |     |                   |       |
| HEARTS_SGL_SLASH    | varchar(10) | NO   |     |                   |       |
| Pet_food_slurry_lbs | varchar(10) | NO   |     |                   |       |
+---------------------+-------------+------+-----+-------------------+-------+
16 rows in set (0.00 sec)

mysql> describe stockyards;
+-------------------------+-------------+------+-----+-------------------+------
-+
| Field                   | Type        | Null | Key | Default           | Extra
 |
+-------------------------+-------------+------+-----+-------------------+------
-+
| timestamp               | timestamp   | NO   | PRI | CURRENT_TIMESTAMP |
 |
| weekending              | date        | NO   |     |                   |
 |
| date                    | date        | NO   |     |                   |
 |
| start_time              | time        | NO   |     |                   |
 |
| stop_time               | time        | NO   |     |                   |
 |
| Gang_time               | varchar(10) | NO   |     |                   |
 |
| Gang_Run_Hours          | varchar(10) | NO   |     |                   |
 |
| Total_hogs_received     | varchar(10) | NO   |     |                   |
 |
| Total_hogs_heldover     | varchar(10) | NO   |     |                   |
 |
| Dead_on_trucks          | varchar(10) | NO   |     |                   |
 |
| Dead_in_pens            | varchar(10) | NO   |     |                   |
 |
| Processed_Stressors     | varchar(10) | NO   |     |                   |
 |
| Processed_Stressors_Pct | varchar(10) | NO   |     |                   |
 |
| Ante_Mortem_Condemned   | varchar(10) | NO   |     |                   |
 |
| Outside_Temp            | varchar(10) | NO   |     |                   |
 |
| Barn_Temp               | varchar(10) | NO   |     |                   |
 |
+-------------------------+-------------+------+-----+-------------------+------
-+
16 rows in set (0.00 sec)

mysql> desc kill_floor;
+---------------------------------+-------------+------+-----+------------------
-+-------+
| Field                           | Type        | Null | Key | Default
 | Extra |
+---------------------------------+-------------+------+-----+------------------
-+-------+
| timestamp                       | timestamp   | NO   | PRI | CURRENT_TIMESTAMP
 |       |
| weekending                      | date        | NO   |     |
 |       |
| date                            | date        | NO   |     |
 |       |
| start_time                      | time        | NO   |     |
 |       |
| stop_time                       | time        | NO   |     |
 |       |
| perfect_kill                    | varchar(10) | NO   |     |
 |       |
| actual_kill                     | varchar(10) | NO   |     |
 |       |
| gang_time                       | varchar(10) | YES  |     | NULL
 |       |
| Gang_Run_Hours                  | varchar(10) | YES  |     | NULL
 |       |
| Std_Chain_Speed                 | varchar(10) | YES  |     | NULL
 |       |
| Net_Chain_Speed                 | varchar(10) | YES  |     | NULL
 |       |
| Efficiency                      | varchar(10) | YES  |     | NULL
 |       |
| Mechancial_Downtime_Total_mins  | varchar(10) | YES  |     | NULL
 |       |
| Operational_Downtime_Total_mins | varchar(10) | YES  |     | NULL
 |       |
| Condemned_Viscera_path          | varchar(10) | YES  |     | NULL
 |       |
| Contaminated_Viscera            | varchar(10) | YES  |     | NULL
 |       |
| Condemned_Heads_path            | varchar(10) | YES  |     | NULL
 |       |
| Contaminated_Heads              | varchar(10) | YES  |     | NULL
 |       |
| Condemned_Hearts                | varchar(10) | YES  |     | NULL
 |       |
| Condemned_half_sets             | varchar(10) | YES  |     | NULL
 |       |
| Disposition                     | varchar(10) | YES  |     | NULL
 |       |
| USDA_condemned                  | varchar(10) | YES  |     | NULL
 |       |
| Broken_Backs                    | varchar(10) | YES  |     | NULL
 |       |
| Broken_Backs_Pct                | varchar(10) | YES  |     | NULL
 |       |
| Blown_Aitch_Bones               | varchar(10) | YES  |     | NULL
 |       |
| Blown_Aitch_Bones_Pct           | varchar(10) | YES  |     | NULL
 |       |
| Total_Hogs_to_the_EQ_Bays       | varchar(10) | YES  |     | NULL
 |       |
| Triumph_Condemned               | varchar(10) | YES  |     | NULL
 |       |
+---------------------------------+-------------+------+-----+------------------
hope this is what you were after thanks for looking at the problem. I think with just a little mod to the column names i could have all this in the same table.

Posted: Mon Dec 17, 2007 10:15 am
by RobertGonzalez
jmut wrote:
Everah wrote:The database should do as much data related work as possible/feasible for the app. Whatever you can push to the database, do it. Let PHP do the app work, the database server, the data work.
you're saying this for performance issues only or - would that be relevant if php and database are on same server?
Remember that a server can be a machine or a piece of software. Just because the database server daemon and the web server daemon are on the same box does not mean that each daemon cannot be invoked in an optimum way.

MySQL runs as a process called (typically) mysqld. It is a database server (software). Apache usually runs as httpd (or apache, though this is in some more rare ocassions) and is a web server (software). Both of these can run on a server (hardware). Both of them can be overburdened or used in a not-so-optimized fashion.

When I say let the database server do the data stuff I am saying that the database server process should be directed to handle as much data processing as is logical for the database to handle. Same goes for the web server. When you are using MySQL (or whatever your database server is) to literally serve up 10 SELECT queries while your PHP (or whatever server-side application processor) is processing a bunch of the data you will see that the processing time for the DB is really low (like 0.0005 seconds low) while your application processing speed goes up (0.09 seconds up, for example). These are not dramatic numbers when there are 10 requests a minute to your web server. But if your app ever gets to the point where you are serving up 100 simultaneous requests, then you are going to want to balance that load as much as possible. Improving the app process so that it goes down while having the database process time go up, but in a much smaller increment, makes your application more stable and the servers less prone to demand related issues.

Does that sort of make sense?

Posted: Mon Dec 17, 2007 12:40 pm
by jmut
Everah wrote:
jmut wrote:
Everah wrote:The database should do as much data related work as possible/feasible for the app. Whatever you can push to the database, do it. Let PHP do the app work, the database server, the data work.
you're saying this for performance issues only or - would that be relevant if php and database are on same server?
....

basically balance it...so that neither db nor web server make most of the work :)

Posted: Mon Dec 17, 2007 8:19 pm
by sharpstyx
so now im wondering if i dont need some kind of pivot table. It seems like it might work.

EDIT: Im thinking a pivot table is definitely what i need

Posted: Tue Dec 18, 2007 8:30 pm
by sharpstyx
Ok so the pivot table isnt working out so good so im thinking i need to populate the 'template' cell by cell which i think i can get all the records by coming up with some kind of loop with a

Code: Select all

SELECT date,c_id,dayofweek(CURDATE()) as c FROM xxtbl WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL (dayofweek(CURDATE())-x) DAY ) AND CURDATE( )
or something like that, any hints or suggestions welcome

Posted: Wed Dec 19, 2007 10:14 am
by RobertGonzalez
How are your dates stored again?

Posted: Wed Dec 19, 2007 10:43 am
by sharpstyx
How do you mean? yyyy/mm/dd?

Posted: Wed Dec 19, 2007 12:21 pm
by RobertGonzalez
Yes, what format are your dates in ?

Posted: Wed Dec 19, 2007 12:35 pm
by sharpstyx
thats it yyyy/mm/dd
i had the thing sort of working and then i tried to reorder some things and i broke it

Posted: Wed Dec 19, 2007 12:39 pm
by RobertGonzalez
Only reason I ask is because the default date format for mysql is YYYY-MM-DD mm:hh:ss. I know date math with this format is as simple as date < date_field.

Posted: Wed Dec 19, 2007 2:49 pm
by sharpstyx
ok i figured it out, its ugly but it works thanks again for looking

Posted: Wed Dec 19, 2007 9:27 pm
by sharpstyx
ok i could use one more nugget of wisdom if anybody knows how to do it. What would the syntax be for getting the dates and days of the week on either side of another date. I know its some kind of array but its late and im not getting it. So i have a date, 2007/11/02 which is a friday, this particular variable called wkend will always be friday. Now what i need to do if find the date for the next saturday and the previous monday tuesday and wednesday and thursday. thanx in advance

Posted: Thu Dec 20, 2007 9:57 am
by RobertGonzalez
strtotime() is your friend in this situation.