Page 1 of 3

[SOLVED]need help with query or stored procedure

Posted: Fri Dec 14, 2007 4:06 pm
by sharpstyx
I'm new to php and mysql, I'm creating an intranet site that will collect data that people enter on a daily basis and then i want it to be displayed by week upon request I have the collection part done. now i just need to figure out how to retrieve the data and organize it into something presentable. My question is should i be doing the bulk of the work in php or mysql? If somebody could point me in the right direction, Id really appreciate it. Thanks in advance.

Re: need help with query or stored procedure or something el

Posted: Fri Dec 14, 2007 4:09 pm
by Chalks
sharpstyx wrote:...I have the collection part done...
When you collect something, you typically put it somewhere. My guess would be that you put it in a mysql database. So, simply pull the data out using a few mysql queries, echo the data to the screen, and display it elegantly using css.

Re: need help with query or stored procedure or something el

Posted: Fri Dec 14, 2007 4:36 pm
by sharpstyx
Chalks wrote: So, simply pull the data out using a few mysql queries, echo the data to the screen, and display it elegantly using css.
Before I forget, thanks for the timely response.

So I need to make the php in the app do the work? My problem is I’m not sure where to go from where I am at. I have the database and tables and forms all set up my problem is that I have a template that has 86 rows and 8 columns I want the person to select a weekending date and populate all the cells in the table. The rows in the template correspond to columns in the database. The columns in the template are days of the week. Now I’m still learning php and mysql, but what I’m doing sounds to me like something I could program once and reuse, but back to the original problem, I don’t know where to start. I am even wondering if there’s not a way to make all the data from each form into a single record or at least have the timestamp or something correspond to another table that would pull the data from the first table and populate the fields, I am really out of my element here. Thanks in advance for any help.

Posted: Fri Dec 14, 2007 4:57 pm
by John Cartwright
my problem is that I have a template that has 86 rows and 8 columns I want the person to select a weekending date and populate all the cells in the table.
I have no idea what you are talking about. Typically you will loop each field in your database and dynamically create each row, not the other way around ;).

Why don't you start by posting your tables structures and we will see if we can improve that first.

Posted: Fri Dec 14, 2007 6:31 pm
by sharpstyx
ok I’m trying to replace excel spreadsheets with a couple of web pages the only real requirement other than maintaining the data is to make the new page look similar to the excel spreadsheet I’m replacing. Labels down the left side, days of the week across the top and data in the middle, if I can do that dynamically, great. I’m not looking for anybody to do this for me I just need a shove or maybe a kick in the right direction. What I was doing was making a table and putting all the labels in the first column and the days of the week in the first row and then I got stuck trying to figure out how to A) call my data up by week and B) call my data so it went in the right cell. Some of the data from different tables goes in the same "report". Anyway I realized after I started this project that it was a big project. But I can’t back out now so that’s where I am at. If there’s an easier way, please let me know. Thanks for all the help so far. Cool board btw

p.s. Do you mean my db table structure? or the table im trying to fill up?

Posted: Sat Dec 15, 2007 2:23 pm
by sharpstyx
ok i'm making progress, but theres still a couple problems. I need to know how to get the data to be vertical instead of horizontal and i cant figure out where my drop down goes in the code


Ok I got no 2 taken care of

Posted: Sat Dec 15, 2007 10:20 pm
by RobertGonzalez
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.

Posted: Sun Dec 16, 2007 10:57 am
by jmut
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?

Posted: Sun Dec 16, 2007 11:12 am
by John Cartwright
Typically they are on the same server yes. The only instances where I've put them on seperate servers was with extremely high volume websites.

Posted: Sun Dec 16, 2007 11:16 am
by jmut
Jcart wrote:Typically they are on the same server yes. The only instances where I've put them on seperate servers was with extremely high volume websites.
So what benefit is there in putting as much as possible in db as of php. I would not say portability is one of it either....is it not just personal preference..whatever you fill more comfy with....and not that there is any real benefit.

Posted: Sun Dec 16, 2007 11:22 am
by John Cartwright
Performance.

Posted: Sun Dec 16, 2007 12:03 pm
by sharpstyx
I’m really, really new to php and mysql if you can’t tell. I just “heard” that php with mysql was the way to go for this project. If not somebody please let me know before I go any further. I’ve only been working with them for about 2 weeks and most of that has been spent reading posts and tutorials. That being said I really appreciate all the replies to this post.
This is a one machine deal, db and web server on the same box, where I think I’m running into problems is for instance my drop down menu has to have a button. I was trying to do a menu where when you selected from the drop down list it automatically displayed that weeks data. I’m finding out that with a server side language it needs input from the user before it moves any further, but that’s a piddly complaint. The other is trying to get my data to display by week in a column instead of rows. Maybe I need to redesign my db as tables named days of the week instead. I don’t know but I can show the recordsets as columns or I can display by week as rows but I don’t know how to do both. So any advice on what to do would be greatly appreciated.

Posted: Sun Dec 16, 2007 12:09 pm
by John Cartwright
sharpstyx wrote:This is a one machine deal, db and web server on the same box, where I think I’m running into problems is for instance my drop down menu has to have a button. I was trying to do a menu where when you selected from the drop down list it automatically displayed that weeks data. I’m finding out that with a server side language it needs input from the user before it moves any further, but that’s a piddly complaint.
I don't understand what you mean by it requires user input. By no means do programs require any input.
sharpstyx wrote:The other is trying to get my data to display by week in a column instead of rows. Maybe I need to redesign my db as tables named days of the week instead. I don’t know but I can show the recordsets as columns or I can display by week as rows but I don’t know how to do both. So any advice on what to do would be greatly appreciated.
Yes it sounds like you have a poorly structure table. Try posting the table structure so we can determine the best route for you.

Posted: Sun Dec 16, 2007 12:14 pm
by sharpstyx
Do you mean db table structure or html table?

Posted: Sun Dec 16, 2007 2:07 pm
by John Cartwright
database