Scribd
Upload
2 views9 pages

If Die: Mysqli - Connect

The document is a PHP script for a web-based SQL practice lab that allows users to create databases, tables, and perform CRUD operations. It includes functionality for altering tables, inserting data, and executing various SQL queries. The script also provides a user interface for interacting with the database and displaying results.

Uploaded by

Prafulla Kinkar
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
2 views9 pages

If Die: Mysqli - Connect

The document is a PHP script for a web-based SQL practice lab that allows users to create databases, tables, and perform CRUD operations. It includes functionality for altering tables, inserting data, and executing various SQL queries. The script also provides a user interface for interacting with the database and displaying results.

Uploaded by

Prafulla Kinkar
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

<?

php
/* ================= VARIABLES (MUST BE FIRST) ================= */
$msg = "";
$results = [];

/* ================= CONNECTION ================= */


$conn = mysqli_connect("localhost","root","");
if(!$conn) die("DB Error");

/* ================= DATABASE ================= */


if(isset($_POST['create_db'])){
$db = $_POST['db_name'];
mysqli_query($conn,"CREATE DATABASE IF NOT EXISTS `$db`");
mysqli_select_db($conn,$db);
$msg = "Database <b>$db</b> created / selected";
}
mysqli_select_db($conn,"transport_db");

/* ================= CREATE TABLE ================= */


