Multi dimensional array value with SELECT BETWEEN - PHP MYSQL

Question

Hi basically my aim is to get total weight count and its sum form specific range , like :

0.1 to 0.5 or 0.6 - 1.0 or 1.01 - 1.50 .... so on on...

Hence for each range i have to write separate select query ? like below :

SELECT count(*),sum(wght) FROM PROD WHERE wght BETWEEN 0.1 AND 0.5
SELECT count(*),sum(wght) FROM PROD WHERE wght BETWEEN 0.6 AND 1.0
SELECT count(*),sum(wght) FROM PROD WHERE wght BETWEEN 1.01 AND 1.5

It may have many range like above its not fixed.

Hence result i want is as below :

0.1 - 0.5    = 3 / 9.4   - ( its total count / sum of weight )
0.6 - 1.0    = 2 / 4.5
1.01 - 1.5   = 0 / 0

... so on and on....

Hence i taught to keep that range in an multidimensional array and pass to select query in for loop / for each as below :

$info = array("0" =>array("a1"=>0.1,"a2"=>0.5),
              "1" => array("b1"=>0.6,"b2"=>1.0),
              "2" => array("c1"=>1.01,"c2"=>1.5),
              "3" => array("d1"=>1.51,"d2"=>2.0)
             );


echo"<table>";
 foreach($info as $row) {
  echo('<tr>');
  foreach($row as $cell) {
    echo('<td>' . $cell . '</td>');

   $sql = "SELECT count(*),sum(wght) FROM PROD WHERE wght BETWEEN ".$cell." and ??????";

  }
  echo('</tr>');
} 
echo "</table>";

AS ITS taking each single value in SELECT value , when i echo sql select i see 8 times select query , where as i should get only 4 select query as per ranges

how i can work out with BETWEEN

IF ANY OTHER SUGGESTION TO GET BELOW RESULT PLEASE LET ME KNOW :

0.1 - 0.5    = 3 / 9.4   - ( its total count / sum of weight )
0.6 - 1.0    = 2 / 4.5
1.01 - 1.5   = 0 / 0

... so on and on....

Richard Parnaby-King · Accepted Answer · 2015-04-20 10:49:10Z

2

I would suggest rewriting your foreach:

echo"<table>";
foreach($info as $row) {
    echo('<tr>');
    echo "<td>SELECT count(*),sum(wght) FROM PROD WHERE wght BETWEEN ".implode(' and ', $row) . "</td>";
    echo('</tr>');
}
echo "</table>";

implode will combine the elements of the array into a string with the word and between each array element.

For example:

array("a1"=>0.1,"a2"=>0.5)

becomes:

0.1 and 0.5

The above output is:

SELECT count(*),sum(wght) FROM PROD WHERE wght BETWEEN 0.1 and 0.5
SELECT count(*),sum(wght) FROM PROD WHERE wght BETWEEN 0.6 and 1
SELECT count(*),sum(wght) FROM PROD WHERE wght BETWEEN 1.01 and 1.5
SELECT count(*),sum(wght) FROM PROD WHERE wght BETWEEN 1.51 and 2

edited Apr 20, 2015 at 10:49

answered Apr 20, 2015 at 9:46

Richard Parnaby-King

15k11 gold badges75 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user3209031 Over a year ago

hi richard , thanks , i tried as you said , but result i got as " SELECT count(),sum(wght) FROM PROD WHERE wght BETWEEN " and that to 8 time - SELECT count(),sum(wght) FROM PROD WHERE wght BETWEEN

user3209031 Over a year ago

i think implode is not working , also i should get SELECT QUERY ONLY 4, but i am getting 8 times select query in loop

user3209031 Over a year ago

hi richard can you help me

Richard Parnaby-King Over a year ago

Fixed answer. I had put the implode in the wrong foreach.

user3209031 Over a year ago

thanks :) ... but can i get result as .... 0.1 - 0.5 = 3 / 9.4 - ( its total count / sum of weight ) ??? ... because i m not getting - in between 0.1 0.5

|

Collectives™ on Stack Overflow

Multi dimensional array value with SELECT BETWEEN - PHP MYSQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related