Please help, can I do this with arrays?

[gregsmith]

Hi there,

I have two arrays with different column names, types and lengths.  They do however share a column called order_num which is the order that I want to display the results in.

eg.

[code]array 1:

cats | order_num
cat1    1
cat2    4
cat3    6[/code]

[code]array 2:

dogs | breed | order_num
dog1  breed1  2
dog2  breed2  3
dog3  breed3  5
dog4  breed4  7[/code]

I want to list the items by order_num so it'll be:

[code]cat1 (order_num = 1)
dog1 (order_num = 2)
dog2 (order_num = 3)
cat2 (order_num = 4)
dog3 (order_num = 5)
cat3 (order_num = 6)
dog4 (order_num = 7)[/code]

I just can't work out how to go about this.  Any help appreciated.

[zq29]

I don't fully understand how you're storing this information in arrays - Could you provide the structure of your arrays using the output of print_r() or something?

[gregsmith]

These are just simplified examples of the actual arrays.  I'm using two SQL statements to get the arrays out of a MySQL database.

I figured I'd be able to do it by joining my SQL statements but can't figure out the best way to do it ([url=http://www.phpfreaks.com/forums/index.php/topic,116999.0.html]http://www.phpfreaks.com/forums/index.php/topic,116999.0.html[/url])

This is driving me nuts as it's the last part of this project I'm working on and everything else has gone swimmingly.

Anyhow, here's the output of print_r (for one row of the array):

Array 1:

Array ( [0] => 42 [page_id] => 42 [1] => anotherpagetest [page_name] => anotherpagetest [2] => 2 [order_num] => 2 )


Array 2:

Array ( [0] => 21 [cat_id] => 21 [1] => cat1 [cat_name] => cat1 [2] => 1 [order_num] => 1 )

Dunno if it'll help.

[craygo]

you will be MUCH better off linking the tables through an sql statement. List the structure of your tables and we can help with the sql statement.

Ray

[gregsmith]

That's what I figured, but I ran out of replies in the mysql help section!

This post should tell you what you need: [url=http://www.phpfreaks.com/forums/index.php/topic,116999.0.html]http://www.phpfreaks.com/forums/index.php/topic,116999.0.html[/url] (look at the last post in the thread by me)

thankyou

[craygo]

I posted in the forum for you.

Ray

[gregsmith]

Thanks Ray, just in case you didn't notice, it didn't work.  I've replied in the other post with more info.

[gregsmith]

Ray and I have come to the conclusion that I can't do this at the query level so we're back to this thread.

Can anyone else offer any help on how I can list these table rows from 2 seperate queries in 1 ordered list?

[craygo]

I don't think I fully understand what you  are trying to do here.

Are the 2 tables related???
You say you have 2 tables pages and catagories.
are the pages in a catagory?? If so what does the parent_id have to do with anything??

tables should be set up like so
Catagories:
cat_id, cat_name

pages
page_id, cat_id, page_name, page_contents, order_num

now the tables are related through the cat_id. And you can order them by the order_num in the one table.

or you could try using the UNION function of mysql. read the mysql manual
http://dev.mysql.com/doc/refman/5.0/en/union.html

Ray

[gregsmith]

It's difficult:

The pages can be in categories but the categories can also be in categories, hence the need for a parent_id.  I have put cat_id in the pages table already.  It's a good point that the cat_id in the pages table will be the same as parent_id - I just gave it that field to try and make the join more understandable.

I'm reading the items out into a menu made up of nested lists so and each list can have any number of categories AND pages in it with the pages and categories in any order amongst each other ie. page, category, category, page, page - hence the need to have read them simultaneously - I figured the best way to do this was to have them in one array together from one SQL statement or joined with PHP code somehow.

I have tried UNION but to no avail.  This is only to the extent of my knowledge and understanding though so I figured maybe someone who knows better might be able to figure it out.

I've come to the conclusion that I'm not going about it the best way and would be better off with categories and pages in one table with a defining field.  At least then I can get them with one SQL statement.

If Ray or anyone else has any more suggestions and can save me a rewrite, I'd love to hear them as you guys are a lot more experienced with this than I am.  Thanks for the help to this point.

[roopurt18]

Just curious, how large are the sets you're working with?

[gregsmith]

They are always going to be pretty small.  Combined they are rarely going to get over 30 rows.

[roopurt18]

Often times we get caught up doing things in the most efficient manner possible, but with a result set that small you could loop over it 4 or 5 times without dramatically reducing performance IMO.  So it might be possible to come up with a working, albeit messy, solution if you really had to.

Here is a little something I threw together that might help you out though:
[code]<?php
  // test.php
  /* We have two tables like so:
    pages:
    page_id, parent_id, page_name, page_contents, order_num

    categories:
    cat_id, parent_id, cat_name, order_num
  ** We want a query to pull from each table and combine the results like so:
    page_id | page_name | cat_id | cat_name | order_num |
    10        page10                          1
                          15      cat10      2
    11        page11                          3
    12        page12                          4
                          16      cat_11    5
  **
  ** If I understand you correctly, a page can be in a category but so can
  ** a category.  This would mean that, ideally, we'd want to list the cat_name
  ** for the rows coming out of the categories table.  I'm going on the
  ** assumption that in each table, parent_id is a possible but not required
  ** relation to a cat_id in the categories table.
  **
  ** I don't know of a way to do this through just queries in SQL, so we will
  ** use two queries to pull a result set from each table and then combine
  ** those in PHP.
  **
  ** I'm also working on the assumption that a value for order_num in one table
  ** is not repeated in the other table.  I.E. that if you have a 2 for
  ** order_num in pages, there is not a 2 for order_num in categories.
  */

  $Final = Array(); // Init our final array

  /* First we pull information from the pages table.  For this query we are
  ** going to left join with categories so that our returned rows will have
  ** a cat_name where there is a parent ID.  We use LEFT JOIN because I'm
  ** assuming not all pages will have a category.
  */
  $sql = "SELECT "
      . "p.page_id AS PageID, "
      . "p.page_name AS PageName, "
      . "c.cat_id AS CatID, "
      . "c.cat_name AS CatName, "
      . "c.cat_name AS ParentName, " // This will be more apparent in the  next
                                      // sql statement, hopefully.
      . "p.order_num AS OrderNum "
      . "FROM pages p "
      . "LEFT JOIN categories c ON p.parent_id=c.cat_id "
      // If you have a WHERE, add it here
      . "ORDER BY p.order_num";
  $q = mysql_query($sql);
  if($q){
    while($row = mysql_fetch_array($q)){
      // Keep in mind this assignment assumes OrderNum is unique across tables
      $Final[$row['OrderNum']] = $row;
    }
  }

  /* Now we pull information from our categories table.  For this query we are
  ** going to left join the table on itself in case any of the categories belong
  ** to a category.
  */
  $sql = "SELECT "
      . "NULL AS PageID, "
      . "NULL AS PageName, "
      . "c1.cat_id AS CatID, "
      . "c1.cat_name AS CatName, "
      . "c2.cat_name AS ParentName, "
      . "c1.order_num AS OrderNum "
      . "FROM categories c1 "
      . "LEFT JOIN categories c2 ON c1.parent_id=c2.cat_id "
      // Insert your WHERE clause here
      . "ORDER BY c1.order_num";
  $q = mysql_query($sql);
  if($q){
    while($row = mysql_fetch_array($q)){
      // Keep in mind this assignment assumes OrderNum is unique across tables
      $Final[$row['OrderNum']] = $row;
    }
  }

  // Output what we have so far:
  echo "<pre style=\"text-align: left;\">" . print_r($Final, true) . "</pre>";
?>[/code]

Hope it helps.

[gregsmith]

Thanks Roopurt that definitely does help.  Cheers for putting your time into this.