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

Learn More

How to delete a record in MySQL [databases]

1633 Runs 38636 Views 3809 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



// See the table before running the DELETE query
print "Before DELETE command: \n";
print_table($db, 'mytable');


/**
 * DELETE A RECORD
 * Deletes a single record in table 'mytable' (where id = 1)
 * 
 * 
 */

// Build the query that will insert the record
$query = "DELETE FROM `mytable` 
          WHERE `id` = 1";


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


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

} else {

// Print the table
  print "\n\nAfter DELETE command: \n";
  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`)
          );";
  
  $db->query($query);
  
  // Insert 3 records into the table
  $query2 = "INSERT INTO `mytable` (`item`, `completed`) VALUES
            ('Item 1', 0),
            ('Item 2', 1),
            ('Item 3', 1);";

  // Execute the query
  $db->query($query2);
  
}


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).