Solved

Loop though MySQL table updating visit count for each unique key

Posted on 2011-02-15
6
468 Views
Last Modified: 2012-05-11
I've got an existing table, visit_tablet.  Pertinent fields are User_ID, visit_date, visit_time and visit_count.
visit_count is incremented each time the user visits, so that the records for each user will have a 1 for the first visit, a 2 for the second and so on.  Lots of records exist and visit_Count needs to be recalculated in all records.

I need the php code to loop though the mySQL table and update the visit_count.

Thanks
0
Comment
Question by:Waterstone
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:thecrew
ID: 34899836
Are you looking to increment visit_count each time the table is accessed?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34900407
Something like this...

$sql = "SELECT User_ID, visit_count FROM visit tablet";
$res = mysql_query($sql) or die( MySQL_Error() );
while ($row = mysql_fetch_assoc($res))
{
    $u= $row["User_ID"];
    $n = $row["visit_count"] + 1;
    $uql = "UPDATE visit_tablet SET visit_count = $n WHERE User_ID = $u LIMIT 1";
    $ues = mysql_query($uql) or die( mysql_error() );
}
0
 

Author Comment

by:Waterstone
ID: 34902473
Not quite.  figured it out

Here's what I did

<?php
$result = mysql_query("SELECT * FROM `visittable` order by userkey, `time`;");

$currentCount = 0;
$currentKey = 0;
$newKey = 0; 
 
while ( $row = mysql_fetch_assoc($result) ) {
	 $newKey = $row['userkey']; 
	 if ($currentKey <> $newKey)
	    {
		 $currentKey = $newKey;
		 $currentCount = 1;
		
		}
	else 
	    {

		 $currentCount =  $currentCount + 1;  
		}
	
    mysql_query("UPDATE visittable SET visit_count =  ".$currentCount." where visittable_key = ".$row['visittablekey'].";");
}
?>

Open in new window

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 34906675
In the original question you wrote, "Pertinent fields are User_ID, visit_date, visit_time and visit_count." and in the code example you posted, you showed a query that says, "SELECT * FROM `visittable` order by userkey, `time`;"

Not to put too fine a point on it, but computer programming is a fairly precise craft, and giving us the wrong variable names almost certainly cripples your fellow experts ability to help you.  Going forward, it is a good idea to post information like the CREATE TABLE statement, the expected outputs, etc.  More information, especially accurate information, is likely to help you get a better answer.

In a semi-related vein, you might want to get some foundation in PHP and MySQL.  This book will be helpful to you.  If you read that and work through the examples you will be light-years ahead.
http://www.sitepoint.com/books/phpmysql4/

MySQL has several reserved words, including the word, "time."  It is a good programming practice to avoid using reserved words for column names.  Check the results of this search to find other reserved words.
http://lmgtfy.com?q=MySQL+Reserved+Words

Good luck with your project, ~Ray
0
 

Author Comment

by:Waterstone
ID: 34907065
Thanks Ray.  I've been helped many times by the questions you've answered here.  Sorry for the mistype between my solution code and the question's code.  I was working on a test table of my own and then implementing it in a client's database, where I had no influence over table design. Not enough time or resources to restructure the tables at this time, and not in anyone's best interest to start pointing out all the design flaws in the database or the code.

I've been a database developer for over 30 years but am relatively new to PHP and MySQL.  Having worked with many different databases and environments I most often know exactly what I want to do but don't know how to code it in the specific environment I'm in at the moment.  I'd love to find a resource for experienced developers that is a strict how-to without any added verbiage.  Something like a cross-platform reference guide.

That being said, the book looks like it may be valuable.  I'll take a look at the free preview and take it from there.

Again, sorry for any confusion.  You're presence here is invaluable.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35032821
With your experience as a data base developer, you will go through that book like a hot knife through butter, and you'll come out the end of it with a lot of gratifying results.  I do not know of any good cross-platform reference guide.  It's a little like translating technical or idiomatic English to Russian.  You start our with "hydraulic ram" and pretty soon you're looking at "water goat."  Going forward, if you just post a pidgin-language sketch of what you want to do, we will almost always be able to help with language-specific solutions.

Thanks for the points, ~Ray
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now