count rows, display data, then update by row

[gearsgod]

ok so for awhile I have had this problem, I have been creating an easy to use SQL database editor, and its working great, but there's one problem, it relies on a auto-incrementing column to work, bah. Now if your a good admin you would have one in every table, but the world is never good like that (or maybe it doesn't need it in rare cases) and so I want to do it so that even if the table doesn't have one it can still,

1.count rows

2.display the data for each for in a while loop

3.update the data (like on row 4, column 'icecream')

 

but I just can not for the life of me figer it out.

 

here's a copy of it for you to use/ figer out how I might go about changing it to how I want it to be if you want

http://siteofpurity.gotdns.com/sql/easy-SQL-editor-0-4-0.zip

[QuickOldCar]

Using auto_increment gives each a truly unique value, unless you are using unique values as a primary key you could easily have duplicates or issues when trying to fetch/update/delete the values.

 

The one big problem with auto_increment is when merging, but that could also be solved by exporting and importing the sql instead. But could have duplicate data with a different id key. A simple script deleting any duplicate data could remedy that. Then can even reset the auto_increment id's removing empty ones.

 

These 3 items are very common and basic things to do.

1.count rows

http://dev.mysql.com/doc/refman/5.6/en/counting-rows.html

2.display the data for each for in a while loop

http://dev.mysql.com/doc/refman/5.6/en/select.html

http://dev.mysql.com/doc/refman/5.6/en/while-statement.html

3.update the data (like on row 4, column 'icecream')

http://dev.mysql.com/doc/refman/5.6/en/update.html

 

I recommend people use auto_increment unless have a special purpose to not use it.

Why not simply just alter the table and make it auto_increment?

alter table table_name add id int(11) primary key auto_increment not null; 

http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

[gearsgod]

the links you posted I already knew about, it's when I go to update the data that just knowing which number the row is doesn't help when using the WHERE clause. I wanted it to work with every database no matter how it was setup but I guess relying on a auto-incrementing column isn't bad since just about everything has one. thanks for the help though 

[ebmigue]

....it's when I go to update the data that just knowing which number the row is doesn't help when using the WHERE clause.

 

If your table doesn't have a defined candidate key (i.e., an auto-increment field guaranteed to be unique), then by default,  every field/attribute of the table/relvar in question are in the set of the sole composite candidate key.

 

For instance, if you have a relvar/table A, with attributes attr1, attr2, attr3, ...attrN; and A has no explicitly defined candidate key; then by default, the the candidate key of relvar/table A is, say, CK. CK is a composite candidate key whose elements are the attributes of A, i.e.,

attr1, attr2, attr3, ..., attrN.

 

This is the what Relational Theory teaches. In theory a table should strictly not contain duplicate rows, since relations are sets, and sets have no duplicates (in math). Now, in SQL, sadly, tables are not entirely the same with relations, even if those products are advertised as "relational."

 

 

Now, how does it apply to you?

 

1.) For updating rows:

 

Obviously, if a user didn't define an auto-increment field for some table A, then automatically, all the attributes of A are members of the sole candidate key.

 

If so, instead of your WHERE clause being:

UPDATE A SET attr1 = <some_new_value> WHERE auto_inc_field = <some_old_value>;

 

It should be:

UPDATE A SET attr1 = <new_value_of_attr1>, ..., attrN = <new_value_of_attrN> 
WHERE attr1 = <old_value_of_attr1>  ... AND ...  attrN = <old_value_of_attrN>

 

 

2.) Counting rows:

Since you can't do this because there isn't a defined auto_inc field:

SELECT COUNT(auto_inc_field) FROM A;

 

You can do this instead:

SELECT COUNT(DISTINCT *) FROM A;

But if that is not possible with you version, try this instead:

SELECT COUNT(*) FROM (SELECT DISTINCT * FROM A)x

 

3.) Display data:

