Steven Dowd
|
| Posted: 06/24/2002, 1:55 AM |
|
Its not an CodeCharge thing , sorry guys,
I am having total brain fade here trying to update this database, I
obviously have been lookng at it way too long, can anyone help with
this problem,
I am trying to sort some old data files that have turned up, records of
who lived where in my town, they are quite incomplete, and I have to
reformat the information in a 20k line database, and could really do
with a little help
the tables and basic data is like this:
ID , Address, Name, Relation
1, market st, fred waters, head
2, market st, mrs waters, wife
3, market st, paul waters, son
4, market st, jim wheeler, head
5, market st, mrs wheeler, wife
6, market st, ann wheeler, dau
my problem is that about 5k of the address's are not unique I.e. there
may be a whole street of people with their address listed as 'Market
Street' and there isn't a good way to tell a whole household of people
in the street from the next-door household, except that each 'household'
only has 1 'Head' as its value in the 'relation' column, and these
records are almost all correctly formatted.
so, I want to alter the database table to be
ID , Address, Name, Relation, household
this will allow me to count and number the relation tables 'Heads'
entries, I want to keep the same count number for all relations/visitors
of that 'Household' until the next instance of 'Head' in the 'Relation'
column, so that the household count would increment as below:
ID , Address, Name, Relation, household
1, market st, fred waters, head, 1
2, market st, mrs waters, wife, 1
3, market st, paul waters, son, 1
4, market st, jim wheeler, head, 2
5, market st, mrs wheeler, wife, 2
6, market st, ann wheeler, dau, 2
by dong this, I can easily search and find all the people that are a
'family unit', in any particular house, even if the address field, isn't
unique, i.e. ,market st, instead of #4 market st.
so, I know what I want to do, but am totally at a loss how to do it. I
know I need to cycle through the whole of the 20k records, updating the
new 'household' table with a number that increments by +1 and use that
number for all entries until after each instance of the data being
'head'. when if its incremented by +1, it will be another family unit.
Can anyone help me with how to do this, I am not sure whether it will
need php to read and insert the incrementing number, or if its possible
with just a mysql statement ?
any help at all will be appreciated
Steven
ps. once i figure how to update this db, i can then start using it with
CCS
______________________
Steven.Dowd@dowd.co.uk
http://www.dowd.info
______________________
|
|
|
 |
|
