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

Learn More

Update a mySQL record with PHP

5164 Runs 86255 Views 9767 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_three_records($db, 'mytable');


/**
 * UPDATE A RECORD
 * Updates a record with the `id` > 1 in table `mytable`
 * 
 * 
 */
 
// Build the query used to update the record
$query = "UPDATE `mytable` 
          SET `item` = 'UPDATED ITEM'
          WHERE `id` > 1;";

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


// Check for errors
if (!$result) {
  
  echo "Update record 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(
   '127.0.0.1',     // 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) {
  // Remove old table if exists
  $db->query("DROP TABLE IF EXISTS `$table`;");

  // Write query to create a table if needed
  $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 add_three_records($db, $table) {
  $query = "INSERT INTO `mytable` (`item`, `completed`) VALUES
            ('Item1', 0),
            ('Item2', 1),
            ('Item3', 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`");
  
  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).