Solved

Loop though MySQL table updating visit count for each unique key

Posted on 2011-02-15
6
476 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 109

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 109

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 109

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Making API calls from hashed passwords 26 54
Using javascript confirmation for a delete in mysql 5 34
PHP Installer 5 28
Complex MySQL Query 2 20
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

825 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