• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 890
  • Last Modified:

PHP MYSQL ARRAY PAIRING

I have a few arrays, a parent and some children which have different sizes. I'm trying to render a table rows with a children in the adjacent if their key match the parent value.. example:

$parents =  02/01/2010, 02/02/2010,02/03/2010
$child1 =   02/03/2010=>c1
$child2 = 02/01/2010=>c2, 02/02/2010=>c2,02/03/2010=>c2
$child3 = 02/01/2010=>c3, 02/02/2010=>c3

the desired output should be as follows:

Thanks in advance... beginner here.


<table width="100%" border="1" cellspacing="1" cellpadding="1">
  <tr>
    <td>Date</td>
    <td>Child 1</td>
    <td>Child 2</td>
    <td>Child3</td>
  </tr>
  <tr>
    <td>02/01/2010</td>
    <td>n/a</td>
    <td>c2</td>
    <td>c3</td>
  </tr>
  <tr>
    <td>02/02/2010</td>
    <td>n/a</td>
    <td>c2</td>
    <td>c3</td>
  </tr>
  <tr>
    <td>02/03/2010</td>
    <td>c1</td>
    <td>c2</td>
    <td>n/a</td>
  </tr>
</table>

Open in new window

0
stuckintexas
Asked:
stuckintexas
  • 5
  • 3
1 Solution
 
JayDiabloCommented:
This is probably easier if you setup your children to be in a single array, rather than a variable for each one (unless there are always only 3 children, but I assume that's not the case).

I don't know how you're building those variables now, but here's how the array could look when completed.

<?php

$parents =  array('02/01/2010', '02/02/2010', '02/03/2010');

$children = array(
    'Child 1' => array(
        '02/03/2010' => 'c1'
    ),
    'Child 2' => array(
        '02/01/2010' => 'c2',
        '02/02/2010' => 'c2',
        '02/03/2010' => 'c2'
    ),
    'Child 3' => array(
        '02/01/2010' => 'c3',
        '02/02/2010' => 'c3',
    )
);

?>

Open in new window


This makes it easier for you to loop through all of the available children, which may vary in quantity.

Then the code that builds the table could look something like this:

<table width="100%" border="1" cellspacing="1" cellpadding="1">
  <tr>
    <td>Date</td>
    <?php foreach ($children as $child => $data): ?>
      <td><?php echo $child; ?></td>
    <?php endforeach; ?>
  </tr>
  <?php foreach ($parents as $date): ?>
    <tr>
      <td><?php echo $date; ?></td>
      <?php foreach ($children as $child => $data): ?>
        <td><?php if (isset($data[$date])) { echo $data[$date]; } else { echo 'n/a'; } ?></td>
      <?php endforeach; ?>
    </tr>
  <?php endforeach; ?>
</table>

Open in new window

0
 
stuckintexasAuthor Commented:
Thanks Jay, this is exactly what I was looking for. In working with it I am however struggling to to construct those arrays as you suggested from mysql results. Can you assist further?

The SQL results look like this:

Date, Property, Value which correspond to date = parent, property is child, date is child key and value is child value.

if you an if property == 'property' to create arrays of each property/child... then trying to piece those together into your example... first time I'm doing this so seems very convuluted... whats best practice for doing this?

Thanks in advance and ofcourse for your earlier response.
0
 
stuckintexasAuthor Commented:
* I'm using if property == 'property' to create arrays of each property/child... then trying to piece those together into your example... first time I'm doing this so seems very convoluted... whats best practice for doing this?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JayDiabloCommented:
Could you provide a sample of what the table structure looks like for this data?  and possibly what a couple of rows of data would look like?  (you can use fake data for the rows example)
0
 
stuckintexasAuthor Commented:
Jay,

The following is sql table, idea is to report on all dates, all properties and all values. Where properties have no values for a given date, we would show empty field. I was using a distinct date date and then an IN array:

1/20/2010      p1      100
1/21/2010      p1      200
1/20/2010      p2      150
1/21/2010      p2      300
1/22/2010      p2      350
1/23/2010      p2      150
1/24/2010      p2      211
1/21/2010      p3      145
1/22/2010      p3      215
1/23/2010      p3      50

0
 
stuckintexasAuthor Commented:
desired output would look like this I guess:

Date      Property p1      Property p2      Property p3
1/20/2010      100      150      n
1/21/2010      200      300      145
1/22/2010      n      350      215
1/23/2010      n      150      50
1/24/2010      n      211      n
0
 
JayDiabloCommented:
Ok, I think this will be a pretty simple solution.

When you query on the data, I'd sort by the date column in whichever order you want the dates to appear in the output.  I don't think you need to do anything too crazy to get the data (as long as the data is returned as you have it in the above example).

Depending on what library you're using to get the data, you'll want to be able to loop through the result (each iteration of the loop will be a row from the table).  Each row that you get back will have the three columns above.

Example with mysql_fetch_array:
$parents = array();
$children = array();

while ($row = mysql_fetch_array($result)) {
  // Insert date into parents array, but don't insert a value if it already exists
  if (array_search($row['date_column'], $parents) === false) {
    $parents[] = $row['date_column'];
  }

  // Avoids notices if not an array yet
  if (!isset($children[$row['property_name_column']]) {
    $children[$row['property_name_column']] = array();
  }

  // Add the value tied to the date
  $children[$row['property_name_column']][$row['date_column']] = $row['property_value_column'];
}

Open in new window


That should give you a good start.  Modify as needed to match your querying method.
0
 
stuckintexasAuthor Commented:
JayDiablo, thanks for your help with this... sry, first chance I have had to reply.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now