Using Variables in MYSQL Query not working. Help please!

madjack87 · May 26, 2011

I am trying to run a mysql query to get the sum of a column.

When I type out the column name it works. When the column name is stored in a variable it does not seem to work.

<?php
$total = $_GET['total'];
if ($order != "" && $total != ""){	
$query2 = "SELECT SUM('.$type2.') FROM customers WHERE sched=1"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
echo "Total ". " = $". $row["SUM('.$type2.')"];
echo "<br />";
}
}
?>

Any Help would be appreciated.

Maq · May 26, 2011

Where is $type2 defined?

madjack87 · May 26, 2011

<?php
if ($order == ""){
$order2 = "";
$costing = "";
}
if ($order == "sGrading"){
$type = "Grading";	
$costing = "";
}elseif ($order == "sPaving"){
$type = "Paving";	
$type2 = "paving_price";
}elseif ($order == "sPatch"){
$type = "Patching";
$costing = "";
}elseif ($order == "sMaint"){
$type = "Sealing";
$type2 = "sealing_price";
}elseif ($order == "sStriping"){
$type = "Striping";
$costing = "";
}else {
$type="All Jobs";
$costing = "";
}?>

When I echo $type2 It's correct.

xyph · May 26, 2011

Try this

<?php
$total = $_GET['total'];
if ($order != "" && $total != ""){	
$query2 = "SELECT SUM('.$type2.') as total FROM customers WHERE sched=1"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
echo 'Total = $'.$row['total'];
echo '<br />';
}
}
?>

madjack87 · May 26, 2011

Try this

<?php
$total = $_GET['total'];
if ($order != "" && $total != ""){	
$query2 = "SELECT SUM('.$type2.') as total FROM customers WHERE sched=1"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
echo 'Total = $'.$row['total'];
echo '<br />';
}
}
?>

When I do that get "$ ". When I do it the original way I get a "$0". When I type in paving_price in place of $type2 I get the correct dollar amount

madjack87 · May 26, 2011

Here is more of the code if it helps.

<body>
<?php include ("includes/nav.php")?>
<?php 
$order = $_GET['order'];
$order2 = "AND " . $order . ">= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)" .  " ORDER BY " . $order;
if ($order == ""){
$order2 = "";
$costing = "";
}
if ($order == "sGrading"){
$type = "Grading";	
$costing = "";
}elseif ($order == "sPaving"){
$type = "Paving";	
$type2 = "paving_price";
}elseif ($order == "sPatch"){
$type = "Patching";
$costing = "";
}elseif ($order == "sMaint"){
$type = "Sealing";
$type2 = "sealing_price";
}elseif ($order == "sStriping"){
$type = "Striping";
$costing = "";
}else {
$type="All Jobs";
$costing = "";
}
?>

<div id="estimatetable">
<a href="scheduled.php?total=total&order=<?php echo $order?>">Show Total</a><br />
<?php
$total = $_GET['total'];
if ($order != "" && $total != ""){	
$query2 = "SELECT SUM('.$type2.') FROM customers WHERE sched=1"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
echo "Total ". " = $". $row["SUM('.$type2.')"];
echo "<br />";
}
}
?>
<a href="scheduled.php?order=sGrading">Grading</a>
<a href="scheduled.php?order=sPaving">Paving</a>
<a href="scheduled.php?order=sPatch">Patching</a>
<a href="scheduled.php?order=sMaint">Maintenance</a>
<a href="scheduled.php?order=sStriping">Striping</a>
<h4>Scheduled (<?php echo $type; ?>)</h4>
<table border="0" cellspacing="0" cellpadding="4">
<tr align="left">
<td></td>
    <td><h3>Company/Property</h3></td>
    <td><h3>Contact</h3></td>
    <td><h3>Property Address</h3></td>
    <td><h3>City</h3></td>
    <td width="123"><h3>Phone</h3></td>
    <td><h3>Date</h3></td>
    <td><h3>Job Notes</h3></td>
</tr>
<?php
$result = mysql_query("SELECT * FROM customers WHERE stage = 2 AND sched=1 $order2");

while ($row = mysql_fetch_array($result)){
$companyName = $row['companyName'];
$propertyName = $row['title'];
if ($companyName != "" && $propertyName != "")
$companyName = $companyName . "<br />";
else $companyName = $companyName;
$id = $row['custID'];
$temp = "?id=" . $id;
$user = $_SESSION['myusername'];
$timestamp = strtotime($row["$order"]);
  echo"<tr valign=\"top\" align=\"left\">";
echo"<td>" . "<a href='process_sched.php$temp' target='_blank'>" . "S" . "</td>";
echo"<td>" . $companyName . $propertyName . "</td>";
echo"<td>" . $row ['firstName'] . "<br />" . $row ['lastName'] . "</td>";
echo"<td>" . $row ['propertyAddress'] . "</td>";
echo"<td>" . $row ['propertyCity'] . "</td>";
echo"<td>" . "P: " . $row ['phone'] . "<br />" . "C: " . $row ['cell'] . "</td>";
echo"<td>" . date("m/d", $timestamp) . "</td>";
echo"<td>" . $row ['jobNotes'] . "</td>";
  echo"</tr>";
}
?>
</table>
</div>
</body>

xyph · May 26, 2011

You'll probably find the issue with a little debugging

<?php
$total = $_GET['total'];
if ($order != "" && $total != ""){
echo "type2 - $type2<br />";
$query2 = "SELECT SUM('.$type2.') as total FROM customers WHERE sched=1";
echo "query - $query2<br />";
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
echo 'Total = $'.$row['total'];
echo '<br />';
}
}
?>

Maq · May 26, 2011

Echo $query2 out and see exactly what the query is.

madjack87 · May 26, 2011

Echo $query2 out and see exactly what the query is.

