gregsmith Posted December 5, 2006 Share Posted December 5, 2006 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_numcat1 1cat2 4cat3 6[/code][code]array 2:dogs | breed | order_numdog1 breed1 2dog2 breed2 3dog3 breed3 5dog4 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. Quote Link to comment Share on other sites More sharing options...
zq29 Posted December 5, 2006 Share Posted December 5, 2006 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? Quote Link to comment Share on other sites More sharing options...
gregsmith Posted December 5, 2006 Author Share Posted December 5, 2006 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. Quote Link to comment Share on other sites More sharing options...
craygo Posted December 5, 2006 Share Posted December 5, 2006 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 Quote Link to comment Share on other sites More sharing options...
gregsmith Posted December 5, 2006 Author Share Posted December 5, 2006 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 Quote Link to comment Share on other sites More sharing options...
craygo Posted December 5, 2006 Share Posted December 5, 2006 I posted in the forum for you.Ray Quote Link to comment Share on other sites More sharing options...
gregsmith Posted December 5, 2006 Author Share Posted December 5, 2006 Thanks Ray, just in case you didn't notice, it didn't work. I've replied in the other post with more info. Quote Link to comment Share on other sites More sharing options...
gregsmith Posted December 5, 2006 Author Share Posted December 5, 2006 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? Quote Link to comment Share on other sites More sharing options...
craygo Posted December 5, 2006 Share Posted December 5, 2006 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 soCatagories:cat_id, cat_namepagespage_id, cat_id, page_name, page_contents, order_numnow 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 manualhttp://dev.mysql.com/doc/refman/5.0/en/union.htmlRay Quote Link to comment Share on other sites More sharing options...
gregsmith Posted December 5, 2006 Author Share Posted December 5, 2006 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. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted December 5, 2006 Share Posted December 5, 2006 Just curious, how large are the sets you're working with? Quote Link to comment Share on other sites More sharing options...
gregsmith Posted December 5, 2006 Author Share Posted December 5, 2006 They are always going to be pretty small. Combined they are rarely going to get over 30 rows. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted December 5, 2006 Share Posted December 5, 2006 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. Quote Link to comment Share on other sites More sharing options...
gregsmith Posted December 6, 2006 Author Share Posted December 6, 2006 Thanks Roopurt that definitely does help. Cheers for putting your time into this. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.