Page count function

Question

Every time I get a visit to my index page I need to check if a record exists already in my database, if it does, it will need to update the field 'count' if not it will add a row.

I've managed to get it to create a row on visit, but cant seem to get it to update the count if the row already exists.

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Check to see if record exists
$sql = "SELECT * FROM page_tracking where name ="index.php"";
if (mysqli_query ($conn, $sql)) {
 $sql= "UPDATE page_tracking SET count=count+1 where name= "index.php")";
}

else if {
//Insert Query
$sql = "INSERT INTO page_tracking (name, count)
VALUES ('index.php', '1')"; 
}
if (mysqli_query($conn, $sql)) {
    echo "Thanks for visiting! Your visit has been recorded";
} else {
    echo "Unfortunately we were unable to record your visit: " . $sql . "<br>" . mysqli_error($conn);
}

There's probably a really simple explanation! Issue is definitely with the code for 'check to see if record exists'

you can remove Select query and directly execute update query if it return false then you can insert vale . — Md Hasibur Rahaman, Nov 08 '16 at 11:27
Do you need the output? [How to INSERT a record or UPDATE if it already exists?](https://stackoverflow.com/questions/1952922/how-to-insert-a-record-or-update-if-it-already-exists) — Andreas, Nov 08 '16 at 11:29

sidyll · Answer 1 · 2016-11-08T11:39:42.957

In your code, you never executed the query. Only created it in a string.

if (mysqli_query ($conn, $sql)) {
    $sql= "UPDATE page_tracking SET count=count+1 where name= "index.php")";
    # execute the query
}

Also, not sure why this loose else if without a condition:

else if {
    //Insert Query

An you may want to checkout the INSERT ... ON DUPLICATE KEY UPDATE statement, which can reduce your whole code to a single query if the file name is your unique key:

mysqli_query ($conn, "
    INSERT INTO page_tracking (name, count)
         VALUES ('index.php', 1)
    ON DUPLICATE KEY UPDATE count=count+1
");

Passionate Coder · Accepted Answer · 2016-11-08T11:34:48.203

1) Dont use double quotes around index.php use single quotes. Just run update query

    $sql = "SELECT * FROM page_tracking where name ="index.php"";
    $result =  mysqli_query ($conn, $sql);
    $num_rows = mysqli_num_rows($result);
    if ($num_rows) {
        $sql= "UPDATE page_tracking SET count=count+1 where name= 'index.php'";

    }else{
    $sql = "INSERT INTO page_tracking (name, count) VALUES ('index.php', '1')"; 
}

if (mysqli_query($conn, $sql)) {
    echo "Thanks for visiting! Your visit has been recorded";
} else {
    echo "Unfortunately we were unable to record your visit: " . $sql . "<br>" . mysqli_error($conn);
}

score 0 · Answer 3 · answered Nov 08 '16 at 11:31

Get the results and check whether it has records.

  // Check to see if record exists
    $sql = "SELECT * FROM page_tracking where name ="index.php"";
    $query = mysqli_query ($conn, $sql);

    $result=mysqli_fetch_all($query,MYSQLI_ASSOC);
    if (count($result) > 0) {
        $sql= "UPDATE page_tracking SET count=count+1 where name="index.php")";
    }

    else {
    //Insert Query
    $sql = "INSERT INTO page_tracking (name, count)
    VALUES ('index.php', '1')"; 
    }

Ibrar Jadoon · Answer 4 · 2016-11-22T10:10:23.527

0

Please try this. Use single quote instead of double quote.

$link = mysql_connect("localhost", $mysql_user, $mysql_password);
mysql_select_db($database, $link);

$result = mysql_query("SELECT * FROM page_tracking where name ='index.php'", $link);
$num_rows = mysql_num_rows($result);

if($num_rows==1){
  //Update Query
}else{
  //Insert Query
}

edited Nov 22 '16 at 10:10

answered Nov 08 '16 at 11:36

Ibrar Jadoon

1
3

Page count function

4 Answers4