SELECT SUM('.sealing_price.') FROM customers WHERE sched=1

Maq · May 26, 2011

Take out the periods:

$query2 = "SELECT SUM('$type2') as total FROM customers WHERE sched=1";

$type2 will interpolate because the primary string is in double quotes.

xyph · May 26, 2011

Take out the dots.

Odd I didn't see that earlier.

madjack87 · May 26, 2011

Take out the periods:

$query2 = "SELECT SUM('$type2') as total FROM customers WHERE sched=1";

<?php
$total = $_GET['total'];
if ($order != "" && $total != ""){	
$query2 = "SELECT SUM($type2) FROM customers WHERE sched=1"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
echo "Total ". " = $". $row['SUM($type2)'];
echo "<br />";
}
}
echo $query2;
?>

The above statement gives me

SELECT SUM(sealing_price) FROM customers WHERE sched=1

and my total is still null

<?php
$total = $_GET['total'];
if ($order != "" && $total != ""){	
$query2 = "SELECT SUM('$type2') FROM customers WHERE sched=1"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
echo "Total ". " = $". $row['SUM('$type2')'];
echo "<br />";
}
}
echo $query2;
?>

The above statement gives me

SELECT SUM('sealing_price') FROM customers WHERE sched=1

and my total is still null

xyph · May 26, 2011

The issue might be in your data.

A typo somewhere?

What happens when $type2 = 'paving_price'

Maq · May 26, 2011

Are you sure you have values in your db WHERE sched=1? The only time SUM returns null is when the result set is empty.

madjack87 · May 26, 2011

Take out the dots.

Odd I didn't see that earlier.

My original code before I posted here didnt have the periods or the single quotes around the variable. But after searching for hours I found using concatenation as a possible solution which obviously did not work. So Hopefully you geniuses can help me out.

madjack87 · May 26, 2011

<?php
$total = $_GET['total'];
if ($order != "" && $total != ""){	
$query2 = "SELECT SUM(sealing_price) FROM customers WHERE sched=1"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
echo "Total ". " = $". $row['SUM(sealing_price)'];
echo "<br />";
}
}
echo $query2;
?>

If I use the code above i get.

Total = $47487

SELECT SUM(sealing_price) FROM customers WHERE sched=1

Maq · May 26, 2011

Change these lines to (I think your $row line wasn't correct):

$query2 = "SELECT SUM($type2) AS total FROM customers WHERE sched=1";

and

echo "Total ". " = $". $row['total'];

xyph · May 26, 2011

You're still using $row['SUM($type2)'] which I'd imagine is WRONG.

Use

<?php
$total = $_GET['total'];
if ($order != "" && $total != ""){
$query2 = "SELECT SUM(`$type2`) as `order_total` FROM `customers` WHERE sched=1";
echo "query - $query2<br />";
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
echo 'Total = $'.$row['order_total'];
echo '<br />';
}
}
?>

Pointing out the same error twice is frustrating. Please pay attention to every solution given.

madjack87 · May 26, 2011

<?php
$total = $_GET['total'];
if ($order != "" && $total != ""){	
$query2 = "SELECT SUM($type2) FROM customers WHERE sched=1"; 	 
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
echo "Total ". " = $". $row['total'];
echo "<br />";
}
}
echo $query2;
?>

When I use the above code I get:

Total = $

SELECT SUM(sealing_price) FROM customers WHERE sched=1

Maq · May 26, 2011

Missing the AS total:

$query2 = "SELECT SUM($type2) AS total FROM customers WHERE sched=1";

madjack87 · May 26, 2011

You're still using $row['SUM($type2)'] which I'd imagine is WRONG.

Use
<?php
$total = $_GET['total'];
if ($order != "" && $total != ""){
$query2 = "SELECT SUM(`$type2`) as `order_total` FROM `customers` WHERE sched=1";
echo "query - $query2<br />";
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
echo 'Total = $'.$row['order_total'];
echo '<br />';
}
}
?>
Pointing out the same error twice is frustrating. Please pay attention to every solution given.

Sorry I didnt realize you posted this twice.

my results are as follows

query - SELECT SUM(`sealing_price`) as `order_total` FROM `customers` WHERE sched=1

Total = $47487

SO it did work!

Thanks for your help..

What does "as 'order_total'" do exactly?

madjack87 · May 26, 2011

Missing the AS total:

$query2 = "SELECT SUM($type2) AS total FROM customers WHERE sched=1";

Thanks for your help. I didnt realize why I needed AS total. I guess I am confused because if I type out the field name it worked fine but as a variable it did not.

Anyways I am grateful for your help.

Thanks again

Maq · May 26, 2011

Thanks for your help. I didnt realize why I needed AS total. I guess I am confused because if I type out the field name it worked fine but as a variable it did not.

What does "as 'order_total'" do exactly?

You don't absolutely need it. It's just an alias to reference the column easier for brevity purposes.

madjack87 · May 26, 2011

Thanks for your help. I didnt realize why I needed AS total. I guess I am confused because if I type out the field name it worked fine but as a variable it did not.

What does "as 'order_total'" do exactly?

You don't absolutely need it. It's just an alias to reference the column easier for brevity purposes.

I see. Kind of like storing it in a variable instead of having to type SUM['$var'] just like when you store $var = $_GET['name']

Maq · May 26, 2011

Thanks for your help. I didnt realize why I needed AS total. I guess I am confused because if I type out the field name it worked fine but as a variable it did not.

What does "as 'order_total'" do exactly?

You don't absolutely need it. It's just an alias to reference the column easier for brevity purposes.

I see. Kind of like storing it in a variable instead of having to type SUM['$var'] just like when you store $var = $_GET['name']

You could think of it that way

Sign In

Using Variables in MYSQL Query not working. Help please!

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information