moagrius

what follows is a simplified version of what i'm attempting to do. say i have a mysql table called 'pages', that looks roughly like this: id parent label 1 0 home 2 0 about 3 0 events 4 2 history 5 2 philosophy 6 3 past-events 7 3 future-events 8 6 past-event-gallery which uses the parent key as a self-referencing index. items with parent=0 don't have a parent - all other items refer to rows in the same table. so, 'about' has 2 children: 'history' and 'philosophy'. this can extend an unlimited number of levels in depth. e.g., 'past-event-gallery' has a parent of 'past-events' which has a parent of 'events'. building it out is pretty straightforward - start with all rows that have a parent of 0, then recurse... select id, label from pages where parent=0 // grab the id... select id, label from pages where where parent={$id} etc. which works (for example) to build out a uri for an <a> tag's href attribute. the problem arises when trying to go backwards... i'm trying to get back the id of the row from that example uri... so if the output was 'events/past-events/past-event-gallery', i'm exploding on slashes to get the component parts, and want to walk it back to get the id of the row. if the label keys were unique, it'd be simple enough... select id from pages where label={$label} but labels might be duplicated. for example, 'past-events' might have a child called 'gallery', but it might be possible that 'about' also has a child called 'gallery', etc. it might even occur several levels deep, so i need to walk it backwards until i've determined the correct id from the component parts of the URI. my initial thought was to run from left-to-right, something like: while(count($parts) > 0){ $component = array_shift($parts); $result = mysql_query("select id from pages where label='{$component}'"); // this is where i lose it... maybe create a temp table from the results and continue...? } or maybe from right-to-left... while(count($parts) > 0){ $component = array_pop($parts); $result = mysql_query("select id from pages where label='{$component}'"); $row_count = mysql_num_rows($result); switch($row_count){ case 1 : // this is the only one with that label, so return the ID and be done break; case 0 : // no labels match, so return a 404 or missing item or something and be done break; default : // if there are more than 1 matching labels, figure out which one - here is where i get lost on this approach... break; } } also considered a self-returning function for the second (right-to-left) idea, but didn't get far with it. any ideas? i could be (probably am) totally off on both approaches mentioned, and there might be a much easier way to do this. i'd be happy to hear any suggestions... tyia

i'm creating a small app for someone who wants to control the content of each page from a DB. effectively it'd be a template file with header, footer, etc all pre-built, and the content area supplied from a TEXT field in a MySQL table - basically the same idea wordpress uses. this is all fine, but a couple pages would require some php - e.g., a list of events or users or articles, whatever, that are managed in different tables. i can do this with eval - something like: function render_content($string) { ob_start(); eval("?>$string<?php "); $returns = ob_get_contents(); ob_end_clean(); return $returns; } but i wonder if there's a better way. i can probably limit whatever code needs to be executed in include files, so i thought maybe include some arbitrary tag and use regexp to parse it out... maybe modeled after a conditional comment, e.g., <!--[include]some-file.inc.php--> // or even... <include>some-file.inc.php</include> but, again, not thrilled with the approach, and wondered if anyone had a better idea. i should probably mention that it's not going to be a "content or include" setup - it probably won't be one or the other, exclusively, and is likely to be a mix on those pages that require it - the php might need to appear before, after, or in the middle of whatever arbitrary markup the user happens to supply, e.g. <h1>This is a list of stuff</h1> <p>Some explanation lorem ipsum dolor sit ahmet.</p> <?php include('some-file.inc.php'); ?> <em>But this caveat applies to the above list.</em> <div> Something totally unrelated. <img src="pic.jpg" /> </div> not sure I explained that very well, but hopefully the concept comes across. TYIA

Thanks for the replies. I've started implementing an approach similar to the one suggested by dgoosens, but am just using a single column: ADDRESS_FK that's a unique key. CUSTOMERS - ID, NAME ADDRESSES - ID, CUSTOMER_FK, STREET, CITY, STATE, ZIP BILLING_ADDRESSES - ADDRESS_FK are there any problems anyone foresees with this idea? thanks again

