Basic DB connect, update and insert to MySQL. Updated for PHP7

Posted under » PHP » MySQL on Updated 11 August 2020

First establish connection.

<¿php
//connect to your database 
mysql_connect("localhost","username","password"); //(host, username, password)

//specify database 
mysql_select_db("database") or die("Unable to select database"); //select which database we're using
 ?>

For the newer PHP7. Use this (MySqli) instead because the old connection (MySql) has depreciated (since PHP ver 5.5).

/* 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.

For PHP 7, preferred way is not to use (brackets) when "including" or "requiring" file.

require_once __DIR__ .'/includes/connect.php';

using mysql_fetch_array. Procedural way.

<¿php
include('connect.php');
$result = mysql_query("select * from tracker WHERE server = '$q'") or die(mysql_error());
while ($row = mysql_fetch_array($result))
{
$isi .= "<tr><td>";
$isi .= $row['date'];
$isi .= "</td><td>";
$isi .= $row['ip'];
$isi .= "</td><tr>";
}
mysql_free_result($result);
$tabletop = "<table>";
$tablebottom = "</table>";
echo $tabletop.$isi.$tablebottom;
 ?>

Within a date range and sorted by date.

<¿php
$result = mysql_query("select * from memused where hari > '2010-02-10 15:40:01' AND hari < '2010-02-12 15:40:01' ORDER BY date ASC, time ASC");
 ?>

Also using mysql_fetch_object. Object oriented way.

<¿php
include './includes/connect.php';
$result = mysql_query("select * from celeng");
while ($baongs = mysql_fetch_object($result))  {
   echo "<p>".$baongs->mid;
   echo $baongs->Moral;
}
mysql_free_result($result);
?>

If PHP7 using MySQLi, there are many ways and one of them is to Fetch a result row as an associative array. Notice there is no mention of MySQLi.

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

Insert records

mysql_query("INSERT INTO tracker (date, time, server, url, referer, ip) VALUES('$tahun','$masa','$server','$url','$refer','$ip') ") or die(mysql_error());

'or die(mysql_error()); ' helps in diagnosing errors.

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

* you cannot insert a row if there are fields that don't have a default value.. eg. NULL

Delete

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

If you out of nowhere put this

mysql_query("INSERT INTO tracker (date) VALUES('$tahun') ") or die(mysql_error());

You might get this error. Especially so in PHP7.

mysqli_query() expects at least 2 parameters, 1 given.

You should put this instead.

mysql_query($dbhandle,"INSERT INTO tracker (date) VALUES('$tahun') ") or die(mysql_error());

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.

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

If PHP7, then use this to update. 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'");