| Loop through array, change headings [message #184432] |
Tue, 31 December 2013 21:08  |
Adrienne Boswell
Messages: 25
Registered: October 2010
Karma: 0
|
Junior Member |
|
|
I'm sure this has been asked and answered, but I am either too stupid to
figure out the right way to ask Google, or I'm lazy, or it really hasn't
been asked. Sorry in advance, and if it has been answered, please refer
me as needed.
Here's my situation:
I have a table that has three classifications:
id Classification
1 Stores
2 Restaurants
3 Services
I have another table that has categories in each classification, eg.
id Category Classification_id
1 Market 1
2 Pet Shop 1
3 Pizza 2
4 Sandwich 2
5 Notary 3
6 Repair 3
My query is
SELECT c.id, category, classification, cl.id AS classid FROM category
c JOIN classification cl ON c.classification_id = cl.id ORDER BY classid,
category
What I want to do is:
-Stores
Market
Pet Shop
-Restaurants
Pizza
Sandwich
-Services
Notary
Repair
So, as I'm looping through my recordset, if the classid has changed, I
want to change the header. I don't want to make one query to get the
headings, and then do another query for each classid. I KNOW there has
got to be a better way.
I'm trying to get this all done so I can have a Happy New Year, and
wishing you and yours a Happy New Year as well.
--
Adrienne Boswell
Arbpen Web Site Design Services - http://www.cavalcade-of-coding.info/
The Good Plate - Fresh Gourmet Recipes - http://the-good-plate.com/
Please respond to the group so others can share
|
|
|
|
| Re: Loop through array, change headings [message #184433 is a reply to message #184432] |
Tue, 31 December 2013 21:22  |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Tue, 31 Dec 2013 21:08:42 +0000, Adrienne Boswell wrote:
> So, as I'm looping through my recordset, if the classid has changed, I
> want to change the header. I don't want to make one query to get the
> headings, and then do another query for each classid. I KNOW there has
> got to be a better way.
Something like the following (untested, may not use the right call names
etc) php:
$class = "";
while ( $row = result->get_row_assoc() !== false ) {
if ( $class != $row["classification"] ) {
$class = $row["classification"];
output "-" . $class . "\n";
}
output " " . $row["category"];
}
Loop through the result set, every time the class of the record doesn't
match the existing class, output the class header.
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
| Re: Loop through array, change headings [message #184435 is a reply to message #184432] |
Tue, 31 December 2013 22:06 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
On Tue, 31 Dec 2013 21:08:42 +0000 (UTC), Adrienne Boswell wrote:
> I'm sure this has been asked and answered, but I am either too stupid to
> figure out the right way to ask Google, or I'm lazy, or it really hasn't
> been asked. Sorry in advance, and if it has been answered, please refer
> me as needed.
>
> Here's my situation:
> I have a table that has three classifications:
> id Classification
> 1 Stores
> 2 Restaurants
> 3 Services
>
> I have another table that has categories in each classification, eg.
> id Category Classification_id
> 1 Market 1
> 2 Pet Shop 1
> 3 Pizza 2
> 4 Sandwich 2
> 5 Notary 3
> 6 Repair 3
>
> My query is
> SELECT c.id, category, classification, cl.id AS classid FROM category
> c JOIN classification cl ON c.classification_id = cl.id ORDER BY classid,
> category
>
> What I want to do is:
> -Stores
> Market
> Pet Shop
> -Restaurants
> Pizza
> Sandwich
> -Services
> Notary
> Repair
>
>
> So, as I'm looping through my recordset, if the classid has changed, I
> want to change the header. I don't want to make one query to get the
> headings, and then do another query for each classid. I KNOW there has
> got to be a better way.
>
> I'm trying to get this all done so I can have a Happy New Year, and
> wishing you and yours a Happy New Year as well.
Why not have one table?
id | stores | restaurant | services
if you wanted to include actual names of
:
type | class | name
_______________________________________
stores | |
Restarants | |
Services | |
You could have as many "stores" and such as needed.
(simplified)
while (type==stores){echo 'class'; echo 'name'}
|
|
|
|
| Re: Loop through array, change headings [message #184436 is a reply to message #184435] |
Tue, 31 December 2013 23:00 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Tue, 31 Dec 2013 17:06:38 -0500, richard wrote:
> On Tue, 31 Dec 2013 21:08:42 +0000 (UTC), Adrienne Boswell wrote:
>> I have a table that has three classifications.
>> I have another table that has categories in each classification.
> Why not have one table?
Richard, you've proved time and time again that you have no understanding
of relational database design theory. Please stop trying to suggest that
people with sensibly designed databases should do it the stoopid way.
Adrienne's query joins the multiple tables into a single dataset and
sorts the dataset, the question asked was how to process that sorted
dataset in a specific manner. The question has nothing to do with the
structure of the data in the database, the answer lies solely in how the
result object returned by the mysql[i]->query() is processed.
Here's one example of why Adriennes approach is easier:
Supposing Adrienne decides to rename one of the three classifications. In
Adriennes database, that involves a single sql operation on one row in
one table. In your hypothetical and seriously fucked up database design,
you would need to test each row in the whole database to see if the
category was the one that needed to be changed, and then change it. Such
a process is highly inefficient in terms of both cpu effort, memory
management and disk io, and it is to prevent such inefficiencies amongst
other things that relational database design theory has evolved to where
it is today.
Unlike your comprehension of computer systems which appears to have
started at nothing and continues to devolve backwards.
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
| Re: Loop through array, change headings [message #184437 is a reply to message #184433] |
Tue, 31 December 2013 23:40 |
Adrienne Boswell
Messages: 25
Registered: October 2010
Karma: 0
|
Junior Member |
|
|
Denis McMahon <denismfmcmahon(at)gmail(dot)com> wrote
> On Tue, 31 Dec 2013 21:08:42 +0000, Adrienne Boswell wrote:
>
>> So, as I'm looping through my recordset, if the classid has changed, I
>> want to change the header. I don't want to make one query to get the
>> headings, and then do another query for each classid. I KNOW there
has
>> got to be a better way.
>
> Something like the following (untested, may not use the right call
names
> etc) php:
>
> $class = "";
> while ( $row = result->get_row_assoc() !== false ) {
> if ( $class != $row["classification"] ) {
> $class = $row["classification"];
> output "-" . $class . "\n";
> }
> output " " . $row["category"];
> }
>
> Loop through the result set, every time the class of the record doesn't
> match the existing class, output the class header.
>
Dennis, thank you so much, that worked beautifully!
--
Adrienne Boswell
Arbpen Web Site Design Services - http://www.cavalcade-of-coding.info/
The Good Plate - Fresh Gourmet Recipes - http://the-good-plate.com/
Please respond to the group so others can share
|
|
|
|
| Re: Loop through array, change headings [message #184438 is a reply to message #184436] |
Wed, 01 January 2014 00:19 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
On Tue, 31 Dec 2013 23:00:32 +0000 (UTC), Denis McMahon wrote:
> On Tue, 31 Dec 2013 17:06:38 -0500, richard wrote:
>
>> On Tue, 31 Dec 2013 21:08:42 +0000 (UTC), Adrienne Boswell wrote:
>
>>> I have a table that has three classifications.
>
>>> I have another table that has categories in each classification.
>
>> Why not have one table?
>
> Richard, you've proved time and time again that you have no understanding
> of relational database design theory. Please stop trying to suggest that
> people with sensibly designed databases should do it the stoopid way.
>
> Adrienne's query joins the multiple tables into a single dataset and
> sorts the dataset, the question asked was how to process that sorted
> dataset in a specific manner. The question has nothing to do with the
> structure of the data in the database, the answer lies solely in how the
> result object returned by the mysql[i]->query() is processed.
>
> Here's one example of why Adriennes approach is easier:
>
> Supposing Adrienne decides to rename one of the three classifications. In
> Adriennes database, that involves a single sql operation on one row in
> one table. In your hypothetical and seriously fucked up database design,
> you would need to test each row in the whole database to see if the
> category was the one that needed to be changed, and then change it. Such
> a process is highly inefficient in terms of both cpu effort, memory
> management and disk io, and it is to prevent such inefficiencies amongst
> other things that relational database design theory has evolved to where
> it is today.
>
> Unlike your comprehension of computer systems which appears to have
> started at nothing and continues to devolve backwards.
I was merely suggesting one possible way.
I have never liked the idea of making two tables where one is sufficient.
In the OP's example, we have going on exactly what you speak of.
Let's say he wants to add on a second pet shop
Now he has to change the id's in two tables.
Plus, the remaining id's all have to be changed accordingly.
So what if he has 1,000 rows?
He made a change on row 5. So now the sequence of events has to change
1990+ items?
In my example, if you want to add on another pet store, all you do is
insert the new row and increment the id's.
Otherwise, he should have a table for each category.
Forget cross referencing with id's.
|
|
|
|
| Re: Loop through array, change headings [message #184439 is a reply to message #184438] |
Wed, 01 January 2014 00:39 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Tue, 31 Dec 2013 19:19:27 -0500, richard wrote:
> On Tue, 31 Dec 2013 23:00:32 +0000 (UTC), Denis McMahon wrote:
>
>> On Tue, 31 Dec 2013 17:06:38 -0500, richard wrote:
>>
>>> On Tue, 31 Dec 2013 21:08:42 +0000 (UTC), Adrienne Boswell wrote:
>>
>>>> I have a table that has three classifications.
>>
>>>> I have another table that has categories in each classification.
>>
>>> Why not have one table?
>>
>> Richard, you've proved time and time again that you have no
>> understanding of relational database design theory. Please stop trying
>> to suggest that people with sensibly designed databases should do it
>> the stoopid way.
>>
>> Adrienne's query joins the multiple tables into a single dataset and
>> sorts the dataset, the question asked was how to process that sorted
>> dataset in a specific manner. The question has nothing to do with the
>> structure of the data in the database, the answer lies solely in how
>> the result object returned by the mysql[i]->query() is processed.
>>
>> Here's one example of why Adriennes approach is easier:
>>
>> Supposing Adrienne decides to rename one of the three classifications.
>> In Adriennes database, that involves a single sql operation on one row
>> in one table. In your hypothetical and seriously fucked up database
>> design, you would need to test each row in the whole database to see if
>> the category was the one that needed to be changed, and then change it.
>> Such a process is highly inefficient in terms of both cpu effort,
>> memory management and disk io, and it is to prevent such inefficiencies
>> amongst other things that relational database design theory has evolved
>> to where it is today.
>>
>> Unlike your comprehension of computer systems which appears to have
>> started at nothing and continues to devolve backwards.
>
> I was merely suggesting one possible way.
> I have never liked the idea of making two tables where one is
> sufficient.
>
> In the OP's example, we have going on exactly what you speak of.
> Let's say he wants to add on a second pet shop Now he has to change the
> id's in two tables.
No the OP doesn't. If the OP wants to add a new category eg "Hat shop" in
classification 1 the OP goes from this:
cl / classification
id Classification
1 Stores
2 Restaurants
3 Services
c / category
id Category Classification_id
1 Market 1
2 Pet Shop 1
3 Pizza 2
4 Sandwich 2
5 Notary 3
6 Repair 3
To this:
cl / classification
id Classification
1 Stores
2 Restaurants
3 Services
c / category
id Category Classification_id
1 Market 1
2 Pet Shop 1
3 Pizza 2
4 Sandwich 2
5 Notary 3
6 Repair 3
7 Hat shop 1
There is no need to have multiple "pet shop" categories. All pet shops
are of category "pet shop". The actual shop is probably in another table
which has shop details and category id.
eg: Establishments:
id Name Category id
1 smart pets 2
2 bettah pets 2
3 pets-r-us 2
4 toppers 7
5 silk fascination 7
6 flowers on top 7
7 subway 4
8 rolls-4-u 4
9 bagels-away 4
10 easy-wills 5
And if the OP wants to add a new pet store they can add:
11 cats and dogs 2
Without needing to change anything in the other two tables.
And that is why you completely fail to understand how databases work.
> Plus, the remaining id's all have to be changed accordingly.
No, no other ids need to change at all.
> So what if he has 1,000 rows?
What if they do?
> He made a change on row 5. So now the sequence of events has to change
> 1990+ items?
Not at all. If the OP wants to reclassify the category "pet shop" from
"store" to "services", they merely need to change the classification id
of the category "pet shop" in the category table from the id of "stores"
in the classifications table to that of "services", and all pet shops
will be changed from stores to services.
> In my example, if you want to add on another pet store, all you do is
> insert the new row and increment the id's.
In the OPs example, their data doesn't extend to showing individual
stores, it only shows categories of establishment, and wider
classifications that those categories are grouped into.
> Otherwise, he should have a table for each category.
It is your flawed understanding of database concepts that leads you to
think this way. The more you try and explain why you think your way is
best, the more you demonstrate your complete lack of comprehension of
databases.
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
| Re: Loop through array, change headings [message #184440 is a reply to message #184438] |
Wed, 01 January 2014 00:56 |
Beauregard T. Shagnas
Messages: 154
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
richard the sto0pid wrote:
> Denis McMahon wrote, to richard the sto0pid:
>> Unlike your comprehension of computer systems which appears to have
>> started at nothing and continues to devolve backwards.
>
> I was merely suggesting one possible way.
> I have never liked the idea of making two tables where one is
> sufficient.
Bullis, meet Dr. E. F. Codd.
Look up, for example, "Third Normal Form."
Oh wait ... that will be *completely* beyond your understanding.
--
-bts
-This space for rent, but the price is high
|
|
|
|
| Re: Loop through array, change headings [message #184442 is a reply to message #184438] |
Wed, 01 January 2014 01:50 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 12/31/2013 7:19 PM, richard wrote:
> On Tue, 31 Dec 2013 23:00:32 +0000 (UTC), Denis McMahon wrote:
>
>> On Tue, 31 Dec 2013 17:06:38 -0500, richard wrote:
>>
>>> On Tue, 31 Dec 2013 21:08:42 +0000 (UTC), Adrienne Boswell wrote:
>>
>>>> I have a table that has three classifications.
>>
>>>> I have another table that has categories in each classification.
>>
>>> Why not have one table?
>>
>> Richard, you've proved time and time again that you have no understanding
>> of relational database design theory. Please stop trying to suggest that
>> people with sensibly designed databases should do it the stoopid way.
>>
>> Adrienne's query joins the multiple tables into a single dataset and
>> sorts the dataset, the question asked was how to process that sorted
>> dataset in a specific manner. The question has nothing to do with the
>> structure of the data in the database, the answer lies solely in how the
>> result object returned by the mysql[i]->query() is processed.
>>
>> Here's one example of why Adriennes approach is easier:
>>
>> Supposing Adrienne decides to rename one of the three classifications. In
>> Adriennes database, that involves a single sql operation on one row in
>> one table. In your hypothetical and seriously fucked up database design,
>> you would need to test each row in the whole database to see if the
>> category was the one that needed to be changed, and then change it. Such
>> a process is highly inefficient in terms of both cpu effort, memory
>> management and disk io, and it is to prevent such inefficiencies amongst
>> other things that relational database design theory has evolved to where
>> it is today.
>>
>> Unlike your comprehension of computer systems which appears to have
>> started at nothing and continues to devolve backwards.
>
> I was merely suggesting one possible way.
> I have never liked the idea of making two tables where one is sufficient.
>
You were suggesting a way which proves you have NO IDEA of what RELATION
database design mean.
For instance, in YOUR design, what happens if he has no restaurants in
the current classification?
> In the OP's example, we have going on exactly what you speak of.
> Let's say he wants to add on a second pet shop
> Now he has to change the id's in two tables.
> Plus, the remaining id's all have to be changed accordingly.
> So what if he has 1,000 rows?
> He made a change on row 5. So now the sequence of events has to change
> 1990+ items?
>
He needs to add the pet shop in one table and a relationship entity in a
second table. That's what RELATIONAL means.
> In my example, if you want to add on another pet store, all you do is
> insert the new row and increment the id's.
>
> Otherwise, he should have a table for each category.
> Forget cross referencing with id's.
>
This is beyond STOOPID!
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Loop through array, change headings [message #184443 is a reply to message #184442] |
Wed, 01 January 2014 02:08 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
On Tue, 31 Dec 2013 20:50:31 -0500, Jerry Stuckle wrote:
> On 12/31/2013 7:19 PM, richard wrote:
>> On Tue, 31 Dec 2013 23:00:32 +0000 (UTC), Denis McMahon wrote:
>>
>>> On Tue, 31 Dec 2013 17:06:38 -0500, richard wrote:
>>>
>>>> On Tue, 31 Dec 2013 21:08:42 +0000 (UTC), Adrienne Boswell wrote:
>>>
>>>> > I have a table that has three classifications.
>>>
>>>> > I have another table that has categories in each classification.
>>>
>>>> Why not have one table?
>>>
>>> Richard, you've proved time and time again that you have no understanding
>>> of relational database design theory. Please stop trying to suggest that
>>> people with sensibly designed databases should do it the stoopid way.
>>>
>>> Adrienne's query joins the multiple tables into a single dataset and
>>> sorts the dataset, the question asked was how to process that sorted
>>> dataset in a specific manner. The question has nothing to do with the
>>> structure of the data in the database, the answer lies solely in how the
>>> result object returned by the mysql[i]->query() is processed.
>>>
>>> Here's one example of why Adriennes approach is easier:
>>>
>>> Supposing Adrienne decides to rename one of the three classifications. In
>>> Adriennes database, that involves a single sql operation on one row in
>>> one table. In your hypothetical and seriously fucked up database design,
>>> you would need to test each row in the whole database to see if the
>>> category was the one that needed to be changed, and then change it. Such
>>> a process is highly inefficient in terms of both cpu effort, memory
>>> management and disk io, and it is to prevent such inefficiencies amongst
>>> other things that relational database design theory has evolved to where
>>> it is today.
>>>
>>> Unlike your comprehension of computer systems which appears to have
>>> started at nothing and continues to devolve backwards.
>>
>> I was merely suggesting one possible way.
>> I have never liked the idea of making two tables where one is sufficient.
>>
>
> You were suggesting a way which proves you have NO IDEA of what RELATION
> database design mean.
>
> For instance, in YOUR design, what happens if he has no restaurants in
> the current classification?
>
>> In the OP's example, we have going on exactly what you speak of.
>> Let's say he wants to add on a second pet shop
>> Now he has to change the id's in two tables.
>> Plus, the remaining id's all have to be changed accordingly.
>> So what if he has 1,000 rows?
>> He made a change on row 5. So now the sequence of events has to change
>> 1990+ items?
>>
>
> He needs to add the pet shop in one table and a relationship entity in a
> second table. That's what RELATIONAL means.
>
>> In my example, if you want to add on another pet store, all you do is
>> insert the new row and increment the id's.
>>
>> Otherwise, he should have a table for each category.
>> Forget cross referencing with id's.
>>
>
> This is beyond STOOPID!
well duhh, jerry stickett, whenever a data cell has no value, the returned
value is zero or null.
Suppose I had a table that had 12 columns and 365 rows.
Which would represent a calendar.
What data would be in the cell for Feb, 31?
There would be data only in the cells for that given column.
row1 would be Jan 1, column 1.
row1 coluimns 2 through 12 would be blank.
According to you, I would need 12 tables.
|
|
|
|
| Re: Loop through array, change headings [message #184444 is a reply to message #184443] |
Wed, 01 January 2014 02:43 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 12/31/2013 9:08 PM, richard wrote:
> On Tue, 31 Dec 2013 20:50:31 -0500, Jerry Stuckle wrote:
>
>> On 12/31/2013 7:19 PM, richard wrote:
>>> On Tue, 31 Dec 2013 23:00:32 +0000 (UTC), Denis McMahon wrote:
>>>
>>>> On Tue, 31 Dec 2013 17:06:38 -0500, richard wrote:
>>>>
>>>> > On Tue, 31 Dec 2013 21:08:42 +0000 (UTC), Adrienne Boswell wrote:
>>>>
>>>> >> I have a table that has three classifications.
>>>>
>>>> >> I have another table that has categories in each classification.
>>>>
>>>> > Why not have one table?
>>>>
>>>> Richard, you've proved time and time again that you have no understanding
>>>> of relational database design theory. Please stop trying to suggest that
>>>> people with sensibly designed databases should do it the stoopid way.
>>>>
>>>> Adrienne's query joins the multiple tables into a single dataset and
>>>> sorts the dataset, the question asked was how to process that sorted
>>>> dataset in a specific manner. The question has nothing to do with the
>>>> structure of the data in the database, the answer lies solely in how the
>>>> result object returned by the mysql[i]->query() is processed.
>>>>
>>>> Here's one example of why Adriennes approach is easier:
>>>>
>>>> Supposing Adrienne decides to rename one of the three classifications. In
>>>> Adriennes database, that involves a single sql operation on one row in
>>>> one table. In your hypothetical and seriously fucked up database design,
>>>> you would need to test each row in the whole database to see if the
>>>> category was the one that needed to be changed, and then change it. Such
>>>> a process is highly inefficient in terms of both cpu effort, memory
>>>> management and disk io, and it is to prevent such inefficiencies amongst
>>>> other things that relational database design theory has evolved to where
>>>> it is today.
>>>>
>>>> Unlike your comprehension of computer systems which appears to have
>>>> started at nothing and continues to devolve backwards.
>>>
>>> I was merely suggesting one possible way.
>>> I have never liked the idea of making two tables where one is sufficient.
>>>
>>
>> You were suggesting a way which proves you have NO IDEA of what RELATION
>> database design mean.
>>
>> For instance, in YOUR design, what happens if he has no restaurants in
>> the current classification?
>>
>>> In the OP's example, we have going on exactly what you speak of.
>>> Let's say he wants to add on a second pet shop
>>> Now he has to change the id's in two tables.
>>> Plus, the remaining id's all have to be changed accordingly.
>>> So what if he has 1,000 rows?
>>> He made a change on row 5. So now the sequence of events has to change
>>> 1990+ items?
>>>
>>
>> He needs to add the pet shop in one table and a relationship entity in a
>> second table. That's what RELATIONAL means.
>>
>>> In my example, if you want to add on another pet store, all you do is
>>> insert the new row and increment the id's.
>>>
>>> Otherwise, he should have a table for each category.
>>> Forget cross referencing with id's.
>>>
>>
>> This is beyond STOOPID!
>
> well duhh, jerry stickett, whenever a data cell has no value, the returned
> value is zero or null.
>
You don't even understand the question, RTS!
Here's a hint: In your design, if there were no restaurants, there would
be no restaurant category.
>
> Suppose I had a table that had 12 columns and 365 rows.
> Which would represent a calendar.
> What data would be in the cell for Feb, 31?
>
You tell me. I wouldn't even try to design a database in such a stoopid
way.
> There would be data only in the cells for that given column.
>
>
>
> row1 would be Jan 1, column 1.
> row1 coluimns 2 through 12 would be blank.
>
> According to you, I would need 12 tables.
>
Nope. But you're too stoopid to understand that.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Loop through array, change headings [message #184445 is a reply to message #184443] |
Wed, 01 January 2014 03:24 |
Beauregard T. Shagnas
Messages: 154
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
richard the sto0pid wrote:
> Suppose I had a table that had 12 columns and 365 rows. Which would
> represent a calendar.
One row for every day; I suppose that could be some form of "calendar" -
albeit a strange one. What will you do for Leap Year? Why do you need 11
extra fields?
> What data would be in the cell for Feb, 31?
Database tables have fields, not cells. A database is not a spreadsheet.
There is no Feb 31.
> There would be data only in the cells for that given column.
That sentence is untranslatable.
> row1 would be Jan 1, column 1.
> row1 coluimns 2 through 12 would be blank.
What would you want to place in 11 extra *fields* for each day?
> According to you, I would need 12 tables.
Nothing of the sort, just another untranslatable statement. It enforces
the fact that you know absolutely nothing about database design,
especially _relational_ database design. You continue to reinforce Denis's
statement of your "comprehension of computer systems which appears to have
started at nothing and continues to devolve backwards."
While you're reading about Third Normal Form, also study Primary Keys and
Foreign Keys.
--
-bts
-This space for rent, but the price is high
|
|
|
|
| Re: Loop through array, change headings [message #184446 is a reply to message #184443] |
Wed, 01 January 2014 03:26 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Tue, 31 Dec 2013 21:08:20 -0500, richard wrote:
> Suppose I had a table that had 12 columns and 365 rows.
> Which would represent a calendar.
That's not how you'd represent a calendar in a database. You'd represent
a calendar as:
start time, end time (or duration), event id
In the events table, you'd have more info about the event keyed by the
event id, which might in turn link to other tables like contacts (for
people attending the event) by way of link tables (contact id, event id)
and locations (keyed by location id) for where the events are held.
The whole idea of relational databases is that the key information about
each contact, event or location is only held in one table in one place,
so eg if a contact happens to be an event attendee but also a location
manager, the same contact id is used in the attendee link table and in
the location table. Then if the contact changes their phone number,
instead of having to change that phone number in the attendee list for
every event the contact is attending and in the location table as the
location manager, you only need to change it in the contact table, one
change.
Not sure why I'm bothering to explain this. You seem to be genetically
incapable of absorbing and comprehending database theory.
There's all these people designing working databases all over the planet
using standard database theory, not to mention all the professors and
undergrads and PhDs etc that study, develop and teach those theories, and
then there's the richard methodology which seems to completely disagree
with what everyone else is doing and teaching and researching.
And richard continues to insist that he is right and the rest of the
world is wrong.
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
| Re: Loop through array, change headings [message #184449 is a reply to message #184435] |
Wed, 01 January 2014 16:56 |
Arno Welzel
Messages: 317
Registered: October 2011
Karma: 0
|
Senior Member |
|
|
Am 31.12.2013 23:06, schrieb richard:
> On Tue, 31 Dec 2013 21:08:42 +0000 (UTC), Adrienne Boswell wrote:
>
>> I'm sure this has been asked and answered, but I am either too stupid to
>> figure out the right way to ask Google, or I'm lazy, or it really hasn't
>> been asked. Sorry in advance, and if it has been answered, please refer
>> me as needed.
>>
>> Here's my situation:
>> I have a table that has three classifications:
[...]
>> I have another table that has categories in each classification, eg.
>> id Category Classification_id
>> 1 Market 1
>> 2 Pet Shop 1
>> 3 Pizza 2
>> 4 Sandwich 2
>> 5 Notary 3
>> 6 Repair 3
[...]
> Why not have one table?
> id | stores | restaurant | services
Please ignore such recommendations - richard seems to be incapable to
understand software development at all when you review his posts in the
last couple if years.
--
Arno Welzel
http://arnowelzel.de
http://de-rec-fahrrad.de
|
|
|
|
| Re: Loop through array, change headings [message #184450 is a reply to message #184438] |
Wed, 01 January 2014 17:01 |
Arno Welzel
Messages: 317
Registered: October 2011
Karma: 0
|
Senior Member |
|
|
Am 01.01.2014 01:19, schrieb richard:
> On Tue, 31 Dec 2013 23:00:32 +0000 (UTC), Denis McMahon wrote:
>
[...]
>> Here's one example of why Adriennes approach is easier:
>>
>> Supposing Adrienne decides to rename one of the three classifications. In
>> Adriennes database, that involves a single sql operation on one row in
>> one table. In your hypothetical and seriously fucked up database design,
>> you would need to test each row in the whole database to see if the
>> category was the one that needed to be changed, and then change it. Such
>> a process is highly inefficient in terms of both cpu effort, memory
>> management and disk io, and it is to prevent such inefficiencies amongst
>> other things that relational database design theory has evolved to where
>> it is today.
>>
>> Unlike your comprehension of computer systems which appears to have
>> started at nothing and continues to devolve backwards.
>
> I was merely suggesting one possible way.
> I have never liked the idea of making two tables where one is sufficient.
One table is NOT SUFFICIENT!
Because then EVERY classification has to be repeated over and over again
in every single record.
> In the OP's example, we have going on exactly what you speak of.
No we don't!
> Let's say he wants to add on a second pet shop
> Now he has to change the id's in two tables.
No! "Store" is a CATEGORY and "pet shop" is just an entry. One would
just create a record "Pet shop 2" with the CATEGORY "Store".
> Plus, the remaining id's all have to be changed accordingly.
> So what if he has 1,000 rows?
Even if the CATEGORY of existing entries would have to be changed - this
would be a simple SQL statement.
> He made a change on row 5. So now the sequence of events has to change
> 1990+ items?
No. You don't understand - as usual :-(
--
Arno Welzel
http://arnowelzel.de
http://de-rec-fahrrad.de
|
|
|
|
| Re: Loop through array, change headings [message #184462 is a reply to message #184438] |
Thu, 02 January 2014 11:23 |
Norman Peelman
Messages: 126
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 12/31/2013 07:19 PM, richard wrote:
> On Tue, 31 Dec 2013 23:00:32 +0000 (UTC), Denis McMahon wrote:
>
>> On Tue, 31 Dec 2013 17:06:38 -0500, richard wrote:
>>
>>> On Tue, 31 Dec 2013 21:08:42 +0000 (UTC), Adrienne Boswell wrote:
>>
>>>> I have a table that has three classifications.
>>
>>>> I have another table that has categories in each classification.
>>
>>> Why not have one table?
>>
>> Richard, you've proved time and time again that you have no understanding
>> of relational database design theory. Please stop trying to suggest that
>> people with sensibly designed databases should do it the stoopid way.
>>
>> Adrienne's query joins the multiple tables into a single dataset and
>> sorts the dataset, the question asked was how to process that sorted
>> dataset in a specific manner. The question has nothing to do with the
>> structure of the data in the database, the answer lies solely in how the
>> result object returned by the mysql[i]->query() is processed.
>>
>> Here's one example of why Adriennes approach is easier:
>>
>> Supposing Adrienne decides to rename one of the three classifications. In
>> Adriennes database, that involves a single sql operation on one row in
>> one table. In your hypothetical and seriously fucked up database design,
>> you would need to test each row in the whole database to see if the
>> category was the one that needed to be changed, and then change it. Such
>> a process is highly inefficient in terms of both cpu effort, memory
>> management and disk io, and it is to prevent such inefficiencies amongst
>> other things that relational database design theory has evolved to where
>> it is today.
>>
>> Unlike your comprehension of computer systems which appears to have
>> started at nothing and continues to devolve backwards.
>
> I was merely suggesting one possible way.
> I have never liked the idea of making two tables where one is sufficient.
>
> In the OP's example, we have going on exactly what you speak of.
> Let's say he wants to add on a second pet shop
> Now he has to change the id's in two tables.
> Plus, the remaining id's all have to be changed accordingly.
> So what if he has 1,000 rows?
> He made a change on row 5. So now the sequence of events has to change
> 1990+ items?
>
> In my example, if you want to add on another pet store, all you do is
> insert the new row and increment the id's.
>
> Otherwise, he should have a table for each category.
> Forget cross referencing with id's.
>
The whole idea behind relational databases is to set things up so
that you don't need to make changes like that.
--
Norman
Registered Linux user #461062
-Have you been to www.php.net yet?-
|
|
|
|
| Re: Loop through array, change headings [message #184464 is a reply to message #184462] |
Thu, 02 January 2014 13:09 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Thu, 02 Jan 2014 06:23:23 -0500, Norman Peelman wrote:
> The whole idea behind relational databases is to set things up so
> that you don't need to make changes like that.
Norman, remember that richard is the guy that sets up a separate table
for each year's records because that's more sensible (in richard land[1])
than having a year field in the record table.
[1] Any semblance of richard land to cloud cuckoo land is probably not
coincidental.
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
| Re: Loop through array, change headings [message #184466 is a reply to message #184464] |
Thu, 02 January 2014 15:20 |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Thu, 2 Jan 2014 13:09:10 +0000 (UTC), Denis McMahon wrote:
> On Thu, 02 Jan 2014 06:23:23 -0500, Norman Peelman wrote:
>
>> The whole idea behind relational databases is to set things up so
>> that you don't need to make changes like that.
>
> Norman, remember that richard is the guy that sets up a separate table
> for each year's records because that's more sensible (in richard land[1])
> than having a year field in the record table.
>
> [1] Any semblance of richard land to cloud cuckoo land is probably not
> coincidental.
I think there was something about that helping to manage the size of the
tables as well. Because any database will break if you put more than a
couple thousand rows into a table...
--
It seems that we were audited recently, and the auditors found a certain
'f' word in the comments of a configuration file, and deemed that this
is a 'security risk'.
-- Paul Fenwick
|
|
|
|
| Re: Loop through array, change headings [message #184467 is a reply to message #184466] |
Thu, 02 January 2014 15:39 |
Beauregard T. Shagnas
Messages: 154
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
Peter H. Coffin wrote:
> Denis McMahon wrote:
>> Norman Peelman wrote:
>>> The whole idea behind relational databases is to set things up so that
>>> you don't need to make changes like that.
>>
>> Norman, remember that richard is the guy that sets up a separate table
>> for each year's records because that's more sensible (in richard
>> land[1]) than having a year field in the record table.
>>
>> [1] Any semblance of richard land to cloud cuckoo land is probably not
>> coincidental.
>
> I think there was something about that helping to manage the size of the
> tables as well. Because any database will break if you put more than a
> couple thousand rows into a table...
Peter, please don't recommend the place you found that information as any
kind of reputable reference site. I've worked with databases that had
literally millions of records in single tables.
If your "database" consists of an Excel spreadsheet, you may have a minor
point.
--
-bts
-This space for rent, but the price is high
|
|
|
|
| Re: Loop through array, change headings [message #184468 is a reply to message #184467] |
Thu, 02 January 2014 15:52 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 1/2/2014 10:39 AM, Beauregard T. Shagnasty wrote:
> Peter H. Coffin wrote:
>
>> Denis McMahon wrote:
>>> Norman Peelman wrote:
>>>> The whole idea behind relational databases is to set things up so that
>>>> you don't need to make changes like that.
>>>
>>> Norman, remember that richard is the guy that sets up a separate table
>>> for each year's records because that's more sensible (in richard
>>> land[1]) than having a year field in the record table.
>>>
>>> [1] Any semblance of richard land to cloud cuckoo land is probably not
>>> coincidental.
>>
>> I think there was something about that helping to manage the size of the
>> tables as well. Because any database will break if you put more than a
>> couple thousand rows into a table...
>
> Peter, please don't recommend the place you found that information as any
> kind of reputable reference site. I've worked with databases that had
> literally millions of records in single tables.
>
> If your "database" consists of an Excel spreadsheet, you may have a minor
> point.
>
I may be wrong - but I think he meant that was another richardism.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
|
|
| Re: Loop through array, change headings [message #184473 is a reply to message #184466] |
Thu, 02 January 2014 18:04 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Thu, 02 Jan 2014 09:20:55 -0600, Peter H. Coffin wrote:
> On Thu, 2 Jan 2014 13:09:10 +0000 (UTC), Denis McMahon wrote:
>> On Thu, 02 Jan 2014 06:23:23 -0500, Norman Peelman wrote:
>>
>>> The whole idea behind relational databases is to set things up so that
>>> you don't need to make changes like that.
>> Norman, remember that richard is the guy that sets up a separate table
>> for each year's records because that's more sensible (in richard
>> land[1])
>> than having a year field in the record table.
> I think there was something about that helping to manage the size of the
> tables as well. Because any database will break if you put more than a
> couple thousand rows into a table...
Oh shit, you mean this small table of 80,000 rows is broken?
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
| Re: Loop through array, change headings [message #184529 is a reply to message #184468] |
Mon, 06 January 2014 00:23 |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Thu, 02 Jan 2014 10:52:49 -0500, Jerry Stuckle wrote:
> On 1/2/2014 10:39 AM, Beauregard T. Shagnasty wrote:
>
>> Peter, please don't recommend the place you found that information as
>> any kind of reputable reference site. I've worked with databases that
>> had literally millions of records in single tables.
>>
>> If your "database" consists of an Excel spreadsheet, you may have a
>> minor point.
>
>
> I may be wrong - but I think he meant that was another richardism.
Indeed!
Most of the stuff I work with at $dayjob start off with 120 million
rows and we winnow it down to only the "most important" 7-8 million
records by sales records, maniacal guesswork, and willful violations
of normal form before loading it to the warehouse. One of the interim
tables usually pops a terarow by the 4th quarter run....
--
Better to teach a man to fish than to give him a fish. And if he can't
be bothered to learn to fish and starves to death, that's a good enough
outcome for me.
-- Steve VanDevender
|
|
|
|
| Re: Loop through array, change headings [message #184530 is a reply to message #184529] |
Mon, 06 January 2014 00:37 |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Sun, 5 Jan 2014 18:23:02 -0600, Peter H. Coffin wrote:
> Most of the stuff I work with at $dayjob start off with 120 million
> rows and we winnow it down to only the "most important" 7-8 million
> records by sales records, maniacal guesswork, and willful violations
> of normal form before loading it to the warehouse. One of the interim
> tables usually pops a terarow by the 4th quarter run....
terabyte. mixing units with too much wine this evening. Though it gets
really close to a billion rows.
--
51. If one of my dungeon guards begins expressing concern over the
conditions in the beautiful princess' cell, I will immediately
transfer him to a less people-oriented position.
--Peter Anspach's list of things to do as an Evil Overlord
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]