PHP

Getting Started String Date If Else Loop Array Functions Session Files Super Global Variables Debugging MySQL MySQL Read Data JSON Markdown
O O

Weather Controls

Time Of Day
Rain
Wind Speed
Wind Direction
Clouds

PHP : MySQL

2018-03-03

Connect To The MySQL Database

define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'RobPowers');
define('DB_PASSWORD', 'robsPassword');
define('DB_DATABASE', 'robsDatabase');

$db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

if (!$db) {
    die("Connection failed: " . mysqli_connect_error());
} else {
    echo "Connected successfully";
}
mysqli_close($db); 

Get Records

$sql = "SELECT * FROM robsTable3  WHERE type = 3 ";
$rsData = mysqli_query($db, $sql); // Execute the SQL
if (!$rsData) {die("Read Problem - " . mysqli_error($db));}
//...code
mysqli_free_result($rsData);

Create Records

$sql = "INSERT INTO rp_robsTable1 (name, dateAdded) VALUES ( '{$name}', CURDATE())";
$rsData = mysqli_query($db, $sql); 
$newID = mysqli_insert_id($db); // ID of newly created record

Update Records

$sql = "UPDATE robsTable1 SET name='{$name}', dateModified=CURDATE() WHERE categoryID={$categoryID}";
$rsData = mysqli_query($db, $sql);

Delete Records

$sql = "DELETE FROM robsGoodTimesTable  WHERE goodTimeID = {$goodTimeID} ";
$rsData = mysqli_query($db, $sql);

Prepared Statement

Prevent SQL injection attacks. May cause better or worse performance, depending on uses.

$stmt = $db->prepare("INSERT INTO rp_table1 (name, description, category, dateModified, dateAdded) VALUES (?, ?, ?, CURDATE(), CURDATE())");
$stmt->bind_param("ssi", $name, $description, $category); //ssi = string string int
$stmt->execute();
$stmt->close();
$newID mysqli_insert_id($db); 

//update
$stmt = $db->prepare("UPDATE robsAwesomeTable SET name = ?, dateModified = CURDATE() WHERE pageID = ?");
$stmt->bind_param("si", $name, $pageID);

Put Records in Array

$arr1 = array();
$sql = "SELECT * FROM  myTable ORDER BY sort";
$rsTable = mysqli_query($db, $sql);
while ($row = mysqli_fetch_assoc($rsTable))  {
    $arr1 [] = $row;
}
mysqli_free_result($rsTable);

//loop through the records
foreach($rsTable as $row) {
    echo $row["name"];
}