Solved

Loop though MySQL table updating visit count for each unique key

Posted on 2011-02-15
6
471 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

895 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

12 Experts available now in Live!

Get 1:1 Help Now