Streamline SQL queries

[SC68Cal]

Okay, so I have some functions that contain a huge SQL command to input data into a database. Problem is that I can't really find an elegant way to call all of them. Here's my current setup:

				//run the queries
				$result1 = @mysql_query(section1insert());
				$result2 = @mysql_query(section2insert());
				$result3 = @mysql_query(section3insert());
				$result4 = @mysql_query(section4insert());
				$result5 = @mysql_query(section5insert());
				$result6 = @mysql_query(section6insert());
				$result7 = @mysql_query(section7insert());
				$result8 = @mysql_query(section8insert());
				$result9 = @mysql_query(section9insert());
				$result12= @mysql_query(section12insert());

The functions sectionXinsert() basically returns a string with my SQL commands, which then gets executed. I won't list them here, because they are really, really, long. Really long.

The $results just act as my debugging, so that if something goes wrong it'll output the error messages.

I mean, I guess the easiest thing to do would be to create an array with all of the functions stored inside it, then just iterate through it, calling each function.

Any suggestions from anyone more knowledgeable, or who had a similar situation?

EDIT:

Just thinking as I go along, maybe just having a master function such as DoAllQueries where all of the functions get called would be a step in the right direction. But I mean, all I'm doing is shuffling deck chairs around, I'm still going to have some code where I just list all those functions to do.

 

[angelneo]

while you can do looping with arrays to make the code looks neater, you can also store all your insert statements in one string and send everything in one query call to your database server (chunking preferred), that might help to reduce some overhead.

Alternatively, you can use dynamic functions in php instead of arrays.

Anyway, I think there must be something weird going on (just guessing) if you are required to call so many sql queries in different functions which are similarly named. Perhaps, you should look at your internal structure of your program.

 

[SC68Cal]

Not For The Weak of Heart.....Alpha Code

while you can do looping with arrays to make the code looks neater, you can also store all your insert statements in one string and send everything in one query call to your database server (chunking preferred), that might help to reduce some overhead.

Sadly, the statements that I've stored are far too large to clump into one function. It would be so big, that there'd be no sane way of debugging it.

Alternatively, you can use dynamic functions in php instead of arrays.

I'll look into it.

Anyway, I think there must be something weird going on (just guessing) if you are required to call so many sql queries in different functions which are similarly named. Perhaps, you should look at your internal structure of your program.

I have thought about putting some code in to check and see what sections were answered and need to be entered into the database, which is why I split off all the queries into separate functions. The problem is that would break my reporting, which built on the assumption that there is a unique client_id inserted into each table, that is used to retrieve all the information from the database. So even if the client doesn't answer anything in the section, I still need it to preserve data integrity.

HR also still needs to know that they did indeed, not answer that section. I don't believe a N/A can be omitted.

Here's the section insert functions. When you look below, you'll understand why I kept them all seperate. They're huge!

EDIT: Attaching code as txt file

Maybe it would be worthwhile to look into stored procedures.

 

[SC68Cal]

Here's the SQL functions

 

[angelneo]

Oh, there are indeed quite a number of queries, personally, I usually separate out my functions in terms of usage and named them accordingly. Looking at how varied your queries are, I don't think there is any short method to it. I think stored procedures is another good way to group your functions.