I have an advanced search option for the user, where they can build the query up and display the results.
The user then has the option to export those results to an exe file.
The operation of sending the query over and populating the exe file works, but what I’m having trouble with is the actual query that is generated to populate the exe file with the search that is being carried out.
<?php
$queryIsValid = true;
if ($contractLike == "CSF" or $contractLike == "ADM") {
$query = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})
select COUNT(*) from LIMIT WHERE {limit clause}";
$queryContracts = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})
select * from LIMIT WHERE {limit clause}";
$limitClause = "";
if (isset($_POST['advrows'])) {
for ($i = 0; $i < sizeof($_POST['field_names']); $i++) {
if (strlen(trim($_POST['field_names'][$i])) !== 0 && strlen(trim($_POST['operator'][$i])) !== 0)
//&& strlen(trim($_POST['keyword'][$i])) !== 0)
{
if (($_POST['field_names'][$i]) && ($_POST['operator'][$i])) {
$field_name = ($_POST['field_names'][$i]);
$operator = ($_POST['operator'][$i]);
$keyword = ($_POST['keyword'][$i]);
if ($i === 0) {
if ($operator == "like") {
$limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
} else {
$limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
}
} else {
if ($operator == "like") {
$limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
} else {
$limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
}
}
} else {
$queryIsValid = false;
echo "required information missing or is invalid - query was not executed";
break;
}
}
}
$_SESSION['limitClause'] = $limitClause; // added this line
} else {
if (isset($_SESSION['limitClause'])) {
$limitClause = $_SESSION['limitClause'];
}
}
if ($queryIsValid)
{
$query = str_replace('{limit clause}', $limitClause, $query); // added this line
//var_dump ($query);
$queryContracts = str_replace('{limit clause}', $limitClause, $queryContracts); // added this line
var_dump ($queryContracts);
$_SESSION['limitClauseAdv'] = $query;
That above gathers all the info from the query builder, then via the $_SESSION passes it over to be grabbed and used by the code below
$query = $_SESSION['limitClauseAdv'];
function cleanData(&$str)
{
if($str == 't') $str = 'TRUE';
if($str == 'f') $str = 'FALSE';
if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
$str = "'$str";
}
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
$str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8');
}
// filename for download
$filename = "website_data_" . date('Ymd') . ".csv";
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: text/csv; charset=UTF-16LE");
$out = fopen("php://output", 'w');
$flag = false;
$result = sqlsrv_query($conn, $query) or die('Query failed!');
while(false !== ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))) {
if(!$flag) {
// display field/column names as first row
fputcsv($out, array_keys($row), ',', '"');
$flag = true;
}
//array_walk($row, 'cleanData');
//start
foreach ($row as $key => $value)
{
if ($value instanceof DateTime)
{
$row[$key] = date_format($value, 'd/m/y');
}
}
//end
$row = str_replace(",", ",", $row);
$row = str_replace(".", ".", $row);
fputcsv($out, array_values($row), ',', '"');
}
fclose($out);
exit;
?>
This is a query below that I var_dumped to the exe file
string(249) "WITH LIMIT AS ( SELECT * ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Status = 'Active' AND Contract_Number like 'MEX%') select COUNT(*) from LIMIT WHERE Contract_Status = 'Active' AND Contract_Number like 'MEX%'"
And if I turn the var_dump off the only result I get is ‘116’ which is the actual number of contracts being pulled out of the database by the query
I have then tried to use str_replace to help create the query but gave up in the end as you cant cover all eventualities, this is what I was trying below
/*$query = str_replace('WITH LIMIT AS ( ', '', $query);
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber'", '', $query);
$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
$query = str_replace('))', ')', $query);
$query = str_replace('SELECT * FROM Intranet)', 'SELECT * FROM Intranet', $query);
$query = str_replace('SELECT * FROM Intranet WHERE )', 'SELECT * FROM Intranet', $query);
$query = str_replace('select COUNT(*) from LIMIT WHERE', '', $query);
$query = str_replace("%')", "%'", $query);*/
