[go: up one dir, main page]
Scribd
Upload
27 views17 pages

Atharva - Database Connectivity With PHP

The document provides a comprehensive guide on database operations using PHP, including creating a database, creating tables, inserting, updating, deleting, and altering records. It includes code snippets for each operation along with expected outputs. The document also covers commands for describing, truncating, renaming, and dropping tables in a MySQL database.

Uploaded by

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

Atharva - Database Connectivity With PHP

The document provides a comprehensive guide on database operations using PHP, including creating a database, creating tables, inserting, updating, deleting, and altering records. It includes code snippets for each operation along with expected outputs. The document also covers commands for describing, truncating, renaming, and dropping tables in a MySQL database.

Uploaded by

syediliyas8282
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 17

Internship Codes Database With PHP

-----------------------( Database Connectivity With PHP ) -----------------


➢ Creation Of Database –

---------------------------------------( Program Code )---------------------------------------


<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "atharva_db";
$conn = new mysqli($servername, $username, $password);
if ($conn->connect_error) {
die("<h3 style='color:red;'>Connection failed: " . $conn->connect_error .
"</h3>");
} else {
echo "<h3 style='color:green;'>Connection Successful</h3>";
}
$sql = "CREATE DATABASE IF NOT EXISTS $dbname";
if ($conn->query($sql) === TRUE) {
echo "<p style='color:blue;'>Database <b>$dbname</b> created successfully (or
already exists).</p>";
} else {
echo "<p style='color:red;'>Error creating database: " . $conn->error .
"</p>";
}
$conn->close();
?>

------------------------------------( Program Output )---------------------------------------

Atharva Bodhankar Page 1 of 17


Internship Codes Database With PHP

---------------------------------------------------------------
➢ Table Creation

---------------------------------------( Program Code )---------------------------------------


<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "atharva_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("<h3 style='color:red;'>Connection failed: " . $conn->connect_error .
"</h3>");
} else {
echo "<h3 style='color:green;'>Connected to database successfully!</h3>";
}
$sql = "CREATE TABLE IF NOT EXISTS studentdata (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
stu_name VARCHAR(100) NOT NULL,
email VARCHAR(100),
course VARCHAR(100)
)";
if ($conn->query($sql) === TRUE) {
echo "<p style='color:blue;'>Table <b>students</b> created successfully (or
already exists).</p>";
} else {
echo "<p style='color:red;'>Error creating table: " . $conn->error . "</p>";
}
$conn->close();
Atharva Bodhankar Page 2 of 17
Internship Codes Database With PHP

?>
------------------------------------( Program Output )---------------------------------------

---------------------------------------------------------------

Atharva Bodhankar Page 3 of 17


Internship Codes Database With PHP

Operation Done On Tables


1. Insertion And Displaying Data

---------------------------------------( Program Code )---------------------------------------


<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "atharva_db";
$conn = new mysqli($host, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$insertQuery = "
INSERT INTO studentdata (id, stu_name, email, course) VALUES
(501, 'Atharva Bodhankar', 'atharvabodhankar@gmail.com', 'Computer Science'),
(502, 'Ritesh Doibale', 'riteshdoibale@gmail.com', 'IT'),
(503, 'Pratik Bais', 'pratikbais@gamil.com', 'Mechanical'),
(504, 'Parth Kulkarni', 'parthkulkarni@gmail.com', 'Electrical'),
(505, 'Rudra Pampatwar', 'rudrapampatwar@gmail.com', 'Civil');
";
if ($conn->query($insertQuery) === TRUE) {
echo "<p style='color:green;'>5 students inserted successfully.</p>";
} else {
echo "<p style='color:red;'> Insert error: " . $conn->error . "</p>";
}
echo "<h2>Student Records</h2>";
echo "<table border='1' cellpadding='10' cellspacing='0'>
<tr style='background-color:#eee;'>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Course</th>
</tr>";
$result = $conn->query("SELECT * FROM studentdata");
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "<tr>
<td>{$row['id']}</td>
<td>{$row['stu_name']}</td>
<td>{$row['email']}</td>
<td>{$row['course']}</td>
</tr>";

Atharva Bodhankar Page 4 of 17


Internship Codes Database With PHP

}
} else {
echo "<tr><td colspan='4'>No records found.</td></tr>";
}
echo "</table>";
$conn->close();
?>
------------------------------------( Program Output )---------------------------------------

