Parsing an array

General PHP Help
#1

Hi everyone!

I am trying to optimize a script where I perform a query of a [font=courier]jobs[/font] table. Jobs are recorded by [font=courier]department[/font] and by [font=courier]contractor[/font]. Currently, what I do is I query the jobs table, determine the unique department numbers and then perform another query for each department number and get the job data.

This is the skeleton of my script:

[php]
$getdepts = $db->get_departments($startdate, $enddate);
foreach ($getdepts as $dept) { // cycle through all departments with recorded time entries
// get job data for invoice
$getjobs = $db->get_job_data($invoice_department, $startdate, $enddate);
// process queried rows
foreach ($getjobs as $job_row) { // build a row for each job serviced
} // foreach getjobs
# display the department invoice
} // foreach getdepts
[/php]

As you can see, for every [font=courier]department[/font], my script queries the database for each job row. Even though this works, I am sure there is a more efficient way to do it, by querying all the jobs between the start and end dates into an array and then parsing through the array.

So far, this is what I have:

[php]
$jobs = $db->get_jobs($startdate, $enddate);
$depts = array();
foreach ($jobs as $job) {
$depts[] = $job[‘job_dept’];
}
$uniqueDepts = array_unique($depts);
[/php]

So now I have an array with all the unique [font=courier]department[/font] numbers, and this is where I am stuck.

How can I parse the entire array ([font=courier]$jobs[/font]) and echo all the rows grouped by [font=courier]department[/font] number?

Thanks!

#2

Why would you not let the database do the work and do it in sql? Post your database schema with some sample data.

#3

Hi Kevin and thanks for your reply!

Its not that I don’t want to. I am actually letting it do it right now and it is working.

I am using other scripts to generate invoices and reports (i.e., html2pdf and PHPExcel) and I am not 100% sure if my method of letting MySQL do the job of querying the table is the most efficient way to do it.

I will work on posting the schema and some sample data. There is a lot of confidential information in the data that I have to filter out, but how will it be more helpful to post the data, as insert statements or a result table?

#4

For posting the data, I meant an sql dump so I can import it and run querys on the DB. Using the db to do the work is always better than doing it in code.

#5

Gotcha! Here it is!

jobs.sql.zip (13.3 KB)

#6

You said you are already letting the db get your data. Post the query you are using.

Not sure what date field you are using for the start and end date. Using the job date, you would do the following:

[php]SELECT
j.id,
j.job_num,
j.facility_id,
j.job_dept,
j.interp_id,
j.job_date,
j.job_start,
j.job_end
FROM
jobs AS j
WHERE
j.job_date BETWEEN ‘2015-01-01’
AND ‘2015-02-10’
GROUP BY
j.id,
j.job_dept[/php]

#7

Thanks again Kevin!

Alright, so this is what I have:

  1. [font=courier]job_start[/font] and [font=courier]job_end[/font] are in DATETIME format. For my script:
    [php]// make sure that dates in the range are inclusive
    $startdate = date(“Y-m-d H:i:s”, strtotime($startdate . " 00:00:00"));
    $enddate = date(“Y-m-d H:i:s”, strtotime($enddate . " 23:59:59"));[/php]

  2. The first query is:

[php]SELECT DISTINCT job_dept
FROM jobs
WHERE job_date BETWEEN ? AND ?
ORDER BY job_dept ASC[/php]

  1. My “row building” query is:

[php]SELECT *
FROM jobs
WHERE job_dept = ?
AND job_date BETWEEN ? AND ?
ORDER BY job_date[/php]

I’m thinking that if I use “GROUP BY” as in your query, I won’t have to use my first query. But then how do I parse the resulting array in a way that I can create one invoice/report for each department?

#8

Are you trying to create multiple invoices at once or just one based on a particular dept?

[php]<?php
try
{
$sql = “SELECT
j.id,
j.job_num,
j.facility_id,
j.job_dept,
j.interp_id,
j.job_date,
j.job_start,
j.job_end
FROM
jobs AS j
WHERE
j.job_date BETWEEN ‘2015-01-01’
AND ‘2015-02-10’
GROUP BY
j.id,
j.job_dept”;

$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
}

catch (PDOException $e)
{
die($e);
}

foreach ($result as $row)
{
$data[$row[‘job_dept’]][] = “’{$row[‘id’]}’,’{$row[‘job_num’]}’”;
}

echo “

”;

print_r($data);

echo “
”;
?>[/php]

