Solved

PHP MYSQL ARRAY PAIRING

Posted on 2011-02-11
8
867 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
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.

 
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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how can i gnupg encrypt a PDF document? 12 61
mysqli_query() expects parameter 1 to be msqli, string given in 4 62
hbo knew my windows software 4 62
Log in through ID 5 17
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

839 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