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

Learn More

Insert a record [php-mysql, databases and MySQL]

755 Runs 5842 Views 570 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



/**
 * INSERT A RECORD
 * Inserts two new records in table 'mytable'
 * 
 *   - ID: a unique identifier for each item. This will be set automatically.
 *   - ITEM: a string/varchar. This will be specified in the query.
 *   - COMPLETED: a boolean that indicates if an item has been completed. This will be set automatically.
 * 
 */

// Build the query that will insert the record
$query = "INSERT INTO `mytable` (`item`, `completed`) VALUES
            ('Item 1', 0),
            ('Item 2', 1);";


// Execute the query
$result = $db->query($query);


// Check for errors
if (!$result) {
  
  echo "Table creation failed: (" . $db->errno . ") " . $db->error;

} else {

// 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) {
  // Drop table if it exists
  $db->query("DROP TABLE IF EXISTS `$table`;");
  
  // Write query to create a fresh table
  $query = "CREATE TABLE `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 print_table($db, $table) {
  $result = $db->query("SELECT * FROM `$table`");
  
  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).