Page visit check... SQLSTATE[HY093] Error message

[SalientAnimal]

Hi Guys,

 

I've been working on a site for quite a while now, and still being a "learner" in the area of development I have run into a number of challenges that I am not sure how to resolve, or exactly what to look for. The most recent as a script that is executed by JavaScript each time a user visits a page. This way I am able to track how frequently a page is visited and by whom.

 

I am however getting the following error when testing the script:

 

 

Array
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined' in C:\xampp\htdocs\includes\page_hit.php:41 Stack trace: #0 C:\xampp\htdocs\includes\page_hit.php(41): PDOStatement->execute(Array) #1 {main} thrown in C:\xampp\htdocs\includes\page_hit.php on line 41

 

Here is my code for the "page_hit.php" file:

<?php 
error_reporting(E_ALL & ~E_NOTICE);
require_once($_SERVER['DOCUMENT_ROOT'] . '/includes/dbconfig.php');
$timestamp = date("Y-m-d H:i:s");



#RETURN A LIST OF WHICH USERS ARE INDICATED IN THE PAGE_HIT TABLE
			$stmtvisit = $db->prepare('SELECT * FROM sys_page_hit WHERE memberID = $_SESSION[memberID]'); 
			$stmtvisit->execute;	
			$results = $stmtvisit->fetchAll(PDO::FETCH_ASSOC);			
			echo $results;


if($results=="0")
		{


#INSERT THE USER INTO THE PAGE_HIT TABLE IF THEY DO NOT EXIST
			$stmtinsrt = $db->prepare('INSERT INTO sys_page_hit (memberID, username, last_active) VALUES (:memberID, :username, :last_active)');
			$stmtinsrt->execute(array(
				':memberID' => $_SESSION['memberID'],
				':username' => $_SESSION['username'],
				':last_active' => $timestamp				
			));
			$id = $db->lastInsertId('memberID');
		}
		else 
		{
#UPDATE THE USER'S CURRENT VISIT IN THE TABLE IF THEY DO EXIST
			$stmtupd = $db->prepare('UPDATE sys_page_hit SET last_active = :lastactive WHERE memberID = :memberID');
			$stmtupd->execute(array(
				':memberID' => $_SESSION['memberID'],
				':last_active' => $timestamp				
			));
			$id = $db->lastInsertId('memberID');
		}	
	
	
	


?>

Thanks in advance

[SalientAnimal]

I have actually managed to resolve this by simplifying my query quite a bit. Here is the solution that worked for me, although I do not know if it is the best solution. Please let me know your thoughts, and advise if you would do anything differently.

<?php 
error_reporting(E_ALL & ~E_NOTICE);
require_once($_SERVER['DOCUMENT_ROOT'] . '/includes/dbconfig.php');
$timestamp = date("Y-m-d H:i:s");
		{


#INSERT THE USER INTO THE PAGE_HIT TABLE IF THEY DO NOT EXIST ELSE UPDATE THE DUPLICATE KEY (memberID)
			$stmtinsrt = $db->prepare('INSERT INTO sys_page_hit (memberID, username, last_active) VALUES (:memberID, :username, :last_active) ON DUPLICATE KEY UPDATE last_active= :last_active');
			$stmtinsrt->execute(array(
				':memberID' => $_SESSION['memberID'],
				':username' => $_SESSION['username'],
				':last_active' => $timestamp				
			));
			$id = $db->lastInsertId('memberID');
		}
?>

[requinix]

Is

#RETURN A LIST OF WHICH USERS ARE INDICATED IN THE PAGE_HIT TABLE
$stmtvisit = $db->prepare('SELECT * FROM sys_page_hit WHERE memberID = $_SESSION[memberID]'); 
$stmtvisit->execute;	
$results = $stmtvisit->fetchAll(PDO::FETCH_ASSOC);			
echo $results;

that code still around? Because there are a couple big problems with it.

[SalientAnimal]

Is

#RETURN A LIST OF WHICH USERS ARE INDICATED IN THE PAGE_HIT TABLE
$stmtvisit = $db->prepare('SELECT * FROM sys_page_hit WHERE memberID = $_SESSION[memberID]'); 
$stmtvisit->execute;	
$results = $stmtvisit->fetchAll(PDO::FETCH_ASSOC);			
echo $results;

that code still around? Because there are a couple big problems with it.

 

 

I have removed it so the script only looks at the newly posted part. That said, I wouldn't mind learning and understanding what is wrong with the part of the code you are commenting on... With what I have done, I have seen the possibility of also using the same code to indicate my online users. Also, I have updated the code to include the page id, which I create each time using a variable, but this is not throwing out a new error:

 

 

 

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'page_id' cannot be null' in C:\xampp\htdocs\includes\online_status.php:15 Stack trace: #0 C:\xampp\htdocs\includes\online_status.php(15): PDOStatement->execute(Array) #1 {main} thrown in C:\xampp\htdocs\includes\online_status.php on line 15

 

The updated code with a page_id

 

 

<?php 
error_reporting(E_ALL & ~E_NOTICE);
require_once($_SERVER['DOCUMENT_ROOT'] . '/includes/dbconfig.php');
$timestamp = date("Y-m-d H:i:s");
		{


#INSERT THE USER INTO THE PAGE_HIT TABLE IF THEY DO NOT EXIST ELSE UPDATE THE DUPLICATE KEY (memberID)
			$stmtinsrt = $db->prepare('INSERT INTO sys_online_users (memberID, username, last_active, page_id) VALUES (:memberID, :username, :last_active, :page_id) ON DUPLICATE KEY UPDATE last_active= :last_active AND page_id = :page_id');
			$stmtinsrt->execute(array(
				':memberID' => $_SESSION['memberID'],
				':username' => $_SESSION['username'],
				':last_active' => $timestamp,
				':page_id' => $_POST['page_id'],
			));
			$id = $db->lastInsertId('memberID');
		}
?>