Solved

PHP MYSQL ARRAY PAIRING

Posted on 2011-02-11
8
857 Views
Last Modified: 2012-05-11
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
Comment
Question by:stuckintexas
  • 5
  • 3
8 Comments
 
LVL 4

Expert Comment

by:JayDiablo
ID: 34874902
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
 

Author Comment

by:stuckintexas
ID: 34876209
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
 

Author Comment

by:stuckintexas
ID: 34876224
* 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
 
LVL 4

Expert Comment

by:JayDiablo
ID: 34876229
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:stuckintexas
ID: 34876288
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
 

Author Comment

by:stuckintexas
ID: 34876304
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
 
LVL 4

Accepted Solution

by:
JayDiablo earned 500 total points
ID: 34876419
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
 

Author Closing Comment

by:stuckintexas
ID: 34903005
JayDiablo, thanks for your help with this... sry, first chance I have had to reply.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now