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.

<¿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();

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.

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 will fail.

Update

mysql_query("UPDATE tanble SET street  = '1 Empress Place', building = 'Asian Civilisations Museum' WHERE id = '6'");

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.

Also the mysqli way. This is for newer php version but for some web hosters that do not provide mysqli, back to mysql_connect you go.

<¿php 
$mysqli = mysqli_connect("localhost", "joeuser", "somepass", "testDB");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
} else {
$sql = "INSERT INTO testTable (testField) VALUES ('some value')";
// $result = mysqli_query($dbhandle,"select * from ImageTags;");  
$res = mysqli_query($mysqli, $sql);
if ($res === TRUE) {
echo "A record has been inserted.";
} else {
printf("Could not insert record: %s\n", mysqli_error($mysqli));
}
mysqli_close($mysqli);
}
?>

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());

If PHP7, then use this.

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

See also sample SQL statements

web security linux ubuntu GIT svn Raspberry apache mysql php drupal cake symfony javascript Ajax css