---------------------------------------------------------------
Atharva Bodhankar Page 5 of 17
Internship Codes Database With PHP

2. Updates Commands –
---------------------------------------( Program Code )---------------------------------------
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "atharva_db";
$conn = new mysqli($host, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$updateQuery1 = "UPDATE studentdata SET course = '**Cyber Security' WHERE
stu_name = 'Atharva Bodhankar'";
$updateQuery2 = "UPDATE studentdata SET email = '**newriteshid@gmail.com' WHERE
stu_name = 'Ritesh Doibale'";
$updateQuery3 = "UPDATE studentdata SET course = '*BCA' WHERE stu_name = 'Rudra
Pampatwar'";
if (
$conn->query($updateQuery1) === TRUE &&
$conn->query($updateQuery2) === TRUE &&
$conn->query($updateQuery3) === TRUE
) {
echo "<p style='color:green;'>Records updated successfully.</p>";
} else {
echo "<p style='color:red;'>Update error: " . $conn->error . "</p>";
}
echo "<h2>Updated Student Records</h2>";
echo "<table border='1' cellpadding='10' cellspacing='0'>
<tr style='background-color:#eee;'>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Course</th>
</tr>";
$result = $conn->query("SELECT * FROM studentdata");
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "<tr>
<td>{$row['id']}</td>
<td>{$row['stu_name']}</td>
<td>{$row['email']}</td>
<td>{$row['course']}</td>
</tr>";
}
} else {
Atharva Bodhankar Page 6 of 17
Internship Codes Database With PHP

echo "<tr><td colspan='4'>No records found.</td></tr>";


}
echo "</table>";
$conn->close();
?>
------------------------------------( Program Output )---------------------------------------

---------------------------------------------------------------
Atharva Bodhankar Page 7 of 17
Internship Codes Database With PHP

3. Delete Command
---------------------------------------( Program Code )---------------------------------------
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "atharva_db";
$conn = new mysqli($host, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$deleteQuery1 = "DELETE FROM studentdata WHERE stu_name = 'Parth Kulkarni'";
$deleteQuery2 = "DELETE FROM studentdata WHERE stu_name = 'Rudra Pampatwar'";
if (
$conn->query($deleteQuery1) === TRUE &&
$conn->query($deleteQuery2) === TRUE
) {
echo "<p style='color:green;'>Records deleted successfully.</p>";
} else {
echo "<p style='color:red;'>Deletion error: " . $conn->error . "</p>";
}
echo "<h2>Remaining Student Records</h2>";
echo "<table border='1' cellpadding='10' cellspacing='0'>
<tr style='background-color:#eee;'>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Course</th>
</tr>";
$result = $conn->query("SELECT * FROM studentdata");
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "<tr>
<td>{$row['id']}</td>
<td>{$row['stu_name']}</td>
<td>{$row['email']}</td>
<td>{$row['course']}</td>
</tr>";
}
} else {
echo "<tr><td colspan='4'>No records found.</td></tr>";
}
echo "</table>";
$conn->close();

Atharva Bodhankar Page 8 of 17


Internship Codes Database With PHP

?>

------------------------------------( Program Output )---------------------------------------

---------------------------------------------------------------

Atharva Bodhankar Page 9 of 17


Internship Codes Database With PHP

4. Alter Command Modification


