Jump to content

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


SalientAnimal

Recommended Posts

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

Link to comment
Share on other sites

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');
		}
?>
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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');
		}
?>
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.