Re: [PHP] php or mysql db update
Steven:
On Sun, Jun 23, 2002 at 10:30:56PM +0100, Steven Dowd wrote:
>
> 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
>
> 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
>
> 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.
But, what's the guarantee that the head is always the first record in the
group? Similarly, what's even guaranteeing that the houeholds even have
their members in consecutive rows?
Anyway, you'd be wise to normalize your table structure. At a minimum,
the "relation" should not be a text field, but rather a tiny integer, with
the actual names of the type of relation in a separate table which you'd
"join" to in your queries.
Okay, back to your question, assuming everything is miraculously ordered
and grouped correctly...
Write and execute your main query. Then start a while loop to go through
each record. Assuming you're using MySQL...
In that while loop, have an if statement checking if you're looking at the
head of the household. In case you are, bump up the counter. Regardless,
make a query string to run later updating the current record.
Lastly, loop through the array of update queries, executing each as you
go along.
Here's some untested code for you to try:
$Queries = array();
$Counter = 0;
while ( $Record = mysql_fetch_array($resource, MYSQL_ASSOC) ) {
if ($Record['Relation'] = 'head') {
$Counter++;
}
$Queries[] = "UPDATE table SET Household=$Counter WHERE ID=" .
$Record['ID'];
}
while ( list(,$Val) = each($Queries) ) {
mysql_query($Val);
}
Any questions? Read the PHP manual for the particular function.
Enjoy,
--Dan
--
PHP classes that make web design easier
SQL Solution | Layout Solution | Form Solution
sqlsolution.info | layoutsolution.info | formsolution.info
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
4015 7 Av #4AJ, Brooklyn NY v: 718-854-0335 f: 718-854-0409
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] php or mysql db update
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
