With a My SQL database with the fields of:
`Name` `Type` `Match` `Level` `Levelmatch`
I need a PHP script that does the following:
If `Type` equals `Match` from another record, AND the `Levelmatch` value
differs by less than 2, then:
the `Name` is copied into the `Match` field,
the `Level` value is copied into the other `Level` field,
and the `Levelmatch` value is copied into the other `Levelmatch` field.
If there are multiple records where `Type` equals `Match`,
a new record is created with the unique `Name` in the `Match` field.
So using the sample data -
Name Type Match Level Levelmatch
Sarah a 5 1
Fred a 3
Joe a 4 2
Jill a 7 3
Only Joe & Jill would be matched with Fred, because their `Levelmatch` values
differ by less than 2. Sarah & Fred's `Levelmatch` values differ by 2, so they
fall outside the matching range. From the above data, the script
would return:
Name Type Match Level Levelmatch
Sarah a 5 1
Fred Joe 4 3
Joe a Fred 4 2
Fred Jill 7 3
Jill a Fred 7 3 |
Request for Question Clarification by
webadept-ga
on
15 Jul 2004 13:19 PDT
This is just a question of curiosity really. Are these tables cut in
stone already? because if this is the type of query you are going to
do often, its going to be slow going from this point. The looping that
is being caused is buffing this, rather simple process of what could
be a basic left join query into a nightmare of sub query while looping
happy-dance-tell-we-bleed functions.
A table set something like
name level match
name type levelmatch
gives you a query that could be done in one statement
lets see that would be
update table1 set table1.match = (select name from table2 where
levelmatch between table1.level -1 and table1.level+1 and table1.match
= table2.type and table1.name != table2.name and table1.match = ""
limit 1);
don't even need php then. Of course you'll probably run it from there,
but you see my point. Really it should be three tables the first one
name_id name
and then replace the names in the others with name_id. and work off
those. The reason is that you are now able to work with a unique Id,
which is numeric, and your queries will move much faster. As it is,
working with names like that, the indexs aren't going to do you much
good, as they still have to idenify the daves and jims with the mary's
and sue's.
I'm sure what you have given us is a very basic break down of your
real database, I understand that, but even at this basic level, with
that requirment, its not pretty.
As it is, without a good indexing, and say 30,000 names (??) just a
guess, this could literaly take all day to run. Really, all day. So
PHP is out because you probably have a 1-5 minute time limit on your
scripts running from the webpage. This can be adjusted, but its
suicide to do so. You want that limit just in case something goes
wacky and tries to eat itself.
Anyway, I just thought i would bring that up and see if your tables
could be worked around or if you are stuck with this setup, and also
to let you know why the person's script that answers this, will
probably keep freezing up.
webadept-ga
|
Clarification of Question by
lstsb-ga
on
16 Jul 2004 23:55 PDT
Yes I can split it into multiple tables. When I gave your update
query a quick try, it returned "Parse error: parse error, unexpected
T_CONSTANT_ENCAPSED_STRING". Am I missing a punctuation?
|
Request for Question Clarification by
webadept-ga
on
18 Jul 2004 07:13 PDT
Hi
qouting you --"Am I missing a punctuation?"
Probably not, :-) it was late and I was in a bit of a hurry, so my SQL
syntax is probably off. But now that you have suggested that table
could be changed, I may give this question some thought. I'm on my way
out right now for the day, but that doesn't mean another researcher
couldn't come by, clean up my SQL and give you an answer for this
thing. And I'm sure it will happen. If not, then I'll be back latter
to get you fixed up.
--webadept-ga
|
Clarification of Question by
lstsb-ga
on
19 Jul 2004 12:33 PDT
Great; looking forward to it.
|
