MySQLi

From SF Daver Wiki
Jump to navigation Jump to search

See further examples in https://www.w3schools.com/php/php_ref_mysqli.asp

Connect

mysqli_connect()

<?php
// this db connection is used in all examples on this page
$con = mysqli_connect("localhost","my_user","my_password","my_db"); 

// Check connection
if (mysqli_connect_errno())
{
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>

mysqli_close()

Close a previously opened database connection:

<?php

// ....some PHP code...

mysqli_close($con); 
?>

Query

mysqli_fetch_assoc()

<?php
// $con is the db connection defined above

$sql="SELECT Lastname,Age FROM Persons ORDER BY Lastname";
$result=mysqli_query($con,$sql);

// Associative array
$row=mysqli_fetch_assoc($result);
printf ("%s (%s)\n",$row["Lastname"],$row["Age"]);

// Free result set
mysqli_free_result($result);

mysqli_close($con);
?>

mysqli_fetch_all()

Returns an array of rows (where rows is also an array)

<?php
$sql="SELECT Lastname,Age FROM Persons ORDER BY Lastname";
$result=mysqli_query($con,$sql);

// Fetch all
$rows = mysqli_fetch_all($result,MYSQLI_ASSOC);

// Close connection
mysqli_free_result($result);
mysqli_close($con);
?>

mysqli_prepare()

<?php
// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();
?>

Info

mysqli_affected_rows()

<?php
// Perform queries and print out affected rows
mysqli_query($con,"SELECT * FROM Persons");
echo "Affected rows: " . mysqli_affected_rows($con);

mysqli_query($con,"DELETE FROM Persons WHERE Age>32");
echo "Affected rows: " . mysqli_affected_rows($con);

mysqli_close($con);
?>