Search Results

Search found 18153 results on 727 pages for 'null coalescing'.

Page 50/727 | < Previous Page | 46 47 48 49 50 51 52 53 54 55 56 57  | Next Page >

  • SQL Server: SELECT rows with MAX(Column A), MAX(Column B), DISTINCT by related columns

    - by z531
    Scenario: Table A MasterID, Added Date, Added By, Updated Date, Updated By, 1, 1/1/2010, 'Fred', null, null 2, 1/2/2010, 'Barney', 'Mr. Slate', 1/7/2010 3, 1/3/2010, 'Noname', null, null Table B MasterID, Added Date, Added By, Updated Date, Updated By, 1, 1/3/2010, 'Wilma', 'The Great Kazoo', 1/5/2010 2, 1/4/2010, 'Betty', 'Dino', 1/4/2010 Table C MasterID, Added Date, Added By, Updated Date, Updated By, 1, 1/5/2010, 'Pebbles', null, null 2, 1/6/2010, 'BamBam', null, null Table D MasterID, Added Date, Added By, Updated Date, Updated By, 1, 1/2/2010, 'Noname', null, null 3, 1/4/2010, 'Wilma', null, null I need to return the max added date and corresponding user, and max updated date and corresponding user for each distinct record when tables A,B,C&D are UNION'ed, i.e.: 1, 1/5/2010, 'Pebbles', 'The Great Kazoo', 1/5/2010 2, 1/6/2010, 'BamBam', 'Mr. Slate', 1/7/2010 3, 1/4/2010, 'Wilma', null, null I know how to do this with one date/user per row, but with two is beyond me. DBMS is SQL Server 2005. T-SQL solution preferred. Thanks in advance, Dave

    Read the article

  • sql: Group by x,y,z; return grouped by x,y with lowest f(z)

    - by Sai Emrys
    This is for http://cssfingerprint.com I collect timing stats about how fast the different methods I use perform on different browsers, etc., so that I can optimize the scraping speed. Separately, I have a report about what each method returns for a handful of URLs with known-correct values, so that I can tell which methods are bogus on which browsers. (Each is different, alas.) The related tables look like this: CREATE TABLE `browser_tests` ( `id` int(11) NOT NULL AUTO_INCREMENT, `bogus` tinyint(1) DEFAULT NULL, `result` tinyint(1) DEFAULT NULL, `method` varchar(255) DEFAULT NULL, `url` varchar(255) DEFAULT NULL, `os` varchar(255) DEFAULT NULL, `browser` varchar(255) DEFAULT NULL, `version` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, `user_agent` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=33784 DEFAULT CHARSET=latin1 CREATE TABLE `method_timings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `method` varchar(255) DEFAULT NULL, `batch_size` int(11) DEFAULT NULL, `timing` int(11) DEFAULT NULL, `os` varchar(255) DEFAULT NULL, `browser` varchar(255) DEFAULT NULL, `version` varchar(255) DEFAULT NULL, `user_agent` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=28849 DEFAULT CHARSET=latin1 (user_agent is broken down pre-insert into browser, version, and os from a small list of recognized values using regex; I keep the original user-agent string just in case.) I have a query like this that tells me the average timing for every non-bogus browser / version / method tuple: select c, avg(bogus) as bog, timing, method, browser, version from browser_tests as b inner join ( select count(*) as c, round(avg(timing)) as timing, method, browser, version from method_timings group by browser, version, method having c > 10 order by browser, version, timing ) as t using (browser, version, method) group by browser, version, method having bog < 1 order by browser, version, timing; Which returns something like: c bog tim method browser version 88 0.8333 184 reuse_insert Chrome 4.0.249.89 18 0.0000 238 mass_insert_width Chrome 4.0.249.89 70 0.0400 246 mass_insert Chrome 4.0.249.89 70 0.0400 327 mass_noinsert Chrome 4.0.249.89 88 0.0556 367 reuse_reinsert Chrome 4.0.249.89 88 0.0556 383 jquery Chrome 4.0.249.89 88 0.0556 863 full_reinsert Chrome 4.0.249.89 187 0.0000 105 jquery Chrome 5.0.307.11 187 0.8806 109 reuse_insert Chrome 5.0.307.11 123 0.0000 110 mass_insert_width Chrome 5.0.307.11 176 0.0000 231 mass_noinsert Chrome 5.0.307.11 176 0.0000 237 mass_insert Chrome 5.0.307.11 187 0.0000 314 reuse_reinsert Chrome 5.0.307.11 187 0.0000 372 full_reinsert Chrome 5.0.307.11 12 0.7500 82 reuse_insert Chrome 5.0.335.0 12 0.2500 102 jquery Chrome 5.0.335.0 [...] I want to modify this query to return only the browser/version/method with the lowest timing - i.e. something like: 88 0.8333 184 reuse_insert Chrome 4.0.249.89 187 0.0000 105 jquery Chrome 5.0.307.11 12 0.7500 82 reuse_insert Chrome 5.0.335.0 [...] How can I do this, while still returning the method that goes with that lowest timing? I could filter it app-side, but I'd rather do this in mysql since it'd work better with my caching.

    Read the article

  • Django Comments and Users integration

    - by Patrick
    Hi folks, I am new to django. I am trying to use django.contrib.comments, I saw the table in the database like this, but how can I integrate it with user_id, user_photos, user_name, user_email....and those things with the django commenting system? I also tried to use thread-comments, but I can't configure it properly, is the django threadedcomments table similar to following ? Please let me know if any of you have done this before....all I need is a user login, and post comments, and then show list of comments with users' profile photos and username, as well as there comments. I think shouldn't be that hard. Thank you very much again. +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | content_type_id | int(11) | NO | MUL | NULL | | | object_pk | longtext | NO | | NULL | | | site_id | int(11) | NO | MUL | NULL | | | user_id | int(11) | YES | MUL | NULL | | | user_name | varchar(50) | NO | | NULL | | | user_email | varchar(75) | NO | | NULL | | | user_url | varchar(200) | NO | | NULL | | | comment | longtext | NO | | NULL | | | submit_date | datetime | NO | | NULL | | | ip_address | char(15) | YES | | NULL | | | is_public | tinyint(1) | NO | | NULL | | | is_removed | tinyint(1) | NO | | NULL | | +-----------------+--------------+------+-----+---------+----------------+

    Read the article

  • Problems getting foreign keys working in MySQL

    - by thehuby
    I've been trying to get a delete to cascade and it just doesn't seem to work. I'm sure I am missing something obvious, can anyone help me find it? I would expect a delete on the 'articles' table to trigger a delete on the corresponding rows in the 'article_section_lt' table. CREATE TABLE articles ( id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT, url_stub VARCHAR(255) NOT NULL UNIQUE, h1 VARCHAR(60) NOT NULL UNIQUE, title VARCHAR(60) NOT NULL, description VARCHAR(150) NOT NULL, summary VARCHAR(150) NOT NULL DEFAULT "", html_content TEXT, date DATE NOT NULL, updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )ENGINE=INNODB; CREATE TABLE article_sections ( /* blog, news etc */ id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT, url_stub VARCHAR(255) NOT NULL UNIQUE, h1 VARCHAR(60) NOT NULL, title VARCHAR(60) NOT NULL, description VARCHAR(150) NOT NULL, summary VARCHAR(150) NOT NULL DEFAULT "", html_content TEXT NOT NULL DEFAULT "" )ENGINE=INNODB; CREATE TABLE article_section_lt ( fk_article_id INTEGER UNSIGNED NOT NULL REFERENCES articles(id) ON DELETE CASCADE, fk_article_section_id INTEGER UNSIGNED NOT NULL )ENGINE=INNODB;

    Read the article

  • why is there extra using where in execution plan of query

    - by user366534
    I see plan of query: EXPLAIN SELECT * FROM `subscribers` WHERE state =4 AND date_added < '2010-12-23 11:47:45' It shows: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE subscribers range state_date_added state_date_added 9 NULL 8 Using where Here is indexes of table: Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment subscribers 0 PRIMARY 1 subscriber_id A 382039 NULL NULL BTREE subscribers 0 email_list_id 1 email_address A 191019 NULL NULL BTREE subscribers 0 email_list_id 2 list_id A 382039 NULL NULL BTREE subscribers 1 FK_list_id 1 list_id A 10 NULL NULL BTREE subscribers 1 state_date_added 1 state A 12 NULL NULL BTREE subscribers 1 state_date_added 2 date_added A 8128 NULL NULL BTREE The last two lines describes index what is supposed for the query. Why is there in extra column using where? Even If I fetch only state and date_added column, it has in extra column: Using where; Using index. I understand why it has using index, but I don't understand Using where here.

    Read the article

  • Very difficult SQL query

    - by db666
    For the following table definitions: Name Null? Type Comments ------------------------------- -------- ---- ------------------------------------ ENUM NOT NULL NUMBER(4) ENUM should not exceed a length of 4. ENAME CHAR(15) ADDRESS CHAR(25) ADDRESS should not exceed 25 characters. SALARY NUMBER(5) OFFICE CHAR(4) DNUM NOT NULL NUMBER(4) Department which this employee belongs to department Name Null? Type Comments ------------------------------- -------- ---- ------------------------------------- DNUM NOT NULL NUMBER(4) DMGR NOT NULL NUMBER(4) Department manager DNAME NOT NULL CHAR(15) project Name Null? Type Comments ------------------------------- -------- ---- ------------------------------------- PNUM NOT NULL NUMBER(4) PMGR NOT NULL NUMBER(4) Project manager PTITLE NOT NULL CHAR(15) emp_proj Name Null? Type ------------------------------- -------- ---- PNUM NOT NULL NUMBER(4) ENUM NOT NULL NUMBER(4) I have to write SQL query which will find the names of employees who do not share an office but work on the same project, and have different salaries... I've spent last three days trying to figure out something, but no idea as far. I will appreciate any advice.

    Read the article

  • Need MYSQL query for finding lowest score per game player

    - by Chris Barnhill
    I have a game on Facebook called Rails Across Europe. I have a Best Scores page where I show the players with the best 20 scores, which in game terms refers to the lowest winning turn. The problem is that there are a small number of players who play frequently, and their scores dominate the page. I'd like to make the scores page open to more players. So I thought that I could display the single lowest winning turn for each player instead of displaying all of the lowest winning turns for all players. The problem is that the query for this eludes me. So I hope that one of you brilliant StackOverflow folks can help me with this. I have included the relevant MYSQL table schemas below. Here are the the table relationships: player_stats contains statistics for either a game in progress or a completed game. If a game is in progress, winning_turn is zero (which means that games with a winning_turn of zero should not be included in the query). player_stats has a game_player table id reference. game_player contains data describing games currently in progress. game_player has a player table id reference. player contains data describing a person who plays the game. Here's the query I'm currently using: 'SELECT p.fb_user_id, ps.winning_turn, gp.difficulty_level, c.name as city_name, g.name as goods_name, d.cost FROM game_player as gp, player as p, player_stats as ps, demand as d, city as c, goods as g WHERE p.status = "ACTIVE" AND gp.player_id = p.id AND ps.game_player_id = gp.id AND d.id = ps.highest_demand_id AND c.id = d.city_id AND g.id = d.goods_id AND ps.winning_turn > 0 ORDER BY ps.winning_turn ASC, d.cost DESC LIMIT '.$limit.';'; Here are the relevant table schemas: -- -- Table structure for table `player_stats` -- CREATE TABLE IF NOT EXISTS `player_stats` ( `id` int(11) NOT NULL auto_increment, `game_player_id` int(11) NOT NULL, `winning_turn` int(11) NOT NULL, `highest_demand_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `game_player_id` (`game_player_id`,`highest_demand_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3814 ; -- -- Table structure for table `game_player` -- CREATE TABLE IF NOT EXISTS `game_player` ( `id` int(10) unsigned NOT NULL auto_increment, `game_id` int(10) unsigned NOT NULL, `player_id` int(10) unsigned NOT NULL, `player_number` int(11) NOT NULL, `funds` int(10) unsigned NOT NULL, `turn` int(10) unsigned NOT NULL, `difficulty_level` enum('STANDARD','ADVANCED','MASTER','ULTIMATE') NOT NULL, `date_last_used` datetime NOT NULL, PRIMARY KEY (`id`), KEY `game_id` (`game_id`,`player_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3814 ; -- -- Table structure for table `player` -- CREATE TABLE IF NOT EXISTS `player` ( `id` int(11) NOT NULL auto_increment, `fb_user_id` char(255) NOT NULL, `fb_proxied_email` text NOT NULL, `first_name` char(255) NOT NULL, `last_name` char(255) NOT NULL, `birthdate` date NOT NULL, `date_registered` datetime NOT NULL, `date_last_logged_in` datetime NOT NULL, `status` enum('ACTIVE','SUSPENDED','CLOSED') NOT NULL, PRIMARY KEY (`id`), KEY `fb_user_id` (`fb_user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1646 ;

    Read the article

  • correct mysql syntax error

    - by user2981651
    please could someone tell me the problem with this syntax because mysql 5.5.32 keeps tell me about an error CREATE TABLE `clients` ( `ID` tinyint(11) NOT NULL auto_increment, `title` varchar(10) NOT NULL default '', `firstName` varchar(30) NOT NULL default '', `lastName` varchar(30) NOT NULL default '', `address1` varchar(100) NOT NULL default '', `address2` varchar(100) NOT NULL default '', `town` varchar(100) NOT NULL default '', `province` varchar(100) NOT NULL default '', `country` varchar(40) NOT NULL default '', `postCode` varchar(20) NOT NULL default '', `telephone` varchar(20) NOT NULL default '', `email` varchar(100) NOT NULL default '', `cardNo` varchar(16) NOT NULL default '0000-00-00', `expiryDate` date NOT NULL default '0000-00-00', PRIMARY KEY (`ID`) ) TYPE=MyISAM COMMENT='customer table' AUTO_INCREMENT=1 ;

    Read the article

  • Generate a merge statement from table structure

    - by Nigel Rivett
    This code generates a merge statement joining on he natural key and checking all other columns to see if they have changed. The full version deals with type 2 processing and an audit trail but this version is useful. Just the insert or update part is handy too. Change the table at the top (spt_values in master in the version) and the join columns for the merge in @nk. The output generated is at the top and the code to run to generate it below. Output merge spt_values a using spt_values b on a.name = b.name and a.number = b.number and a.type = b.type when matched and (1=0 or (a.low b.low) or (a.low is null and b.low is not null) or (a.low is not null and b.low is null) or (a.high b.high) or (a.high is null and b.high is not null) or (a.high is not null and b.high is null) or (a.status b.status) or (a.status is null and b.status is not null) or (a.status is not null and b.status is null) ) then update set low = b.low , high = b.high , status = b.status when not matched by target then insert ( name , number , type , low , high , status ) values ( b.name , b.number , b.type , b.low , b.high , b.status ); Generator set nocount on declare @t varchar(128) = 'spt_values' declare @i int = 0 -- this is the natural key on the table used for the merge statement join declare @nk table (ColName varchar(128)) insert @nk select 'Number' insert @nk select 'Name' insert @nk select 'Type' declare @cols table (seq int, nkseq int, type int, colname varchar(128)) ;with cte as ( select ordinal_position, type = case when columnproperty(object_id(@t), COLUMN_NAME,'IsIdentity') = 1 then 3 when nk.ColName is not null then 1 else 0 end, COLUMN_NAME from information_schema.columns c left join @nk nk on c.column_name = nk.ColName where table_name = @t ) insert @cols (seq, nkseq, type, colname) select ordinal_position, row_number() over (partition by type order by ordinal_position) , type, COLUMN_NAME from cte declare @result table (i int, j int, k int, data varchar(500)) select @i = @i + 1 insert @result (i, data) select @i, 'merge ' + @t + ' a' select @i = @i + 1 insert @result (i, data) select @i, ' using cte b' select @i = @i + 1 insert @result (i, j, data) select @i, nkseq, ' ' + case when nkseq = 1 then 'on' else 'and' end + ' a.' + ColName + ' = b.' + ColName from @cols where type = 1 select @i = @i + 1 insert @result (i, data) select @i, ' when matched and (1=0' select @i = @i + 1 insert @result (i, j, k, data) select @i, seq, 1, ' or (a.' + ColName + ' b.' + ColName + ')' + ' or (a.' + ColName + ' is null and b.' + ColName + ' is not null)' + ' or (a.' + ColName + ' is not null and b.' + ColName + ' is null)' from @cols where type 1 select @i = @i + 1 insert @result (i, data) select @i, ' )' select @i = @i + 1 insert @result (i, data) select @i, ' then update set' select @i = @i + 1 insert @result (i, j, data) select @i, nkseq, ' ' + case when nkseq = 1 then ' ' else ', ' end + colname + ' = b.' + colname from @cols where type = 0 select @i = @i + 1 insert @result (i, data) select @i, ' when not matched by target then insert' select @i = @i + 1 insert @result (i, data) select @i, ' (' select @i = @i + 1 insert @result (i, j, data) select @i, seq, ' ' + case when seq = 1 then ' ' else ', ' end + colname from @cols where type 3 select @i = @i + 1 insert @result (i, data) select @i, ' )' select @i = @i + 1 insert @result (i, data) select @i, ' values' select @i = @i + 1 insert @result (i, data) select @i, ' (' select @i = @i + 1 insert @result (i, j, data) select @i, seq, ' ' + case when seq = 1 then ' ' else ', ' end + 'b.' + colname from @cols where type 3 select @i = @i + 1 insert @result (i, data) select @i, ' );' select data from @result order by i,j,k,data

    Read the article

  • Problems extracting information from RSS feed description field

    - by Graeme
    Hi, I've built an iPhone application using the parsing code from the TopSongs sample iPhone application. I've hit a problem though - the feed I'm trying to parse data from doesn't have a separate field for every piece of information (i.e. if it was for a feed about dogs, all the information such as dog type, dog age and dog price is contained in the feed. However, the TopSongs app relies on information having its own tags, so instead of using it uses and . So my question is this. How do I extract this information from the description field so that it can be parsed using the TopSongs parser? Can you somehow extract the dog age, price and type information using Yahoo Pipes and use that RSS feed for the feed? Or is there code that I can add to do it in application? Update: To view the code of my application parser (based on the TopSongs Core Data Apple provided application, see below. Here's a sample of one item from the the actual RSS feed I'm using (the description is longer, and has status,size, and a couple of other fields, but they're all formatted the same.: <item> <title>MOE, MARGRET STREET</title> <description> <b>District/Region:</b>&nbsp;REGION 09</br><b>Location:</b>&nbsp;MOE</br><b>Name:</b>&nbsp;MARGRET STREET</br></description> <pubDate>Thu,11 Mar 2010 05:43:03 GMT</pubDate> <guid>1266148</guid> </item> /* File: iTunesRSSImporter.m Abstract: Downloads, parses, and imports the iTunes top songs RSS feed into Core Data. Version: 1.1 Disclaimer: IMPORTANT: This Apple software is supplied to you by Apple Inc. ("Apple") in consideration of your agreement to the following terms, and your use, installation, modification or redistribution of this Apple software constitutes acceptance of these terms. If you do not agree with these terms, please do not use, install, modify or redistribute this Apple software. In consideration of your agreement to abide by the following terms, and subject to these terms, Apple grants you a personal, non-exclusive license, under Apple's copyrights in this original Apple software (the "Apple Software"), to use, reproduce, modify and redistribute the Apple Software, with or without modifications, in source and/or binary forms; provided that if you redistribute the Apple Software in its entirety and without modifications, you must retain this notice and the following text and disclaimers in all such redistributions of the Apple Software. Neither the name, trademarks, service marks or logos of Apple Inc. may be used to endorse or promote products derived from the Apple Software without specific prior written permission from Apple. Except as expressly stated in this notice, no other rights or licenses, express or implied, are granted by Apple herein, including but not limited to any patent rights that may be infringed by your derivative works or by other works in which the Apple Software may be incorporated. The Apple Software is provided by Apple on an "AS IS" basis. APPLE MAKES NO WARRANTIES, EXPRESS OR IMPLIED, INCLUDING WITHOUT LIMITATION THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, REGARDING THE APPLE SOFTWARE OR ITS USE AND OPERATION ALONE OR IN COMBINATION WITH YOUR PRODUCTS. IN NO EVENT SHALL APPLE BE LIABLE FOR ANY SPECIAL, INDIRECT, INCIDENTAL OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) ARISING IN ANY WAY OUT OF THE USE, REPRODUCTION, MODIFICATION AND/OR DISTRIBUTION OF THE APPLE SOFTWARE, HOWEVER CAUSED AND WHETHER UNDER THEORY OF CONTRACT, TORT (INCLUDING NEGLIGENCE), STRICT LIABILITY OR OTHERWISE, EVEN IF APPLE HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. Copyright (C) 2009 Apple Inc. All Rights Reserved. */ #import "iTunesRSSImporter.h" #import "Song.h" #import "Category.h" #import "CategoryCache.h" #import <libxml/tree.h> // Function prototypes for SAX callbacks. This sample implements a minimal subset of SAX callbacks. // Depending on your application's needs, you might want to implement more callbacks. static void startElementSAX(void *context, const xmlChar *localname, const xmlChar *prefix, const xmlChar *URI, int nb_namespaces, const xmlChar **namespaces, int nb_attributes, int nb_defaulted, const xmlChar **attributes); static void endElementSAX(void *context, const xmlChar *localname, const xmlChar *prefix, const xmlChar *URI); static void charactersFoundSAX(void *context, const xmlChar *characters, int length); static void errorEncounteredSAX(void *context, const char *errorMessage, ...); // Forward reference. The structure is defined in full at the end of the file. static xmlSAXHandler simpleSAXHandlerStruct; // Class extension for private properties and methods. @interface iTunesRSSImporter () @property BOOL storingCharacters; @property (nonatomic, retain) NSMutableData *characterBuffer; @property BOOL done; @property BOOL parsingASong; @property NSUInteger countForCurrentBatch; @property (nonatomic, retain) Song *currentSong; @property (nonatomic, retain) NSURLConnection *rssConnection; @property (nonatomic, retain) NSDateFormatter *dateFormatter; // The autorelease pool property is assign because autorelease pools cannot be retained. @property (nonatomic, assign) NSAutoreleasePool *importPool; @end static double lookuptime = 0; @implementation iTunesRSSImporter @synthesize iTunesURL, delegate, persistentStoreCoordinator; @synthesize rssConnection, done, parsingASong, storingCharacters, currentSong, countForCurrentBatch, characterBuffer, dateFormatter, importPool; - (void)dealloc { [iTunesURL release]; [characterBuffer release]; [currentSong release]; [rssConnection release]; [dateFormatter release]; [persistentStoreCoordinator release]; [insertionContext release]; [songEntityDescription release]; [theCache release]; [super dealloc]; } - (void)main { self.importPool = [[NSAutoreleasePool alloc] init]; if (delegate && [delegate respondsToSelector:@selector(importerDidSave:)]) { [[NSNotificationCenter defaultCenter] addObserver:delegate selector:@selector(importerDidSave:) name:NSManagedObjectContextDidSaveNotification object:self.insertionContext]; } done = NO; self.dateFormatter = [[[NSDateFormatter alloc] init] autorelease]; [dateFormatter setDateStyle:NSDateFormatterLongStyle]; [dateFormatter setTimeStyle:NSDateFormatterNoStyle]; // necessary because iTunes RSS feed is not localized, so if the device region has been set to other than US // the date formatter must be set to US locale in order to parse the dates [dateFormatter setLocale:[[[NSLocale alloc] initWithLocaleIdentifier:@"US"] autorelease]]; self.characterBuffer = [NSMutableData data]; NSURLRequest *theRequest = [NSURLRequest requestWithURL:iTunesURL]; // create the connection with the request and start loading the data rssConnection = [[NSURLConnection alloc] initWithRequest:theRequest delegate:self]; // This creates a context for "push" parsing in which chunks of data that are not "well balanced" can be passed // to the context for streaming parsing. The handler structure defined above will be used for all the parsing. // The second argument, self, will be passed as user data to each of the SAX handlers. The last three arguments // are left blank to avoid creating a tree in memory. context = xmlCreatePushParserCtxt(&simpleSAXHandlerStruct, self, NULL, 0, NULL); if (rssConnection != nil) { do { [[NSRunLoop currentRunLoop] runMode:NSDefaultRunLoopMode beforeDate:[NSDate distantFuture]]; } while (!done); } // Display the total time spent finding a specific object for a relationship NSLog(@"lookup time %f", lookuptime); // Release resources used only in this thread. xmlFreeParserCtxt(context); self.characterBuffer = nil; self.dateFormatter = nil; self.rssConnection = nil; self.currentSong = nil; [theCache release]; theCache = nil; NSError *saveError = nil; NSAssert1([insertionContext save:&saveError], @"Unhandled error saving managed object context in import thread: %@", [saveError localizedDescription]); if (delegate && [delegate respondsToSelector:@selector(importerDidSave:)]) { [[NSNotificationCenter defaultCenter] removeObserver:delegate name:NSManagedObjectContextDidSaveNotification object:self.insertionContext]; } if (self.delegate != nil && [self.delegate respondsToSelector:@selector(importerDidFinishParsingData:)]) { [self.delegate importerDidFinishParsingData:self]; } [importPool release]; self.importPool = nil; } - (NSManagedObjectContext *)insertionContext { if (insertionContext == nil) { insertionContext = [[NSManagedObjectContext alloc] init]; [insertionContext setPersistentStoreCoordinator:self.persistentStoreCoordinator]; } return insertionContext; } - (void)forwardError:(NSError *)error { if (self.delegate != nil && [self.delegate respondsToSelector:@selector(importer:didFailWithError:)]) { [self.delegate importer:self didFailWithError:error]; } } - (NSEntityDescription *)songEntityDescription { if (songEntityDescription == nil) { songEntityDescription = [[NSEntityDescription entityForName:@"Song" inManagedObjectContext:self.insertionContext] retain]; } return songEntityDescription; } - (CategoryCache *)theCache { if (theCache == nil) { theCache = [[CategoryCache alloc] init]; theCache.managedObjectContext = self.insertionContext; } return theCache; } - (Song *)currentSong { if (currentSong == nil) { currentSong = [[Song alloc] initWithEntity:self.songEntityDescription insertIntoManagedObjectContext:self.insertionContext]; } return currentSong; } #pragma mark NSURLConnection Delegate methods // Forward errors to the delegate. - (void)connection:(NSURLConnection *)connection didFailWithError:(NSError *)error { [self performSelectorOnMainThread:@selector(forwardError:) withObject:error waitUntilDone:NO]; // Set the condition which ends the run loop. done = YES; } // Called when a chunk of data has been downloaded. - (void)connection:(NSURLConnection *)connection didReceiveData:(NSData *)data { // Process the downloaded chunk of data. xmlParseChunk(context, (const char *)[data bytes], [data length], 0); } - (void)connectionDidFinishLoading:(NSURLConnection *)connection { // Signal the context that parsing is complete by passing "1" as the last parameter. xmlParseChunk(context, NULL, 0, 1); context = NULL; // Set the condition which ends the run loop. done = YES; } #pragma mark Parsing support methods static const NSUInteger kImportBatchSize = 20; - (void)finishedCurrentSong { parsingASong = NO; self.currentSong = nil; countForCurrentBatch++; // Periodically purge the autorelease pool and save the context. The frequency of this action may need to be tuned according to the // size of the objects being parsed. The goal is to keep the autorelease pool from growing too large, but // taking this action too frequently would be wasteful and reduce performance. if (countForCurrentBatch == kImportBatchSize) { [importPool release]; self.importPool = [[NSAutoreleasePool alloc] init]; NSError *saveError = nil; NSAssert1([insertionContext save:&saveError], @"Unhandled error saving managed object context in import thread: %@", [saveError localizedDescription]); countForCurrentBatch = 0; } } /* Character data is appended to a buffer until the current element ends. */ - (void)appendCharacters:(const char *)charactersFound length:(NSInteger)length { [characterBuffer appendBytes:charactersFound length:length]; } - (NSString *)currentString { // Create a string with the character data using UTF-8 encoding. UTF-8 is the default XML data encoding. NSString *currentString = [[[NSString alloc] initWithData:characterBuffer encoding:NSUTF8StringEncoding] autorelease]; [characterBuffer setLength:0]; return currentString; } @end #pragma mark SAX Parsing Callbacks // The following constants are the XML element names and their string lengths for parsing comparison. // The lengths include the null terminator, to ensure exact matches. static const char *kName_Item = "item"; static const NSUInteger kLength_Item = 5; static const char *kName_Title = "title"; static const NSUInteger kLength_Title = 6; static const char *kName_Category = "category"; static const NSUInteger kLength_Category = 9; static const char *kName_Itms = "itms"; static const NSUInteger kLength_Itms = 5; static const char *kName_Artist = "description"; static const NSUInteger kLength_Artist = 7; static const char *kName_Album = "description"; static const NSUInteger kLength_Album = 6; static const char *kName_ReleaseDate = "releasedate"; static const NSUInteger kLength_ReleaseDate = 12; /* This callback is invoked when the importer finds the beginning of a node in the XML. For this application, out parsing needs are relatively modest - we need only match the node name. An "item" node is a record of data about a song. In that case we create a new Song object. The other nodes of interest are several of the child nodes of the Song currently being parsed. For those nodes we want to accumulate the character data in a buffer. Some of the child nodes use a namespace prefix. */ static void startElementSAX(void *parsingContext, const xmlChar *localname, const xmlChar *prefix, const xmlChar *URI, int nb_namespaces, const xmlChar **namespaces, int nb_attributes, int nb_defaulted, const xmlChar **attributes) { iTunesRSSImporter *importer = (iTunesRSSImporter *)parsingContext; // The second parameter to strncmp is the name of the element, which we known from the XML schema of the feed. // The third parameter to strncmp is the number of characters in the element name, plus 1 for the null terminator. if (prefix == NULL && !strncmp((const char *)localname, kName_Item, kLength_Item)) { importer.parsingASong = YES; } else if (importer.parsingASong && ( (prefix == NULL && (!strncmp((const char *)localname, kName_Title, kLength_Title) || !strncmp((const char *)localname, kName_Category, kLength_Category))) || ((prefix != NULL && !strncmp((const char *)prefix, kName_Itms, kLength_Itms)) && (!strncmp((const char *)localname, kName_Artist, kLength_Artist) || !strncmp((const char *)localname, kName_Album, kLength_Album) || !strncmp((const char *)localname, kName_ReleaseDate, kLength_ReleaseDate))) )) { importer.storingCharacters = YES; } } /* This callback is invoked when the parse reaches the end of a node. At that point we finish processing that node, if it is of interest to us. For "item" nodes, that means we have completed parsing a Song object. We pass the song to a method in the superclass which will eventually deliver it to the delegate. For the other nodes we care about, this means we have all the character data. The next step is to create an NSString using the buffer contents and store that with the current Song object. */ static void endElementSAX(void *parsingContext, const xmlChar *localname, const xmlChar *prefix, const xmlChar *URI) { iTunesRSSImporter *importer = (iTunesRSSImporter *)parsingContext; if (importer.parsingASong == NO) return; if (prefix == NULL) { if (!strncmp((const char *)localname, kName_Item, kLength_Item)) { [importer finishedCurrentSong]; } else if (!strncmp((const char *)localname, kName_Title, kLength_Title)) { importer.currentSong.title = importer.currentString; } else if (!strncmp((const char *)localname, kName_Category, kLength_Category)) { double before = [NSDate timeIntervalSinceReferenceDate]; Category *category = [importer.theCache categoryWithName:importer.currentString]; double delta = [NSDate timeIntervalSinceReferenceDate] - before; lookuptime += delta; importer.currentSong.category = category; } } else if (!strncmp((const char *)prefix, kName_Itms, kLength_Itms)) { if (!strncmp((const char *)localname, kName_Artist, kLength_Artist)) { NSString *string = importer.currentSong.artist; NSArray *strings = [string componentsSeparatedByString: @", "]; //importer.currentSong.artist = importer.currentString; } else if (!strncmp((const char *)localname, kName_Album, kLength_Album)) { importer.currentSong.album = importer.currentString; } else if (!strncmp((const char *)localname, kName_ReleaseDate, kLength_ReleaseDate)) { NSString *dateString = importer.currentString; importer.currentSong.releaseDate = [importer.dateFormatter dateFromString:dateString]; } } importer.storingCharacters = NO; } /* This callback is invoked when the parser encounters character data inside a node. The importer class determines how to use the character data. */ static void charactersFoundSAX(void *parsingContext, const xmlChar *characterArray, int numberOfCharacters) { iTunesRSSImporter *importer = (iTunesRSSImporter *)parsingContext; // A state variable, "storingCharacters", is set when nodes of interest begin and end. // This determines whether character data is handled or ignored. if (importer.storingCharacters == NO) return; [importer appendCharacters:(const char *)characterArray length:numberOfCharacters]; } /* A production application should include robust error handling as part of its parsing implementation. The specifics of how errors are handled depends on the application. */ static void errorEncounteredSAX(void *parsingContext, const char *errorMessage, ...) { // Handle errors as appropriate for your application. NSCAssert(NO, @"Unhandled error encountered during SAX parse."); } // The handler struct has positions for a large number of callback functions. If NULL is supplied at a given position, // that callback functionality won't be used. Refer to libxml documentation at http://www.xmlsoft.org for more information // about the SAX callbacks. static xmlSAXHandler simpleSAXHandlerStruct = { NULL, /* internalSubset */ NULL, /* isStandalone */ NULL, /* hasInternalSubset */ NULL, /* hasExternalSubset */ NULL, /* resolveEntity */ NULL, /* getEntity */ NULL, /* entityDecl */ NULL, /* notationDecl */ NULL, /* attributeDecl */ NULL, /* elementDecl */ NULL, /* unparsedEntityDecl */ NULL, /* setDocumentLocator */ NULL, /* startDocument */ NULL, /* endDocument */ NULL, /* startElement*/ NULL, /* endElement */ NULL, /* reference */ charactersFoundSAX, /* characters */ NULL, /* ignorableWhitespace */ NULL, /* processingInstruction */ NULL, /* comment */ NULL, /* warning */ errorEncounteredSAX, /* error */ NULL, /* fatalError //: unused error() get all the errors */ NULL, /* getParameterEntity */ NULL, /* cdataBlock */ NULL, /* externalSubset */ XML_SAX2_MAGIC, // NULL, startElementSAX, /* startElementNs */ endElementSAX, /* endElementNs */ NULL, /* serror */ }; Thanks.

    Read the article

  • Mysql connections hanging in login state

    - by Mark Rose
    Suddenly today, I had an issue with connections to mysql hanging when connecting by IP address (e.g. mysql -h 10.1.248.20), but I could still connection using localhost fine, regardless the user. mysql> show processlist; +----+----------------------+------------------+------+---------+------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------------------+------------------+------+---------+------+-----------------------------------------------------------------------+------------------+ | 1 | system user | | NULL | Connect | 10 | Connecting to master | NULL | | 2 | system user | | NULL | Connect | 10 | Has read all relay log; waiting for the slave I/O thread to update it | NULL | | 37 | unauthenticated user | 10.1.248.3:36694 | NULL | Connect | NULL | login | NULL | | 38 | unauthenticated user | 10.1.248.3:36695 | NULL | Connect | NULL | login | NULL | | 39 | unauthenticated user | 10.1.248.3:36696 | NULL | Connect | NULL | login | NULL | | 40 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+----------------------+------------------+------+---------+------+-----------------------------------------------------------------------+------------------+ 6 rows in set (0.00 sec) Yes, I realize replication was broken in that output above; that was after I tried restart MySQL (it was an emergency). And as suddenly as things stopped working, they started working again. DNS was working fine at the time. Replication was still working. MySQL was responsive. Does anyone have any idea what would cause MySQL logins from remote IPs to hang suddenly?

    Read the article

  • SubSonic 3 ignoring columns in Select()

    - by jessegavin
    I have a table like so.. CREATE TABLE [dbo].[Locations_Hours]( [LocationID] [int] NOT NULL, [sun_open] [nvarchar](10) NULL, [sun_close] [nvarchar](10) NULL, [mon_open] [nvarchar](10) NULL, [mon_close] [nvarchar](10) NULL, [tue_open] [nvarchar](10) NULL, [tue_close] [nvarchar](10) NULL, [wed_open] [nvarchar](10) NULL, [wed_close] [nvarchar](10) NULL, [thu_open] [nvarchar](10) NULL, [thu_close] [nvarchar](10) NULL, [fri_open] [nvarchar](10) NULL, [fri_close] [nvarchar](10) NULL, [sat_open] [nvarchar](10) NULL, [sat_close] [nvarchar](10) NULL, [StoreNumber] [int] NULL, [LocationHourID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_Locations_Hours] PRIMARY KEY CLUSTERED ( [LocationHourID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] And SubSonic 3 is generating a class with the following properties int LocationID string monopen string monclose string tueopen string tueclose string wedopen string wedclose string thuopen string thuclose string friopen string friclose string satopen string satclose string sunopen string sunclose int? StoreNumber int LocationHourID When I try to perform a query against this class like so.. var result = DB.LocationHours.Where(o => o.LocationID == _locationId); This is the resulting SQL query that SubSonic generates. SELECT [t0].[LocationHourID], [t0].[LocationID], [t0].[StoreNumber] FROM [dbo].[Locations_Hours] AS t0 WHERE ([t0].[LocationID] = 4019) I cannot figure out why SubSonic is omitting the nvarchar fields when it generates the SELECT statement. Anyone got any ideas?

    Read the article

  • Merging Passed Parameters

    - by Josh Crowder
    I have a two data arrays sent in from a form, one called transloaded and the other video which is the actual form for the model. I need to get [:video_encoded][:url] and save that to [:video][:flash_url] This is the passed arguments or transloaded, when I try and access [:transload][:results][:video_encode] I get nil. print params[:transload] { "assembly_id":"d59b4293b3d79d2ccd1948c02421c6a6", "status":"success", "uploads":{ "video":{ "name":"bbc_one.mp4", "mime":"video/mp4", "ext":"mp4", "size":601104, "meta":{ "width":720, "height":404, "video_fps":25, "video_bitrate":null, "video_format":"avc1", "video_codec":"ffh264", "audio_bitrate":"128k", "audio_codec":"faad", "duration":3.07, "device_vendor":null, "device_name":null, "device_software":null, "latitude":null, "longitude":null }, "url":"http://tmp.transloadit.com/" } }, "results":{ "video_encode":{ "name":"bbc_one.flv", "mime":"video/x-flv", "steps":["encode","export"], "ext":"flv", "size":388317, "meta":{ "width":480, "height":320, "video_fps":25, "video_bitrate":"512k", "video_format":"FLV1", "video_codec":"ffflv", "audio_bitrate":"64k", "audio_codec":"mp3", "duration":3.11, "device_vendor":null, "device_name":null, "device_software":null, "latitude":null, "longitude":null }, "url":"http://s3.transloadit.com/b7deac9c96af6c745e914e25d0350baa/7a/2b09e822265ac2328789b40dcc02ae/bbc_one.flv" }, "video_encode_iphone":{ "name":"bbc_one.qt", "mime":"video/quicktime", "steps":["encode_iphone","export"], "ext":"qt", "size":218236, "meta":{ "width":480, "height":320, "video_fps":25, "video_bitrate":null, "video_format":"avc1", "video_codec":"ffh264", "audio_bitrate":"128k", "audio_codec":"faad", "duration":3.04, "device_vendor":null, "device_name":null, "device_software":null, "latitude":null, "longitude":null }, "url":"http://s3.transloadit.com/31/58bcc80d5345e52a42c9773125e8f0/bbc_one.qt" } } } Here is what I am trying to use video_links = { :flash_url => params[:transload][:results][:video_encode][:url], :mp4_url => params[:transload][:results][:video_encode_iphone][:url] } params[:video].merge(video_links)

    Read the article

  • sql foreign keys

    - by Paul Est
    I was create tables with the syntax in phpmyadmin: DROP TABLE IF EXISTS users; DROP TABLE IF EXISTS info; CREATE TABLE users ( user_id int unsigned NOT NULL auto_increment, email varchar(100) NOT NULL default '', pwd varchar(32) NOT NULL default '', isAdmin int(1) unsigned NOT NULL, PRIMARY KEY (user_id) ) TYPE=INNODB; CREATE TABLE info ( info_id int unsigned NOT NULL auto_increment, first_name varchar(100) NOT NULL default '', last_name varchar(100) NOT NULL default '', address varchar(300) NOT NULL default '', zipcode varchar(100) NOT NULL default '', personal_phone varchar(100) NOT NULL default '', mobilephone varchar(100) NOT NULL default '', faxe varchar(100) NOT NULL default '', email2 varchar(100) NOT NULL default '', country varchar(100) NOT NULL default '', sex varchar(1) NOT NULL default '', birth varchar(1) NOT NULL default '', email varchar(100) NOT NULL default '', PRIMARY KEY (info_id), FOREIGN KEY (email) REFERENCES users(email) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB; But shows the error "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=INNODB' at line 11 " If i remove the TYPE=INNODB in the end of create the tables, it will show the error "#1005 - Can't create table 'curriculo.info' (errno: 150) ".

    Read the article

  • rake test not copying development postgres db with sequences

    - by Robert Crida
    I am trying to develop a rails application on postgresql using a sequence to increment a field instead of a default ruby approach based on validates_uniqueness_of. This has proved challenging for a number of reasons: 1. This is a migration of an existing table, not a new table or column 2. Using parameter :default = "nextval('seq')" didn't work because it tries to set it in parenthesis 3. Eventually got migration working in 2 steps: change_column :work_commencement_orders, :wco_number_suffix, :integer, :null => false#, :options => "set default nextval('wco_number_suffix_seq')" execute %{ ALTER TABLE work_commencement_orders ALTER COLUMN wco_number_suffix SET DEFAULT nextval('wco_number_suffix_seq'); } Now this would appear to have done the correct thing in the development database and the schema looks like: wco_number_suffix | integer | not null default nextval('wco_number_suffix_seq'::regclass) However, the tests are failing with PGError: ERROR: null value in column "wco_number_suffix" violates not-null constraint : INSERT INTO "work_commencement_orders" ("expense_account_id", "created_at", "process_id", "vo2_issued_on", "wco_template", "updated_at", "notes", "process_type", "vo_number", "vo_issued_on", "vo2_number", "wco_type_id", "created_by", "contractor_id", "old_wco_type", "master_wco_number", "deadline", "updated_by", "detail", "elective_id", "authorization_batch_id", "delivery_lat", "delivery_long", "operational", "state", "issued_on", "delivery_detail") VALUES(226, '2010-05-31 07:02:16.764215', 728, NULL, E'Default', '2010-05-31 07:02:16.764215', NULL, E'Procurement::Process', NULL, NULL, NULL, 226, NULL, 276, NULL, E'MWCO-213', '2010-06-14 07:02:16.756952', NULL, E'Name 4597', 220, NULL, NULL, NULL, 'f', E'pending', NULL, E'728 Test Road; Test Town; 1234; Test Land') RETURNING "id" The explanation can be found when you inspect the schema of the test database: wco_number_suffix | integer | not null So what happened to the default? I tried adding task: template: smmt_ops_development to the database.yml file which has the effect of issuing create database smmt_ops_test template = "smmt_ops_development" encoding = 'utf8' I have verified that if I issue this then it does in fact copy the default nextval. So clearly rails is doing something after that to suppress it again. Any suggestions as to how to fix this? Thanks Robert

    Read the article

  • Loop over Json using Jquery

    - by mayanna
    Below is my Json Data received from Ajax response. { "error": { "errorCode": "0001", "errorText": "SUCCESS" }, "responselist": [ { "count": 2, "event": [ { "startDate": null, "eventId": 1234, "eventName": "Interview", "modifiedUser": "User", "eventTypeCode": "1", "eventVenue": null, "eventSpecialInst": "isnsdf", "eventStatusCode": "OP", "eventLangCode": "Eng", "eventDesc": "sdfsadfsd", "fromEmailId": "[email protected]", "rsvpDeadline": 5, "canceledInd": "yes", "canceldEmailText": "sdfasdfasdfasfasdfasdfasdf", "daysToWaitlistLastCall": 5, "daysToReminderAdmin": 6, "daysToReminderEvent": 3, "daysToReminderInvitation": 2, "endDate": null, "venueAddrLine1": null, "venueAddrLine2": null, "venueAddrLine3": null, "cityCode": null, "stateCode": null, "appId": null, "modifiedDate": "2010-12-16", "countryCode": null, "zipCode": null, "user_id": null, "updateFlag": "R" }, { "startDate": null, "eventId": 4321, "eventName": "Seasonal Hiring", "modifiedUser": "User", "eventTypeCode": "1", "eventVenue": null, "eventSpecialInst": "isnsdf", "eventStatusCode": "OP", "eventLangCode": "Eng", "eventDesc": "sdfsadfsd", "fromEmailId": "[email protected]", "rsvpDeadline": 5, "canceledInd": "yes", "canceldEmailText": "sdfasdfasdfasfasdfasdfasdf", "daysToWaitlistLastCall": 5, "daysToReminderAdmin": 6, "daysToReminderEvent": 3, "daysToReminderInvitation": 2, "endDate": null, "venueAddrLine1": "KFC", "venueAddrLine2": "The Forum", "venueAddrLine3": "Koramangala", "cityCode": "Bangalore", "stateCode": "Karnataka", "appId": null, "modifiedDate": "2010-12-16", "countryCode": "India", "zipCode": "560040", "user_id": null, "updateFlag": "R" } ] } ] } Using below code to extract information inside event object. But I am not able to do it. Need guidance. $.ajax({ url:"<%=request.getContextPath()%>/service/showInvitedEvents/21", dataType:"json", success: function(jsonData) { alert("Inside response success"); $.each(jsonData.responselist.event,function(i,item) $.each(Employees,function(i,item) { alert('Iteration is' + i); var teventName = item.eventName; var teventVenue = item.eventVenue; var tstartDate = item.startDate; var tendDate = item.endDate; var tstarend = tstartDate +" - "+ tendDate ; $("#eventTable tbody").append("<tr><td><a id="+teventName+i+" href=<%=request.getContextPath()%>/service/session/1234>"+teventName+"</a></td><td>"+teventVenue+"</td><td>"+tstarend+"</td></tr>"); });

    Read the article

  • Getting the most recent post based on date

    - by camcim
    Hi guys, How do I go about displaying the most recent post when I have two tables, both containing a column called creation_date This would be simple if all I had to do was get the most recent post based on posts created_on value however if a post contains replies I need to factor this into the equation. If a post has a more recent reply I want to get the replies created_on value but also get the posts post_id and subject. The posts table structure: CREATE TABLE `posts` ( `post_id` bigint(20) unsigned NOT NULL auto_increment, `cat_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `subject` tinytext NOT NULL, `comments` text NOT NULL, `created_on` datetime NOT NULL, `status` varchar(10) NOT NULL default 'INACTIVE', `private_post` varchar(10) NOT NULL default 'PUBLIC', `db_location` varchar(10) NOT NULL, PRIMARY KEY (`post_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; The replies table structure: CREATE TABLE `replies` ( `reply_id` bigint(20) unsigned NOT NULL auto_increment, `post_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `comments` text NOT NULL, `created_on` datetime NOT NULL, `notify` varchar(5) NOT NULL default 'YES', `status` varchar(10) NOT NULL default 'INACTIVE', `db_location` varchar(10) NOT NULL, PRIMARY KEY (`reply_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; Here is my query so far. I've removed my attempt of extracting the dates. $strQuery = "SELECT posts.post_id, posts.created_on, replies.created_on, posts.subject "; $strQuery = $strQuery."FROM posts ,replies "; $strQuery = $strQuery."WHERE posts.post_id = replies.post_id "; $strQuery = $strQuery."AND posts.cat_id = '".$row->cat_id."'";

    Read the article

  • mysql query help, take total sum from a table, and based on discount value on another table calcula

    - by vegatron
    hi I have a table called invoices: CREATE TABLE IF NOT EXISTS `si_invoices` ( `id` int(10) NOT NULL AUTO_INCREMENT, `biller_id` int(10) NOT NULL DEFAULT '0', `customer_id` int(10) NOT NULL DEFAULT '0', `type_id` int(10) NOT NULL DEFAULT '0', `inv_tax_id` int(10) NOT NULL, `date` date NOT NULL DEFAULT '0000-00-00', `unreg_customer` tinyint(1) NOT NULL DEFAULT '0', `discount` decimal(10,2) NOT NULL DEFAULT '0.00', `discount_type` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=20 ; each invoice has items that are stored in invoice_items table : CREATE TABLE IF NOT EXISTS `si_invoice_items` ( `id` int(10) NOT NULL AUTO_INCREMENT, `invoice_id` int(10) NOT NULL DEFAULT '0', `quantity` int(10) unsigned NOT NULL DEFAULT '0', `product_id` int(10) DEFAULT '0', `warehouse_id` int(10) NOT NULL, `unit_price` decimal(25,2) DEFAULT '0.00', `total` decimal(25,2) DEFAULT '0.00', `description` text, PRIMARY KEY (`id`), KEY `invoice_id` (`invoice_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=56 ; and tax table CREATE TABLE IF NOT EXISTS `si_tax` ( `tax_id` int(11) NOT NULL AUTO_INCREMENT, `tax_description` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `tax_percentage` decimal(25,6) DEFAULT '0.000000', `type` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL, `tax_enabled` varchar(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1', PRIMARY KEY (`tax_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ; here is what I want to do step 1: get the sum_total of the invoice Items for a speciefic invoice step 2: calculate the discount, in the invoice table I have a discount_type field : if its equal to 0 , then there will be no discount if its equal to 1 , the discount value will be stored in the discount field if its equal to 2 , the discount is a percentage of sum_total step 3: calculate the taxes based on inv_tax_id based on the tax id , I will look in the tax table , get the tax_percentage and multiply it by the (sum_total - discount) in short here is the equation $gross_total = $sum_total - $disount + taxes

    Read the article

  • Fetch the most viewed data in databases

    - by Erik Edgren
    I want to get the most viewed photo from the database but I don't know how I shall accomplish this. Here's my SQL at the moment: SELECT * FROM photos AS p, viewers AS v WHERE p.id = v.id_photo GROUP BY v.id_photo The databases: CREATE TABLE IF NOT EXISTS `photos` ( `id` int(10) NOT NULL AUTO_INCREMENT, `photo_filename` varchar(50) NOT NULL, `photo_camera` varchar(150) NOT NULL, `photo_taken` datetime NOT NULL, `photo_resolution` varchar(10) NOT NULL, `photo_exposure` varchar(10) NOT NULL, `photo_iso` varchar(3) NOT NULL, `photo_fnumber` varchar(10) NOT NULL, `photo_focallength` varchar(10) NOT NULL, `post_coordinates` text NOT NULL, `post_description` text NOT NULL, `post_uploaded` datetime NOT NULL, `post_edited` datetime NOT NULL, `checkbox_approxcoor` enum('0','1') NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) CREATE TABLE IF NOT EXISTS `viewers` ( `id` int(10) NOT NULL AUTO_INCREMENT, `id_photo` int(10) DEFAULT '0', `ipaddress` text NOT NULL, `date_viewed` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) The data in viewers looks like this: (1, 85, '3892a0ab97d6ff325f285b27b847070f', '2012-06-21 22:49:25'), (2, 84, '3892a0ab97d6ff325f285b27b847070f', '2012-06-21 22:49:25'), (3, 85, '3892a0ab97d6ff325f285b27b847070f', '2012-06-21 22:49:25'); One single row from the database for photos to understand how the rows looks like in this database: (85, 'P1170986.JPG', 'Panasonic DMC-LX3', '2012-06-19 18:00:40', '3968x2232', '10/8000', '80', '50/10', '51/10', '', '', '2012-06-19 18:45:17', '0000-00-00 00:00:00', '0') At the moment the SQL only prints the photo with ID 84. In this case it's wrong - it should print out the photo with ID 85. How can I fix this problem? Thanks in advance.

    Read the article

  • Hibernate does not allow an embedded object with an int field to be null?

    - by Jason Novak
    Hibernate does not allow me to persist an object that contains an null embedded object with an integer field. For example, if I have a class called Thing that looks like this @Entity public class Thing { @Id public String id; public Part part; } where Part is an embeddable class that looks like this @Embeddable public class Part { public String a; public int b; } then trying to persist a Thing object with a null Part causes Hibernate to throw an Exception. In particular, this code Thing th = new Thing(); th.id = "thing.1"; th.part = null; session.saveOrUpdate(th); causes Hibernate to throw this Exception org.hibernate.PropertyValueException: not-null property references a null or transient value: com.ace.moab.api.jobs.Thing.part My guess is that this is happening because Part is an embedded class and so Part.a and Part.b are simply columns in the Thing database table. Since the Thing.part is null Hibernate wants to set the Part.a and Part.b column values to null for the row for thing.1. However, Part.b is an integer and Hibernate will not allow integer columns in the database to be null. This is what causes the Exception, right? So I am looking for workarounds for this problem. I noticed making Part.b an Integer instead of an int seems to work, but for reasons I won't bore you with this is not a good option for us. Thanks!

    Read the article

  • Optimize SQL query (Facebook-like application)

    - by fabriciols
    My application is similar to Facebook, and I'm trying to optimize the query that get user records. The user records are that he as src ou dst. The src is in usermuralentry directly, the dst list are in usermuralentry_user. So, a entry can have one src and many dst. I have those tables: mysql> desc usermuralentry ; +-----------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_src_id | int(11) | NO | MUL | NULL | | | private | tinyint(1) | NO | | NULL | | | content | longtext | NO | | NULL | | | date | datetime | NO | | NULL | | | last_update | datetime | NO | | NULL | | +-----------------+------------------+------+-----+---------+----------------+ 10 rows in set (0.10 sec) mysql> desc usermuralentry_user ; +-------------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | usermuralentry_id | int(11) | NO | MUL | NULL | | | userinfo_id | int(11) | NO | MUL | NULL | | +-------------------+---------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) And the following query to retrieve information from two users. mysql> explain SELECT * FROM usermuralentry AS a , usermuralentry_user AS b WHERE a.user_src_id IN ( 1, 2 ) OR ( a.id = b.usermuralentry_id AND b.userinfo_id IN ( 1, 2 ) ); +----+-------------+-------+------+-------------------------------------------------------------------------------------------+------+---------+------+---------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------------------------------------------------------------------------------+------+---------+------+---------+------------------------------------------------+ | 1 | SIMPLE | b | ALL | usermuralentry_id,usermuralentry_user_bcd7114e,usermuralentry_user_6b192ca7 | NULL | NULL | NULL | 147188 | | | 1 | SIMPLE | a | ALL | PRIMARY | NULL | NULL | NULL | 1371289 | Range checked for each record (index map: 0x1) | +----+-------------+-------+------+-------------------------------------------------------------------------------------------+------+---------+------+---------+------------------------------------------------+ 2 rows in set (0.00 sec) but it is taking A LOT of time... Some tips to optimize? Can the table schema be better in my application?

    Read the article

  • Why does a bash-zenity script has that title on Unity Panel and that icon on Unity Launcher?

    - by Sadi
    I have this small bash script which helps use Infinality font rendering options via a more user-friendly Zenity window. But whenever I launch it I have this "Color Picker" title on Unity Panel together with the icon assigned for "Color Picker" utility. I wonder why and how this is happening and how I can change it? #!/bin/bash # A simple script to provide a basic, zenity-based GUI to change Infinality Style. # v.1.2 # infinality_current=`cat /etc/profile.d/infinality-settings.sh | grep "USE_STYLE=" | awk -F'"' '{print $2}'` sudo_password="$( gksudo --print-pass --message 'Provide permission to make system changes: Enter your password to start or press Cancel to quit.' -- : 2>/dev/null )" # Check for null entry or cancellation. if [[ ${?} != 0 || -z ${sudo_password} ]] then # Add a zenity message here if you want. exit 4 fi # Check that the password is valid. if ! sudo -kSp '' [ 1 ] <<<"${sudo_password}" 2>/dev/null then # Add a zenity message here if you want. exit 4 fi # menu(){ im="zenity --width=500 --height=490 --list --radiolist --title=\"Change Infinality Style\" --text=\"Current <i>Infinality Style</i> is\: <b>$infinality_current</b>\n? To <i>change</i> it, select any other option below and press <b>OK</b>\n? To <i>quit without changing</i>, press <b>Cancel</b>\" " im=$im" --column=\" \" --column \"Options\" --column \"Description\" " im=$im"FALSE \"DEFAULT\" \"Use default settings - a compromise that should please most people\" " im=$im"FALSE \"OSX\" \"Simulate OSX rendering\" " im=$im"FALSE \"IPAD\" \"Simulate iPad rendering\" " im=$im"FALSE \"UBUNTU\" \"Simulate Ubuntu rendering\" " im=$im"FALSE \"LINUX\" \"Generic Linux style - no snapping or certain other tweaks\" " im=$im"FALSE \"WINDOWS\" \"Simulate Windows rendering\" " im=$im"FALSE \"WIN7\" \"Simulate Windows 7 rendering with normal glyphs\" " im=$im"FALSE \"WINLIGHT\" \"Simulate Windows 7 rendering with lighter glyphs\" " im=$im"FALSE \"VANILLA\" \"Just subpixel hinting\" " im=$im"FALSE \"CLASSIC\" \"Infinality rendering circa 2010 - No snapping.\" " im=$im"FALSE \"NUDGE\" \"Infinality - Classic with lightly stem snapping and tweaks\" " im=$im"FALSE \"PUSH\" \"Infinality - Classic with medium stem snapping and tweaks\" " im=$im"FALSE \"SHOVE\" \"Infinality - Full stem snapping and tweaks without sharpening\" " im=$im"FALSE \"SHARPENED\" \"Infinality - Full stem snapping, tweaks, and Windows-style sharpening\" " im=$im"FALSE \"INFINALITY\" \"Infinality - Standard\" " im=$im"FALSE \"DISABLED\" \"Act without extra infinality enhancements - just subpixel hinting\" " } # option(){ choice=`echo $im | sh -` # if echo $choice | grep "DEFAULT" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"DEFAULT\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "OSX" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"OSX\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "IPAD" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"IPAD\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "UBUNTU" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"UBUNTU\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "LINUX" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"LINUX\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "WINDOWS" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"WINDOWS\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "WIN7" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"WINDOWS7\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "WINLIGHT" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"WINDOWS7LIGHT\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "VANILLA" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"VANILLA\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "CLASSIC" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"CLASSIC\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "NUDGE" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"NUDGE\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "PUSH" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"PUSH\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "SHOVE" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"SHOVE\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "SHARPENED" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"SHARPENED\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "INFINALITY" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"INFINALITY\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # if echo $choice | grep "DISABLED" > /dev/null; then sudo -Sp '' sed -i "s/USE_STYLE=\"${infinality_current}\"/USE_STYLE=\"DISABLED\"/g" '/etc/profile.d/infinality-settings.sh' <<<"${sudo_password}" fi # } # menu option # if test ${#choice} -gt 0; then echo "Operation completed" fi # exit 0

    Read the article

  • Cannot call SAPI from dll

    - by Quandary
    Question: The below code works fine as long as it is in an executable. It uses the msft (text-to-)speech API (SAPI). But as soon as I put it in a dll and load it with loadlibrary from an executable, it doesn't work. I've also tried to change CoInitialize(NULL); to CoInitializeEx(NULL,COINIT_MULTITHREADED); and I tried with all possible flags ( COINIT_APARTMENTTHREADED, COINIT_MULTITHREADED, COINIT_DISABLE_OLE1DDE, COINIT_SPEED_OVER_MEMORY) But it's always stuck at hr = CoCreateInstance(__uuidof(SpVoice), NULL, CLSCTX_INPROC_SERVER, IID_ISpVoice, (void **) &pVoice); I also tried those flags here: CLSCTX_INPROC_SERVER,CLSCTX_SERVER, CLSCTX_ALL, but nothing seems to help... There are no errors, it doesn't crash, it just sleeps forever at CoCreateInstance... This is the code as single exe (working) #include <windows.h> #include <sapi.h> #include <iostream> #include <cstdlib> int main(int argc, char* argv[]) { ISpVoice * pVoice = NULL; //CoInitializeEx(NULL,COINIT_MULTITHREADED); HRESULT hr = CoInitialize(NULL); if( FAILED(hr) ) { MessageBox(NULL, TEXT("Failed To Initialize"), TEXT("Error"),0); printf("Failed!\n"); char buffer[2000] ; sprintf(buffer, "An error occured: 0x%08X.\n", hr); FILE * pFile = fopen ( "c:\\temp\\CoInitialize_exe.txt" , "w" ); fwrite (buffer , 1 , sizeof(buffer) , pFile ); fclose (pFile); } else { //CoGetClassObject(CLSID_SpVoice, CLSCTX_INPROC_SERVER, NULL, IID_IClassFactory, (void**) &pClsF); //hr = CoGetClassObject(CLSID_SpVoice, CLSCTX_INPROC_SERVER, NULL, IID_IClassFactory, (void**) &pClsF); hr = CoCreateInstance(__uuidof(SpVoice), NULL, CLSCTX_INPROC_SERVER, IID_ISpVoice, (void **) &pVoice); //HRESULT hr = CoCreateInstance(CLSID_SpVoice, NULL, CLSCTX_ALL, IID_ISpVoice, (void **) &pVoice); if( SUCCEEDED( hr ) ) { hr = pVoice->Speak(L"Test Test", 0, NULL); hr = pVoice->Speak(L"This sounds normal <pitch middle = '-10'/> but the pitch drops half way through", SPF_IS_XML, NULL ); pVoice->Release(); pVoice = NULL; } else { MessageBox(NULL, TEXT("Failed To Create a COM instance..."), TEXT("Error"),0); char buffer[2000] ; sprintf(buffer, "An error occured: 0x%08X.\n", hr); FILE * pFile = fopen ( "c:\\temp\\CoCreateInstance_exe.txt" , "w" ); fwrite (buffer , 1 , sizeof(buffer) , pFile ); fclose (pFile); } } CoUninitialize(); return EXIT_SUCCESS; } This is the exe loading the dll (stays forever at printf("trying to create instance.\n"); ) #include <windows.h> #include <sapi.h> #include <iostream> #include <cstdlib> int main(int argc, char* argv[]) { // C:\Windows\System32\Speech\Common\sapi.dll //LoadLibraryA("sapi.dll"); LoadLibraryA("Sapidll2.dll"); return EXIT_SUCCESS; // Frankly, that would be nice... } And this is Sapidll2.dll // dllmain.cpp : Defines the entry point for the DLL application. #include "stdafx.h" #include <iostream> #include <cstdlib> #include <string> #include <windows.h> #include <sapi.h> int init_engine() { ISpVoice * pVoice = NULL; //HRESULT hr = CoInitializeEx(NULL, COINIT_MULTITHREADED); HRESULT hr = CoInitialize(NULL); if(FAILED(hr) ) { MessageBox(NULL, TEXT("Failed To Initialize"), TEXT("Error"), 0); char buffer[2000] ; sprintf(buffer, "An error occured: 0x%08X.\n", hr); FILE * pFile = fopen ( "c:\\temp\\CoInitialize_dll.txt" , "w" ); fwrite (buffer , 1 , strlen(buffer) , pFile ); fclose (pFile); } else { printf("trying to create instance.\n"); //HRESULT hr = CoCreateInstance(CLSID_SpVoice, NULL, CLSCTX_ALL, IID_ISpVoice, (void **) &pVoice); //hr = CoCreateInstance(CLSID_SpVoice, NULL, CLSCTX_ALL, IID_ISpVoice, (void **) &pVoice); //HRESULT hr = CoCreateInstance(__uuidof(ISpVoice), NULL, CLSCTX_INPROC_SERVER, IID_ISpVoice, (void **) &pVoice); HRESULT hr = CoCreateInstance(__uuidof(SpVoice), NULL, CLSCTX_ALL, IID_ISpVoice, (void **) &pVoice); if( SUCCEEDED( hr ) ) { printf("Succeeded\n"); //hr = pVoice->Speak(L"The text to speech engine has been successfully initialized.", 0, NULL); } else { printf("failed\n"); MessageBox(NULL, TEXT("Failed To Create COM instance"), TEXT("Error"), 0); char buffer[2000] ; sprintf(buffer, "An error occured: 0x%08X.\n", hr); FILE * pFile = fopen ( "c:\\temp\\CoCreateInstance_dll.txt" , "w" ); fwrite (buffer , 1 , strlen(buffer) , pFile ); fclose (pFile); } } if(pVoice != NULL) { pVoice->Release(); pVoice = NULL; } CoUninitialize(); return true ; } BOOL APIENTRY DllMain( HMODULE hModule, DWORD ul_reason_for_call, LPVOID lpReserved) { switch (ul_reason_for_call) { case DLL_PROCESS_ATTACH: init_engine(); break; case DLL_THREAD_ATTACH: case DLL_THREAD_DETACH: case DLL_PROCESS_DETACH: break; } return TRUE; }

    Read the article

  • (mySQL) Unable to query 2 tables properly for data

    - by Devner
    I have 2 tables. One is 'page_links' and the other is 'rpp'. Table page_links is the superset of table rpp. The following is the schema of my tables: -- Table structure for table `page_links` -- CREATE TABLE IF NOT EXISTS `page_links` ( `page` varchar(255) NOT NULL, `page_link` varchar(100) NOT NULL, `heading_id` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`page`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `page_links` -- INSERT INTO `page_links` (`page`, `page_link`, `heading_id`) VALUES ('a1.php', 'A1', 8), ('b1.php', 'B1', 8), ('c1.php', 'C1', 5), ('d1.php', 'D1', 5), ('e1.php', 'E1', 8), ('f1.php', 'F1', 8), ('g1.php', 'G1', 8), ('h1.php', 'H1', 1), ('i1.php', 'I1', 1), ('j1.php', 'J1', 8), ('k1.php', 'K1', 8), ('l1.php', 'L1', 8), ('m1.php', 'M1', 8), ('n1.php', 'N1', 8), ('o1.php', 'O1', 8), ('p1.php', 'P1', 4), ('q1.php', 'Q1', 5), ('r1.php', 'R1', 4); -- Table structure for table `rpp` -- CREATE TABLE IF NOT EXISTS `rpp` ( `role_id` tinyint(3) unsigned NOT NULL, `page` varchar(255) NOT NULL, `is_allowed` tinyint(1) NOT NULL, PRIMARY KEY (`role_id`,`page`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `rpp` -- INSERT INTO `rpp` (`role_id`, `page`, `is_allowed`) VALUES (3, 'a1.php', 1), (3, 'b1.php', 1), (3, 'c1.php', 1), (3, 'd1.php', 1), (3, 'e1.php', 1), (3, 'f1.php', 1), (3, 'h1.php', 1), (3, 'i1.php', 1), (3, 'l1.php', 1), (3, 'm1.php', 1), (3, 'n1.php', 1), (4, 'a1.php', 1), (4, 'b1.php', 1), (4, 'q1.php', 1), (5, 'r1.php', 1); WHAT I AM TRYING TO DO: I am trying to query both the above tables (in a single query) in such a way that all the pages from page_links are displayed along with the is_allowed value from rpp for a particular role. For example, I want to get the is_allowed value of all the pages from rpp for role_id = 3 and at the same time, list all the available pages from page_links. A clear example of my expected result would be: page is_allowed role_id ---------------------------------------- a1.php 1 3 b1.php 1 3 c1.php 1 3 d1.php 1 3 e1.php 1 3 f1.php 1 3 g1.php NULL NULL h1.php 1 3 i1.php 1 3 j1.php NULL NULL k1.php NULL NULL l1.php 1 3 m1.php 1 3 n1.php 1 3 o1.php NULL NULL p1.php NULL NULL q1.php NULL NULL r1.php NULL NULL One more example of my desired result could be achieved by doing a LEFT JOIN rpp ON page_links.page = rpp.page but we need to omit using role_id = 3 (or any value) to be able to get that. But I do want to specify the role_id as well and get the results. I need the query to be able to get this result. I would appreciate any replies that could help me with this. If you can suggest me any changes as well to the table(s) design to be able to achieve the desired result, that's good as well. Thanks in advance.

    Read the article

< Previous Page | 46 47 48 49 50 51 52 53 54 55 56 57  | Next Page >