---------------------------------------( Program Code )---------------------------------------
<?php
$conn = new mysqli("localhost", "root", "", "atharva_db");
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql1 = "ALTER TABLE studentdata ADD newcol_marks VARCHAR(100)";
$conn->query($sql1);
$sql2 = "ALTER TABLE studentdata CHANGE course department VARCHAR(100)";
$conn->query($sql2);
$sql3 = "ALTER TABLE studentdata ADD age INT(3)";
$conn->query($sql3);
echo "<h3>Alter operations applied successfully!</h3>";
$result = $conn->query("SELECT * FROM studentdata");
if ($result->num_rows > 0) {
echo "<table border='1' cellpadding='8'><tr>";
while ($field = $result->fetch_field()) {
echo "<th>{$field->name}</th>";
}
echo "</tr>";
while ($row = $result->fetch_assoc()) {
echo "<tr>";
foreach ($row as $val) {
echo "<td>{$val}</td>";
}
echo "</tr>";
}
echo "</table>";
} else {
echo "No data found in students_data table.";
}
$conn->close();
?>

Atharva Bodhankar Page 10 of 17


Internship Codes Database With PHP

------------------------------------( Program Output )---------------------------------------

---------------------------------------------------------------

Atharva Bodhankar Page 11 of 17


Internship Codes Database With PHP

5. Describe Command
---------------------------------------( Program Code )---------------------------------------
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "atharva_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "DESCRIBE studentdata";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<h3>Table Structure of 'studentdata'</h3>";
echo "<table border='1' cellpadding='10'>";
echo "<tr><th>Field</th><th>Type</th></tr>";
while($row = $result->fetch_assoc()) {
echo "<tr>
<td>{$row['Field']}</td>
<td>{$row['Type']}</td>
</tr>";
}
echo "</table>";
} else {
echo "No structure found.";
}
$conn->close();
?>
------------------------------------( Program Output )---------------------------------------

Atharva Bodhankar Page 12 of 17


Internship Codes Database With PHP

---------------------------------------------------------------

Atharva Bodhankar Page 13 of 17


Internship Codes Database With PHP

6. Truncate Command
---------------------------------------( Program Code )---------------------------------------
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "atharva_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "TRUNCATE TABLE studentdata";
if ($conn->query($sql) === TRUE) {
echo "Table 'studentdata' truncated successfully.<br><br>";
} else {
echo "Error truncating table: " . $conn->error . "<br><br>";
}
$checkData = "SELECT * FROM studentdata";
$result = $conn->query($checkData);
if ($result->num_rows == 0) {
echo "Verified: No data found in 'students_data' after truncation.";
} else {
echo "Some data still exists in the table:<br>";
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " | Name: " . $row["name"] . "<br>";
}
}
$conn->close();
?>

------------------------------------( Program Output )---------------------------------------

Atharva Bodhankar Page 14 of 17


Internship Codes Database With PHP

---------------------------------------------------------------
7. Rename Command –
---------------------------------------( Program Code )---------------------------------------
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "atharva_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "RENAME TABLE studentdata TO college_students";
if ($conn->query($sql) === TRUE) {
echo "Table renamed successfully from 'studentdata' to
'college_students'.<br>";
} else {
echo "Error renaming table: " . $conn->error . "<br>";
}
$check = "SHOW TABLES LIKE 'college_students'";
$result = $conn->query($check);
if ($result->num_rows > 0) {
echo "Verified: 'college_students' table now exists.";
} else {
echo "Verification failed: 'college_students' table not found.";
}
$conn->close();
?>

Atharva Bodhankar Page 15 of 17


Internship Codes Database With PHP

------------------------------------( Program Output )---------------------------------------

---------------------------------------------------------------
8. Drop Command
---------------------------------------( Program Code )---------------------------------------
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "atharva_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "DROP TABLE IF EXISTS college_students";
if ($conn->query($sql) === TRUE) {
echo "Table 'college_students' dropped successfully.<br><br>";
} else {
echo "Error dropping table: " . $conn->error . "<br><br>";

Atharva Bodhankar Page 16 of 17


Internship Codes Database With PHP

}
$check = "SHOW TABLES LIKE 'college_students'";
$result = $conn->query($check);
if ($result->num_rows == 0) {
echo "Verified: Table 'college_students' no longer exists in the database.";
} else {
echo "Table still exists.";
}
$conn->close();
?>
------------------------------------( Program Output )---------------------------------------

---------------------------------------------------------------

Atharva Bodhankar Page 17 of 17

You might also like