Programming advice - Which Loops?

Question

Theres no easy way to say this so ill just say it in the form of a story. Im looking for advice on which loops and where.

Here goes:

• out of 200-odd fields in the database, i need to run the following against each field.
• extract allowed values using extract function
• place allowed values into an array
• loop the array to be inserted into a db table
• first check records dont already exist.
• if they dont exist insert into table.

ive found myself playing with this for the past two days and getting tangled and tangled in loops.

wondering if anybody can guide.

Answer 1

I'd do something like this :

Once you have your allowed values array :

• Create a SELECT query on your DB table with empty WHERE clause
• Loop on your allowed values add them to the SELECT qwery WHERE clause. Always get sure that you insert valid values in the query (valid integers, or escaped strings if any strings)
• Execute your SELECT query to retrieve all existing values from DB
• Loop on your query results, and remove all similar elements in the allowed values array
• Loop on the remaining allowed values and execute an INSERT query for each of them

Answer 2

Not very clear what your question is.

The best solution is to use no loops at all in your PHP code - use non-procedural SQL, e.g.

INSERT IGNORE INTO output (f1, f2, f3)
SELECT a1, a2+a3, max(a4) 
FROM input
GROUP BY a1, a2+a3;

first check records dont already exist.

if they dont exist insert into table.

No - this is a waste of time and code you're running at least 1, usually 2 queries per row - try to insert and either direct the DBMS to ignore duplicates (see above - INSERT IGNORE) or ignore duplicate key errors returned by the query in your code.

But you keep talking about 200 fields which implies a very odd database structure. Since you've tagged this as a mysql question, I'll assume that is the DBMS you are using - in which case, if the problem is identifying what fields are available, then you can get a list of the fields in a table (or attributes in a relation, to be more specific) by using desc (which produces a result set just like a select query).

C.