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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
