Basic DB connect to MySQL by Php

Posted under » PHP » MySQL on 11 April 2009

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

You can save this as a config file say "connect.php" and include it in your scripts.

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 .= "";
$isi .= $row['date'];
$isi .= "";
$isi .= $row['time'];
$isi .= "";
$isi .= $row['server'];
$isi .= "";
$isi .= $row['url'];
$isi .= "";
$isi .= $row['referer'];
$isi .= "";
$isi .= $row['ip'];
$isi .= "";
}
mysql_free_result($result);
$tabletop = "";
$tablebottom = " ";
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('connect.php');
$result = mysql_query("select * from celeng");
while ($baongs = mysql_fetch_object($result)) {
   echo "".$baongs->mid;
   echo $baongs->Moral;
}
mysql_free_result($result);
?>

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.

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 and also more mySQL functionality. However, some web hosters do not provide mysqli, so 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')";
$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);
}
?>