• PHP Help
  • Last id from different insert function? or something like that

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();
}

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.

    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.

    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

      11 days later

      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);
       }
      });
      	
      

      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
      }

      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?

      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

        Write a Reply...