Add 1 to existing table row if condition...

[DavidPr]

I want to track how many times an article gets read by a unique reader. In other words, if the same person opens the same article a hundred times, it still only counts as one when it comes to the number displayed in the row "accessed" in the "articles" table.

The row titled "accessed" starts out with "0" and grows by 1 each time a unique or different reader opens the article.

I need some ideas as to how I should setup the database table to do this.

I'll have tables:

• submit_article_users - rows: s_user_id, s_user_name, etc.
• articles - rows: article_id, s_user_id, accessed, title, content
• read_article_users - rows: r_user_id, r_user_name
• track - rows: article_id, r_user_id

[PHP]<?php
$article_id' = $_GET['article_id''];
$query = "SELECT * FROM articles WHERE article_id = '$article_id'";
$result = mysql_query ($query);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$title = stripslashes($row['title']);
$article_id = $row["article_id"];
$s_user_name = stripslashes($row['s_user_name']);
$s_user_id = $row["s_user_id"];
}
echo "<strong>$title</strong> &nbsp;by $s_user_name<br><br>$content";


// Check if article has been opened before by reader.

$query = "SELECT r_user_id FROM track
WHERE r_user_id = '".$_SESSION['r_user_id']."'
AND article_id = '$article_id'";

$result = mysql_query ($query);
if ($result) { // Reader has accessed this article before so do nothing.

Should I put this here or leave this area blank?
include ('includes/bottom.php'); // Include the footer.
exit();

}
else
{

// The reader has not accessed this article before so add '".$_SESSION['r_user_id']."' and $article_id to table track and increase $accessed by 1 in the articles table.

// I'll need help with how to do this

$query = "INSERT into tract VALUES ('$article_id', '".$_SESSION['r_user_id']."');
$result = mysql_query ($query);

$accessed = ($accessed + 1);
$query = "UPDATE articles SET accessed='$accessed' WHERE article_id = $article_id";
$result = mysql_query ($query);
}

include('includes/bottom.php'); // Include the footer.
?>[/PHP]

I have not tried this code yet because I know that it would fail. I would like to know if what I have here would come anywhere close to doing what I need it to do? Any help woud be appreciated.

Thanks.
David

[dlite922]

I want to track how many times an article gets read by a unique reader. In other words, if the same person opens the same article a hundred times, it still only counts as one when it comes to the number displayed in the row "accessed" in the "articles" table.

The row titled "accessed" starts out with "0" and grows by 1 each time a unique or different reader opens the article.

I need some ideas as to how I should setup the database table to do this.

I'll have tables:

• submit_article_users - rows: s_user_id, s_user_name, etc.
• articles - rows: article_id, s_user_id, accessed, title, content
• read_article_users - rows: r_user_id, r_user_name
• track - rows: article_id, r_user_id

[PHP]<?php
$article_id' = $_GET['article_id''];
$query = "SELECT * FROM articles WHERE article_id = '$article_id'";
$result = mysql_query ($query);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$title = stripslashes($row['title']);
$article_id = $row["article_id"];
$s_user_name = stripslashes($row['s_user_name']);
$s_user_id = $row["s_user_id"];
}
echo "<strong>$title</strong> &nbsp;by $s_user_name<br><br>$content";


// Check if article has been opened before by reader.

$query = "SELECT r_user_id FROM track
WHERE r_user_id = '".$_SESSION['r_user_id']."'
AND article_id = '$article_id'";

$result = mysql_query ($query);
if ($result) { // Reader has accessed this article before so do nothing.

Should I put this here or leave this area blank?
include ('includes/bottom.php'); // Include the footer.
exit();

}
else
{

// The reader has not accessed this article before so add '".$_SESSION['r_user_id']."' and $article_id to table track and increase $accessed by 1 in the articles table.

// I'll need help with how to do this

$query = "INSERT into tract VALUES ('$article_id', '".$_SESSION['r_user_id']."');
$result = mysql_query ($query);

$accessed = ($accessed + 1);
$query = "UPDATE articles SET accessed='$accessed' WHERE article_id = $article_id";
$result = mysql_query ($query);
}

include('includes/bottom.php'); // Include the footer.
?>[/PHP]

I have not tried this code yet because I know that it would fail. I would like to know if what I have here would come anywhere close to doing what I need it to do? Any help woud be appreciated.

Thanks.
David

first two things: validate $_SESSION['r_user_id'] (secure it)
ie make sure its a valid one.

i assume you don't care if non-registered users see this article they don't count or can't access it anyway.

Other than that you're on the right track.

Alternatively, you can have one table and you just INSERT the user_id and article_id into it if no such row exists.

example if JANE has never accessed article 54, then add this row into the table.

To check if JANE has accessed it, use your method above, if $result is not empty, then she's accessed it.

Which is what you have,

Then to see how many people have accessed a particular article just do SELECT count(*) FROM article WHERE article_id = '54';

What advantage this has over yours is that it tells you EXACTLY which users have access the article.

So you can create something like a most recently accessed article for the user homepage, etc ... many possibilities.

If this information is beyond your outlook of this project, i'd just do what you arlready have.

good luck.

[DavidPr]

Thanks, the SELECT count(*) is a good idea. May also be a good idea to add a date row to see how recent the article was accessed. You're right - many possibilities here.

Yes, only certain registered users will be able to access the articles.

Thanks for the help.