Jump to content

ElmoTheClown

Members
  • Posts

    18
  • Joined

  • Last visited

Profile Information

  • Gender
    Not Telling

ElmoTheClown's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. Thank you! I had to put this aside for a bit to get a grip on sanity. Beat my head on this for a while. The second table idea came from an article on closure tables. I couldn't get what I wanted with a single table. Now that I have a working example of Union working correctly on a website, that will help too. Sincerest appreciation, Joe
  2. Howdy, I'm trying to get a result from my database in the proper order. CREATE TABLE IF NOT EXISTS `clc_crz_regions` ( `region_id` tinyint(2) NOT NULL, `parent_id` tinyint(2) NOT NULL DEFAULT '0', `regAbbreviation` varchar(4) NOT NULL DEFAULT '', `reg_name` varchar(40) NOT NULL DEFAULT '', ) INSERT INTO `clc_crz_regions` (`region_id`, `parent_id`, `regAbbreviation`, `reg_name`) VALUES (4, 0, 'soam', 'South America'), (1, 0, 'cari', 'Caribbean'), (20, 1, 'eaca', 'Eastern Caribbean'), (44, 1, 'soca', 'Southern Caribbean'), (21, 1, 'weca', 'Western Caribbean'), (41, 4, 'amri', 'Amazon River'); CREATE TABLE IF NOT EXISTS `clc_crz_region_groups` ( `parent_id` tinyint(2) NOT NULL, `child_id` tinyint(2)NOT NULL ) INSERT INTO `clc_crz_region_groups` (`parent_id`, `child_id`) VALUES (1, 1), (1, 20), (1, 21), (1, 44), (2, 2), (3, 3), (3, 40), (3, 57), (4, 4), I'd like it to sort by 'reg_name' for the parents, with the children under the parents also sorted by name. Caribbean Eastern Caribbean Southern Caribbean Western Caribbean South America Amazon River Cape Horn I can get the results, just can't get them to order the way I want to. SELECT r1.region_id, r1.reg_name, r1.reg_entry_name, r1.parent_id FROM $wpdb->crz_regions r1 JOIN $wpdb->crz_region_groups r2 ON r1.region_id = r2.child_id I just can't get it sorted correctly. I'd like to do it in SQL if possible. Thank you
  3. Is it ever a good idea to use a query in a loop?
  4. That's because of my awesome 'splainin' skills. I was wondering if it would be better to use the "meta" table, and store company and asset names in actual text (Delta Airlines, Delta Adventures), which although would require roughly 10 to 20 times more disk space.... As opposed to the three tables (terms, term taxonomies, and term relationships), which takes more storage, but also much more processing power (6 join statements instead of 2).
  5. Guess I am stuck with it. With today's technology, could databases become a bit less relationship driven, and more use be made of storage? I could easily change the JOIN wp_term_relationships tr1 ON p.ID = tr1.object_id JOIN wp_term_taxonomy tt1 ON tr1.term_taxonomy_id = tt1.term_taxonomy_id JOIN wp_terms t1 ON tt1.term_id = t1.term_id JOIN wp_term_relationships tr2 ON p.ID = tr2.object_id JOIN term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id JOIN wp_terms t2 ON tt2.term_id = t2.term_id to [code] JOIN wp_postmeta m4 ON (p.ID = m4.post_id AND m4.meta_key = 'company') JOIN wp_postmeta m5 ON (p.ID = m4.post_id AND m4.meta_key = 'asset') This would tax the storage more, but processing and memory less?
  6. SELECT p.ID, p.post_content, p.post_title, p.post_name, p.guid, m1.meta_value AS cruise_date, m2.meta_value AS alt_dates, m3.meta_value as days, t1.name as company, t2.name as asset FROM wp_posts p JOIN wp_postmeta m1 ON (p.ID = m1.post_id AND m1.meta_key = 'cruise_date') LEFT JOIN wp_postmeta m2 ON (p.ID = m2.post_id AND m2.meta_key = 'alt_dates') JOIN wp_postmeta m3 ON (p.ID = m3.post_id AND m3.meta_key = 'number_days') JOIN wp_term_relationships tr1 ON p.ID = tr1.object_id JOIN wp_term_taxonomy tt1 ON tr1.term_taxonomy_id = tt1.term_taxonomy_id JOIN wp_terms t1 ON tt1.term_id = t1.term_id JOIN wp_term_relationships tr2 ON p.ID = tr2.object_id JOIN Kterm_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id JOIN wp_terms t2 ON tt2.term_id = t2.term_id WHERE p.post_type = 'cruise' AND p.post_status = 'publish' AND tr1.term_taxonomy_id={$term_tax[0]} AND tt2.parent = {$term_tax[0]}"; It works. There should only be 1 company, and 1 asset per post. Imagine things will be broken if more end up in. Is there a better way? Seems to me to be a pretty stinking big query.. but I don't know. What do you think?
  7. Currently, the query is: SELECT p.ID, p.post_content, p.post_title, p.post_name, p.guid, m1.meta_value AS date, m2.meta_value AS alt_dates, term1.name as charter, term2.name as itin FROM wp_posts p JOIN wp_postmeta m1 ON (p.ID = m1.post_id AND m1.meta_key = 'date') LEFT JOIN wp_postmeta m2 ON (p.ID = m2.post_id AND m2.meta_key = 'alt_dates') JOIN wp_postmeta m3 ON (p.ID = m3.post_id AND m3.meta_key = 'number_days') JOIN wp_term_relationships tr ON (p.ID = tr.object_id) $term_tax[0] is an integer representing a page(post.ID). I get how to join the same table more than once, but I can't get my head around joining two tables twice. need to get the charter name (terms.name) from somehow making terms.term_id=term_taxonomy.term_id AND term_taxonomy.parent = 0 AND terms.term_id=$term_tax[0] AND I need to get the itin name (terms.name) from somehow making terms.term_id=term_taxonomy.term_id AND term_taxonomy.parent = $term_tax[0] AND terms.term_id=$term_tax[0] Here is a breakdown of the database with relevant columns: CREATE TABLE `wp_posts` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_content` longtext COLLATE utf8_unicode_ci NOT NULL, `post_title` text COLLATE utf8_unicode_ci NOT NULL, `post_status` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'publish', `post_name` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `guid` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `post_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'post', PRIMARY KEY (`ID`), KEY `post_name` (`post_name`), KEY `type_status_date` (`post_type`,`post_status`,`ID`), ) ENGINE=MyISAM AUTO_INCREMENT=169 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `wp_postmeta` ( `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_id` bigint(20) unsigned NOT NULL DEFAULT '0', `meta_key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `meta_value` longtext COLLATE utf8_unicode_ci, PRIMARY KEY (`meta_id`), KEY `post_id` (`post_id`), KEY `meta_key` (`meta_key`) ) ENGINE=MyISAM AUTO_INCREMENT=576 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `wp_terms` ( `term_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`term_id`), KEY `name` (`name`) ) ENGINE=MyISAM AUTO_INCREMENT=40 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `wp_term_relationships` ( `object_id` bigint(20) unsigned NOT NULL DEFAULT '0', `term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`object_id`,`term_taxonomy_id`), KEY `term_taxonomy_id` (`term_taxonomy_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `wp_term_taxonomy` ( `term_taxonomy_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `term_id` bigint(20) unsigned NOT NULL DEFAULT '0', `taxonomy` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `parent` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`term_taxonomy_id`), UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`), KEY `taxonomy` (`taxonomy`) ) ENGINE=MyISAM AUTO_INCREMENT=40 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci Thank you for your help.
  8. Server version: 5.1.56 The query works great without the "MORE TO JOIN HERE" I honestly can;t seem to wrap my head around the rest. I know what I want it t ot tell me... Just not how to tell it to tell me.
  9. Base idea, and then other stuff in () Category: catID catName (description / slug / short name) Subcategory subID catID subName (description / slug / short name) Products proID catID subID proName proSubName proDescription (picture / slug / price / link to whatever ) If I perused your site, I could probably come up with more... I will probably suggest WordPress + ecommerce plugin. WordPress does a lot with the right plug-ins. check it out at wordpress.org.
  10. Gah.. Approaching suicidal.. SELECT p.ID, p.post_content, p.post_title, p.post_name, p.guid, m1.meta_value AS date, m2.meta_value AS alt_dates, term1.name as charter, term2.name as itin FROM $wpdb->posts p JOIN $wpdb->postmeta m1 ON (p.ID = m1.post_id AND m1.meta_key = 'date') LEFT JOIN $wpdb->postmeta m2 ON (p.ID = m2.post_id AND m2.meta_key = 'alt_dates') JOIN $wpdb->postmeta m3 ON (p.ID = m3.post_id AND m3.meta_key = 'number_days') JOIN $wpdb->term_relationships tr ON (p.ID = tr.object_id) MORE TO JOIN HERE WHERE p.post_type = 'cruise' AND p.post_status = 'publish' AND tr.term_taxonomy_id={$term_tax[0]}"; All goes according to plan... but I have more to join, and I can't seem to get it. I need to get the charter name (terms.name) from somehow making terms.term_id=term_taxonomy.term_id AND term_taxonomy.parent = 0 AND terms.term_id=$term_tax[0] AND I need to get the itin name (terms.name) from somehow making terms.term_id=term_taxonomy.term_id AND term_taxonomy.parent = $term_tax[0] AND terms.term_id=$term_tax[0] I would appreciate any help!
  11. Thanks again for your code! The rest scratched because I muffed it up. The first line name called was omitted. edited out my screwed up code correction.
  12. Thank you. That's the first time I have used INNER JOIN. I had the hardest time understanding it, and found WHERE to work just fine most times (until I found our I was not supposed to have a call within a loop). "as" still doesn't work in my scripts. Works fine in the mysql admin query box, but not in my scripts, but I make table names I understand. And curly brackets worked too... WOOOT I never could get them to work for me and always ended up ".$rs['this']." it. So, thank you, and thank you. You gave me a clearcut way of doing it. Check out one of the things I have been trying to work with to do the same thing: function search($array, $key, $value) { $results = array(); if (is_array($array)) { if ($array[$key] == $value) $results[] = $array; foreach ($array as $subarray) $results = array_merge($results, search($subarray, $key, $value)); } return $results; } $arr = array(0 => array(id=>1,name=>"cat 1"), 1 => array(id=>2,name=>"cat 2"), 2 => array(id=>3,name=>"cat 1")); print_r(search($arr, 'name', 'cat 1')); WTH? Thanks!
  13. Thank you. $resMenuLines=mysql_query("SELECT linName, line_id FROM line ORDER BY linName") or die(mysql_error()); while($rsMenuLines=mysql_fetch_array($resMenuLines)) { $lineName=$rsMenuLines[linName]; $resMenuShips=mysql_query("SELECT shiName,shiMates,shiBlogURL FROM ship WHERE line_id=".$rsMenuLines['line_id']." ORDER BY shiName") or die(mysql_error()); $shipDisplay=array(); while($rsMenuShip=mysql_fetch_array($resMenuShips)){ $shipName=$rsMenuShip['shiName']; $shipHyper=F_crunch($rsMenuShip['shiName']); if($rsMenuShip['shiMates']) { $mates=$rsMenuShip['shiMates']; $blogs=$rsMenuShip['shiBlogURL']; $shipDisplay[$mates] = $blogs; } else $shipDisplay[$shipName] = $shipHyper; } array_unique($shipDisplay); foreach ($shipDisplay as $thePage=>$theLink){ echo "<a href='$H_absURL"."info/".F_crunch($rsMenuLines['linName'])."/$theLink/'>$thePage</a>"; } } I was thinking: SELECT line.linName, line.line_id, ship.shiName,ship.shiMates,ship.shiBlogURL FROM line, ship WHERE line.line_id=ship.ship_id ORDER BY linName But I can't seem to comprehend how the heck to get it to return: Company property property, property.
  14. Apparently, this is bad. Wish someone would have told me. Sadly, it is too simple. I had <? $company=mysql_query("select this and that") while($company=mysql_fetch_array($company)) { a bit of output; $properties=mysql_query("Select this and that from properties where company_id=$company['id']") while(properties=mysql_fetch_array()) { what I want to output } } I seem to be missing some understanding of what the heck to do now. Please, please any suggestions. I suck at object orientation, and arrays of arrays of arrays confuse the crap out of me. Am I missing something simple. Am I over-reacting? I have 15 companies, and about 100 properties total.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.