MySQL / PHP problem

[wonderboy]

Hi,

Just wondering what the most efficient way of doing the following would be:

Query a list of members, with the few that are left pull 10 variables (h1, h2, h3....) associated with their ID from another table, do some processes on them to calculate a few values, and then update another table with these values...

I run a while loop for the relevant members, but then my mind gets messed up and I can't think straight!

Any fresh views to this easy problem, my mind is tired, and I need some guidance!

Cheers,
W.

[ergophobe]

start here [webmasterworld.com]

[wonderboy]

Hey,

I must have worded my question wrongly, I have everything setup perfectly, this is just an additional script I need to keep the database up to date...

My question probably should have been: What querys and loops should I use to complete the following in one go:

1) Call selected members (if active = 1)
2) For these active members grab 10 numbers from a seperate table (they are stored on a row with same ID as original member's ID in other table)
3)Do a few processes with the numbers... such as "if number5>number4" then "variable1 = 2"
4) Insert these calculated variables into another table.

It has to run through all the selected members for all 10 numbers, so is it a loop within a loop or something?

Thanks,

W.

[ergophobe]

SELECT table1.userid, table1.active, table2.field1, table2.field2, ... table2.filed10
FROM table1, table2
WHERE table1.active=1 and table1.userid=table2.userid

now just work through your result set.

while ($member = msyql_fetch_assoc($result))
{
if ($member['field1'] == $member['field2']) {do something}
}

Tom

[wonderboy]

ok, I was trying to find a way of doing it with a loop for all the fields, as there is in fact 20... I said 10, I don't know why :s

I guess I will have to do it all manually! =0
Thanks,
W.

[ergophobe]

Well, there are various ways of looping through the fields, but I don't really see what it's going to save you if you are comparing fields.

Maybe I don't understand what you're doing.

What is the actual structure of the two tables and can you give two examples of what you want to do with these fields as you loop through?

Tom

[wonderboy]

Hey Tom,

The structure is

ID,
h1, h2, h3... up to h10
a1, a2, a3... up to a10

Each member has their own row with different values for h1 etc...

I want to compare h1 with a1, h2 with a2 etc.. and create temporary variables saying the outcome of the comparison, these variables will then be compared against xh1, xa1 etc... which are pulled from another table (Only 1 line to this table)
The results of all the comparisons are then put into new variables and inserted into a new table called results which has a similar structure to the first table, each member having their own line.

Thanks for your time, and I hope you understand it!

W.

[ergophobe]

So are those the actual column names? If so, you could use foreach so you have

foreach ($row as $key=>$val)
{
if ($key{0} == 'h')
{
$idx = substr($key, 1);
$h[$idx] = $val;
}
elseif ($key{0} == 'a')
{
$idx = substr($key, 1);
$a[$idx] = $val;
}
} // end first foreach

foreach($h as $key => $val)
{
if ($a[$key] == $val) {do something}
}

Keep in mind that you now have terrible programatic abstraction. If you change a field name or delete a field or something, you have to rewrite your script.

More likely, you might add a field like "home_address" which will match the $key{0} == 'h' criterion, so everything will be screwed up.

One last question. How normalized is your db? Whenever I see a DB with fields a1-a10 and h1-h20, I suspect that it is not normalized.

Tom

[wonderboy]

Hey Tom,

thanks for that, will have a mess round when I get some motivation from somewhere!

I thought my database was fairly well normalized, the h1-10 and a1-10 are neccessary in this form (I think)... In this case anyways.

W.