how to put mysql generated values in an php array

Question

I need to generate an excel report populated from mysql database. I have already made it worked, which generates a report in excel.

But in one column of that report I need to populate the number of pending cases.

Database Details:

one h_id has many sl_id.

one sl_id has many pending cases listed as ENUM M,I.

I need to find the total number of rows of pending cases per sl_id.

Here is my code:

$value = $_POST['hospitalname'];

if($_POST["Submit"]=="Submit") {
        for ($i=0; $i<sizeof($value); $i++) {

$sql = "SELECT sl_id from mfb_servicelog where h_id LIKE ('".$value[$i]."')";

$result = mysql_query($sql);
$slid = array();
   while ($row = mysql_fetch_assoc($result)) {
        if (!in_array($row["sl_id"], $slid)) {
             $slid[] = $row["sl_id"];
        }
   }

   foreach($slid as $id) {

$query = "SELECT info_type from mfb_agent_status_details where info_type = 'M' and sl_id LIKE ('".$id."')";

$resultpending = mysql_query($query);
$num_rows = mysql_num_rows($resultpending);
echo "$num_rows\n";
        }
    }
}

Above code returns number of pending cases per sl_id together like

0 0 0 0 0 2 1 0 1 0 0 2 0 1 0 0 1 0 0 0 1 1 1 0

How to put these to an array so that I can export these to the excel sheet using below code

if ($result1 = mysql_query($query1) or die(mysql_error())) {
                while ($rowhos = mysql_fetch_row($result1)) {
                    $hospitalname[$i] = $rowhos;
                    if($i == 0) {
                        $col = 'D';
                    }
                    else{
                        $col = $k;
                    }
                    foreach($rowhos as $cell) {
                        $objPHPExcel->getActiveSheet()->setCellValue($col.$rowhosname,$cell);
                        $col++;
                    }
                    $rowhosname++;
                }
            }

Thanks. But can you please tell me... can I do it like $yourArray[$i]... so that I can return each result using echo $yourArray[0]; $yourArray[1]; etc. — PeeJay
– PeeJay, Commented Apr 25, 2015 at 17:07
yes, if you use it in a loop, with $i incrementing, the $youArray[$i] will equate to $yourArray[0]/$yourArray[1]/etc — Sean
– Sean, Commented Apr 25, 2015 at 17:09
if you see my code I already have a loop for ($i=0; $i<sizeof($value); $i++). Can I use this loop or I have to create another? — PeeJay
– PeeJay, Commented Apr 25, 2015 at 17:12

maalls · Accepted Answer · 2015-04-25 17:51:21Z

1

I am going to assume mfb_agent_status_details table has an id attribute (you can use an unique field in the table, maybe it's actually called asd_id for example):

$sql = "SELECT mfb_servicelog.sl_id, count(mfb_agent_status_details.*) as count FROM mfb_servicelog LEFT JOIN mfb_agent_status_details ON mfb_servicelog.sl_id = mfb_agent_status_details.sl_id WHERE  mfb_servicelog.h_id LIKE ('".$value[$i]."') GROUP BY mfb_servicelog.sl_id";

Now each resulting rows will a "count" attribute that you can insert into the excel.

edited Apr 25, 2015 at 17:51

answered Apr 25, 2015 at 17:12

maalls

7598 silver badges20 bronze badges

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

10 Comments

PeeJay Over a year ago

My Code: SELECT mfb_servicelog.sl_id, count(mfb_agent_status_details.id) as count from mfb_servicelog LEFT JOIN mfb_agent_status_details WHERE mfb_agent_status_details.sl_id LIKE mfb_servicelog.sl_id AND mfb_servicelog.h_id LIKE "42" GROUP BY mfb_servicelog.sl_id;

PeeJay Over a year ago

Error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE mfb_agent_status_details.sl_id LIKE mfb_servicelog.sl_id AND mfb_servicel' at line 1

maalls Over a year ago

I am not sure why. I modified the query, is it working better ? and does mfb_agent_status_details has an id attribute ?

maalls Over a year ago

just modified it again, I think it was the group by that was wrong.

PeeJay Over a year ago

ohh I am sorry..mfb_agent_status_details has no id attr. but it has sl_id

|

Alfred · Accepted Answer · 2015-04-25 18:03:23Z

0

You may try php's explode() function. The explode() function breaks a string into an array.

Try something like;

$str = "0 0 0 0 0 2 1 0 1 0 0 2 0 1 0 0 1 0 0 0 1 1 1 0";
$array = explode(" ",$str);

Hope this helps.

answered Apr 25, 2015 at 18:03

Alfred

21.5k64 gold badges175 silver badges258 bronze badges

Collectives™ on Stack Overflow

how to put mysql generated values in an php array

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related