Im working on ways to save more information for a link; eg:
I have set a new field "HitIn" for a link.
Now i have modiefied the rate.cgi to set Votes, Rates AND HitsIn. The build.cgi must be modified too in build_update_counters. A new Tag for Links.html shows the HitsIn. No problem so far and i have the goal reached that every rating makes a hit in.

Now i copied the rate.cgi to hitin.cgi and make a new table HitIn_Track for that. Why: If someone calls now the hitit.cgi, it does the same than the rate.cgi, but dont update Rates or Votes, but only HitIn. Step1: Add an entree to HitIn_Track; then to table Build_Update. The next time i build i got the HitIn to the table link. All fine so far, too.

But now my problem:
the table Rate_Track shows with phpMyAdmin:

lndx Yes LinkID
lndx Yes IP
cndx No Created

My new table HitIn_Track shows:

LinkID Ja LinkID
IP Ja IP
Created Nein Created

But this means, that an ID cant hit two times, cause the field is unique! So it must be something with the lndx or cndx; but there seems really no way to set my fields to that names. What is that? How could i get this to work?
If i kill these (or dont klick at unique, index ...) it runs, but i think Alex would think something if he had done this like it is.

Hope some SQL-people are around, too.
Robert

I'm not sure what you are trying to do.

I track both rates, hits, jumps, ratings, and other things.

I insert the updates directly into the Links tables (can't figure out why not to do that, since I could track the data separately as well), and created a Rate_Track, Search_Track, Hit_Track, etc that follows exactly the same format as the Rate_Track.

The same IP can't vote for the same link in 24 hours (or 12, I forget). The same IP can't get their "hit" counted as a jump to the site except once in 12 hours, etc. I've blocked the search from recording hits in 15 minutes from the same IP on the same word...

The unique key for these tables is "IP + Link ID"

By using separate tables, I don't have to track complex data. I have one unique index in each table, and it generates an error if you try to insert the same key (or, not generating an error if the key is not found).

I think my problem is I'm not sure what you are trying to do, but it seems to me you have tried to make this more complicated than it has to be.

The "Build UPdate" was a hold over from the flat file days, so that the main database only had to be opened on a build. Not every "jump". The SQL database is recording each "jump" in a table anyway, it might as well record it directly to the Links table. (Update Table_Name Set Field=Field+1 where LinkID=nn)

Then insert that into the appropriate "track" table, and bingo! No "update" needed.

Rather than store a munged version of the "ratings" why not store each "rating" in an [ "R" . $rating ] field, and each time you update the rating, you re-calculate the total in the RTotal Field ?? You can print out pretty graphs and keep exact tallys of 1's, 3's, 10's, etc. Is a rating of 7 with 10 votes -- 10 7's or were there a bunch of 10's and a couple of 1's and 0's?? Big difference -- people either liked it or hated it, they didn't really stand on middle ground.

Anyway, the penalty is very small, and by recalculating each "vote" when it happens, the penalty on a build is very small as well.

You can then bypass the Build_update table further, by doing your own gathering of statistics, by tracking hits to each link, but using a unique key for the DATE + LinkID. If you are trying to be able to get a 24 hour "Window" use a DATETIME field, and you can pick links for (I forget how many) seconds in the past.

I have not released any of the mods (well, I did release the Rate_Log and Search_Log and Hits_Track) because the next version of Links promises to do away with all of this. Stats are probably calculated in real-time, and the build_update table is eliminated.

A Seek/Write to an indexed table is just about the same if the table is 100 records or 100,000 with MySQL. So, write directly to the Links table!

Caveat Emptor -- The #1 problem we have seen here with Indexes/tables is corruption of the Build_Update and Hits_Track tables. For this reason, it is probably IMPERITIVE you run the "isamchk" utility as suggested in the MySQL docs to repair your indexes on a daily or at least weekly basis. Your builds are still important -- and maintaining 7 or 14 copies of old data is ON ANOTHER DEVICE COMPLETELY is still the best way of safeguarding your data.




http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

I fixed it, but dont now why, but how:
Status now:
I have copied the rate.cgi to hitin.cgi, like for rate.cgi with rate_track i need now a new table hitin_track. Both work with build_update.
Why doing this. I will give my webmasters another banner in future not only to link to my site, but calling the hitin.cgi, when sorting only for that i have links behave more than a toplist. (need now doing the ranking, and show rank and rankold)

My problem was/is to create a table as 1:1 copy of rate_track; its no problem to do that.
But with phpmyadmin i could only do the same for hitin_track to rate_track for:

LinkID int(10) UNSIGNED No 0
IP char(25) No
Created timestamp(14)

Thats ok and orginal the same than in rate_track

But there are more things in rate_track:
lndx Yes LinkID
lndx Yes IP
cndx No Created

No i want to know what LNDX and CNDX have a meaning?

The only way to get an 1:1 copy for rate_track to hitin_track is to copy the three mysql-files in the data dir, rate_track.ISD, ISM, frm over the new hitin_track.ISD, ISM, frm.
After doing this i erase all fields and suddenly i got
the right syntax for "name".

I dont know how to explain this better.

But its reality that my new table could not save the LinkID twice (from different IPs) when it looks like:

LinkID Yes LinkID
IP Yes IP
Created No Created

instead of:

lndx Yes LinkID
lndx Yes IP
cndx No Created


I need this new hitin_track cause:
A clicks at a foreign homepage on my banner; now the hitin.cgi get a call and bring him to my site, set in hitin_track the data; then he can rate this link where he come from. With only an update in rate_track, he could "hit" the page, but dont rate it!
If its better to write to the link-table directly, isnt the question; first i will try it as it is with that temp. table for updating. Im no programmer, dont understand one word in perl, but got a long list of other and own modifies in my private site at home. So i would go further step for step as its the way i could go.

You can't just take two fields and make them both "unique" and want them to work together.

You have to put BOTH fields into ONE index.

>> LinkID Yes LinkID
>> IP Yes IP
>> Created No Created

"Create INDEX Indx ON hitin_track (LinkID, IP);"

In that case, the primiary index would be LinkID with a secondary one of IP.

You might actually want to make it (IP, LinkID) since the IP will be more unique than the LinkID.

But, you decide -- are you lookups going to be more by IP or LinkID.





http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Thank you, my godess of know-how :-)
I understand that. How can you see which relations are on this table. lndx cndx have a special meaning or are they only names given from Alex? With my PhpMySql i cant see more than that and for future i must set some more tables; as for all three fields in this case,too.

I don't think the names of the indexes matter. They would only matter if you have multiple indexes, you can select which index you want to use. I haven't seen anywhere in the code where the index is selected. Almost all look-ups are by ID # in the basic links code.

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/