Remember that a table, originally, is intended to be a relation. But since most SQL-based software (i.e., today's DBMSs) failed to implement the concept of a relation (which they call as "tables") in their products, we now are forced to use half-relations-half-tables, so to speak.

 

Anyway, to get rid of duplicated rows, do this in every query that you'll make from hereon:

 

SELECT DISTINCT * FROM A;

 

Adding the DISTINCT directive is their "fix" for their shortcoming.

 

 

Hope it helps.

[gearsgod]

....it's when I go to update the data that just knowing which number the row is doesn't help when using the WHERE clause.

 

If your table doesn't have a defined candidate key (i.e., an auto-increment field guaranteed to be unique), then by default,  every field/attribute of the table/relvar in question are in the set of the sole composite candidate key.

 

For instance, if you have a relvar/table A, with attributes attr1, attr2, attr3, ...attrN; and A has no explicitly defined candidate key; then by default, the the candidate key of relvar/table A is, say, CK. CK is a composite candidate key whose elements are the attributes of A, i.e.,

attr1, attr2, attr3, ..., attrN.

 

This is the what Relational Theory teaches. In theory a table should strictly not contain duplicate rows, since relations are sets, and sets have no duplicates (in math). Now, in SQL, sadly, tables are not entirely the same with relations, even if those products are advertised as "relational."

 

 

Now, how does it apply to you?

 

1.) For updating rows:

 

Obviously, if a user didn't define an auto-increment field for some table A, then automatically, all the attributes of A are members of the sole candidate key.

 

If so, instead of your WHERE clause being:

UPDATE A SET attr1 = <some_new_value> WHERE auto_inc_field = <some_old_value>;

 

It should be:

UPDATE A SET attr1 = <new_value_of_attr1>, ..., attrN = <new_value_of_attrN> 
WHERE attr1 = <old_value_of_attr1>  ... AND ...  attrN = <old_value_of_attrN>

I thought about that but what about if attr1 has 5 rows with the number 5?

 

2.) Counting rows:

Since you can't do this because there isn't a defined auto_inc field:

SELECT COUNT(auto_inc_field) FROM A;

 

You can do this instead:

SELECT COUNT(DISTINCT *) FROM A;

But if that is not possible with you version, try this instead:

SELECT COUNT(*) FROM (SELECT DISTINCT * FROM A)x

First one worked, and I forgot about DISTINCT

 

3.) Display data:

Remember that a table, originally, is intended to be a relation. But since most SQL-based software (i.e., today's DBMSs) failed to implement the concept of a relation (which they call as "tables") in their products, we now are forced to use half-relations-half-tables, so to speak.

 

Anyway, to get rid of duplicated rows, do this in every query that you'll make from hereon:

 

SELECT DISTINCT * FROM A;

 

Adding the DISTINCT directive is their "fix" for their shortcoming.

When I tried that out it just threw up at me so I don't think that was it

 

Hope it helps.

 

Thanks for taking the time to explain and stuff though, I learned something new and that's always good

[ebmigue]

I thought about that but what about if attr1 has 5 rows with the number 5?

 

I don't quite understand what you meant by that.

 

First one worked, and I forgot about DISTINCT

Ok, what worked exactly?

 

When I tried that out it just threw up at me so I don't think that was it

Again, I don't understand what you meant. What "threw up" at you? You mean, there was an error?

 

 

In any case, the concept of a relation is not well understood. It is taken as normal that a "relation" (i.e., a table) does have

duplicate rows.

 

For instance, create a table in MySQL with attributes "a" and "b" of type integer. Then insert two rows that

are exactly the same:

INSERT INTO table1 VALUES (1,1), (1,1);

 

Then invoke the project operator (i.e., SELECT) on that table:

SELECT * FROM table1;

 

And you will notice that the two duplicate rows are shown (i.e., the cardinality of the said table is 2 and not 1).

 

But that is definitely wrong, since, in set theory (which is the basis of the Relational Model, and hence should be the basis of software products that claim to be relational) sets do not contain duplicates.

 

Note though that these concepts are not well understood and appreciated by the majority.

 

Now, how does it apply to you?

 

I would recommend that you require your users to always have a defined auto_inc attribute for the tables that they create using your program.

 

Why is that?

 

Since duplicate rows are allowed in SQL, it is only the auto_inc field which will guarantee that this will not happen. This is a hack of course,

but at least it avoids the existence of duplicate rows.

 

But then again there are cases where the auto_inc field is needless, because the so-called "natural keys" are more appropriate.

 

The point is, encourage your users to define candidate keys for their tables.

 

If they won't define one, your program should automatically create an auto_inc attribute for the table being defined. Of course, they must be notified of these built-in features.

 

 

Hope it helps.

[gearsgod]

I get most of what your saying, but right now it just edits whats already there, I am still working on adding tables/databases, also adding a auto-inc column if they don't have one is something els I need to do, It's a one man project right now so it's hard to get a lot done but it is coming along nicely, Thanks for the reply again!