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
