Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Loop though MySQL table updating visit count for each unique key

Posted on 2011-02-15
6
Medium Priority
?
490 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 111

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 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 111

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The viewer will learn how to count occurrences of each item in an array.
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.
Suggested Courses

610 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