This question is an exact duplicate of:
I am trying to build a table display MySQL data based off user input. The user selects various criteria by way of checkboxes and radio's. The values of each selected items is successfully sent to my php page via ajax (tested successfully). I then have 6 different select statements that can be run depending on the user input. Each statement works perfectly when tested individually. However, I tried to set up a series of if statements to select the correct statement. My problem is that no matter what criteria is selected, it always displays the first select statement.
Also, I feel the my current code is highly inefficient and I am unsure if its currently vulnerable to SQL Injection?
PHP CODE:
Get ajax results:
//Get date range.
$revenuefromajax=$_POST['revenuefrom'];
$revenuetoajax=$_POST['revenueto'];
$revenuefromstring = strtotime($revenuefromajax);
$revenuetostring = strtotime($revenuetoajax);
$revenuefrom=date("Y-m-d", $revenuefromstring);
$revenueto=date("Y-m-d", $revenuetostring);
//Get selected Status Values.
if (isset($_POST['revenue_checkboxes'])) {
$revenue_check = $_POST['revenue_checkboxes'];
};
if (isset($_POST['revenueTblType'])) {
$revenueTblType = $_POST['revenueTblType'];
print_r($revenueTblType);
};
if (isset($_POST['revenueWO'])) {
$revenueWO = $_POST['revenueWO'];
print_r($revenueWO);
};
if (isset($_POST['revenueWODate'])) {
$revenueWODate = $_POST['revenueWODate'];
print_r($revenueWODate);
};
If statements:
//*************Customer Table Type************//
if ($revenueTblType = 'Customer') {
//SELECT statement pulls ALL COMPLETED history info by CUSTOMER.
$sql1 = "SELECT x.company, x.revenue, x.stagestatus, x.shippeddate, FORMAT(SUM(x.totprice), 2) as totalprice, FORMAT(SUM(x.sgtotquantity), 2) as totqty, FORMAT(SUM(x.sgtotalsqft), 2) as sgtotsqft, FORMAT(SUM(x.totprice)/SUM(x.sgtotalsqft), 2) as avgsqftrevenue, FORMAT(SUM(x.totprice)/SUM(x.sgtotquantity), 2) as avgunitrevenue FROM (SELECT t1.company, t1.revenue, t1.stagestatus, t1.shippeddate, t1.id, TRIM(LEADING '$' FROM t1.totalprice) AS totprice, t2.invoiceid, SUM(t2.quantity) AS sgtotquantity, SUM(t2.width * t2.height * t2.quantity ) /144 AS sgtotalsqft, (TRIM(LEADING '$' FROM t1.totalprice)/(SUM(t2.width * t2.height * t2.quantity ) /144)) as avgsqftrev, (TRIM(LEADING '$' FROM t1.totalprice) / SUM(t2.quantity)) AS avgunitrev
FROM invoices AS t1 INNER JOIN lineitems AS t2 ON t1.id = t2.invoiceid
WHERE (t2.invoiceid = t1.id)
GROUP BY t1.id) x
WHERE x.stagestatus='Complete'
GROUP BY x.company ASC";
//SELECT statement pulls DATE RANGE COMPLETED history info by CUSTOMER.
$sql2 = "SELECT x.company, x.revenue, x.stagestatus, x.shippeddate, FORMAT(SUM(x.totprice), 2) as totalprice, FORMAT(SUM(x.sgtotquantity), 2) as totqty, FORMAT(SUM(x.sgtotalsqft), 2) as sgtotsqft, FORMAT(SUM(x.totprice)/SUM(x.sgtotalsqft), 2) as avgsqftrevenue, FORMAT(SUM(x.totprice)/SUM(x.sgtotquantity), 2) as avgunitrevenue FROM (SELECT t1.company, t1.revenue, t1.stagestatus, t1.shippeddate, t1.id, TRIM(LEADING '$' FROM t1.totalprice) AS totprice, t2.invoiceid, SUM(t2.quantity) AS sgtotquantity, SUM(t2.width * t2.height * t2.quantity ) /144 AS sgtotalsqft, (TRIM(LEADING '$' FROM t1.totalprice)/(SUM(t2.width * t2.height * t2.quantity ) /144)) as avgsqftrev, (TRIM(LEADING '$' FROM t1.totalprice) / SUM(t2.quantity)) AS avgunitrev
FROM invoices AS t1 INNER JOIN lineitems AS t2 ON t1.id = t2.invoiceid
WHERE (t2.invoiceid = t1.id)
GROUP BY t1.id) x
WHERE x.stagestatus='Complete' AND x.shippeddate BETWEEN '".$revenuefrom."' AND '".$revenueto."'
GROUP BY x.company ASC";
//SELECT statement pulls ALL STATUS history info by CUSTOMER.
$sql3 = "SELECT x.company, x.revenue, x.stagestatus, x.shippeddate, FORMAT(SUM(x.totprice), 2) as totalprice, FORMAT(SUM(x.sgtotquantity), 2) as totqty, FORMAT(SUM(x.sgtotalsqft), 2) as sgtotsqft, FORMAT(SUM(x.totprice)/SUM(x.sgtotalsqft), 2) as avgsqftrevenue, FORMAT(SUM(x.totprice)/SUM(x.sgtotquantity), 2) as avgunitrevenue FROM (SELECT t1.company, t1.revenue, t1.stagestatus, t1.shippeddate, t1.id, TRIM(LEADING '$' FROM t1.totalprice) AS totprice, t2.invoiceid, SUM(t2.quantity) AS sgtotquantity, SUM(t2.width * t2.height * t2.quantity ) /144 AS sgtotalsqft, (TRIM(LEADING '$' FROM t1.totalprice)/(SUM(t2.width * t2.height * t2.quantity ) /144)) as avgsqftrev, (TRIM(LEADING '$' FROM t1.totalprice) / SUM(t2.quantity)) AS avgunitrev
FROM invoices AS t1 INNER JOIN lineitems AS t2 ON t1.id = t2.invoiceid
WHERE (t2.invoiceid = t1.id)
GROUP BY t1.id) x
WHERE x.stagestatus IN (". implode(',', array_map(function($item) {return '"' . $item . '"'; }, $revenue_check)) .")
GROUP BY x.company ASC";
echo "<table id='revenueReportA' align='center' class='report_DT'>
<thead>
<tr>
<th>Customer</th>
<th>Total Revenue</th>
<th>Total SQ FT</th>
<th>AVG Revenue Per SQ FT</th>
<th>Total Number of Units</th>
<th>AVG Revenue Per Unit</th>
</tr>
</head>";
if (($revenueWO = 'Completed Workorders') and ($revenueWODate = 'All Workorders')) {
$result = $conn->query($sql1);
if ($result = $conn->query($sql1)) {
// fetch associative array
while ($row = $result->fetch_assoc()) {
echo "<tbody>";
echo "<tr>";
echo "<td>" . $row['company'] . "</td>";
echo "<td>" ."$". $row['totalprice'] . "</td>";
echo "<td>" . $row['sgtotsqft'] ." ". "ft<sup>2</sup>". "</td>";
echo "<td>" ."$". $row['avgsqftrevenue'] . "</td>";
echo "<td>" . $row['totqty'] . "</td>";
echo "<td>" ."$". $row['avgunitrevenue'] . "</td>";
echo "</tr>";
echo "</tbody>";
}//End table while.
echo "</table>";
echo "<BR>";
}//End table if.
//Free the result variable.
$result->free();
}//End table build if.
else if (($revenueWO = 'Completed Workorders') and ($revenueWODate = 'Workorder Date Range')) {
$result = $conn->query($sql2);
//Display daterange and table.
echo 'Displaying results for: '.$revenuefrom.' to '.$revenueto.'. '.'<BR><BR>';
if ($result = $conn->query($sql)) {
// fetch associative array
while ($row = $result->fetch_assoc()) {
echo "<tbody>";
echo "<tr>";
echo "<td>" . $row['company'] . "</td>";
echo "<td>" ."$". $row['totalprice'] . "</td>";
echo "<td>" . $row['sgtotsqft'] ." ". "ft<sup>2</sup>". "</td>";
echo "<td>" ."$". $row['avgsqftrevenue'] . "</td>";
echo "<td>" . $row['totqty'] . "</td>";
echo "<td>" ."$". $row['avgunitrevenue'] . "</td>";
echo "</tr>";
echo "</tbody>";
}//End table while.
echo "</table>";
echo "<BR>";
}//End table if.
//Free the result variable.
$result->free();
}//End table build if.
else if ($revenueWO = 'Workorder Status') {
$result = $conn->query($sql3);
if ($result = $conn->query($sql)) {
// fetch associative array
while ($row = $result->fetch_assoc()) {
echo "<tbody>";
echo "<tr>";
echo "<td>" . $row['company'] . "</td>";
echo "<td>" ."$". $row['totalprice'] . "</td>";
echo "<td>" . $row['sgtotsqft'] ." ". "ft<sup>2</sup>". "</td>";
echo "<td>" ."$". $row['avgsqftrevenue'] . "</td>";
echo "<td>" . $row['totqty'] . "</td>";
echo "<td>" ."$". $row['avgunitrevenue'] . "</td>";
echo "</tr>";
echo "</tbody>";
}//End table while.
echo "</table>";
echo "<BR>";
}//End table if.
//Free the result variable.
$result->free();
}//End table build if.
}//End Customer Table if statement.
//*************Revenue Category Table Type************//
else if ($revenueTblType = 'Revenue Category') {
//SELECT statement pulls ALL COMPLETED history info by REVENUE.
$sql4 = "SELECT x.company, x.revenue, x.stagestatus, x.shippeddate, FORMAT(SUM(x.totprice), 2) as totalprice, FORMAT(SUM(x.sgtotquantity), 2) as totqty, FORMAT(SUM(x.sgtotalsqft), 2) as sgtotsqft, FORMAT(SUM(x.totprice)/SUM(x.sgtotalsqft), 2) as avgsqftrevenue, FORMAT(SUM(x.totprice)/SUM(x.sgtotquantity), 2) as avgunitrevenue FROM (SELECT t1.company, t1.revenue, t1.stagestatus, t1.shippeddate, t1.id, TRIM(LEADING '$' FROM t1.totalprice) AS totprice, t2.invoiceid, SUM(t2.quantity) AS sgtotquantity, SUM(t2.width * t2.height * t2.quantity ) /144 AS sgtotalsqft, (TRIM(LEADING '$' FROM t1.totalprice)/(SUM(t2.width * t2.height * t2.quantity ) /144)) as avgsqftrev, (TRIM(LEADING '$' FROM t1.totalprice) / SUM(t2.quantity)) AS avgunitrev
FROM invoices AS t1 INNER JOIN lineitems AS t2 ON t1.id = t2.invoiceid
WHERE (t2.invoiceid = t1.id)
GROUP BY t1.id) x
WHERE x.stagestatus='Complete'
GROUP BY x.revenue ASC";
//SELECT statement pulls DATE RANGE COMPLETED history info by REVENUE.
$sql5 = "SELECT x.company, x.revenue, x.stagestatus, x.shippeddate, FORMAT(SUM(x.totprice), 2) as totalprice, FORMAT(SUM(x.sgtotquantity), 2) as totqty, FORMAT(SUM(x.sgtotalsqft), 2) as sgtotsqft, FORMAT(SUM(x.totprice)/SUM(x.sgtotalsqft), 2) as avgsqftrevenue, FORMAT(SUM(x.totprice)/SUM(x.sgtotquantity), 2) as avgunitrevenue FROM (SELECT t1.company, t1.revenue, t1.stagestatus, t1.shippeddate, t1.id, TRIM(LEADING '$' FROM t1.totalprice) AS totprice, t2.invoiceid, SUM(t2.quantity) AS sgtotquantity, SUM(t2.width * t2.height * t2.quantity ) /144 AS sgtotalsqft, (TRIM(LEADING '$' FROM t1.totalprice)/(SUM(t2.width * t2.height * t2.quantity ) /144)) as avgsqftrev, (TRIM(LEADING '$' FROM t1.totalprice) / SUM(t2.quantity)) AS avgunitrev
FROM invoices AS t1 INNER JOIN lineitems AS t2 ON t1.id = t2.invoiceid
WHERE (t2.invoiceid = t1.id)
GROUP BY t1.id) x
WHERE x.stagestatus='Complete' AND x.shippeddate BETWEEN '".$revenuefrom."' AND '".$revenueto."'
GROUP BY x.revenue ASC";
//SELECT statement pulls ALL STATUS history info by REVENUE.
$sql6 = "SELECT x.company, x.revenue, x.stagestatus, x.shippeddate, FORMAT(SUM(x.totprice), 2) as totalprice, FORMAT(SUM(x.sgtotquantity), 2) as totqty, FORMAT(SUM(x.sgtotalsqft), 2) as sgtotsqft, FORMAT(SUM(x.totprice)/SUM(x.sgtotalsqft), 2) as avgsqftrevenue, FORMAT(SUM(x.totprice)/SUM(x.sgtotquantity), 2) as avgunitrevenue FROM (SELECT t1.company, t1.revenue, t1.stagestatus, t1.shippeddate, t1.id, TRIM(LEADING '$' FROM t1.totalprice) AS totprice, t2.invoiceid, SUM(t2.quantity) AS sgtotquantity, SUM(t2.width * t2.height * t2.quantity ) /144 AS sgtotalsqft, (TRIM(LEADING '$' FROM t1.totalprice)/(SUM(t2.width * t2.height * t2.quantity ) /144)) as avgsqftrev, (TRIM(LEADING '$' FROM t1.totalprice) / SUM(t2.quantity)) AS avgunitrev
FROM invoices AS t1 INNER JOIN lineitems AS t2 ON t1.id = t2.invoiceid
WHERE (t2.invoiceid = t1.id)
GROUP BY t1.id) x
WHERE x.stagestatus IN (". implode(',', array_map(function($item) {return '"' . $item . '"'; }, $revenue_check)) .")
GROUP BY x.revenue ASC";
echo "<table id='revenueReportA' align='center' class='report_DT'>
<thead>
<tr>
<th>Revenue Category</th>
<th>Total Revenue</th>
<th>Total SQ FT</th>
<th>AVG Revenue Per SQ FT</th>
<th>Total Number of Units</th>
<th>AVG Revenue Per Unit</th>
</tr>
</head>";
if (($revenueWO = 'Completed Workorders') and ($revenueWODate = 'All Workorders')) {
$result = $conn->query($sql4);
if ($result = $conn->query($sql4)) {
// fetch associative array
while ($row = $result->fetch_assoc()) {
echo "<tbody>";
echo "<tr>";
echo "<td>" . $row['revenue'] . "</td>";
echo "<td>" ."$". $row['totalprice'] . "</td>";
echo "<td>" . $row['sgtotsqft'] ." ". "ft<sup>2</sup>". "</td>";
echo "<td>" ."$". $row['avgsqftrevenue'] . "</td>";
echo "<td>" . $row['totqty'] . "</td>";
echo "<td>" ."$". $row['avgunitrevenue'] . "</td>";
echo "</tr>";
echo "</tbody>";
}//End table while.
echo "</table>";
echo "<BR>";
}//End table if.
//Free the result variable.
$result->free();
}//End table build if.
else if (($revenueWO = 'Completed Workorders') and ($revenueWODate = 'Workorder Date Range')) {
$result = $conn->query($sql5);
//Display daterange and table.
echo 'Displaying results for: '.$revenuefrom.' to '.$revenueto.'. '.'<BR><BR>';
if ($result = $conn->query($sql5)) {
// fetch associative array
while ($row = $result->fetch_assoc()) {
echo "<tbody>";
echo "<tr>";
echo "<td>" . $row['revenue'] . "</td>";
echo "<td>" ."$". $row['totalprice'] . "</td>";
echo "<td>" . $row['sgtotsqft'] ." ". "ft<sup>2</sup>". "</td>";
echo "<td>" ."$". $row['avgsqftrevenue'] . "</td>";
echo "<td>" . $row['totqty'] . "</td>";
echo "<td>" ."$". $row['avgunitrevenue'] . "</td>";
echo "</tr>";
echo "</tbody>";
}//End table while.
echo "</table>";
echo "<BR>";
}//End table if.
//Free the result variable.
$result->free();
}//End table build if.
else if ($revenueWO = 'Workorder Status') {
$result = $conn->query($sql6);
if ($result = $conn->query($sql6)) {
// fetch associative array
while ($row = $result->fetch_assoc()) {
echo "<tbody>";
echo "<tr>";
echo "<td>" . $row['revenue'] . "</td>";
echo "<td>" ."$". $row['totalprice'] . "</td>";
echo "<td>" . $row['sgtotsqft'] ." ". "ft<sup>2</sup>". "</td>";
echo "<td>" ."$". $row['avgsqftrevenue'] . "</td>";
echo "<td>" . $row['totqty'] . "</td>";
echo "<td>" ."$". $row['avgunitrevenue'] . "</td>";
echo "</tr>";
echo "</tbody>";
}//End table while.
echo "</table>";
echo "<BR>";
}//End table if.
//Free the result variable.
$result->free();
}//End table build if.
}//End Revenue Category Table Type if.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Close the Database connection.
$conn->close();
At the very least, suggestions to get this code working will be greatly appreciated. Best outcome would be improved efficiency and safety.
Thank you!!
Aucun commentaire:
Enregistrer un commentaire