Below PHP code with oracle as backend to Show Generalize SQL output and export to csv file - php/oracle
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Oracle Query Tool</title>
<style>
body {
font-family: 'Segoe UI', sans-serif;
background: #f4f6f8;
padding: 30px;
}
h2 {
color: #333;
}
form {
background: #fff;
padding: 20px;
border-radius: 8px;
box-shadow: 0 2px 5px rgba(0,0,0,0.1);
max-width: 1800px;
margin-bottom: 20px;
}
textarea {
width: 100%;
min-height: 100px;
resize: both;
padding: 10px;
font-size: 14px;
border: 1px solid #ccc;
border-radius: 4px;
box-sizing: border-box;
}
input[type="submit"] {
background: #0078D4;
color: white;
border: none;
padding: 10px 20px;
font-size: 14px;
border-radius: 4px;
cursor: pointer;
}
input[type="submit"]:hover {
background: #005a9e;
}
.error {
color: #d8000c;
background: #ffdddd;
padding: 10px;
border-left: 5px solid #d8000c;
margin-bottom: 20px;
}
table {
border-collapse: collapse;
width: 100%;
background: #fff;
box-shadow: 0 2px 5px rgba(0,0,0,0.1);
}
th, td {
padding: 12px;
border: 1px solid #ddd;
text-align: left;
}
th {
background: #0078D4;
color: white;
}
.export-btn {
background: linear-gradient(135deg, #007BFF, #0056b3); /* Blue gradient */
border: none;
color: #fff;
padding: 8px 20px;
font-size: 16px;
font-weight: 600;
border-radius: 8px;
cursor: pointer;
transition: all 0.3s ease;
box-shadow: 0 4px 10px rgba(0,0,0,0.25);
display: inline-flex;
align-items: center;
gap: 8px;
}
.export-btn:hover {
background: linear-gradient(135deg, #0056b3, #004080);
transform: translateY(-2px);
}
.export-btn:active {
transform: translateY(1px);
box-shadow: 0 2px 6px rgba(0,0,0,0.2);
}
.export-btn i {
font-size: 18px;
}
</style>
</head>
<body>
<h2>Oracle Query Tool</h2>
<form method="post">
<label for="query">Enter SQL Query:</label><br>
<textarea name="query" id="query" placeholder="Enter SQL.........."><?php echo htmlspecialchars($_POST['query'] ?? ''); ?></textarea>
<input type="submit" value="Run Query">
<!-- Export Button -->
<button class="export-btn" onclick="downloadTableAsCSV('queryTable','Export_Report.csv')">
Export to CSV
</button>
</form>
<?php
if ($_SERVER['REQUEST_METHOD'] === 'POST' && !empty($_POST['query'])) {
$query = $_POST['query'];
// Oracle connection
$conn = @oci_connect('xxxx', 'xxxx', '//localhost:1521/xxxx');
if (!$conn) {
$e = oci_error();
echo "<div class='error'>? Connection failed: " . htmlspecialchars($e['message']) . "</div>";
} else {
$stmt = @oci_parse($conn, $query);
if (!$stmt) {
$e = oci_error($conn);
echo "<div class='error'>? SQL Parse Error: " . htmlspecialchars($e['message']) . "</div>";
} else {
$exec = @oci_execute($stmt);
if (!$exec) {
$e = oci_error($stmt);
echo "<div class='error'>? Execution Error: " . htmlspecialchars($e['message']) . "</div>";
} else {
echo "<table id='queryTable'><tr>";
$ncols = oci_num_fields($stmt);
for ($i = 1; $i <= $ncols; $i++) {
echo "<th>" . htmlspecialchars(oci_field_name($stmt, $i)) . "</th>";
}
echo "</tr>";
while ($row = oci_fetch_array($stmt, OCI_ASSOC + OCI_RETURN_NULLS)) {
echo "<tr>";
foreach ($row as $val) {
echo "<td>" . htmlspecialchars($val ?? '') . "</td>";
}
echo "</tr>";
}
echo "</table>";
}
}
oci_free_statement($stmt);
oci_close($conn);
}
}
?>
<script>
function downloadTableAsCSV(tableId, baseFilename) {
var csv = [];
var rows = document.querySelectorAll("#" + tableId + " tr");
for (var i = 0; i < rows.length; i++) {
var row = [], cols = rows[i].querySelectorAll("td, th");
for (var j = 0; j < cols.length; j++) {
var data = cols[j].innerText.replace(/"/g, '""');
row.push('"' + data + '"');
}
csv.push(row.join(","));
}
// Create CSV file
var csvFile = new Blob([csv.join("\n")], { type: "text/csv" });
// Build filename with date (YYYYMMDD)
var today = new Date();
var yyyy = today.getFullYear();
var mm = String(today.getMonth() + 1).padStart(2, '0');
var dd = String(today.getDate()).padStart(2, '0');
var filename = baseFilename + "_" + yyyy + mm + dd + ".csv";
// Download link
var downloadLink = document.createElement("a");
downloadLink.download = filename;
downloadLink.href = window.URL.createObjectURL(csvFile);
downloadLink.style.display = "none";
document.body.appendChild(downloadLink);
downloadLink.click();
document.body.removeChild(downloadLink);
}
</script>
</body>
</html>
No comments:
Post a Comment