Sorry about the less-than-descriptive title. I'm having a hard time putting the issue into words. Let's say I have a table CUSTOMERS that has just 2 columns: ID and NAME And I have another table ADDRESSES that has standard address information (street, city, state, zip, etc) and an FK to CUSTOMERS (for this example, I'll call it CUSTOMER_FK). Any customer can have multiple addresses. I wanted to assign one row from ADDRESSES to be the billing/default/"primary" address for a customer. A customer should only have one billing/default/primary address. My initial thought was to add a column to CUSTOMERS called BILLING_ADDRESS that was an FK to the row in ADDRESSES that served as the billing/default/primary address. This seems slightly redundant, though, and more importantly makes insertion a little less than elegant. On creating a new customer from user input, for example, I'd have to create the CUSTOMERS row first, grab that ID, then create the ADDRESSES row (passing the CUSTOMERS ID just generated to CUSTOMER_FK), then go back and update the CUSTOMERS row with the ID of the newly created ADDRESS to serve as the FK for the billing/default/primary address. Another thought was to, instead of the above, add a column to ADDRESSES that was a boolean indicator of whether or not the row was the billing/default/primary address for that customer, but AFAIK there's no way to ensure this is limited to a single address for any customer. I'm guessing this isn't that rare, and there's probably a built-in solution that I'm not aware of. If not, is there a more straightforward solution anyone could suggest? TYIA Using MySQL 5 (and PHP 5.3, if that matters).

awesome - tested against a randomly populated dataset with 5000 items and much larger versions of "list" and it still holds up. i've got this question on several boards and mailing lists and while we've come close with about 9 different approaches, yours appears complete and bullet-proof. thank you!

hi, i'm trying to sort an array of associative arrays, based on one of the keys. if the value of the key is a primitive, normal sorting occurs - this works fine. if the value of the key is an array, i'd like to have it sorted by "groups" here's an example: <?php $data[] = array('name' => 'h', 'list' => array(1,2)); $data[] = array('name' => 'g', 'list' => array(1)); $data[] = array('name' => 'a', 'list' => array(1,3)); $data[] = array('name' => 'f', 'list' => array(2)); $data[] = array('name' => 'e', 'list' => array(2,3)); $data[] = array('name' => 'b', 'list' => array(3)); $data[] = array('name' => 'c', 'list' => array(1,2,3,4)); $data[] = array('name' => 'd', 'list' => array(3,4)); function deep_sort($array, $sorton){ usort($array, function($a, $b) use($sorton) { $a = $a[$sorton]; $b = $b[$sorton]; if(is_array($a) && is_array($b)){ // this bit is obviously flawed - only included for illustrative purposes $a = implode('', $a); $b = implode('', $b); } return ($a == $b) ? 0 : ($a > $b) ? 1 : -1; }); return $array; } $sorted_by_name = deep_sort($data, 'name'); print '<pre>'; print_r($sorted_by_name); print '</pre>'; $sorted_by_list = deep_sort($data, 'list'); print '<pre>'; print_r($sorted_by_list); print '</pre>'; ?> the sorted_by_name bit is fine - but for sorted_by_list, what i want returned is all the array ordered as follows: all those with "1" (or the lowest if none have "1") in it's list value first, then all those remaining that have the next highest ("2"), etc. so right now, the returns for sort_by_list look like this: Array ( [0] => Array ( [name] => g [list] => Array ( [0] => 1 ) ) [1] => Array ( [name] => f [list] => Array ( [0] => 2 ) ) [2] => Array ( [name] => b [list] => Array ( [0] => 3 ) ) [3] => Array ( [name] => h [list] => Array ( [0] => 1 [1] => 2 ) ) [4] => Array ( [name] => a [list] => Array ( [0] => 1 [1] => 3 ) ) [5] => Array ( [name] => e [list] => Array ( [0] => 2 [1] => 3 ) ) [6] => Array ( [name] => d [list] => Array ( [0] => 3 [1] => 4 ) ) [7] => Array ( [name] => c [list] => Array ( [0] => 1 [1] => 2 [2] => 3 [3] => 4 ) ) ) whereas i'd like to get back: Array ( [0] => Array ( [name] => g [list] => Array ( [0] => 1 ) ) [1] => Array ( [name] => h [list] => Array ( [0] => 1 [1] => 2 ) ) [2] => Array ( [name] => c [list] => Array ( [0] => 1 [1] => 2 [2] => 3 [3] => 4 ) ) [3] => Array ( [name] => a [list] => Array ( [0] => 1 [1] => 3 ) ) [4] => Array ( [name] => f [list] => Array ( [0] => 2 ) ) [5] => Array ( [name] => e [list] => Array ( [0] => 2 [1] => 3 ) ) [6] => Array ( [name] => b [list] => Array ( [0] => 3 ) ) [7] => Array ( [name] => d [list] => Array ( [0] => 3 [1] => 4 ) ) ) TYIA for any suggestions

Makes sense. Thanks.

that's great, ignace - thanks very much... before seeing your reply, i had come up with this: select * from PROPERTIES, MODELS, COMMUNITIES where PROPERTIES.MODEL=MODELS.ID and PROPERTIES.COMMUNITY=COMMUNITIES.ID and PROPERTIES.COMMUNITY in (select COMMUNITY from LOCATION_BRIDGE where LOCATION={$location}) which seemed to work, but i wonder if you would recommend one approach over the other? thanks again

Hi, I'm working on a search query I'm having some trouble formulating. I'll try to explain as best I can, starting with the table structure of the DB, in abbreviated form. (Abbreviated) Tables: PROPERTIES ID | ADDRESS | MODEL | COMMUNITY 1 | 123 Abc St. | 1 | 1 2 | 45 Some Street | 1 | 2 3 | #6 Another Av. | 2 | 2 MODELS ID | SQ_FT 1 | 2000 2 | 3000 COMMUNITIES ID | NAME 1 | Deerbrook 2 | Ashford LOCATIONS ID | NAME 1 | North 2 | Northwest 3 | West LOCATION_BRIDGE ID | COMMUNITY | LOCATION 1 | 1 | 1 2 | 1 | 2 3 | 2 | 2 4 | 2 | 3 So pretty much anything in the above with a 1-digit number is a FK to another table. LOCATION_BRIDGE takes a community and a location, one for each, since any community might be in multiple location codes... In this example, the community with ID1 (Deerbrook) qualifies for location 1 (North) and location 2 (Northwest), and community 2 (Ashford) qualifies for location 2 (Northwest) and location 3 (West). How would I structure a query that takes a post variable of location (equal to the ID of the location - for example, "1" for a search on North), and get back all the information for properties that match that location code (Address, Community Name, Square Footage, etc)? The sticky for the board said to include a raw SQL statement, which I don't really have, but started with something like: select * from PROPERTIES, MODELS where PROPERTIES.MODEL=MODELS.ID ... The ... is where I get lost, trying to get back properties whose communities qualify for the location code provided by user input. I'm using MySQL 5 and PHP 5. Any help would be greatly appreciated. TYIA

Thanks for the quick reply. I've only ever installed one version of PHP on this machine. I'm only guessing the problem is with PHP because of Fault Module Name - but it's actually Apache that crashes. The app has several requests that are all very similiar - polling a mysql DB and echoing out the results. What's strange to me is that all the requests that have "small" data sets returned work fine. The 2 largest requests (one selecting from a table with 1800 rows, the other from a table with 4000 rows) are the ones that break. Also strange is that all pages have a limit of 100 rows returned in the sql query. And, it doesn't appear to be a timeout error - it doesn't hang long at all before crashing - maybe 3-5 seconds. Possibly should have mentioned - I'm on Vista, Apache 2.2, PHP 5.3, MySQL 5.0.5 Have had the Apache/PHP/SQL installed for at least a few months and never had a problem before. TYIA

Hi, I've got a web app that works fine on 2 different web hosts. I installed it locally, and for the most part it works fine as well - however, when loading large datasets (from a mysql DB, using PHP), Apache crashes (then restarts immediately). This is pretty consistent - I can predict which requests will crash Apache and which one's will not. No errors are logged from PHP or Apache (the error.log does update normally, but not when this happens). Windows gives the following error details: Problem Event Name: APPCRASH Application Name: httpd.exe Application Version: 2.2.11.0 Application Timestamp: 493f5d44 Fault Module Name: php5ts.dll From the last line, it looks like the problem is with PHP - I checked php.ini for any particularly short timeout directives or script memory allotments, and don't notice anything - any suggestions?

That article was very helpful, thanks

Hi, What's the best approach for table structure when using multiple levels of categories and subcategories for each item? For example, say I a table "Items", and each is to have an FK to a category, and I have multiple "top-level" categories like "Clothes", "Shoes" and "Jewelry". Within "Clothes" there's "Tops", "Bottoms", and "Dresses". Within "Tops" there's "Tees", "Tanks", and "Jackets". Within "Jackets" there's "Sleeved" and "Sleeveless". etc. My first instinct is to create a single "Category" table, and have each Category use a Key to another row in the same table... So if "Clothes" has a PK ID of 1, then "Tops" would have a "Parent Key" of 1, and an PK ID of say 3, so "Jackets" would have a Parent Key of 3, and a PK ID of say 10, and Sleeves would have a Parent Key of 3, and a PK ID of say 22... Is this approach wise, or even feasible? If so, how would retrieval work - to get all "Tops" how would I query it? I presume there's some kind of "self-join" operation I'd need to use...? "select * from Items [some kind of self join] where Items.Parent_Key = 3" But would that grab "Sleeveless", since it's Parent Key is not 3, but it's parent category's parent category Parent Key is...? Sorry if this is stated poorly - I'm having trouble getting my head around it. Any input appreciated. TYIA.

Hi, Say I've got 2 Tables: Supervisors and Employees Supervisors might look something like this: ID Dept_ID FName LName 1 1 Bob Smith 2 1 John Doe 3 1 Ken Jones 4 2 Sam West 5 2 Jim Brown And Employees might look like this: ID Super_ID FName LName 1 5 John Adams 2 3 George Washington 3 4 Thomas Jefferson 4 2 Abe Lincoln 5 5 Ron Reagon 6 1 John Kennedy 7 1 Linden Johnson 8 3 James Carter 9 2 George Bush 10 3 Richard Nixon How would I structure a query that selected all employees whose supervisor had a Dept_ID of 1? TYIA

thanks for the quick reply - but after a quick lookup of nl2br it apparently swaps line breaks for "<br>" entities - i'm echoing javascript variables, not html - should i replace that step with a preg_replace? thanks again