connect_error) die("DB Error");
// FIX BAD DATA (IMPORTANT)
$conn->query("UPDATE balance SET amount = 0 WHERE amount IS NULL OR amount = ''");
$conn->query("UPDATE transaction SET amount = 0 WHERE amount IS NULL OR amount = ''");
// CREATE TABLES
$conn->query("CREATE TABLE IF NOT EXISTS balance (
id INT AUTO_INCREMENT PRIMARY KEY,
user VARCHAR(100) UNIQUE,
amount DECIMAL(15,2) DEFAULT 0
)");
$conn->query("CREATE TABLE IF NOT EXISTS transaction (
id INT AUTO_INCREMENT PRIMARY KEY,
user VARCHAR(100)
)");
$cols = [
'admin'=>"VARCHAR(100)",
'type'=>"ENUM('credit','debit')",
'amount'=>"DECIMAL(15,2)",
'old_balance'=>"DECIMAL(15,2)",
'new_balance'=>"DECIMAL(15,2)",
'reason'=>"VARCHAR(255)",
'created_at'=>"DATETIME DEFAULT CURRENT_TIMESTAMP"
];
foreach($cols as $c=>$d){
$check=$conn->query("SHOW COLUMNS FROM transaction LIKE '$c'");
if($check->num_rows==0) $conn->query("ALTER TABLE transaction ADD $c $d");
}
// SAFE FORMAT FUNCTION
function naira($value){
return number_format((float)($value ?? 0), 2);
}
// STATS
$totalUsers = $conn->query("SELECT COUNT(*) as c FROM balance")->fetch_assoc()['c'];
$totalBalance = $conn->query("SELECT SUM(amount) as s FROM balance")->fetch_assoc()['s'] ?? 0;
$totalTransactions = $conn->query("SELECT COUNT(*) as c FROM transaction")->fetch_assoc()['c'];
// AJAX
if(isset($_GET['action'])){
if($_GET['action']=='search_user'){
$term="%".$_GET['term']."%";
$res=$conn->query("SELECT user FROM balance WHERE user LIKE '$term' LIMIT 10");
$data=[];
while($r=$res->fetch_assoc()) $data[]=$r['user'];
echo json_encode($data); exit;
}
if($_GET['action']=='transactions'){
$user=$_GET['user'];
$res=$conn->query("SELECT * FROM transaction WHERE user='$user' ORDER BY id DESC");
echo "
Transactions for $user
";
while($t=$res->fetch_assoc()){
echo "
".ucfirst($t['type'])."
".$t['created_at']."
₦".naira($t['amount'])."
";
}
exit;
}
if($_GET['action']=='top_search'){
$term="%".$_GET['term']."%";
$res=$conn->query("SELECT * FROM balance WHERE user LIKE '$term'");
while($b=$res->fetch_assoc()){
echo "
".$b['user']."₦".naira($b['amount'])."
";
}
exit;
}
}
// PROCESS
$message="";
if($_SERVER['REQUEST_METHOD']=="POST"){
$user=trim($_POST['user']);
$action=$_POST['action'];
$amount=(float)$_POST['amount'];
$reason=$_POST['reason'];
$admin=$_SESSION['admin'];
$res=$conn->query("SELECT amount FROM balance WHERE user='$user'");
if($res->num_rows==0){
$conn->query("INSERT INTO balance(user,amount) VALUES('$user',0)");
$current=0;
}else{
$current=(float)$res->fetch_assoc()['amount'];
}
// VALIDATION
if($action=='debit' && $amount > $current){
$message="Insufficient balance!";
$type="error";
}else{
$new = ($action=='credit') ? $current + $amount : $current - $amount;
$conn->query("UPDATE balance SET amount='$new' WHERE user='$user'");
$conn->query("INSERT INTO transaction(user,admin,type,amount,old_balance,new_balance,reason)
VALUES('$user','$admin','$action','$amount','$current','$new','$reason')");
$message="Transaction successful!";
$type="success";
}
}
// FETCH
$balances=$conn->query("SELECT * FROM balance ORDER BY id DESC");
$transactions=$conn->query("SELECT * FROM transaction ORDER BY id DESC LIMIT 20");
?>
Admin Pro