Unexpected variable syntax in php when trying to create variables in mysql

Question

Have a small web app that will input form data, and calculate 3 values based on query entered, pretty much just an aggregation of columns that meet specific criteria. The data enters in the database fine, but I'm trying to create a custom search that will set table variables according to selected date in query which is a php variable that is posted to the html form. I'm trying to use this post php variable as input for the date ranges in the search query. Currently am getting this error with $result.

my 'action.php' file has the $conn object

<?php

include 'action.php';

$begindate = isset($_POST['start-date']) ? $_POST['start-date']: "";
$enddate = isset($_POST['end-date']) ? $_POST['end-date']: "";

$total = 
"
SET @total = (SELECT sum(`fl_built`) FROM `records` where `date` = $begindate);
select @total as 'total_fl_built';
SET @qc = (select sum(`fl_qc`) from `records` where `date` = $enddate);
select @qc as 'total_qc_built';
SET @percent = round((@qc/@total) * 100); 
select @percent as 'percentage';
"

$result = $conn->query($total);

echo "<table>";

if($result->num_rows>0){
    while($row = $result->fetch_assoc()){

echo "<tr><td>" . $row['date'] . "</td><td>" . $row['hour'] . "</td><td>" . $row['pnp_kdt'] . "</td><td>" . $row['fl_built'] . "</td><td>" . $row['fl_qc'] . $row['total_fl_built'] . "</td><td>" . $row['total_fl_qc'] . "</td><td>" . $row['percentage'] . "</td><td>" . "</td></tr>" ;
    }
}

echo "</table>";

$conn->close();



?>

Use not SET but SELECT INTO. Also - you may obtain all 3 values in one query without variables usage. — Akina, Jul 06 '22 at 17:16
1) You forgot a semi-colon after your $total query. 2) Dates *must* be quoted, but you really should be using [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead so you don't need to worry about quoting values. — aynber, Jul 06 '22 at 17:55
Also, your statement won't do what you think. You can't chain select statements like that and expect it to work. — aynber, Jul 06 '22 at 17:56
Then you come to your while loop, where the majority of the keys won't exist because you're not grabbing them. — aynber, Jul 06 '22 at 18:00
how would I include the rest of the columns I want to select in front of the columns you listed? @Akina — , Jul 06 '22 at 19:01

score 0 · Accepted Answer · answered Jul 06 '22 at 17:23

SELECT total_fl_built, 
       total_qc_built, 
       round((total_qc_built/total_fl_built) * 100) percentage
FROM ( SELECT sum(`fl_built`) total_fl_built 
       FROM `records` 
       where `date` = $begindate ) total
JOIN ( select sum(`fl_qc`) total_qc_built 
       from `records` 
       where `date` = $enddate ) qc

Unexpected variable syntax in php when trying to create variables in mysql

1 Answers1