[SOLVED]need help with query or stored procedure

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

sharpstyx
Forum Newbie
Posts: 15
Joined: Fri Dec 14, 2007 3:39 pm

Post by sharpstyx »

ok i have 5 tables, do you want all five?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Relevant table(s), yes.
sharpstyx
Forum Newbie
Posts: 15
Joined: Fri Dec 14, 2007 3:39 pm

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post 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 :)
sharpstyx
Forum Newbie
Posts: 15
Joined: Fri Dec 14, 2007 3:39 pm

Post 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
sharpstyx
Forum Newbie
Posts: 15
Joined: Fri Dec 14, 2007 3:39 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

How are your dates stored again?
sharpstyx
Forum Newbie
Posts: 15
Joined: Fri Dec 14, 2007 3:39 pm

Post by sharpstyx »

How do you mean? yyyy/mm/dd?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Yes, what format are your dates in ?
sharpstyx
Forum Newbie
Posts: 15
Joined: Fri Dec 14, 2007 3:39 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
sharpstyx
Forum Newbie
Posts: 15
Joined: Fri Dec 14, 2007 3:39 pm

Post by sharpstyx »

ok i figured it out, its ugly but it works thanks again for looking
sharpstyx
Forum Newbie
Posts: 15
Joined: Fri Dec 14, 2007 3:39 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

strtotime() is your friend in this situation.
Post Reply