Newbie Structure Question

[basic876]

Hi everyone,

I've got the following problem, that for every visitor a new row is added, with multiple extra variables, to Table A but now there are already a lot of rows and i would like to merge them, so instead of ten similar rows with value 1 there would be one row with value 10, but not sure how to best do it.

Should i insert the new rows to the same table or a new table, because table A is constantly updated and what kind of double-checks should I use incase a row with set variables already exists, so that instead of adding 2 copies of the same row it would update the existing row.

Thanks, Basic

[drawmack]

Check the row:
There should be subsets of the data contained in a row that uniquely identifies the row. The smallest such subset should be your primary key. Therefor you have a function that checks for the existence of the primary key such that it matches the primary key of the current user. If this key exists the function returns TRUE otherwise it returns false.

Table Modifycation:
Add an integer column to the end of the table that keeps this count you're looking for.

Initial Instalation of Methodology:
1) create a new table that is the same as the old table but add the new column. Name the new table <TABLE>new where <TABLE> is the name of the current table.
2) run a query something like this.
INSERT INTO <TABLE>new SELECT *, COUNT(<FIELD>) FROM <TABLE> GROUP BY <FIELD>
3) copy the old table to <TABLE>old
4) drop the old table
5) copy <TABLE>new to <TABLE>
6) verify data integrity
7) drop <TABLE>old and <TABLE>new

[basic876]

thanks for the reply... but i found an easier way to do it, i read the totals into variables then delete the rows i read the variables from and then insert a new row with the total variables, now multiple tables and misc confusing stuff needed 🆒

[drawmack]

you're fragmenting the hell out of your database though be sure you optimize and repair those tables when you're done.