[PHP-DB] Listing parent ids
Not strictly a php issue but it's for a php app, hope that counts (plus I haven't had much joy googling this) I have a table with an id and a parentid. If it's a top-level record the parentid is 0, otherwise it points to another record, and if that record isn't a top-level record its parentid points to another record, etc (a linked list). Is there a single select that will return the complete list of parentids? Or do I have to iterate selecting each parent record while parentid 0 and build the list entry by entry? TIA Cheers, Arno -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Listing parent ids
Arno Kuhl wrote: Not strictly a php issue but it's for a php app, hope that counts (plus I haven't had much joy googling this) I have a table with an id and a parentid. If it's a top-level record the parentid is 0, otherwise it points to another record, and if that record isn't a top-level record its parentid points to another record, etc (a linked list). Is there a single select that will return the complete list of parentids? Or do I have to iterate selecting each parent record while parentid 0 and build the list entry by entry? Little difficult to answer what you don't say what you are using as a database. Recursive queries are now possible on many databases, except I think for MySQL. I run this type of query all the time on Firebird and Postgres now supports the same CTE functions. -- Lester Caine - G8HFL - I'm currently using MySQL but I'll switch databases if there's a compelling reason and no drawbacks. Thanks for the lead, I'm googling recursive queries. Cheers Arno -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Listing parent ids
On 28 July 2011 10:39, Arno Kuhl a...@dotcontent.net wrote: Arno Kuhl wrote: Not strictly a php issue but it's for a php app, hope that counts (plus I haven't had much joy googling this) I have a table with an id and a parentid. If it's a top-level record the parentid is 0, otherwise it points to another record, and if that record isn't a top-level record its parentid points to another record, etc (a linked list). Is there a single select that will return the complete list of parentids? Or do I have to iterate selecting each parent record while parentid 0 and build the list entry by entry? Little difficult to answer what you don't say what you are using as a database. Recursive queries are now possible on many databases, except I think for MySQL. I run this type of query all the time on Firebird and Postgres now supports the same CTE functions. Lester Caine - G8HFL - I'm currently using MySQL but I'll switch databases if there's a compelling reason and no drawbacks. Thanks for the lead, I'm googling recursive queries. Arno -- I would take a long hard read of this article http://web.archive.org/web/20100105135622/http://dev.mysql.com/tech-resource s/articles/hierarchical-data.html I can't find it anywhere else now - it used to be on the mysql site - but gone since Oracle has it and I can't find it in Google Cache. But, it explains the pros and cons of using the Adjacency List Model vs the Nested Set Model. The article is quite old (the copyright on the page is 2008, but I've no idea when it was actually created) and so, there are advances in SQL features (CTE's being one of them) which aren't mentioned. But, I've found Nested Sets to be much easier for me to work with, allowing me to provide quite complex searching based upon an n-level tree. How you visualise the data won't change. It is still, visually at least, a set of parent/child relationships, but to build a tree, you don't need to use recursion. In most cases, a single query will be enough to interact with the tree at any level, in any direction, for more or less any purpose. Richard Quadling -- Thanks Richard. Your reference is exactly what I was looking for. I'm just busy reading a sitepoint article about adjacent lists vs the niftily titled modified preorder tree traversal model. http://www.sitepoint.com/hierarchical-data-database/ (really old - 2003) I found I'm using the adjacent list model at the moment (didn't know it had a name, I always thought of it as a type of linked list). The modified preorder tree traversal model in the sitepoint article appears to be equivalent to the nested set model in the mysql article. It seems simple enough to implement, I'll definitely give it a closer look and do some tests. Thanks, it's great to hear the experiences of others who've used this. Cheers Arno -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Listing parent ids
I'm currently using MySQL but I'll switch databases if there's a compelling reason and no drawbacks. Thanks for the lead, I'm googling recursive queries. 'common table expression' is the thing to google for, but MSDN seem to have hijacked all the top spots. http://syntaxhelp.com/SQLServer/Recursive_CTE is a nice example of what you outlined ... -- Lester Caine - G8HFL - Thanks Lester. The data structure is a good example of what I outlined. I want to avoid the recursion, whether in php or sql, possibly by using the nested set model. Cheers Arno -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: Listing parent ids
I have a table with an id and a parentid. If it's a top-level record the parentid is 0, otherwise it points to another record, and if that record isn't a top-level record its parentid points to another record, etc (a linked list). Is there a single select that will return the complete list of parentids? You say you have a parentid and an id - ie, two specific fields in your records. You say that you want to query all the parentids. Nobody else has said this, but why not just Select unique ids where parentid=0 ? That gives you (as you say) all your top-level records, which are the parents of everything, no? Or are you looking for each id that is itself a parent to something else? If the latter, then why not select unique parentid where parentid 0 ? -- Hi Jim. I wanted the list of related parentids from current id to top-level (parentid=0). That could be 0 or more results, regardless of how many non-zero parentids there are in total. Currently I get this from selecting id=parentid while parentid0, ie recursively select each record going up the tree to the top level. The nested set model apparently can return the entire hierarchical list from the current node to the top level with a single select, but I haven't run any tests yet on my specific data. Cheers Arno -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: Listing parent ids
Hi Jim. I wanted the list of related parentids from current id to top-level (parentid=0). Ah - now that is a much different question! From your statement above, are you now saying you only want ONE list of related ids, not the entire society of possible 'families' in your table? Also - do your parent records have multiple children? -- Hi Jim. Only one linked list of parentids. No siblings. Multiple records can have the same parentid, but that doesn't play a role. Cheers Arno -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: Listing parent ids
If your data is based upon parentid and uniqueid, then you will need to change your data... http://data.bangtech.com/sql/nested_set_treeview.htm and once you've done that and find you can't deal with things (maybe it isn't for you), then http://pratchev.blogspot.com/2007/02/convert-tree-structure-from-nested-set. html will help you turn it all back again. Richard Quadling -- Thanks Richard, those look like they could be really useful. Cheers Arno -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Using OR in a SELECT statement
Newbie MySQL question...
I have a situation where if I don't have a custom value then I must use the
default value. There will always be a default value but there may also be a
custom value.
I could do this with:
select value where id and custom condition
if (EOF) {
select value where id and default condition
if (EOF) {
// no value found for this id - error, return false
}
}
// return value
If I change this to:
select value where id and (custom condition or default condition)
... will I always get the custom value if there is one, or do I have to have
2 selects to be sure that I always get the custom value if there is one? IE
does MySQL consistently read and process the OR statement from left to right
or does it change depending on what's most optimal?
Cheers
Arno
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Defined Constants
In your select, you need to remove the quotes around your defines, otherwise it is interpreted as a quoted string. So your select statement should look like: select name=myType option value=JOVIAL?=JOVIAL;?/option option value=CHEERFUL?=CHEERFUL;?/option option value=ADVENTUROUS?=ADVENTUROUS;?/option option value=MYSTERIOUS?=MYSTERIOUS;?/option /select Arno DotContent Professional Content Management Solutions www.dotcontent.net -Original Message- From: Ng Hwee Hwee [mailto:[EMAIL PROTECTED] Sent: 13 October 2005 08:13 To: PHP DB List Subject: [PHP-DB] Defined Constants Hi all, just a very simple question. my form has the following define statements and select box: define(JOVIAL, likes to joke alot); define(CHEERFUL, always wears a smile on his face); define(ADVENTUROUS, likes sports and plays well too); define(MYSTERIOUS, there's always something to discover everyday); select name=myType option value=JOVIAL?=JOVIAL;?/option option value=CHEERFUL?=CHEERFUL;?/option option value=ADVENTUROUS?=ADVENTUROUS;?/option option value=MYSTERIOUS?=MYSTERIOUS;?/option /select after i post my form, the value $myType will be one of my define statements. For example, user chooses CHEERFUL thus when i echo $myType, I will get CHEERFUL but actually, what i really want to get is always wears a smile on his face. can someone advise me how i can achieve this?? sounds simple but i really don't know how!! =( thanks in advance!! hwee -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Defined Constants
Oops - sorry, got that one completely wrong! A solution should say: form code: define(JOVIAL, likes to joke alot); define(CHEERFUL, always wears a smile on his face); define(ADVENTUROUS, likes sports and plays well too); define(MYSTERIOUS, there's always something to discover everyday); select name=myType option value=JOVIAL?=JOVIAL;?/option option value=CHEERFUL?=CHEERFUL;?/option option value=ADVENTUROUS?=ADVENTUROUS;?/option option value=MYSTERIOUS?=MYSTERIOUS;?/option /select form handling code: $Type = array(); $Type[JOVIAL]=JOVIAL; $Type[CHEERFUL]=CHEERFUL; $Type[ADVENTUROUS]=ADVENTUROUS; $Type[MYSTERIOUS]=MYSTERIOUS; echo You selected $Type[$myType]; Hope I got it right this time... Arno DotContent Professional Content Management Solutions Tel 27-11-648-1971 Fax 27-11-487-1199 Cel 082-334-0710 [EMAIL PROTECTED] www.dotcontent.net -Original Message- From: Ng Hwee Hwee [mailto:[EMAIL PROTECTED] Sent: 13 October 2005 08:13 To: PHP DB List Subject: [PHP-DB] Defined Constants Hi all, just a very simple question. my form has the following define statements and select box: define(JOVIAL, likes to joke alot); define(CHEERFUL, always wears a smile on his face); define(ADVENTUROUS, likes sports and plays well too); define(MYSTERIOUS, there's always something to discover everyday); select name=myType option value=JOVIAL?=JOVIAL;?/option option value=CHEERFUL?=CHEERFUL;?/option option value=ADVENTUROUS?=ADVENTUROUS;?/option option value=MYSTERIOUS?=MYSTERIOUS;?/option /select after i post my form, the value $myType will be one of my define statements. For example, user chooses CHEERFUL thus when i echo $myType, I will get CHEERFUL but actually, what i really want to get is always wears a smile on his face. can someone advise me how i can achieve this?? sounds simple but i really don't know how!! =( thanks in advance!! hwee -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
