PHP SQL where statement with loop variable

Question

I am having problems with the retrieval of data from my database using a where clause with a for loop variable. this codes currently can retrieve the data from the database but however the values retrieved from the database stockpiles. at the number 1 in the for loop its still fine. but when it goes to the next one. the data gathered is the combination of 1 and 2. when it finally hit the end of the loop all of the data is displayed. Which part of the codes must be edited to change the display of data to non-cumulative

<?php

$sectorcount = $row_SectorCount['COUNT(*)'];
//number of rows in database
$spendingname= array();
$spendingpercent= array();
$spendingid= array();

?>
// Add some data to the details pie chart
options_detail_1.series.push({ name: 'Tax Spending Detail', data: [] });

$(document).ready(function() {
chart_main = new Highcharts.Chart(options_main);
chart_detail_1 = new Highcharts.Chart(options_detail_1);
})

function push_pie_detail(sectorid) {
switch(sectorid)
{
<?php 

 for ($i=1; $i<=$sectorcount; $i++)
    {
    echo    "case 'sector".$i."':" ,
            "setTimeout", "(" , '"chart_detail_1.series[0].remove(false)", 1000);' ,
            "chart_detail_1.addSeries(options_detail_1, false);" , 
            "chart_detail_1.series[1].setData( [";

            mysql_select_db($database_conn2, $conn2);
            $query_Spending = "SELECT CONCAT(spending.SectorID,     spending.ExpenditureID) AS 'SpendingID',
            expenditure.ExpenditureName, spending.SpendingPercent, spending.SectorID
            FROM spending
            INNER JOIN expenditure ON spending.ExpenditureID = expenditure.ExpenditureID
            WHERE spending.SectorID = '.$i.'";
            $Spending = mysql_query($query_Spending, $conn2) or die(mysql_error());
            $totalRows_Spending = mysql_num_rows($Spending);
            while($row_Spending = mysql_fetch_assoc($Spending))
            {
            $spendingname[] = $row_Spending['ExpenditureName'];
            $spendingpercent[] = $row_Spending['SpendingPercent'];
            $spendingid[]= $row_Spending['SpendingID'];
            }
            mysql_free_result($Spending);

            $a = 0;

            foreach ( $spendingid as $sgi => $sgid)
            {
            if ($a > 0) echo ', '; // add the comma

            echo    "{
                name: '$spendingname[$sgi]',
                y: ". $spendingpercent[$sgi] * $tax .",
                id: ' $sgid',
                    }";

            $a++;   
            }

            echo    "], false);" ,
                    "chart_detail_1.redraw();",
                    "break;";
            }       


            ?>
            default:
            break;
        }

}

score 1 · Accepted Answer · 2012-01-04T02:45:53.880

1

You need to wrap your variable in 'x' single quotes in your where clause, otherwise it will think you mean id = true, which is true for all of them =) I had the same problem recently

EDIT: So instead of saying where spending.SectorID = $i, you should have 'somevalue', just make a new variable $example = "'" . $i . "'", and use that variable in the where clause

I wrote a function that takes text and wraps it in single quotes just for this situation

edited Jan 04 '12 at 02:45

answered Jan 04 '12 at 02:37

how you managed to solve it? im really new to php please guide me :D – Jervis Chionh Jan 04 '12 at 02:39
its not working :( the pie chart im trying to display is still displaying almost everything cumulatively – Jervis Chionh Jan 04 '12 at 02:53
then I would point to a problem with the pie chart and not the sql or loop, and I dont have knowledge of the pie chart functionality yet – Jan 04 '12 at 03:03

score 0 · Answer 2 · answered Jan 04 '12 at 01:18

0

Move your $spendingname = array(); and other array definitions inside the loop.

answered Jan 04 '12 at 01:18

Niet the Dark Absol

320,036
81
464
592

PHP SQL where statement with loop variable

2 Answers2

Linked