Last id from different insert function? or something like that

[cluelessPHP]

I'm trying to get the id from the last inserted id in another table and insert it into well a different table, the idea was so I could start building security

<?php 
require('classes/Database.php');
class Data{
	public function __construct()
    {
        $db = new Database;
        $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
        $this->db = $db;
    }
	
function addData($totalScore, $correct, $wrong){
	$this->db->beginTransaction();
	$insert = $this->db->prepare("
	INSERT INTO gamescore (totalScore, correct, wrong) VALUES (:totalScore, :correct, :wrong);
	");		
	$insert->execute(array(
	':totalScore' => $totalScore,
	':correct' => $correct,
	':wrong' => $wrong
	));
	$this->db->commit();
}

//it doesn't like this part 
	function addName($name){
		$this->db->beginTransaction();
		$insert = $this->db->prepare("
		INSERT INTO usernames (person, scoreId) VALUES (:name, (SELECT scoreId FROM gamescore WHERE scoreId = LAST_INSERT_ID()));
		");		
		$insert->execute(array(
		':name' => $name
		));
		$this->db->commit();
	}
}

I also tried the below which gave me a null in the database field

function addName($name){
		$this->db->beginTransaction();
		$insert = $this->db->prepare("
		INSERT INTO usernames (person, scoreId) VALUES (:name,  LAST_INSERT_ID());
		");		
		$insert->execute(array(
		':name' => $name
		));
		$this->db->commit();
	}

createScore.php

require('classes/Data.php');
session_start();
$post =  json_decode( file_get_contents("php://input"),true );

$updatedvalue = substr(str_shuffle("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ") , 0, 1) . substr(md5(time()) , 1);
$name = $post['person'];
$_SESSION["scoreKey"] = $updatedvalue;	

if($updatedvalue === true){ 
		header('Content-Type: application/json');
	echo  $success = ['response' => 'nope'];
	 json_encode($success);
	
}else{	
	if(empty($name)){
		$character = array("Shrewd Farseer", "Verity Farseer", "Dutiful Farseer", "Chade Fallstar", "Chivalry Farseer", "Regal Farseer",
		"Shrewd Farseer", "FitzChivalry Farseer", "FitzVigilant", "Shine Fallstar", "Prosper Farseer", "Integrity Farseer", 
		"August", "Merry Farseer", "Nettle", "Bee"
	);
	
$rand_keys = array_rand($character, 2);
echo $name = $character[$rand_keys[0]] . "\n";

}
$scored = new Data();
$scored->addName($name);

}

testing.php

<?php 

require('classes/Data.php');

$db = new Database;
$post =  json_decode( file_get_contents("php://input"),true );
$totalScore = $totalScore = $correct = $wrong = "";
$totalScore = $post['totalScore'];
$correct = $post['correct'];
$wrong = $post['wrong'];

$scores = new Data();
$scores->addData($totalScore, $correct, $wrong);

$success = ['response' => 'true'];   

header('Content-Type: application/json');
echo json_encode($success);

I know I can do this when everything is in the same function and it works fine, but with two functions? I'm a bit lost

function addData($totalScore, $correct, $wrong, $name){
	$this->db->beginTransaction();
	$insert = $this->db->prepare("BEGIN;
	INSERT INTO gamescore (totalScore, correct, wrong) VALUES (:totalScore, :correct, :wrong);
	INSERT INTO usernames (person, scoreId) VALUES (:name, LAST_INSERT_ID());
	COMMIT;
	");		
	$insert->execute(array(
	':totalScore' => $totalScore,
	':correct' => $correct,
	':wrong' => $wrong,
	':name' => $name
	));
	$this->db->commit();
}

[pbismad]

Unless the forum software mangled your code, none of posted code shows calling both of the class methods in the same instance of your script. The last insert id is per database session. Each instance of your script and each database connection in a single instance of your script (why is your main code making a connection and the Data class making its own connection) results in a separate database session. You have posted something called createScore.php and testing.php. Are these being required in single script? If not, they are not using the same database session and any last insert id in one, is not present in the other.

cluelessPHP //it doesn't like this part

What exactly is the "it" and what does "doesn't like" mean?

A laundry list of problems in the code -

1. Your application should make one database connection and use dependency injection to supply that connection to any class that needs it.
2. By setting the PDO error mode to warning, you have no error handling in the posted code, so you won't know when and why a database statement is failing. Why aren't you using exceptions for errors? Using exceptions for errors and in most cases letting php catch and handle the exception, will cause php to use its error related settings to control what happens with the actual error information (database errors will 'automatically' get displayed/logged the same as php errors.)
3. Transactions are for when multiple queries must all succeed or fail together. Using a transaction around one query (the current addData code) is pointless.
4. A person's game score is related to the person, not the other way around. For what it appears you are doing, you should first insert the user's information, get the last insert id from that query, and use it when inserting the related game score.

[laserlight]

There is a difference at database-level between your use of one function versus two: with one function, you wrap the two insertions in the same transaction, whereas with two function, each insertion is done in its own transaction.

Perhaps what you can do for the two function case is to rename addData to addScore, then before committing call $this->db->lastInsertId() to save the id that you then return from addScore after a successful commit. addName would then take a score_id parameter that it uses to store the foreign key to the score in the database.

[cluelessPHP]

pbismad

Ah that makes sense I try when I get home.

I didn't realise other people were using transaction that way too, bindValue for singular SQL inserts is preferred I guess. I thought it worth learning so I used it this time

[cluelessPHP]

Well I changed everything I'm still not sure why it's setting my ids as NULL
My connection

class Database extends PDO

{

private $host = 'localhost';
private $user = 'root';
private $pass = '***';
private $dbname = 'scores';

protected static $instance;

   public function __construct($host = null, $user = null, $pass = null, $opts = null)
    {
        $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
        parent::__construct("mysql:host={$this->host};dbname={$this->dbname}", $this->user, $this->pass, $opts);
        $opts = array(
            PDO::ATTR_PERSISTENT    => true,
            PDO::ATTR_ERRMODE       => PDO::ERRMODE_EXCEPTION
        );
        try{
            $this->dbh = new PDO($dsn, $this->user, $this->pass, $opts);
        }
        // Catch any errors
        catch(PDOException $e){
            $this->error = $e->getMessage();
        }
    }
}

My class

<?php 

error_reporting(-1); // reports all errors    

ini_set("display_errors", "1"); // shows all errors    

ini_set("log_errors", 1);    

ini_set("error_log", "data.log");  

require('classes/Database.php');
class Data{
	
function addName($name){
try{
	$db = new Database;	
	$insert = ("INSERT INTO usernames (person) VALUES (:name)");
	$stmt = $db->prepare($insert);
	$stmt->bindValue(':name', $name);
	$stmt->execute();
}
catch(PDOException $e ) {
	throw new pdoDbException($e);
	} 
}

function addScore($totalScore, $correct, $wrong){
try{
	$db = new Database;	
	$insert = ("INSERT INTO gamescore (totalScore, correct, wrong, ids) VALUES (:totalScore, :correct, :wrong, (SELECT ids FROM usernames WHERE ids = LAST_INSERT_ID()))");
	$stmt = $db->prepare($insert);
	$stmt->bindValue(':totalScore', $totalScore);
	$stmt->bindValue(':correct', $correct);
	$stmt->bindValue(':wrong', $wrong);
	$stmt->execute();
}
catch(PDOException $e ) {
	throw new pdoDbException($e);
	} 
}

}

Adding name

require('classes/Data.php');
session_start();
$post =  json_decode( file_get_contents("php://input"),true );

$updatedvalue = substr(str_shuffle("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ") , 0, 1) . substr(md5(time()) , 1);
$name = $post['person'];
$_SESSION["scoreKey"] = $updatedvalue;	

if($updatedvalue === true){ 
	header('Content-Type: application/json');
	$success = ['response' => 'Something'];

 json_encode($success);
}else{	
	if(empty($name)){
		$character = array("Shrewd Farseer", "Verity Farseer", "Dutiful Farseer", "Chade Fallstar", "Chivalry Farseer", "Regal Farseer",
		"Shrewd Farseer", "FitzChivalry Farseer", "FitzVigilant", "Shine Fallstar", "Prosper Farseer", "Integrity Farseer", 
		"August", "Merry Farseer", "Nettle", "Bee"
	);
	
$rand_keys = array_rand($character, 2);
echo $name = $character[$rand_keys[0]] . "\n";

}
$scored = new Data();
$scored->addName($name);

}

Adding score

require('classes/Data.php');


$post =  json_decode( file_get_contents("php://input"),true );
$totalScore = $correct = $wrong = "";
$totalScore = $post['totalScore'];
$correct = $post['correct'];
$wrong = $post['wrong'];

$scores = new Data();
$scores->addScore($totalScore, $correct, $wrong);

$success = ['response' => 'true'];   

header('Content-Type: application/json');
echo json_encode($success);

I've checked my error logs, there's nothing so I tried to create a custom on to see if it would help
I looked at my network responses and no errors are being displayed
The only error I seem to get is

Uncaught (in promise) SyntaxError: Unexpected token C in JSON at position 0
Promise.then (async)
(anonymous) @ scripts.js:148
setInterval (async)
timer @ scripts.js:134
countDown @ scripts.js:69

So this is my JS file...

document.addEventListener('click a', function(e) {
  if (e.target.nodeName === "A") {
    if (e.target.classList.contains("opensModal")) {
      e.preventDefault();
      console.log(e.target.hash);
      document.querySelector(e.target.hash).classList.add("target");
    } else if (e.target.classList.contains("closeModal")) {
      e.preventDefault();
      e.target.closest(".modalDialog").classList.remove("target");
    }
  } else {
    e.preventDefault();
    var target = document.getElementsByTagName('a');
    var scrolling = e.target.getAttribute('href');
    scrolling.scrollIntoView({
      behavior: 'smooth',
      block: 'start'
    });
  }
});

document.addEventListener("DOMContentLoaded", function() {
	

const leaderBoard = document.querySelector('.scored'),
gamer = document.querySelector('.gamer'),
gameDisplayed = document.querySelector('.game-display'),
scoreDisplayed = document.querySelector('.score-display'),
saveName = document.querySelector('.player-save'),
plyBtn = document.querySelector('.play-btn');

let	name = document.querySelector('[name="person"]');
let modalBtn = document.getElementById("modal-btn")
let modal = document.querySelector(".modal")
let closeBtn = document.querySelector(".close-btn")
modalBtn.onclick = function(){
  modal.style.display = "block"
}
saveName.onclick = function(){
modal.style.display = "none";
}
window.onclick = function(e){
  if(e.target == modal){
	modal.style.display = "none";
  }
}
  
fetch('getscores.php').then(r => r.json()).then(data=>{
	scoreDisplayed.innerHTML = data.map(record=>`${record.person} : ${record.totalScore} : ${record.ids}`).join("<br>");
}).catch(console.error);

leaderBoard.onclick = function() {
	scoreDisplayed.style.display = "block"; 		
	gameDisplayed.style.display = "none"; 		
};

gamer.onclick = function() {
	scoreDisplayed.style.display = "none"; 		
	gameDisplayed.style.display = "block"; 	
};

document.addEventListener("keydown",e=> document.querySelector(e.key==="y" ? "#yes" : "#no").click());
let	score = 0,
wrong = 0,
totalScore = 0,
pen = 10;
document.querySelector(".startBtn").onclick = countDown
function countDown(){
	timer();
	this.closest("section").classList.remove("show");
	document.querySelectorAll(".anime").forEach((section, i) => {
	setTimeout(() => {
		section.classList.add("show")
		if (i < 4)
		setTimeout(() => {
		randomColour();
		  section.classList.remove("show");
		  plyBtn.style.display = "none";
		}, 250)
	}, 1000 * i)
  })


}	
function randomColour() {
	const box = document.createElement("div"),
	oldBox = document.querySelector(".box:last-of-type"),
	colours = document.querySelector(".inner-colours");
	let colourName = ["Red", "Orange", "Yellow", "Green", "Blue", "Indigo", "Black"],
	textColour = ["Red", "Orange", "Yellow", "Green", "Blue", "Indigo", "Black"],
	name = colourName,
	randomItem = name[Math.floor(Math.random() * name.length)],
	randomColours = box.style.color = textColour[Math.floor(Math.random() * textColour.length)];	
	box.classList.add("box");
	box.textContent = randomItem;
	colours.append(box);
	setTimeout(() => {
    box.classList.add('arrive');
    if (oldBox) {
      oldBox.classList.remove('arrive');
      oldBox.ontransitionend = e => oldBox.remove();
      oldBox.classList.add('depart');
    }
	document.querySelector(".answers").onclick = e => {
	if ((randomItem === randomColours) && (e.target.textContent === "YES") || (randomItem !== randomColours) && (e.target.textContent === "NO")) {
	randomColour();
    score++;
	} else if((randomItem === randomColours) && (e.target.textContent === "NO") || (randomItem !== randomColours) && (e.target.textContent === "YES")) {
	randomColour();
    wrong++;
	} 
	totalWrong = wrong - pen;
	totalScore = score - totalWrong;
	console.log({score, totalWrong, totalScore});
}
	
  }, 100)
	
}

function timer(){
	let timeleft = 13,
	ticker = document.querySelector(".countdown"),
	gameContain = document.querySelector(".game-contain"),
	gameController = document.querySelector(".game-control"),
	right = document.createElement("div"),
	incorrect = document.createElement("div"),
	startButton = document.createElement("BUTTON"),
	hide = document.createElement("div");
	right.className = "correct";
	incorrect.className = "wrong";
	startButton.className = "startBtn";
	startButton.textContent = "Play again";
	downloadTimer = setInterval(function(){
	ticker.textContent = timeleft;
    timeleft -= 1;
	if(timeleft <= 0){
	clearInterval(downloadTimer);
	scores = gameContain.insertAdjacentElement('beforeEnd', right);
	wronged = gameContain.insertAdjacentElement('beforeEnd', incorrect);
	scores.textContent = name.value + " you scored: " + totalScore;
	wronged.textContent = "Wrong answers: " + wrong;
	gameContain.insertAdjacentElement('beforeEnd', startButton);
	ticker.style.display = "none"; 
	gameController.style.display = "none"; 
	plyBtn.style.display = "block";

PostJSONReturnJSON("createScore.php",{person: name.value}).then(console.log);
PostJSONReturnJSON("testing.php",{totalScore: totalScore, correct: score, wrong: wrong}).then(data=>{
// do something good
score = 0,
wrong = 0,
totalScore = 0;

fetch('getscores.php').then(r => r.json()).then(data=>{
	scoreDisplayed.innerHTML = data.map(record=>`${record.person} : ${record.totalScore} : ${record.ids}`).join("<br>");
}).catch(console.error);

},error=>{
// report something bad

})

function putout(data){
    output.innerHTML=data.join("<br>")
}

function  PostJSONReturnJSON(url, data) {
	return fetch(url, {
    method: 'POST',
    body: JSON.stringify(data),
    headers: {
    'Content-Type': 'application/json'
    }
    }).then(r => r.json())
    }

startButton.addEventListener("click", function(e) {
ticker.style.display = "block"; 	
gameController.style.display = "block"; 	
right.remove();
scores.remove();
wronged.remove();
startButton.remove();
countDown.call(ticker);
});
  }
}, 1000);
 }
});
	

[maxxd]

cluelessPHP SyntaxError: Unexpected token C in JSON at position 0

What does your JSON look like in the network tab of your developer tools?

Couple other random things - I didn't have time to go through all the code you've posted, but here are some initial thoughts.

Database.php:

<?php
/**
 * what benefit do you get from using this class over a simple PDO class? you could always
 * store your credentials in an .env file and pass those into the PDO constructor from your
 * controllers.
 */
class Database extends PDO
{

  private $host = 'localhost';
  private $user = 'root';
  private $pass = '***';
  private $dbname = 'scores';

  protected static $instance; // is this supposed to be $dbh? you never actually use self::$instance
/**
 * @param [type] $host - you don't use this, so why are you passing it in?
 * @param [type] $user - see comment above
 * @param [type] $pass - see comment above
 * @param [type] $opts - given the usage, I'd argue you can skip this parameter, too.
 */
  public function __construct($host = null, $user = null, $pass = null, $opts = null)
  {
    $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;

    //here you're creating a PDO object but not assigning it to anything.
    parent::__construct("mysql:host={$this->host};dbname={$this->dbname}", $this->user, $this->pass, $opts);

    //now you overwrite $opts with hard-coded options...
    $opts = array(
      PDO::ATTR_PERSISTENT    => true,
      PDO::ATTR_ERRMODE       => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_EMULATE_PREPARES => false, // don't forget to turn on true prepared statements
    );
    try {
    //... in order to create another PDO object, which you do nothing with
      $this->dbh = new PDO($dsn, $this->user, $this->pass, $opts);
    }
    // Catch any errors
    // don't catch the errors. you use try ... catch blocks in your other code, but you're short-circuiting
    //  the throw here, so if this part (db connection creation) fails, in your other code you'll never know.
    catch (PDOException $e) {
      $this->error = $e->getMessage();
    }
  }
}

Data.php:

<?php

error_reporting(-1); // reports all errors    
ini_set("display_errors", "1"); // shows all errors    
ini_set("log_errors", 1);
ini_set("error_log", "data.log");

require('classes/Database.php');
class Data
{

  function addName($name)
  {
    try {
      $db = new Database;
      $insert = ("INSERT INTO usernames (person) VALUES (:name)");
      $stmt = $db->prepare($insert);
      $stmt->bindValue(':name', $name);
      $stmt->execute();
    /**
     * I prefer using the shorter $stmt->execute(['name'=>$name]); syntax, but that's
     * just personal taste. what you have here is perfectly legit.
     */
    } catch (PDOException $e) {
    // here's where the exception catching in Database::__construct() could become a problem - if the connection
    // fails, the exception is already caught and never gets to this section of code.
      throw new pdoDbException($e);
    }
  }
// omitted the other function for brevity
}

[pbismad]

cluelessPHP Adding name

cluelessPHP Adding score

As already stated, if you are not running those in the same, single, instance of your script using the same, single, database connection, they are not using the same database session and the last insert id from the first query is not present when the second query is executed.

What is your 'main' code that's causing the add name and add score code to be executed?

[cluelessPHP]

maxxd
This?

Request URL: http://localhost/portfolio/colours/testing.php
Request Method: POST
Status Code: 200 OK
Remote Address: [::1]:80
Referrer Policy: no-referrer-when-downgrade
Connection: Keep-Alive
Content-Length: 19
Content-Type: application/json
Date: Mon, 02 Dec 2019 08:26:47 GMT
Keep-Alive: timeout=5, max=100
Server: Apache/2.4.33 (Win64) PHP/7.2.4
X-Powered-By: PHP/7.2.4
Accept: */*
Accept-Encoding: gzip, deflate, br
Accept-Language: en-GB,en-US;q=0.9,en;q=0.8
Connection: keep-alive
Content-Length: 39
Content-Type: application/json
Cookie: mysite_hit_counter=b314eek7bho14dm0dpb3rg2lqa; PHPSESSID=0se06mf2lgoqdsrbburqsp5lqa
Host: localhost
Origin: http://localhost
Referer: http://localhost/portfolio/colours/index.php
Sec-Fetch-Mode: cors
Sec-Fetch-Site: same-origin
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36
{totalScore: 11, correct: 1, wrong: 0}
correct: 1
totalScore: 11
wrong: 0

Request URL: http://localhost/portfolio/colours/createScore.php
Request Method: POST
Status Code: 200 OK
Remote Address: [::1]:80
Referrer Policy: no-referrer-when-downgrade
Cache-Control: no-store, no-cache, must-revalidate
Connection: Keep-Alive
Content-Length: 0
Content-Type: text/html; charset=UTF-8
Date: Mon, 02 Dec 2019 08:26:47 GMT
Expires: Thu, 19 Nov 1981 08:52:00 GMT
Keep-Alive: timeout=5, max=100
Pragma: no-cache
Server: Apache/2.4.33 (Win64) PHP/7.2.4
X-Powered-By: PHP/7.2.4
Accept: */*
Accept-Encoding: gzip, deflate, br
Accept-Language: en-GB,en-US;q=0.9,en;q=0.8
Connection: keep-alive
Content-Length: 17
Content-Type: application/json
Cookie: mysite_hit_counter=b314eek7bho14dm0dpb3rg2lqa; PHPSESSID=0se06mf2lgoqdsrbburqsp5lqa
Host: localhost
Origin: http://localhost
Referer: http://localhost/portfolio/colours/index.php
Sec-Fetch-Mode: cors
Sec-Fetch-Site: same-origin
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36
{person: "Test"}
person: "Test"

/**

• what benefit do you get from using this class over a simple PDO class? you could always
• store your credentials in an .env file and pass those into the PDO constructor from your
• controllers.
  */

Just some tutorial I followed, not all tutorials are created equal I guess

I didn't know .env was a thing, I'll look into it more

This works

function addData($totalScore, $correct, $wrong, $name){
	$this->db->beginTransaction();
	$insert = $this->db->prepare("BEGIN;
	INSERT INTO gamescore (totalScore, correct, wrong) VALUES (:totalScore, :correct, :wrong);
	INSERT INTO usernames (person, scoreId) VALUES (:name, LAST_INSERT_ID());
	COMMIT;
	");		
	$insert->execute(array(
	':totalScore' => $totalScore,
	':correct' => $correct,
	':wrong' => $wrong,
	':name' => $name
	));
	$this->db->commit();
}

I guess as pointed out I'm trying to do something that's just digging my own grave, I was trying to learn how to stop people injecting a score into the game, I thought that might be the way to do it. I'll try again with everything in one function there's no point trying to reinvent the wheel