New to PHP - General Design Questions

[nitrusoxide]

Hello,

 

I'm very new to PHP (just started messing around with it last night) and I'm trying to create a simple lookup form that takes in a keyword, queries a MySQL database, and returns the data to either a table or some textboxes on the same page.  I have it working, but I know it can be cleaner.  I have some general questions:

 

1) Is it better to have one page that creates your HTML output and a separate page to perform the database extracts?  If so, how do you send your DB results back to the display page?

2) Should I be putting my database queries in separate scripts?  Or create one generic database extract script and pass SQL statements to it?

3) I'm executing a lot of SQL statements, mainly for creating temp tables and inserting data into them.  Can I combine these into one statement?  Is that even worth it?

 

Thanks in advance.

[betterphp]

you should try to keep your logic separate form html output

 

Q1

The way I usually do it it to create a load of backend type files that are included by the front end pages these provide some functions for getting data from the database. Or classes if it makes it any easier.

 

Some people will argue that you need to follow something like the MVC approach, personally I find that that introduces a lot of pointless files and code, but its a personal preference really.

 

Q2

I have seen some people that have all of their sql in a separate file all stored in variables, but without knowing exactly where they are used it becomes hard to maintain

 

Q3

minimising the number of queries is always a good thing to do

[nitrusoxide]

Thanks for the feedback.  I think once I move the database interaction to a separate script it will become a cleaner solution.

 

Followup question - in terms of performance, is it worth it to create temporary tables each time the lookup is run?  I have two main data tables and the final output I need to present at the end is the result of 2 intermediate queries.  My initial plan was to load each intermediate query's results to a temp table and run my final query off of those temp tables.  What do you think?

[betterphp]

I thought temporary tables were a slow thing to do that should be avoided :s

 

you might want to post that in the mysql section and see if one of the pros knows.

[SamT_]

Opt for a caching architecture instead of temp tables. Not all DBMS support temp tables as well as others, which causes a lot of pain and suffering for the developers if you wanted to create applications that work on more than just MySQL.