PHP and MySQL counter

Question

I'm making a simple website the counts up every time someone clicks on the link, I'm new to PHP and MySQL, but I have the basics down. So, I'm having a problem with making a 'goal counter' So lets say I have ht goal set to 700 views. And the page is at 500 views. When the views reach 700, I want the goal to raise 200. So it would be 900, I want this to happen every time the views reach the goal. This is how I tried to do it:

$goalQuery = mysql_query("SELECT * FROM goal");
$goalRow = mysql_fetch_assoc($goalQuery);
if($viewNewCounts == $goal)  {
    $goalCounts = $goalRow['counts'];
    $goalNewCounts = $goalCounts + 200;
    $goalUpdate = mysql_query("UPDATE `Recoreder` . `goal` SET `counts` $goalNewCounts");
}

My DB (named 'Recorder') is setup where I have 2 tables: "goal" and "views" each table has a row named "counts"

Here is a visual of what my Database looks like:

                    |---counts
           |---views|      
---Recorder| [Tables]   [Rows]
           |---goal |    
                    |---counts

My counter code looks like this

NOTE: IT WORKS FINE, I'M NOT HAVING TROUBLE WITH MY COUNTER, I'M HAVING TROUBLE WITH MY GOAL

$viewQuery = mysql_query("SELECT * FROM views");

while($viewRow = mysql_fetch_assoc($viewQuery))
{
    $viewCounts = $viewRow['counts'];
    $viewNewCounts = $viewCounts + 1;
    $viewUpdate = mysql_query("UPDATE `Recorder` . `views` SET `counts` = $viewNewCounts");
}

Stop using the mysql_ libs they have been outdated for more than 10 years now. Use pdo instead. See: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — Johan, Oct 27 '13 at 23:25

Johan · Accepted Answer · 2013-10-28T00:02:33.673

The following code should do the trick.

Php:  
if ((($counts -100) % 200) === 0) { do the update statement

SQL:
update recorder.goal g set g.counts = 
  (select v.counts from recorder.views v where .....)
where .....

notes on the code
Your code is poor, for the following reasons:
Never use select * unless you are really going to work with all fields.
Select the field(s) you want instead.

select v.counts from recorder.views v where ......

Please stop using the outdated and insecure mysql_ libs.
Use pdo instead your code will be cleaner, more efficient and most important safe from sql-injection. See: http://php.net/manual/en/book.pdo.php

<?php
try {
  $conn = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
} catch(PDOException $e) {log the error and die}

$sql = "select v.counts from recorder.views v where player = ?";
$statement = $conn->prepare($sql);
$statement->execute(array(100));
$vcounts = $statement->fetchColumn(0);
....
$conn = null; #clean up connection not needed for long periods.

Thanks for all the suggestions, and going the extra mile to help me in the future. — Roooooo, Oct 28 '13 at 00:07

PHP and MySQL counter

1 Answers1