Scribd
Upload
14 views9 pages

MySQL Connection and CRUD Operations

Uploaded by

kibohe2968
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
14 views9 pages

MySQL Connection and CRUD Operations

Uploaded by

kibohe2968
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

MySQL_EX_1_Creating_Connection

<?php
// when you install XAMPP on your computer, the password for the root
user is left empty.

$servername = "localhost";
$username = "root";
$password = "";

// mysqli_connect() function opens a new connection to the MySQL server.

// //The die() function is an alias of the exit() [Link] a


message and terminate the current script:

// mysqli_connect_error() function returns the error description from the


last connection error, if any.

//Creating a connection here with MySQL.


$conn = mysqli_connect($servername, $username, $password);

// I am Checking connection here.


if (!$conn) {
die("Connection failed: " . mysqli_connect_error());

}
echo "Connected successfully";
?>
MySQL_EX_2_Creating_A_Database

<?php
$servername = "localhost";
$username = "root";
$password = "";

//The mysqli_query() function accepts a string value representing a query


as one of the parameters and, executes/performs the given query on the
database.
// connection Required. Specifies the MySQL connection to use
// query Required. Specifies the SQL query string
// // mysqli_close() function closes a previously opened database
connection.

// I am Creating a connection here with MySQL.


$conn = mysqli_connect($servername, $username, $password);

// I am Checking connection here.


if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// SQL qurey to Creating a database in MySQL.


$sql = "CREATE DATABASE School";

if (mysqli_query($conn, $sql)) {
echo "Database created successfully";
} else {
echo "Error! creating database: " . mysqli_error($conn);
}

mysqli_close($conn);
?>
MySQL_EX_3_Creating_A_Table
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "School";

//Auto-increment allows a unique number to be generated automatically


when a new record is inserted into a table.

// I am Creating a connection here with MySQL.


$conn = mysqli_connect($servername, $username, $password, $dbname);

// I am Checking connection here.


if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// SQL query to creating a table in (School) database.


$sql = "CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
stname VARCHAR(30) NOT NULL,
email VARCHAR(40),
mobile VARCHAR(10) NOT NULL
)";

if (mysqli_query($conn, $sql)) {
echo "Table students created successfully";
} else {
echo "Error! creating table: " . mysqli_error($conn);
}

mysqli_close($conn);
?>
MySQL_EX_4_Inserting_Data_Into_Table
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "School";

// I am Creating a connection here with MySQL.


$conn = mysqli_connect($servername, $username, $password, $dbname);

// I am Checking connection here.


if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// SQL query to inserting data in students table.

$sql = "INSERT INTO students (stname, email, mobile)


VALUES ('rohan', 'rohan@[Link]', '3322334456')";

if (mysqli_query($conn, $sql)) {
echo "New record inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>
MySQL_EX_5_Selecting_Data

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "School";

//mysqli_fetch_assoc() function fetches a result row as an associative


array.
//Returns an associative array of strings representing the fetched row.
NULL if there are no more rows in result-set
// I am Creating a connection here with MySQL.
$conn = mysqli_connect($servername, $username, $password, $dbname);

// I am Checking connection here.


if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// SQL query to selecting all record from table


$sql = "SELECT id, stname, email, mobile FROM students";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) { // Returns the number of rows


in the result set
while($row = mysqli_fetch_assoc($result)) {
echo "<b>Id:</b> " . $row["id"]. ", <b>Name:</b> " . $row["stname"].
", <b>Email:</b> " . $row["email"]. ", <b>Mobile:</b> " .
$row["mobile"]. "<br>";
}
} else {
echo "no record found";
}

mysqli_close($conn);
?>
MySQL_EX_6_Where

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "School";

// I am Creating a connection here with MySQL.


$conn = mysqli_connect($servername, $username, $password, $dbname);

// I am Checking connection here.


if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

//SQL query to showing required record.

$sql = "SELECT id, stname FROM students WHERE mobile='1122334456'";

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "<b>Id:</b> " . $row["id"]. ", <b>Name:</b> " .
$row["stname"]. "<br>";
}
} else {
echo "no record found";
}

