CODEWITHSUNDEEP
phpmysqlsqlarraysfunction
Jervis Chionh
Jervis Chionh

Reputation: 127

PHP Multiarray LOOP WHERE CLAUSE

Hey guys i would like to repeat this statement 13 times by change this statement within a loop or something. "WHERE spending.SectorID = 1,2,3,4,5,6,7,8,9,10,11,12,13" how can i successfully do it without typing this code 12 more times. and i would to display the data individually though~ likewhere sectorid = 1; belong to a table with a button to initiate that particular query

<?php
$spendingname= array();
$spendingpercent = array();
$spendingid = array();

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 = 1";
$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);
?>

Upvotes: 0

Views: 562

Answers (8)

Jervis Chionh
Jervis Chionh

Reputation: 127

<?php
$sectorcount = $row_SectorCount['COUNT(*)'];
//number of rows in database
mysql_select_db($database_conn2, $conn2);
for($x=1; $x<=$sectorcount; $x++) 
{
${'spendingname'.$x} = array();
${'spendingpercent'.$x} = array();
${'spendingid'.$x} = array();

$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 = $x";
$Spending = mysql_query($query_Spending, $conn2) or die(mysql_error());
$totalRows_Spending = mysql_num_rows($Spending);

while($row_Spending = mysql_fetch_assoc($Spending))
{
${'spendingname'.$x}[] = $row_Spending['ExpenditureName'];
${'spendingpercent'.$x}[] = $row_Spending['SpendingPercent'];
${'spendingid'.$x}[]= $row_Spending['SpendingID'];
}
mysql_free_result($Spending);
}                   
?>

i managed to solve it but however spendingname is the only one unable to retrieve. spendingname is the only string where else percent and id and all integer. is that why this problem occurs

Upvotes: 0

tkone
tkone

Reputation: 22728

WHERE spend.SectorID > 0 AND spending.SectorID < 13

Upvotes: 0

Shankar Narayana Damodaran
Shankar Narayana Damodaran

Reputation: 68486

    <?php
    $spendingname= array();
    $spendingpercent = array();
    $spendingid = array();

    mysql_select_db($database_conn2, $conn2);
    for($x=1;$x<14;$x++)
    {
    $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 = $x";
    $Spending = mysql_query($query_Spending, $conn2) or die(mysql_error());
    $totalRows_Spending = mysql_num_rows($Spending);
    while($row_Spending = mysql_fetch_assoc($Spending))
    {
    $spendingname[$x] = $row_Spending['ExpenditureName'];
    $spendingpercent[$x] = $row_Spending['SpendingPercent'];
    $spendingid[$x]= $row_Spending['SpendingID'];
    }
    mysql_free_result($Spending);
    }

//To access and print all elements.
for($x=1;$x<count($spendingname);$x++)
{
echo "The value for query $x";
echo $spendingname[$x]."  ".$spendingpercent[$x]."  ".$spendingid[$x]."<br><br><br>";
}

?>

Upvotes: 1

siberiantiger
siberiantiger

Reputation: 305

If you want to loop a piece of code a specific number of times, you could use a for loop. Here is a PHP script that can be looped 13 times.

$loops = 13;
for ($i = 1; $loops <= $i; $i++) {
    //Your code//
}

You should also replace "WHERE spending.SectorID = 1,2,3,4,5,6,7,8,9,10,11,12,13" with "WHERE spending.SectorID = $i", and this increment what your SQL statement is searching for each time the code is looped.

Hope this helps.

Upvotes: 0

fkerber
fkerber

Reputation: 1042

You could use a preparedStatement with parameters and then, you could loop the executing of the query as well as the result retrieval.

But I don't understand the reason to do so unless you want do something different with different results. Otherwise, why don't you use a

WHERE spending.SectorID BETWEEN 1 AND 13 ORDER BY spending.SectorID

?

Upvotes: 1

user895378
user895378

Reputation:

One thing you DON'T want to do is make 13 database queries when a single query will suffice. Query once with the following WHERE clause, then iterate over the results:

WHERE spending.SectorID <= 13 ";

-OR-

WHERE spending.SectorID IN (1,2,3,4,5,6,7,8,9,10,11,12,13)";

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191749

Use an IN clause. Specifically, you can do something like WHERE spending.SectorID IN ('" . implode("','", range(1,13)) ...

Upvotes: 0

James C
James C

Reputation: 14149

You just need to wrap the main portion of the script in:

for ($n in range(1,13)) {

or

for ($n=1; $n<=13; $n++) {

and replace the constant 1 with $n

edit: or depending on how you want to present the data finally you might be able to modify the SQL from:

WHERE spending.SectorID = 1

to

WHERE spending.SectorID >= 1 AND spending.SectorID <= 13

or

WHERE spending.SectorID IN (1,2,3,4,5,6,7,8,9,10,11,12,13)

(because of the way that MySQL's optimizer works both should be as efficient)

Upvotes: 1

Related Questions