[SOLVED]need help with query or stored procedure
Moderator: General Moderators
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
ok i figured it out, by figured it out i mean i figured out how to post the structure 
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.
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
| |
+---------------------------------+-------------+------+-----+------------------
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.jmut wrote:you're saying this for performance issues only or - would that be relevant if php and database are on same server?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.
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?
Everah wrote:....jmut wrote:you're saying this for performance issues only or - would that be relevant if php and database are on same server?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.
basically balance it...so that neither db nor web server make most of the work
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
or something like that, any hints or suggestions welcome
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( )- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
strtotime() is your friend in this situation.