mysqli_close($conn);
?>
MySQL_EX_7_Updating

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "School";

// I am Creating a connection here with MySQL.


$conn = mysqli_connect($servername, $username, $password, $dbname);

// I am Checking connection here.


if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// SQL query to updating record.

$sql = "UPDATE students SET stname='Sunil' WHERE mobile='1122334456'";

if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error! updating record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>
MySQL_EX_8_Deleting_Data

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "School";

// I am Creating a connection here with MySQL.


$conn = mysqli_connect($servername, $username, $password, $dbname);

// I am Checking connection here.


if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// SQL query to deleting record.

$sql = "DELETE FROM students WHERE id=1";

if (mysqli_query($conn, $sql)) {
echo "Record deleted successfully";
} else {
echo "Error! deleting record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Common questions

Powered by AI

The mysqli_fetch_assoc() function retrieves each row of a result set as an associative array, allowing easy access to the row's data through column names as keys. In the document, this function is used to fetch rows one at a time from the result of a SELECT query on the 'students' table. The fetched data is then echoed, displaying the 'id', 'stname', 'email', and 'mobile' fields for each record .

Conditional data selection can be implemented using the WHERE clause in a MySQL SELECT statement. This clause specifies the conditions that data must meet to be included in the results. In the document's example, a SELECT query is executed to retrieve 'id' and 'stname' from the 'students' table where a specific 'mobile' value ('1122334456') exists. This process allows filtering of data based on particular criteria .

The mysqli_num_rows() function returns the number of rows present in the result set of a SQL query, which is useful for determining whether a query returned any records. In the scripts, it is used after executing a SELECT query on the 'students' table to check if any rows have been fetched. If rows are present, the data is processed and displayed; if not, a message stating 'no record found' is shown .

Inserting data into a MySQL table with PHP involves several key steps: First, a connection is established to the MySQL database using mysqli_connect(). Next, an SQL INSERT statement is crafted specifying the table ('students') and the data to be inserted (values for 'stname', 'email', and 'mobile'). This statement is executed using the mysqli_query() function. If successful, a confirmation message is displayed; otherwise, an error message is outputted .

Error handling in PHP when executing SQL statements is critical for identifying issues and ensuring reliable operations. The provided examples employ error handling by checking the success of mysqli_query() calls. If an error occurs, relevant messages are output using die() and mysqli_error(), which help diagnose the issue (e.g., a failed database creation or an erroneous query). This practice aids in debugging and maintaining application stability .

Updating records in MySQL from PHP involves executing an UPDATE statement. This statement specifies the table, the new values for columns, and a condition under the WHERE clause to identify which records to update. In the document, the script updates the 'stname' to 'Sunil' for a student record with a specified 'mobile' number '1122334456'. This process allows altering existing data, maintaining its accuracy and relevance .

A database connection in PHP is closed using the mysqli_close() function, passing the connection object as an argument. Closing database connections is important to free up resources and avoid potential memory leaks, which can occur if too many connections remain open. This practice ensures efficient management of connections and maintains server performance .

The AUTO_INCREMENT attribute in MySQL is significant as it automatically generates a unique number for each new record inserted into a table, which is commonly used for primary keys. In the provided PHP script, the AUTO_INCREMENT attribute is applied to the 'id' column in the 'students' table, ensuring each entry has a unique identifier that increases sequentially .

The mysqli_connect() function in PHP is used to establish a new connection to a MySQL server. In the provided scripts, it is used by passing the server name (localhost), username (root), and password (empty string in XAMPP's default configuration) as parameters. This function returns a connection object that is subsequently checked to ensure the connection was successful. If the connection fails, the die() function terminates the script, and mysqli_connect_error() provides an error message .

Considerations for designing table structures include defining appropriate data types, setting primary keys, using constraints like NOT NULL and UNIQUE, and employing AUTO_INCREMENT for unique identifiers. In the document, these considerations are illustrated through the 'students' table creation. It uses an AUTO_INCREMENT primary key on 'id', defines the 'stname' and 'mobile' as NOT NULL, and chooses VARCHAR data types suited for text and string values .

You might also like