Search Results

Search found 20931 results on 838 pages for 'mysql insert'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 458/838 | < Previous Page | 454 455 456 457 458 459 460 461 462 463 464 465  | Next Page >

  • MYSQL Data -> PHP arrays -> Javascript (or Jquery) How can I pass the data with JSON ?

    - by Alex
    I am starting with a new site (it's my first one) and I am getting big troubles ! I wrote this code <?php include("misc.inc"); $cxn=mysqli_connect($host,$user,$password,$database) or die("couldn't connect to server"); $query="SELECT DISTINCT country FROM stamps"; $result=mysqli_query($cxn,$query) or die ("couldn't execute query"); $numberOfRows=mysqli_num_rows($result); for ($i=0;$i<$numberOfRows;$i++){ $row=mysqli_fetch_assoc($result); extract($row); $a=json_encode($row); $a=$a.","; echo $a; } ?> and the output is as follows : {"country":"liechtenstein"},{"country":"romania"},{"country":"jugoslavia"},{"country":"polonia"}, which should be a correct JSON outout ... How can I get it now in Jquery ? I tried with $.getJSON but I am not able to fuse it properly. I don't want yet to pass the data to a DIV or something similar in HTML. Alex

    Read the article

  • Jquery draggable persistence either through mysql or saving cookie in the database?

    - by Muhammad Jehanzaib
    I want to know that how can I persist the divs dropped on a draggable. I have been trying since long but stuck at this point. Actually you can see the demo here. I have to save the user designed wedding floor. So whenever user logins next time he/ she is able to see the last design saved. The code is shown below: $(document).ready(function() { $("#droppable").droppable({ accept: '.draggable', drop: function(event, ui) { $(this).append($(ui.draggable).clone()); $("#droppable .draggable").addClass("objects"); $(".objects").removeClass("ui-draggable draggable"); $(".objects").draggable({ containment: 'parent', }); } }); $(".draggable").draggable({ helper: 'clone', tolerance: 'touch', cursor:'move' }); });

    Read the article

  • PHP / MySQL - How to retrieve 2 separate sets of objects?

    - by Dan
    Hi, I have a set of models which correspond to tables in my database. I have been retrieving them easily enough, for example. $result = $mysqli->query('SOME SQL'); $post = $result->fetch_object; What would be the best approach for performing a join and also returning the related objects? For example, each post is created by a user, so for each row returned in the joined statement, both a page and user object should be returned. Any advice appreciated. Thanks.

    Read the article

  • PostgreSQL - fetch the row which has the Max value for a column

    - by Joshua Berry
    I'm dealing with a Postgres table (called "lives") that contains records with columns for time_stamp, usr_id, transaction_id, and lives_remaining. I need a query that will give me the most recent lives_remaining total for each usr_id There are multiple users (distinct usr_id's) time_stamp is not a unique identifier: sometimes user events (one by row in the table) will occur with the same time_stamp. trans_id is unique only for very small time ranges: over time it repeats remaining_lives (for a given user) can both increase and decrease over time example: time_stamp|lives_remaining|usr_id|trans_id ----------------------------------------- 07:00 | 1 | 1 | 1 09:00 | 4 | 2 | 2 10:00 | 2 | 3 | 3 10:00 | 1 | 2 | 4 11:00 | 4 | 1 | 5 11:00 | 3 | 1 | 6 13:00 | 3 | 3 | 1 As I will need to access other columns of the row with the latest data for each given usr_id, I need a query that gives a result like this: time_stamp|lives_remaining|usr_id|trans_id ----------------------------------------- 11:00 | 3 | 1 | 6 10:00 | 1 | 2 | 4 13:00 | 3 | 3 | 1 As mentioned, each usr_id can gain or lose lives, and sometimes these timestamped events occur so close together that they have the same timestamp! Therefore this query won't work: SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM (SELECT usr_id, max(time_stamp) AS max_timestamp FROM lives GROUP BY usr_id ORDER BY usr_id) a JOIN lives b ON a.max_timestamp = b.time_stamp Instead, I need to use both time_stamp (first) and trans_id (second) to identify the correct row. I also then need to pass that information from the subquery to the main query that will provide the data for the other columns of the appropriate rows. This is the hacked up query that I've gotten to work: SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM (SELECT usr_id, max(time_stamp || '*' || trans_id) AS max_timestamp_transid FROM lives GROUP BY usr_id ORDER BY usr_id) a JOIN lives b ON a.max_timestamp_transid = b.time_stamp || '*' || b.trans_id ORDER BY b.usr_id Okay, so this works, but I don't like it. It requires a query within a query, a self join, and it seems to me that it could be much simpler by grabbing the row that MAX found to have the largest timestamp and trans_id. The table "lives" has tens of millions of rows to parse, so I'd like this query to be as fast and efficient as possible. I'm new to RDBM and Postgres in particular, so I know that I need to make effective use of the proper indexes. I'm a bit lost on how to optimize. I found a similar discussion here. Can I perform some type of Postgres equivalent to an Oracle analytic function? Any advice on accessing related column information used by an aggregate function (like MAX), creating indexes, and creating better queries would be much appreciated! P.S. You can use the following to create my example case: create TABLE lives (time_stamp timestamp, lives_remaining integer, usr_id integer, trans_id integer); insert into lives values ('2000-01-01 07:00', 1, 1, 1); insert into lives values ('2000-01-01 09:00', 4, 2, 2); insert into lives values ('2000-01-01 10:00', 2, 3, 3); insert into lives values ('2000-01-01 10:00', 1, 2, 4); insert into lives values ('2000-01-01 11:00', 4, 1, 5); insert into lives values ('2000-01-01 11:00', 3, 1, 6); insert into lives values ('2000-01-01 13:00', 3, 3, 1);

    Read the article

  • Inserting newlines into a GtkTextView widget (GTK+ programming)

    - by Mark Roberts
    I've got a button which when clicked copies and appends the text from a GtkEntry widget into a GtkTextView widget. (This code is a modified version of an example found in the "The Text View Widget" chapter of Foundations of GTK+ Development.) I'm looking to insert a newline character before the text which gets copied and appended, such that each line of text will be on its own line in the GtkTextView widget. How would I do this? I'm brand new to GTK+. Here's the code sample: #include <gtk/gtk.h> typedef struct { GtkWidget *entry, *textview; } Widgets; static void insert_text (GtkButton*, Widgets*); int main (int argc, char *argv[]) { GtkWidget *window, *scrolled_win, *hbox, *vbox, *insert; Widgets *w = g_slice_new (Widgets); gtk_init (&argc, &argv); window = gtk_window_new (GTK_WINDOW_TOPLEVEL); gtk_window_set_title (GTK_WINDOW (window), "Text Iterators"); gtk_container_set_border_width (GTK_CONTAINER (window), 10); gtk_widget_set_size_request (window, -1, 200); w->textview = gtk_text_view_new (); w->entry = gtk_entry_new (); insert = gtk_button_new_with_label ("Insert Text"); g_signal_connect (G_OBJECT (insert), "clicked", G_CALLBACK (insert_text), (gpointer) w); scrolled_win = gtk_scrolled_window_new (NULL, NULL); gtk_container_add (GTK_CONTAINER (scrolled_win), w->textview); hbox = gtk_hbox_new (FALSE, 5); gtk_box_pack_start_defaults (GTK_BOX (hbox), w->entry); gtk_box_pack_start_defaults (GTK_BOX (hbox), insert); vbox = gtk_vbox_new (FALSE, 5); gtk_box_pack_start (GTK_BOX (vbox), scrolled_win, TRUE, TRUE, 0); gtk_box_pack_start (GTK_BOX (vbox), hbox, FALSE, TRUE, 0); gtk_container_add (GTK_CONTAINER (window), vbox); gtk_widget_show_all (window); gtk_main(); return 0; } /* Insert the text from the GtkEntry into the GtkTextView. */ static void insert_text (GtkButton *button, Widgets *w) { GtkTextBuffer *buffer; GtkTextMark *mark; GtkTextIter iter; const gchar *text; buffer = gtk_text_view_get_buffer (GTK_TEXT_VIEW (w->textview)); text = gtk_entry_get_text (GTK_ENTRY (w->entry)); mark = gtk_text_buffer_get_insert (buffer); gtk_text_buffer_get_iter_at_mark (buffer, &iter, mark); gtk_text_buffer_insert (buffer, &iter, text, -1); } You can compile this command (assuming the file is named file.c): gcc file.c -o file `pkg-config --cflags --libs gtk+-2.0` Thanks everybody!

    Read the article

  • Is is better to store serialized data or raw html in mysql?

    - by Yegor
    I de-normalized my database, since the application was crawling otherwise, and Im storing a list of categories for each item in the DB as a raw html version, and simply echoing it out in my design. Each category is actually a link, which is include a tag. Naturally, this is abit of a pain, especially if I want to change the look of how the category links are displayed, since I gotta update all the old cached entries. What if I were to store this data as a serialized array instead, and simply unserialize it, and then apply formatting to it in php. Would there be a significant performance decrease over simply echoing out the raw html?

    Read the article

  • What will the $result be when MySQL returns nothing?

    - by Teddy
    This might seem ridiculously simple, but I've been getting all kinds of error depending on how I handle a query that returns nothing. $query = "SELECT * FROM messages WHERE id > ".$messageId; $result =mysql_query($query); $time = time(); while(time()-$time<60 && $result==false) { $result = mysql_query($query); } if(result != false) //Encode response else //return nothing How do I check whether my mysql_query() returned anything?

    Read the article

  • Advance: Parsing XML into another XML page using only javascript or jquery; Can't use PhP, Java or MySQL

    - by UrBestFriend
    Current site: http://cardwall.tk/ Example of intended outcome: http://www.shockwave.com/downloadWall.jsp I have an embeded flash object that uses XML/RSS (Picasa) to feed itself pictures. Now I created my own XML/RSS feed so that I can add additional XML tags and values. Now here's my big problem: enabling search. Since I'm not relying on Picasa's API anymore to return custom RSS/XML for the user's search, how can I create xml from another xml based on the user's search queries using only JavaScript and Jquery? Here is the current code: <script type="text/javascript"> var flashvars = { feed : "http%3A%2F%2Frssfeed.ucoz.com%2Frssfeed.xml", backgroundColor : "#FFFFFF", metadataFont : "Arial", wmode : "opaque", iFrameScrolling: "no", numRows : "3", }; var params = { allowFullScreen: "true", allowscriptaccess : "always", wmode: "opaque" }; swfobject.embedSWF("http://apps.cooliris.com/embed/cooliris.swf", "gamewall", "810", "410", "9.0.0", "", flashvars, params); $(document).ready(function() { $("#cooliris input").keydown(function(e) { if(e.keyCode == 13) { $("#cooliris a#searchCooliris").click(); return false; } }); doCoolIrisSearch = function() { cooliris.embed.setFeedContents( '** JAVA STRING OF PARSED RSS/XML based on http%3A%2F%2Frssfeed.ucoz.com%2Frssfeed.xml and USER'S SEARCH INPUT** ' ) }); <form id="searchForm" name="searchForm" class="shockwave"> <input type="text" name="coolIrisSearch" id="coolIrisSearch" value="Search..." class="field text short" onfocus="this.value='';" /> <a id="searchCooliris" href="#" onclick="doCoolIrisSearch();return false;" class="clearLink">Search Cooliris</a> </form> <div id="gamewall"></div> So basically, I want to replace cooliris.embed.setFeedContents's value with a Javastring based on the parsed RSS/XML and user search input. Any code or ideas would be greatly appreciated.

    Read the article

  • How can I use a php array in a mysql search query?

    - by ThinkingInBits
    I was going to use the scuttle solution on: http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html for handling searches on my website. I was wondering how I could take the search input from a user and turn it into a single query. For instance, let's say a user inputted 'blue dogs' in their search query... How could I dynamically update the query to include ('blue', 'dogs') in union and intersection queries?

    Read the article

  • Using $this when not in object context--i am using the latest version of php and mysql

    - by user309381
    This is user.php: include("databse.php");//retrieving successfully first name and lastname from databse file into user.php class user { public $first_name; public $last_name; public static function full_name() { if(isset($this->first_name) && isset($this->last_name)) { return $this->first_name . " " . $this->last_name; } else { return ""; } } } ?> Other php file, index.php: <?php include(databse.php); include(user.php); $record = user::find_by_id(1); $object = new user(); $object->id = $record['id']; $object->username = $record['username']; $object->password = $record['password']; $object->first_name = $record['first_name']; $object->last_name = $record['last_name']; // echo $object->full_name(); echo $object->id;// successfully print the id echo $object->username;//success fully print the username echo->$object->full_name();//**ERROR:Using $this when not in object context** ?>

    Read the article

  • how can i export some values in mysql to an php array?

    - by sebastian
    hi, here is what i'm trying to make. i want to select all the users from a table. for all of these users i want to make a for cycle. from what i know for has three statements something like so for($i=0;$i++;$i=$max) how can i put the user id's in to an array so i can define my $max variable? or can this be done in an other method? thanks,

    Read the article

  • PHP, MySQL: Security concern; Page loads in a weird way

    - by Devner
    Hi all, I am testing the security of my website. I am using the following URL to load a PHP page in my website, on localhost: http://localhost/domain/user/index.php/apple.php When I do this, the page is not loading normally; Instead the images, icons used in the page simply vanish/disappear from the page. Only text appears. And also on any link I click on this page, it brings me to this same page again without navigating to the required page. So if I have hyperlinks to other pages, such as "SEARCH", which points to search.php, instead of navigating to the search.php page, it refreshes the index.php page and just appends the page name of the destination page to the end of the URL. For example, say I used the link above. It then loads the index.php page minus the images at it's will. When I click on the "Search" link to navigate to the search page, I see the following in the URL: http://localhost/domain/user/index.php/search.php I have a redirection configured to a 404 error page in my .htaccess file, but the page does not redirect to the 404 error page. Notice the search.php towards the end of the URL above. Any other link that I click, reloads the index.php page and just appends the destination page name to the end of the URL like I have shown above. I was expecting to see a 404 Error but that does not happen. The URL should not even be able to load the page because I do NOT have a "index.php" folder in my website. What can I do to solve this? All help is appreciated. Thank you.

    Read the article

  • Using jQuery to store basic text string in mySQL base?

    - by Kenny Bones
    Could someone point me in the right direction here? Basically, I've got this jQuery code snippet: $('.bggallery_images').click(function () { var newBG = "url('" + $(this).attr('src'); var fullpath = $(this).attr('src'); var filename = fullpath.replace('img/Bakgrunner/', ''); $('#wrapper').css('background-image', newBG); // Lagre til SQL $.ajax({ url: "save_to_db.php", // The url to your function to handle saving to the db data: filename, dataType: 'Text', type: 'POST', // Could also use GET if you prefer success: function (data) { // Just for testing purposes. alert('Background changed to: ' + data); } }); }); This is being run when I click a certain button. So it's actually within a click handler. If I understand this correctly, this snippet takes the source if the image I just clicked and strips it so I end up with only the filename. If I do an alert(filename), I get the filename only. So this is working ok. But then, it does an ajax call to a php file called "save_to_db.php" and sends data: filename. This is correct right? Then, it does a callback which does an alert + data. Does this seem correct so far? Cause my php file looks like this: <?php require("dbconnect2.php"); $uploadstring = $_POST['filename']; $sessionid = $_SESSION['id']; echo ($sessionid); mysql_query("UPDATE brukere SET brukerBakgrunn = '$uploadstring' WHERE brukerID=" .$_SESSION['id']); mysql_close(); ?> When I click the image, the jQuery snippet fires and I get the results of this php file as output for the alert box. I think that the variables somehow are empty. Because notice the echo($sessionid); which is a variable I've created just to test what the session ID is. And it returns nothing. What could be the issue here? Edit: I just tried to echo out the $uploadstring variable as well and it also returns nothing. It's like the jQuery snippet doesn't even pass the variable on to the php file?

    Read the article

  • How do I use on delete cascade in mysql?

    - by Marius
    I have a database of components. Each component is of a specific type. That means there is a many-to-one relationship between a component and a type. When I delete a type, I would like to delete all the components which has a foreign key of that type. But if I'm not mistaken, cascade delete will delete the type when the component is deleted. Is there any way to do what I described?

    Read the article

  • I have a 18MB MySQL table backup. How can I restore such a large SQL file?

    - by Henryz
    I use a Wordpress plugin called 'Shopp'. It stores product images in the database rather than the filesystem as standard, I didn't think anything of this until now. I have to move server, and so I made a backup, but restoring the backup is proving a horrible task. I need to restore one table called wp_shopp_assets which is 18MB. Any advice is hugely appreciated. Thanks, Henry.

    Read the article

  • how read data from file and execute to MYSQL?

    - by Mahran Elneel
    i create form to load sql file and used fopen function top open file and read this but when want to execute this data to database not work? what is wrong in my code????????????? $ofile = trim(basename($_FILES['sqlfile']['name'])); $path = "sqlfiles/".$ofile; //$data = settype($data,"string"); $file = ""; $connect = mysql_connect('localhost','root',''); $selectdb = mysql_select_db('files'); if(isset($_POST['submit'])) { if(!move_uploaded_file($_FILES['sqlfile']['tmp_name'],"sqlfiles/".$ofile)) { $path = ""; } $file = fopen("sqlfiles/".$ofile,"r") or exit("error open file!"); while (!feof($file)) { $data = fgetc($file); settype($data,"string"); $rslt = mysql_query($data); print $data; } fclose($file); }

    Read the article

  • Search-friendly way to store checkbox values in MySQL?

    - by Alex
    What is a search-friendly way to store checkbox values in the database? Currently, checkboxes are processed as an array and values are separated by a ";" As such: <input type="checkbox" name="frequency[]" value="Daily"/> Daily <input type="checkbox" name="frequency[]" value="Weekly"/> Weekly <input type="checkbox" name="frequency[]" value="Monthly"/> Monthly The PHP backend runs implode(';', $frequency) and adds the string to the database. This works fine but it's a nightmare when it comes to searching. Is there a better way to approach this?

    Read the article

  • Doctrine MYSQL 150+ tables: generating models works, but not vice-versa?

    - by ropstah
    I can generate my models and schema.yml file based on an existing database. But when I try to do it the other way around using Doctrine::createTablesFromModels() i get an error: Syntax error or access violation: 1064 So either of these works: Doctrine::generateYamlFromDb(APPPATH . 'models/yaml'); Doctrine::generateYamlFromModels(APPPATH . 'models/yaml', APPPATH . 'models'); Doctrine::generateModelsFromYaml(APPPATH . 'models/yaml', APPPATH . 'models', array('generateTableClasses' => true)); Doctrine::generateModelsFromDb(APPATH . 'models', array('default'), array('generateTableClasses' => true)); But this fails (it drops/creates the database and around 50 tables): Doctrine::dropDatabases(); Doctrine::createDatabases(); Doctrine::createTablesFromModels(); The partially outputted SQL query shows that the error is around the Notification object which looks like this: Any leads would be highly appreciated! <?php // Connection Component Binding Doctrine_Manager::getInstance()->bindComponent('Notification', 'default'); /** * BaseNotification * * This class has been auto-generated by the Doctrine ORM Framework * * @property integer $n_auto_key * @property integer $type * @property string $title * @property string $message * @property timestamp $entry_date * @property timestamp $update_date * @property integer $u_auto_key * @property integer $c_auto_key * @property integer $ub_auto_key * @property integer $o_auto_key * @property integer $notified * @property integer $read * @property integer $urgence * * @package ##PACKAGE## * @subpackage ##SUBPACKAGE## * @author ##NAME## <##EMAIL##> * @version SVN: $Id: Builder.php 6820 2009-11-30 17:27:49Z jwage $ */ abstract class BaseNotification extends Doctrine_Record { public function setTableDefinition() { $this->setTableName('Notification'); $this->hasColumn('n_auto_key', 'integer', 4, array( 'type' => 'integer', 'fixed' => 0, 'unsigned' => false, 'primary' => true, 'autoincrement' => true, 'length' => '4', )); $this->hasColumn('type', 'integer', 1, array( 'type' => 'integer', 'fixed' => 0, 'unsigned' => false, 'primary' => false, 'notnull' => true, 'autoincrement' => false, 'length' => '1', )); $this->hasColumn('title', 'string', 50, array( 'type' => 'string', 'fixed' => 0, 'unsigned' => false, 'primary' => false, 'notnull' => true, 'autoincrement' => false, 'length' => '50', )); $this->hasColumn('message', 'string', null, array( 'type' => 'string', 'fixed' => 0, 'unsigned' => false, 'primary' => false, 'notnull' => true, 'autoincrement' => false, 'length' => '', )); $this->hasColumn('entry_date', 'timestamp', 25, array( 'type' => 'timestamp', 'fixed' => 0, 'unsigned' => false, 'primary' => false, 'notnull' => true, 'autoincrement' => false, 'length' => '25', )); $this->hasColumn('update_date', 'timestamp', 25, array( 'type' => 'timestamp', 'fixed' => 0, 'unsigned' => false, 'primary' => false, 'notnull' => false, 'autoincrement' => false, 'length' => '25', )); $this->hasColumn('u_auto_key', 'integer', 4, array( 'type' => 'integer', 'fixed' => 0, 'unsigned' => false, 'primary' => false, 'notnull' => true, 'autoincrement' => false, 'length' => '4', )); $this->hasColumn('c_auto_key', 'integer', 4, array( 'type' => 'integer', 'fixed' => 0, 'unsigned' => false, 'primary' => false, 'notnull' => false, 'autoincrement' => false, 'length' => '4', )); $this->hasColumn('ub_auto_key', 'integer', 4, array( 'type' => 'integer', 'fixed' => 0, 'unsigned' => false, 'primary' => false, 'notnull' => false, 'autoincrement' => false, 'length' => '4', )); $this->hasColumn('o_auto_key', 'integer', 4, array( 'type' => 'integer', 'fixed' => 0, 'unsigned' => false, 'primary' => false, 'notnull' => false, 'autoincrement' => false, 'length' => '4', )); $this->hasColumn('notified', 'integer', 1, array( 'type' => 'integer', 'fixed' => 0, 'unsigned' => false, 'primary' => false, 'default' => '0', 'notnull' => true, 'autoincrement' => false, 'length' => '1', )); $this->hasColumn('read', 'integer', 1, array( 'type' => 'integer', 'fixed' => 0, 'unsigned' => false, 'primary' => false, 'default' => '0', 'notnull' => true, 'autoincrement' => false, 'length' => '1', )); $this->hasColumn('urgence', 'integer', 1, array( 'type' => 'integer', 'fixed' => 0, 'unsigned' => false, 'primary' => false, 'default' => '0', 'notnull' => true, 'autoincrement' => false, 'length' => '1', )); } public function setUp() { parent::setUp(); } }

    Read the article

  • Is the development stack of java/spring/hibernate/eclipse/tomcat/mysql riddled with config hacking?

    - by Blankman
    From a M$ development perspective, using things like vs.net/c#/asp.net/asp.net mvc/sql server, things are pretty much smooth. (Obviously for someone coming from a microsoft background like me). Now I love the idea of open source, linux and terminal servering into the computer and doing things via the command line, no license issues really, etc. I've tried firing up eclipse etc., but I always seem to get stuck on some configuration issue or another. Does it get easier or is it just the territory when you are dealing with the java stack?

    Read the article

  • How does timezone really work in relation to PHP and MYSQL?

    - by Rick
    I am getting very strange results in terms of timezones. I am currently using Wordpress and everytime I register a new user, I see the wrong datetime in the database. Ok so I am suspecting it is picking up the server time. So then I then set in php.ini to have date.timezone = "America/Los_Angeles" but again the time is still not correct in the database...And yes I have also set the timezone in Wordpress correctly. So how can this be done?

    Read the article

  • Comments Parent-Child query with indentation

    - by poldoj
    I've been trying to retrieve comments to articles in a pretty common blog fashion way. Here's my sample code: -- ---------------------------- -- Sample Table structure for [dbo].[Comments] -- ---------------------------- CREATE TABLE [dbo].[Comments] ( [CommentID] int NOT NULL , [AddedDate] datetime NOT NULL , [AddedBy] nvarchar(256) NOT NULL , [ArticleID] int NOT NULL , [Body] nvarchar(4000) NOT NULL , [parentCommentID] int NULL ) GO -- ---------------------------- -- Sample Records of Comments -- ---------------------------- INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'1', N'2011-11-26 23:18:07.000', N'user', N'1', N'body', null); GO INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'2', N'2011-11-26 23:18:50.000', N'user', N'2', N'body', null); GO INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'3', N'2011-11-26 23:19:09.000', N'user', N'1', N'body', null); GO INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'4', N'2011-11-26 23:19:46.000', N'user', N'3', N'body', null); GO INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'5', N'2011-11-26 23:20:16.000', N'user', N'1', N'body', N'1'); GO INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'6', N'2011-11-26 23:20:42.000', N'user', N'1', N'body', N'1'); GO INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'7', N'2011-11-26 23:21:25.000', N'user', N'1', N'body', N'6'); GO -- ---------------------------- -- Indexes structure for table Comments -- ---------------------------- -- ---------------------------- -- Primary Key structure for table [dbo].[Comments] -- ---------------------------- ALTER TABLE [dbo].[Comments] ADD PRIMARY KEY ([CommentID]) GO -- ---------------------------- -- Foreign Key structure for table [dbo].[Comments] -- ---------------------------- ALTER TABLE [dbo].[Comments] ADD FOREIGN KEY ([parentCommentID]) REFERENCES [dbo]. [Comments] ([CommentID]) ON DELETE NO ACTION ON UPDATE NO ACTION GO I thought I could use a CTE query to do the job like this: WITH CommentsCTE(CommentID, AddedDate, AddedBy, ArticleID, Body, parentCommentID, lvl, sortcol) AS ( SELECT CommentID, AddedDate, AddedBy, ArticleID, Body, parentCommentID, 0, cast(CommentID as varbinary(max)) FROM Comments UNION ALL SELECT P.CommentID, P.AddedDate, P.AddedBy, P.ArticleID, P.Body, P.parentCommentID, PP.lvl+1, CAST(sortcol + CAST(P.CommentID AS BINARY(4)) AS VARBINARY(max)) FROM Comments AS P JOIN CommentsCTE AS PP ON P.parentCommentID = PP.CommentID ) SELECT REPLICATE('--', lvl) + right('>',lvl)+ AddedBy + ' :: ' + Body, CommentID, parentCommentID, lvl FROM CommentsCTE WHERE ArticleID = 1 order by sortcol go but the results have been very disappointing so far, and after days of tweaking I decided to ask for help. I was looking for a method to display hierarchical comments to articles like it happens in blogs. [edit] The problem with this query is that I get duplicates because I couldn't figure out how to properly select the ArticleID which I want comments from to display. I'm also looking for a method that sorts children entries by date within a same level. An example of what I'm trying to accomplish could be something like: (ArticleID[post retrieved]) ------------------------- ------------------------- (Comments[related to the article id above]) first comment[no parent] --[first child to first comment] --[second child to first comment] ----[first child to second child comment to first comment] --[third child to first comment] ----[first child to third child comment to first comment] ------[(recursive child): first child to first child to third child comment to first comment] ------[(recursive child): second child to first child to third child comment to first comment] second comment[no parent] third comment[no parent] --[first child to third comment] I kinda got myself lost in all this mess...I appreciate any help or simpler ways to get this working. Thanks

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 454 455 456 457 458 459 460 461 462 463 464 465  | Next Page >