Basic DB connect, update and insert to MySQL.

Posted under » PHP » MySQL on 23 July 2024

First establish connection for PHP8.

/* Database config */
$db_host		= 'localhost';
$db_user		= 'username';
$db_pass		= 'pas5word';
$db_database	= 'digkam'; 
/* End config */

$dbhandle = new mysqli($db_host,$db_user,$db_pass,$db_database);

/* Check connection */
if ($dbhandle -> connect_errno) {
  echo "Failed to connect to MySQL: ".$dbhandle -> connect_error;
  exit();
}

You can save this as a config file say "connect.php" and include it in your scripts. Where $dbhandle is an instance of mysqli.

In PHP8 using MySQLi, there are many ways and one of them is to Fetch a result row as an associative array.

<¿php
include './includes/connect.php'; 

if ($result = $dbhandle -> query("SELECT * FROM celeng")) {
   while ($baongs = $result -> fetch_assoc()) {
   echo "<p>".$baongs["mid"];
   echo "<p>".$baongs["Moral"];
  }
  $result -> free_result();
}
$dbhandle -> close();

Note that when you close the dbhandle, you will not be able use the dbhandle anymore.

You can use mysqli_fetch_assoc to create JSON or to create an associative array from query.

You will notice that in mysqli_query, you have to put the $dbhandle or the mysqli instance. the beauty of this is that it is easier if you want to change to other database other than mysql later.

If you want to just get the first row data without while loop.

Delete

mysqli_query("DELETE FROM birthdays WHERE id=$id");

Please note that you don't have to do a while loop for the above. All birthdays with id=$id will be deleted.

To insert

mysqli_query($dbhandle, "INSERT INTO `question_sid` (`id`, `qid`, `uid`) VALUES (NULL, '343', '')");

It should be noted that some inserts will fail if non NULL values are not declared. You should declare all and if possible with backticks.

For multiple columns or fields, you have to use 2 different query.

mysqli_query($dbhandle, "UPDATE rare SET cakeid = 'da bastard' WHERE name = 'Chock Tong Taik'");
mysqli_query($dbhandle, "UPDATE rare SET achievement = 'I love peanuts' WHERE name = 'Chock Tong Taik'");

When simplicity is best when naming fields. No underscore of mixed characters or the sql might sometimes fail.