masterlayouts Posted March 23, 2012 Share Posted March 23, 2012 I have an application that has quite a few dropdown menus in the UI form that are very good candidates for an "include" page because they appear on several pages and the values from dropdown rarely or never change. My problem is that these dropdowns I want to be populated from the database. Is there any way I can create a page and run a query to create these dropdowns and than include this newly created page with dropdowns already populated as result of the query everywhere I need avoiding this to run the same query again and again for every user that is using the application? Thank you. Link to comment Share on other sites More sharing options...
Larry Posted March 24, 2012 Share Posted March 24, 2012 If the values are going to change "rarely" or "never", making them database driven would be a bad idea. Just hardcode the values into an included file as HTML. No need to waste all that server processing to fetch them using a database when they aren't likely to change. Link to comment Share on other sites More sharing options...
masterlayouts Posted March 25, 2012 Author Share Posted March 25, 2012 Hi Larry, thank you for your reply. Now I am confused as talking with a database designer about my database he said that it is a bad idea to have the meaning in the PHP and I should make all these linked tables in my database. I was very much inclined to do as you suggested here and hardcoded in the PHP (not to mention it is easier). I understand the theoretical advantages of both approaches, but I lack experience to figure it out on myself. My main issue is that I have way too many linked tables (10) not to be concerned with performance. Also I guess that to populate a dropdown from database I will have to use a query. If I have ten dropdowns/select to populate from database from ten different linked tables, does this mean I will have to make 10 queries? I don't see how and no logic reason to link the "education" table of "country" table for example, nor how this may help to speed up the system. If I should make 10 queries I guess it is a terrible idea, isn't any way around? Please advise and it will be really helpful if you can explain why it is a bad idea, I would like to learn how you're thinking when your taking such decisions. Link to comment Share on other sites More sharing options...
Larry Posted March 26, 2012 Share Posted March 26, 2012 It's not surprising that a DB person would say that all meaning should go into the DB. And there's definitely an argument to that approach, but it depends upon the situation and the goals. If you have ten tables that each present options that need to be shown in a list, you would need 10 queries to retrieve all that information. And you would need those 10 queries for every page request. Personally, I start to get nervous when a page needs more than about 3 queries. In a situation where a lot of database queries are used for generally static results, you'd want to implement lots and lots of caching, which is an advanced topic, and one you may not even be able to implement. Database queries, along with file interactions, are the most time consuming things a PHP script can do. They also don't scale as well. If you have a page that makes 10 queries, then 10 simultaneous requests of that same page results in 100 simultaneous queries! Besides using hardcoded values, the possible alternatives really depend upon the particulars of the situation and the tables and queries themselves. Link to comment Share on other sites More sharing options...
Antonio Conte Posted April 14, 2012 Share Posted April 14, 2012 An alternative is to create something that will build on both approaches strengths. Have the links in a DB and have a function write these links to a page like nav.php when there is a Insert/modify query to the table. Then you don't need to query each time, and can still keep links in the DB. To be honest, I don't really think this is necassary. Just modify the file and be done with it. Link to comment Share on other sites More sharing options...
masterlayouts Posted April 16, 2012 Author Share Posted April 16, 2012 My approach was in the end the following: I used the correct database design and maintain the constraints at the database level as it should. This guarantees me database integrity. However, when it come to creating the user interface, those options that are less likely to ever change and are quite small in terms of choices/options I hard coded them in the page. For example we can easily add a constrain to allow just "m" and "f" for male and female, there is no need to use a query to create, let's say, a radio group in html. The same thing with look-up tables. I see no problem in having a look-up table act as constrain and in the same time hard coding the html. I found that the database integrity is maintained and in the same time, if needed, for example if some options change frequently so changing many html snippets across different pages frequently may become a pain, than I can add a query to the page to create those options dynamically for that html element of the form in the user interface. I guess this is the best of both worlds. Also I found that cached pages are more likely server options and is not difficult at all to cache pages after all. In Zend server for example, it's just a matter of clicking a few buttons. The subject more interesting would probably be query optimization. However, I guess this thread got the answers required. Thank you for your help. 1 Link to comment Share on other sites More sharing options...
Recommended Posts