Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

PHP MYSQL ARRAY PAIRING

Posted on 2011-02-11
8
Medium Priority
?
871 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

610 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