if(isset($_POST['create_table'])){
mysqli_query($conn,"CREATE TABLE IF NOT EXISTS `$_POST[table_name]`
($_POST[table_fields])");
$msg = "Table created";
}

/* ================= ALTER TABLE ================= */


if(isset($_POST['add_column'])){
mysqli_query($conn,"ALTER TABLE `$_POST[alter_table]` ADD $_POST[new_column]");
$msg = "Column Added";
}
if(isset($_POST['modify_column_btn'])){
mysqli_query($conn,"ALTER TABLE `$_POST[modify_table]` MODIFY
$_POST[modify_column]");
$msg = "Column Modified";
}
if(isset($_POST['drop_column_btn'])){
mysqli_query($conn,"ALTER TABLE `$_POST[drop_table]` DROP
`$_POST[drop_column]`");
$msg = "Column Dropped";
}

/* ================= DESCRIBE ================= */


if(isset($_POST['describe'])){
$sql="DESCRIBE `$_POST[describe_table]`";
$results[]=['title'=>'DESCRIBE','sql'=>$sql,'data'=>mysqli_query($conn,$sql)];
}

/* ================= TABLES ================= */


mysqli_query($conn,"CREATE TABLE IF NOT EXISTS firms(
firm_id INT AUTO_INCREMENT PRIMARY KEY,
firm_name VARCHAR(150),
gst_no VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)");
mysqli_query($conn,"CREATE TABLE IF NOT EXISTS ledgers(
ledger_id INT AUTO_INCREMENT PRIMARY KEY,
firm_id INT,
party_name VARCHAR(150),
balance DECIMAL(12,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)");
mysqli_query($conn,"CREATE TABLE IF NOT EXISTS bookings(
booking_id INT AUTO_INCREMENT PRIMARY KEY,
ledger_id INT,
lr_no VARCHAR(50),
source VARCHAR(100),
destination VARCHAR(100),
freight DECIMAL(10,2),
booking_date DATE
)");
if(isset($_POST['select_table'])){
$table = $_POST['table_select'];

$sql = "SELECT * FROM `$table`";


$results[] = [
'title' => "TABLE DATA : $table",
'sql' => $sql,
'data' => mysqli_query($conn,$sql)
];
}
$tables_list = mysqli_query($conn,"SHOW TABLES");
$all_tables = [];

$dbs = mysqli_query($conn,"SHOW DATABASES");


while($db = mysqli_fetch_array($dbs)){
$db_name = $db[0];

// skip system databases (optional but recommended)


if(in_array($db_name,['information_schema','mysql','performance_schema','sys']))
continue;

$tables = mysqli_query($conn,"SHOW TABLES FROM `$db_name`");


while($t = mysqli_fetch_array($tables)){
$all_tables[] = [
'db' => $db_name,
'table' => $t[0]
];
}
}

/* ================= INSERT ================= */


if(isset($_POST['add_firm'])){
mysqli_query($conn,"INSERT INTO firms (firm_name,gst_no) VALUES
('$_POST[firm_name]','$_POST[gst_no]')");
$msg="Firm Added";
}
if(isset($_POST['add_ledger'])){
mysqli_query($conn,"INSERT INTO ledgers (firm_id,party_name,balance)
VALUES ($_POST[firm_id],'$_POST[party_name]',$_POST[balance])");
$msg="Ledger Added";
}
if(isset($_POST['add_booking'])){
mysqli_query($conn,"INSERT INTO bookings
(ledger_id,lr_no,source,destination,freight,booking_date)
VALUES ($_POST[ledger_id],'$_POST[lr_no]','$_POST[source]',
'$_POST[destination]',$_POST[freight],'$_POST[booking_date]')");
$msg="Booking Added";
}

/* ================= SEED ================= */


if(isset($_POST['seed_data'])){
mysqli_query($conn,"INSERT INTO firms (firm_name,gst_no) VALUES ('Demo
Transport','27ABCDE1234F1Z5')");
mysqli_query($conn,"INSERT INTO ledgers (firm_id,party_name,balance) VALUES
(1,'Sharma Traders',15000)");
mysqli_query($conn,"INSERT INTO bookings
(ledger_id,lr_no,source,destination,freight,booking_date)
VALUES (1,'LR001','VAPI','SURAT',8000,CURDATE())");
$msg="Sample Data Inserted";
}

/* ================= CRUD ================= */


if(isset($_POST['select_all']))
$results[]=['title'=>'SELECT','sql'=>"SELECT * FROM
ledgers",'data'=>mysqli_query($conn,"SELECT * FROM ledgers")];

if(isset($_POST['search']))
$results[]=['title'=>'SEARCH','sql'=>"SELECT * FROM ledgers WHERE party_name LIKE
'%$_POST[search_name]%'",
'data'=>mysqli_query($conn,"SELECT * FROM ledgers WHERE party_name LIKE
'%$_POST[search_name]%'")];

if(isset($_POST['update'])){
mysqli_query($conn,"UPDATE ledgers SET balance=$_POST[ubal] WHERE
ledger_id=$_POST[uid]");
$msg="Updated";
}
if(isset($_POST['delete'])){
mysqli_query($conn,"DELETE FROM ledgers WHERE ledger_id=$_POST[did]");
$msg="Deleted";
}

/* ================= QUERY HELPER ================= */


function runQuery($c,$t,$s,&$r){
$r[]=['title'=>$t,'sql'=>$s,'data'=>mysqli_query($c,$s)]; }

/* ================= ALL QUERIES (UNCHANGED) ================= */


if(isset($_POST['between'])) runQuery($conn,"BETWEEN","SELECT * FROM ledgers WHERE
balance BETWEEN 5000 AND 20000",$results);
if(isset($_POST['like'])) runQuery($conn,"LIKE","SELECT * FROM ledgers WHERE
party_name LIKE '%A%'",$results);
if(isset($_POST['in'])) runQuery($conn,"IN","SELECT * FROM bookings WHERE source IN
('VAPI','SURAT')",$results);
if(isset($_POST['distinct'])) runQuery($conn,"DISTINCT","SELECT DISTINCT source FROM
bookings",$results);
if(isset($_POST['limit'])) runQuery($conn,"LIMIT","SELECT * FROM bookings LIMIT
2",$results);
if(isset($_POST['order_limit'])) runQuery($conn,"ORDER+LIMIT","SELECT * FROM ledgers
ORDER BY balance DESC LIMIT 1",$results);
if(isset($_POST['group_by'])) runQuery($conn,"GROUP BY","SELECT source,SUM(freight)
total FROM bookings GROUP BY source",$results);
if(isset($_POST['having'])) runQuery($conn,"HAVING","SELECT source,SUM(freight) total
FROM bookings GROUP BY source HAVING SUM(freight)>5000",$results);
if(isset($_POST['case_when'])) runQuery($conn,"CASE","SELECT party_name,CASE WHEN
balance>10000 THEN 'HIGH' ELSE 'LOW' END status FROM ledgers",$results);
if(isset($_POST['date_func'])) runQuery($conn,"DATE","SELECT
lr_no,booking_date,YEAR(booking_date) yr FROM bookings",$results);
if(isset($_POST['join'])) runQuery($conn,"JOIN","SELECT
f.firm_name,l.party_name,b.lr_no,[Link] FROM bookings b JOIN ledgers l ON
b.ledger_id=l.ledger_id JOIN firms f ON l.firm_id=f.firm_id",$results);
if(isset($_POST['subquery'])) runQuery($conn,"SUBQUERY","SELECT * FROM ledgers WHERE
balance>(SELECT AVG(balance) FROM ledgers)",$results);
if(isset($_POST['union'])) runQuery($conn,"UNION","SELECT source FROM bookings UNION
SELECT destination FROM bookings",$results);
if(isset($_POST['union_all'])) runQuery($conn,"UNION ALL","SELECT source FROM
bookings UNION ALL SELECT destination FROM bookings",$results);
if(isset($_POST['explain'])) runQuery($conn,"EXPLAIN","EXPLAIN SELECT * FROM ledgers
WHERE balance>10000",$results);
if(isset($_POST['sql_injection'])) runQuery($conn,"SQL INJECTION","SELECT * FROM
ledgers WHERE party_name='' OR '1'='1'",$results);

/* ================= TABLE VIEW ================= */


function showTable($r){
if(!$r || mysqli_num_rows($r)==0){ echo "<p>No Records</p>"; return; }
echo "<table><tr>";
while($f=mysqli_fetch_field($r)) echo "<th>{$f->name}</th>";
echo "</tr>";
while($row=mysqli_fetch_assoc($r)){
echo "<tr>"; foreach($row as $v) echo "<td>$v</td>"; echo "</tr>";
}
echo "</table>";
}
?>
<!DOCTYPE html>
<html>
<head>
<title>SQL Practice Lab</title>
<style>
body{font-family:Arial;background:#eef2ff}
.main{width:1200px;margin:auto}
.box{background:#fff;padding:15px;border-radius:8px}
table{border-collapse:collapse;width:100%;margin-top:10px}
th,td{border:1px solid #aaa;padding:6px}
button{margin:4px;padding:6px 10px}
pre{background:#f5f5f5;padding:8px}
.msg{background:#e8f5e9;padding:10px;margin:10px 0}
.tree ul{list-style:none;padding-left:20px}
.tree li{margin:4px 0}
.tree b{color:#3f51b5}
.tree small{color:#555}
.modal{
display:block;
position:fixed;
z-index:1000;
left:0;
top:0;
width:100%;
height:100%;
background:rgba(0,0,0,0.5);
}
.modal-content{
background:#fff;
margin:15% auto;
padding:20px;
width:400px;
border-radius:8px;
text-align:center;
}
.close{
float:right;
font-size:22px;
cursor:pointer;
}

</style>
</head>

<body>

<div class="main box">

<form method="post">

<!-- ================= DATABASE ================= -->


<h3>Create Database</h3>
<input type="text" name="db_name" placeholder="Database Name">
<button name="create_db">Create Database</button>

<!-- ================= CREATE TABLE ================= -->


<h3>Create Table</h3>
<input type="text" name="table_name" placeholder="Table Name"><br><br>

<textarea name="table_fields" rows="4" cols="60"


placeholder="Example:
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
amount DECIMAL(10,2)">
</textarea><br>

<button name="create_table">Create Table</button>

<!-- ================= ALTER TABLE ================= -->


<h3>Alter Table – Add Column</h3>
<input type="text" name="alter_table" placeholder="Table Name">
<input type="text" name="new_column" placeholder="Column Definition">
<small>Example: phone VARCHAR(15)</small><br>
<button name="add_column">Add Column</button>

<h3>Alter Table – Modify Column</h3>


<input type="text" name="modify_table" placeholder="Table Name">
<input type="text" name="modify_column" placeholder="Column Definition">
<small>Example: balance DECIMAL(14,2)</small><br>
<button name="modify_column_btn">Modify Column</button>

<h3>Alter Table – Drop Column</h3>


<input type="text" name="drop_table" placeholder="Table Name">
<input type="text" name="drop_column" placeholder="Column Name">
<button name="drop_column_btn">Drop Column</button>

<!-- ================= DESCRIBE ================= -->


<h3>Describe Table</h3>
<input type="text" name="describe_table" placeholder="Table Name">
<button name="describe">DESCRIBE</button>

<hr>

<!-- ================= CRUD ================= -->


<h2>SQL PRACTICE LAB</h2>

<h3>Basic CRUD</h3>

<h3>Select Table</h3>

<select name="table_select" required>


<option value="">-- Select Table --</option>

<?php
while($t = mysqli_fetch_array($tables_list)){
$tbl = $t[0];
$selected = (isset($_POST['table_select']) && $_POST['table_select']==$tbl) ?
"selected" : "";
echo "<option value='$tbl' $selected>$tbl</option>";
}
?>
</select>
<button name="select_all">SELECT</button><br><br>

<input name="search_name" placeholder="Party Name">


<button name="search">SEARCH</button><br><br>

<input name="uid" placeholder="Ledger ID">


<input name="ubal" placeholder="New Balance">
<button name="update">UPDATE</button><br><br>

<input name="did" placeholder="Ledger ID">


<button name="delete">DELETE</button>

<!-- ================= INSERT ================= -->


<h3>Insert</h3>

<input name="firm_name" placeholder="Firm Name">


<input name="gst_no" placeholder="GST">
<button name="add_firm">Add Firm</button><br><br>

<input name="firm_id" placeholder="Firm ID">


<input name="party_name" placeholder="Party Name">
<input name="balance" placeholder="Balance">
<button name="add_ledger">Add Ledger</button><br><br>

<input name="ledger_id" placeholder="Ledger ID">


<input name="lr_no" placeholder="LR No">
<input name="source" placeholder="Source">
<input name="destination" placeholder="Destination">
<input name="freight" placeholder="Freight">
<input type="date" name="booking_date">
<button name="add_booking">Add Booking</button>

<!-- ================= QUERIES ================= -->


<h3>Queries</h3>
<button name="between">BETWEEN</button>
<button name="like">LIKE</button>
<button name="in">IN</button>
<button name="distinct">DISTINCT</button>
<button name="limit">LIMIT</button>
<button name="order_limit">ORDER + LIMIT</button>
<button name="group_by">GROUP BY</button>
<button name="having">HAVING</button>
<button name="case_when">CASE</button>
<button name="date_func">DATE</button>
<button name="join">JOIN</button>
<button name="subquery">SUBQUERY</button>
<button name="union">UNION</button>
<button name="union_all">UNION ALL</button>
<button name="explain">EXPLAIN</button>
<button name="sql_injection">SQL Injection</button>
<button name="seed_data">Seed Data</button>

</form>

<!-- ================= MESSAGE ================= -->


<?php if(!empty($msg)){ ?>
<div id="msgDialog" class="modal">
<div class="modal-content">
<span class="close" onclick="closeMsg()">&times;</span>
<h3>Message</h3>
<p><?= $msg ?></p>
</div>
</div>

<script>
function closeMsg(){
[Link]('msgDialog').[Link]='none';
}
</script>
<?php } ?>

<!-- ================= DATABASE TREE ================= -->


<h2>� Database Tree</h2>

<div class="tree">
<ul>
<?php
$tables = mysqli_query($conn,"SHOW TABLES");
while($t = mysqli_fetch_array($tables)){
echo "<li><b>{$t[0]}</b><ul>";
$cols = mysqli_query($conn,"DESCRIBE {$t[0]}");
while($c = mysqli_fetch_assoc($cols)){
echo "<li>{$c['Field']} <small>({$c['Type']})</small></li>";
}
echo "</ul></li>";
}
?>
</ul>
</div>

<!-- ================= RESULTS ================= -->


<hr>
<h2>� QUERY RESULTS (BOTTOM)</h2>

<?php
foreach($results as $r){
echo "<h3>{$r['title']}</h3>";
echo "<pre>{$r['sql']}</pre>";
showTable($r['data']);
}
?>
<?php
if(isset($_POST['select_table'])){
$table = $_POST['table_select'];

$sql = "SELECT * FROM `$table`";


$results[] = [
'title' => "SELECT TABLE : $table",
'sql' => $sql,
'data' => mysqli_query($conn,$sql)
];
}
?>
</div>
</body>
</html>

You might also like