basically I want to keep track of each page visited and completed by each user. So I though I would insert into the database when they visited it and then update that entry on completion of that page.

The two main problems are:
1. How do you track individual users?
2. How do you know that the page is "completed" by the user?

The answer for #1 determines the time to insert a new row. The answer for #2 determines the time to update the row.

    1. after user logs in, username and userid is stored in a session.

    2. the end of the page is indicated by the submit button (I will probably develop somekind of javascript function where it checks that the user has scrolled down or has viewed the video)

    hope I was clear, please let me know if you need any more information, thank you so much

      Ah, so now: what does this table model? That is, what status does it record? For example, each row might record the user's visit to a page until he/she submits the form on that page. In such a case, you would insert a new row for that user only if there are no rows for that users that have not been marked (i.e., updated) as submitted.

        this is the table model:
        userid int(125) FOREIGN KEY from table users
        id int(125) FOREIGN KEY from table content
        complete (int) //0=incomplete and 1=incomplete
        date (date) //date incomplete/complete

        entry will not be made until user visits the page, so if user never visited the page, he will not be in the table.
        the insert function works perfect, but then when I update the same entry, it inserts it again.

        thank you again

          hmm... working with your current schema, you could first run the query:

          SELECT COUNT(userid) FROM status WHERE userid=$userid AND id=$id AND complete=0

          If the count is 0, insert a new row. Else, update the row.

          Another possibility would be to attempt the update query. If the database server reports an error saying that the row is not found, then insert the row.

            thank you laserlight,
            I like your idea, I tried to implement it, but I'm stuck with retrieving the value of the result of the query so I can check the count #:

            My query as suggested from you:
            $initial = "SELECT COUNT(userid) FROM status WHERE userid=$userid AND id=$id AND status1='0'";
            $result6= mysql_query($initial) or die (mysql_error());

            from here, how do I get the count # value?

              I've got the answer, thank you anyway. here is my answer:
              $counter = mysql_result($result6, "userid");

                You ARE aware of the fact that MySQL ignores "foreign key references" in a column definition, right? It has to be at the end by itself. See create table reference

                  thanks for the link, what do you mean at the end by itself though?

                    Suppose we have a master table:

                    create table master (a int primary key, b text) engine=innodb;

                    and we create a slave table like this:

                    create table slave (x int references master(x), y text) engine=innodb;

                    insert into slave (x,y) values (1,'abc');

                    There's nothing in the master table, but it doesn't fail. Why? Because MySQL silently accepts column level foreign key references without actually creating them.

                    We have to create the slave table like this:

                    create table slave (x int, y text, foreign key (x) references master(a)) engine=innodb;

                    Then, the insert above will properly fail because the foreign key has actually been created.

                    Note that if you do not declare innodb and myisam is the default type, that too will fail.

                    Silent failures are one of the "features" in mysql. And one of the reasons I don't use it for serious database work. It's too easy to make a mistake, get incoherent data, then spend a weekend trying to fix it.

                      Write a Reply...