Speed up development with full-stack environments for every branch.

Learn More

Return last insert ID in mySQL using PHP

2101 Runs 32911 Views 3482 Copies
Saved

Saved

sundip 66

sundip
published 4 years ago

    <pre><?php
    
    $db = connect_to_db(); // Establish a database connection
    setup_table($db, 'mytable'); // Cleanup existing table
    
    
    // Adds a record to the table 
    add_record($db, 'mytable');
    
    
    /**
     * RETURN LAST INSERT ID
     * Returns the last record's ID that was inserted into table 'mytable'
     * 
     * 
     */
      $id = $db->insert_id;
      printf ("New Record has id:  %d.\n", $id);
    
    
    
    // Print the table
      print_table($db, 'mytable');
    
    
    // Close the connection when finished
    $result->close();
    
    
    
    
    
    
    
    
    
    
    /**  HELPER FUNCTIONS BELOW  **/
    
    
    /**
     * Connect to a mySQL Database
     * The values used here are specific to this sandbox environment;
     * Replace these values with your mySQL server values.
     * 
     */
    function connect_to_db() {
      $mysqli = new mysqli(
       "localhost",     // hostname
       "root", // username
       "", // password
       "mydb"           // database
       );
    
    
      // If the connection attempt returned an error, print it
      if ($mysqli->connect_errno) {
        echo "Failed to connect to MySQL: ( $mysqli->connect_errno ) $mysqli->connect_error";
        return;
      }
    
      return $mysqli;
    }
    
    
    function setup_table($db, $table) {
    
      // Write query to create a table if needed
      $query = "CREATE TABLE IF NOT EXISTS `mytable` (
                `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                `item` VARCHAR(100) NOT NULL,
                `completed` BOOLEAN NOT NULL DEFAULT 0,
                PRIMARY KEY (`id`)
              );";
    
      // Execute the query
      $db->query($query);
        
    }
    
    function add_record($db, $table) {
      $query = "INSERT INTO `mytable` (`item`, `completed`) VALUES
                ('Another Item', 1);";
                
      // Execute the query
      $result = $db->query($query);
    
      // Check for errors
      if (!$result) {  
        echo "Insert item failed: (" . $db->errno . ") " . $db->error;
        return;
      }
      
    }
    
    
    function print_table($db, $table) {
      $result = $db->query("SELECT * FROM `$table` ORDER BY `id` DESC");
      
      if (!$result) {
        echo "Couldn't get data from `$table` table: ( $db->connect_errno ) $db->connect_error";
        
      } else {
        $num_rows = $result->num_rows;
        print "$num_rows rows in table `$table` \n";
    
        /* Get field information for all columns */
        print "\n--------------------------\n";
        while ($row = $result->fetch_row()) {
            printf ("        id: %d \n", $row[0]);
            printf ("      item: '%s' \n", $row[1]);
            printf (" completed: %s \n", ($row[2]) ? 'TRUE' : 'FALSE');
            print "\n--------------------------\n";
        }
        
        $result->close();
        
      }
    }
    
    ?></pre>
    Please login/signup to get access to the terminal.

    Your session has timed out.

    Dismiss (the page may not function properly).