Need Help with Query

[DavidPr]

I have a table that records (suppose to) the number of times a resume is viewed.

When someone clicks on a Send Resume link from a job ad, an email is sent to the employer telling him/her that someone has responded to their ad and sent a resume. This email contains a link that will open my website and display a page containing the job-seeker's resume. When this page is opened (resume viewed) I want it to be recorded in the resume_stats table.

The query on this page is supposed to do one of two things, check if this resume has already been put into the stats table (by being viewed before) and if not add it and set the row "viewed" to 1. If there is already a record of it then it should increase the number in the row "viewed" by 1 - to track how many times the resume has been viewed. Basically, if someone sends their resume to an employer they might like to know if the employer has looked at it or not.

Here's the query (it's not working of course):
[PHP]$file = $_GET['file']; // resume id # - $r_id
$nic = $_GET['nic']; // job-seeker id # - $js-user_id

include("includes/dbstats.php");
$query = "SELECT * FROM resume_stats where r_id='$file'";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0)
{
$query = "INSERT INTO resume_stats VALUES ('', '$nic', '$file', '1', '')";
}
else
{
$query = "SELECT r_id,viewed FROM resume_stats where r_id='$file'";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$r_id = $row['r_id'];
$viewed = $row['viewed'];
$viewed = $viewed + 1;
}
$query = "UPDATE resume_stats SET viewed='$viewed' WHERE r_id=$file";
}[/PHP]

Here's the resume_stats table - stat_id, js_user_id, r_id, viewed, accessed.

viewed = if the resume is viewed when the job-seeker sends it.
accessed = if the resume is viewed by an employer from the resume database.

Any help here would be appreciated.

[dlite922]

I have a table that records (suppose to) the number of times a resume is viewed.

When someone clicks on a Send Resume link from a job ad, an email is sent to the employer telling him/her that someone has responded to their ad and sent a resume. This email contains a link that will open my website and display a page containing the job-seeker's resume. When this page is opened (resume viewed) I want it to be recorded in the resume_stats table.

The query on this page is supposed to do one of two things, check if this resume has already been put into the stats table (by being viewed before) and if not add it and set the row "viewed" to 1. If there is already a record of it then it should increase the number in the row "viewed" by 1 - to track how many times the resume has been viewed. Basically, if someone sends their resume to an employer they might like to know if the employer has looked at it or not.

Here's the query (it's not working of course):
[PHP]$file = $_GET['file']; // resume id # - $r_id
$nic = $_GET['nic']; // job-seeker id # - $js-user_id

include("includes/dbstats.php");
$query = "SELECT * FROM resume_stats where r_id='$file'";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0)
{
$query = "INSERT INTO resume_stats VALUES ('', '$nic', '$file', '1', '')";
}
else
{
$query = "SELECT r_id,viewed FROM resume_stats where r_id='$file'";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$r_id = $row['r_id'];
$viewed = $row['viewed'];
$viewed = $viewed + 1;
}
$query = "UPDATE resume_stats SET viewed='$viewed' WHERE r_id=$file";
}[/PHP]

Here's the resume_stats table - stat_id, js_user_id, r_id, viewed, accessed.

viewed = if the resume is viewed when the job-seeker sends it.
accessed = if the resume is viewed by an employer from the resume database.

Any help here would be appreciated.

Why is it now working? do you get an error? Have you tried troubleshooting it by placing the die() statement and observing what has happened up to that point?

We dont' have your database, setup, in front of us to trouble shoot it for you. Find the problem, when you can't fix it, we're here to help.

Your logic is right, although not perfect.

Why make a separte table? can't you add a column to the resume table and hit it there?

So all resume counts will be set to 0 on insert. Each time they're accessed you'll just need to execute one simple query instead of doing 3.

UPDATE tableName SET count = (SELECT count+1 FROM tableName WHERE id = '$file') WHERE id = '$file';.

and Voila! you just increased it by one.

Also consider that if the employer views it more than one time, it will increase it. If you want to increase the count by one for each employer no matter how many times they go to the link, then we've got more complications.

Let me know if you need help doing that.

Happy Coding and Good Luck,



DM

[dlite922]

Don't know why this shows no replies when i can see my reply above ^^

[Markus]

Don't know why this shows no replies when i can see my reply above ^^

"2 Replies" To me :)

Now i guess it's 3

;)

[thefox149]

We do a similar thing with urls in an email
the url looks somthing like this

http://youdomain.com/product.php?id=23&emailview=Y

this way we can test the $_GET['emailview'] and do something.

why not create a transation table that purely counts the views of that particular resume rather than increment or
$emailview = $_GET['emailview'];
if($emailview == "Y"){
sql to increment $_GET['id'];
}

Am I hleping ...or telling you something you already know?