SalientAnimal Posted December 11, 2015 Share Posted December 11, 2015 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: ArrayFatal 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 Quote Link to comment Share on other sites More sharing options...
SalientAnimal Posted December 11, 2015 Author Share Posted December 11, 2015 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'); } ?> Quote Link to comment Share on other sites More sharing options...
requinix Posted December 11, 2015 Share Posted December 11, 2015 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. Quote Link to comment Share on other sites More sharing options...
SalientAnimal Posted December 11, 2015 Author Share Posted December 11, 2015 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'); } ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.