RESULTS:

[code]Array
(
[6440] => Array
(
[0] => ‘11603’,‘150209-56’
[1] => ‘11617’,‘150210-27’
)

[6904] => Array
    (
        [0] => '11632','150210-18'
    )

)[/code]

#9

WOW!

I am creating multiple invoices at once, and I think your [font=courier]foreach[/font] suggestion will do the trick! I will try it out and let you know!

Thanks!!

#10

Yep, it works perfectly and I am one step closer to my goal. So yes, having [font=courier]$data[/font] is a great help, thank you!

So how do I go about parsing [font=courier]$data[/font] to create one invoice/report per [font=courier]department[/font]?

#11

Rather than just give you all the answers, I will help you to help your self.

What is $data? It is a multidimensional array. So the question is really, “How do I parse a multi-dimensional array?”.

Take it from there…

#12

Fair enough; I wouldn’t expect more than being pointed in the right direction! Thank you for that!

I will post my findings back here for the benefit of others with the same question ;D

#13

Kevin, thank you very much for pointing me in the right direction!

I did some research, trial and error and I was able to figure it out. Final credit goes to the post at stackoverflow.com: /questions/10571346/php-two-dimensional-array-into-html, which ultimately helped me solve my problem and fit perfectly with your initial (and very good) suggestion.

This is the end result. For testing purposes, it worked well and this way it will make creating my invoices/reports much easier.

As you can see, I made a couple of changes to your suggestion that didn’t change the end result, but just made it easier from an visual perspective.

First, I changed the GROUP BY order in the query from “[tt]id, job_num[/tt]” to “[tt]job_num, id[/tt]”.

Second, I put the [tt]id[/tt] & [tt]job_num[/tt] data into an array, instead of just the values per each element of the department array. This makes it easier to switch around where and how I want the data displayed in the final invoice/report.

Again, these changes were just for visual result purposes, not for functionality.

I hope this helps someone else with the same “problem”.

Cheers!

[php]try
{
$sql = “SELECT
j.id,
j.job_num,
j.facility_id,
j.job_dept,
j.interp_id,
j.job_date,
j.job_start,
j.job_end
FROM
jobs AS j
WHERE
j.job_date BETWEEN ‘{$startdate}’
AND ‘{$enddate}’
GROUP BY
j.job_dept,
j.id”;

 $stmt = $pdo->prepare($sql);
 $stmt->execute();
 $result = $stmt->fetchAll();
 }

catch (PDOException $e)
{
die($e);
}

foreach ($result as $row) {
$data[$row[‘job_dept’]][] = array(‘id’=>$row[‘id’],‘job_num’=>$row[‘job_num’]);
}
echo “

”; print_r($data); echo “
”; // just for testing
$out = “”;
$out .= “”; // ugly, but good for visual reference
foreach($data as $key => $element) {
$out .= “<td colspan=“2”>Department:{$key}”;
$out .= “”;
foreach($element as $subkey => $subelement){
$out .= “”;
}
}
$out .= “
id job_num
{$subelement[‘id’]} {$subelement[‘job_num’]}
”;
echo $out;[/php]

Sample output:

[code]Array
(
[0000] => Array
(
[0] => Array
(
[id] => 7483
[job_num] => 140821-07
)

    )

[0031] => Array
    (
        [0] => Array
            (
                [id] => 7876
                [job_num] => 140815-21
            )

    )

[0353] => Array
    (
        [0] => Array
            (
                [id] => 7291
                [job_num] => 140731-18
            )

        [1] => Array
            (
                [id] => 7534
                [job_num] => 140826-16
            )

        [2] => Array
            (
                [id] => 7783
                [job_num] => 140813-36
            )

.
.
.
[/code]

[table][tr][td]Dept:0000[/td][/tr][tr][td]id[/td][td]job_num[/td][/tr][tr][td]7483[/td][td]140821-07[/td][/tr][tr][td]Dept:0031[/td][/tr][tr][td]id[/td][td]job_num[/td][/tr][tr][td]7876[/td][td]140815-21[/td][/tr][tr][td]Dept:0353[/td][/tr][tr][td]id[/td][td]job_num[/td][/tr][tr][td]7291[/td][td]140731-18[/td][/tr][tr][td]7534[/td][td]140826-16[/td][/tr][tr][td]7783[/td][td]140813-36[/td][/tr][/table]

Sponsor our Newsletter | Privacy Policy | Terms of Service