Joining all column values into one string with concat function is a pain, especially for a table with large numbers of columns. This script concats all columns quickly. declare @sql varchar(max) SELECT @sql = ISNULL( @sql+ '' + '+'',''+', '') + 'convert(varchar(50), [' + c.name + '])' FROM sys.all_columns c join sys.tables t ON c.object_id = t.object_id WHERE t.name = 'herb' and c.name!='id' EXEC( 'SELECT ' + @sql + ' FROM herb') Result The script generates long string value of @sql as following: convert(varchar(50), [alias])+','+convert(varchar(50), [application])+','+convert(varchar(50), [attachment])+','+convert(varchar(50), [category])+','+convert(varchar(50), [caution])+','+convert(varchar(50), [chemical])+','+convert(varchar(50), [desc])+','+convert(varchar(50), [english])+','+convert(varchar(50), [enterdate])+','+convert(varchar(50), [extract])+','+convert(varchar(50), [form])+','+convert(varchar(50), [form1])+','+convert(varchar(50), [grow])+','+convert(varchar(50), [guijing])+','+convert(varchar(50), [image_all])+','+convert(varchar(50), [image_leaf])+','+convert(varchar(50), [image_root])+','+convert(varchar(50), [indication])+','+convert(varchar(50), [indication1])+','+convert(varchar(50), [locations])+','+convert(varchar(50), [medicalprop])+','+convert(varchar(50), [name])+','+convert(varchar(50), [notes])+','+convert(varchar(50), [origin])+','+convert(varchar(50), [pharmacology])+','+convert(varchar(50), [pinyin])+','+convert(varchar(50), [process])+','+convert(varchar(50), [source])+','+convert(varchar(50), [store])+','+convert(varchar(50), [url])+','+convert(varchar(50), [usage])  828 total views

Problem The second line contains pingying and alias. html code: Zhōngfǔ   肺之募穴 First try get data roughly: alias = p.Select(Selectors.XPath("//span[@style]"))?.Value.Replace(" ", " "); Result: Zhōngfǔ   肺之募穴 Split the alias column in MySQL: UPDATE `xuewei` set pingying=substring_index(alias,' ',1); UPDATE `xuewei` set bieming=substring_index(alias,' ',-1); Split the column in process: alias = p.Select(Selectors.XPath("//span[@style]"))?.Value.Replace(" ", " "); var arr = alias.Split(' '); pingying = arr[0]; alias = arr[arr.Length-1]; //get last item in the array for the space in between is uncertain. Save to database: await conn.ExecuteAsync( $"INSERT IGNORE INTO xuewei (name,pingying,alias,position,anatomy,indication,operation,imagegeneral,imagepoint) VALUES " + $"('{data.Name}', '{data.Pingying}', '{data.Alias}', '{data.Position}', '{data.Anatomy}', '{data.Indication}', '{data.Operation}', '{data.ImageGeneral}', '{data.ImagePoint}');");  481 total views

Content data on webpage: It's html code looks like this: Parsing data using regular expression with named group. Use foreach loop to avoid the order change on page. Do not use the text value from DOM function/property, because the text value does not contain html tag, which gives you the necessary mark on data.  1,441 total views,  20 views today

Download Visual Paradigm Community https://www.visual-paradigm.com/download/community.jsp?platform=windows&arch=64bit Install Visual Paradigm Online Training https://www.visual-paradigm.com/training/visual-paradigm-essential/ User Guide User Guide  524 total views,  6 views today

SQL Server 2019 Installation Download en_sql_server_2019_developer_x64_dvd_baea4195.iso file from MSDN Subscription. Mount iso file to file system. Run Setup.exe from the mounted CD-ROM. Don't worry about the warning, just ensure the ports are open if you need remote access. Now the installation is completed and ready to use. Let's see other options in the installation SSMS - SQL Server Management Studio Download SQL Server Management Studio (SSMS) SSAS,SSRS,SSIS Install SSDT Launch Visual Studio Installer Install SSAS, SSRS Project Templates Open Manage Extensions from Visual Studio menu. Search for ssdt. When you close Visual Studio the installation started automatically. Install SSIS Project templates Click on Download button, the linked browser will be open for download, or use this link to download Microsoft.DataTools.IntegrationServices.exe file. https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects Done Now open Visual Studio, the newly added project templates are shown in the list.  885 total views

Problem Clicked Revert button by mistake, and then make change on code, try to Commit /Push, got failed. Look at the Commit details, it looks like this: Error message "github failed to push to the remote repository. see the output window for more details". Fix Go to settings -> Emails, uncheck "Block command line pushes that expose my email" option. Successfully pushed.  593 total views

C# Using + operator Console.WriteLine("Hello" + " " + "String " + "!"); String Interpolation string author = "Mahesh Chand"; string book = "C# Programming"; string bookAuthor = $"{author} is the author of {book}."; String.Concatenate() method string fName = "Mahesh"; string lName = "Chand"; string Name = string.Concat(fName, lName); string[] authors = { "Mahesh Chand ", "Chris Love ", "Dave McCarter ", "Praveen Kumar "}; string arrayStr = string.Concat(authors); String.Join() method int[] intArray = { 1, 3, 5, 7, 9 }; String seperator = ", "; string result = "Int, "; result += String.Join(seperator, intArray); // Using String.Join(String, String[], int int) // Let's concatenate first two strings of the array String[] arr2 = { "Mahesh Chand ", "Chris Love ", "Dave McCarter ", "Praveen Kumar " }; String seperator2 = ", "; string result2 = "First Author, "; result2 += String.Join(seperator2, arr2, 1, 2); Console.WriteLine($"Result: {result2}"); String.Format() method string date = String.Format("Today's date is {0}", DateTime.Now); StringBuilder.Append() method builder.Append(", "); PHP Using . operator $b = "Hello " . "World!"; // slow String Interpolation $a = '3'; echo "qwe{$a}rty"; // double quote echo "Result: " . ($a + 3); // result 6 "{$str1}{$str2}{$str3}"; // one concat = fast   $str1. $str2. $str3;    // two concats = slow //Use double quotes to concat more than two strings instead of multiple '.' operators.  PHP is forced to re-concatenate with every '.' operator. $logMessage = "A {$user->type} with e-mailaddress {$user->email} has performed {$action} on {$subject}." Using printf() $logMessage = sprintf('A %s with email %s has performed %s on %s.', $user->type, $user->email, $action, $subject);    725 total views

Analysis url: https://www.luscious.net/albums/cassie-laine_348936/ load images with ajax. It returns json file url: https://api.luscious.net/graphql/nobatch/?operationName=AlbumListOwnPictures&query=+query+AlbumListOwnPictures%28%24input%3A+PictureListInput%21%29+%7B+picture+%7B+list%28input%3A+%24input%29+%7B+info+%7B+...FacetCollectionInfo+%7D+items+%7B+...PictureStandardWithoutAlbum+%7D+%7D+%7D+%7D+fragment+FacetCollectionInfo+on+FacetCollectionInfo+%7B+page+has_next_page+has_previous_page+total_items+total_pages+items_per_page+url_complete+%7D+fragment+PictureStandardWithoutAlbum+on+Picture+%7B+__typename+id+title+description+created+like_status+number_of_comments+number_of_favorites+moderation_status+width+height+resolution+aspect_ratio+url_to_original+url_to_video+is_animated+position+tags+%7B+category+text+url+%7D+permissions+url+thumbnails+%7B+width+height+size+url+%7D+%7D+&variables=%7B%22input%22%3A%7B%22filters%22%3A%5B%7B%22name%22%3A%22album_id%22%2C%22value%22%3A%22348936%22%7D%5D%2C%22display%22%3A%22rating_all_time%22%2C%22page%22%3A1%7D%7D Coding json structure below. We need the highest resolution images. import requests import json from urllib.request import urlretrieve def download(url): header = { "User_Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36" } html = requests.get(url, headers=header).text #return json file jobj = json.loads(html) items = jobj['data']['picture']['list']['items'] for item in items: img = th['url_to_original'] filename = img[img.rfind("/") + 1:] filepath = 'd:/img/'+filename print(filepath) urlretrieve(img, filepath) url = "https://api.luscious.net/graphql/nobatch/?operationName=AlbumListOwnPictures&query=+query+AlbumListOwnPictures%28%24input%3A+PictureListInput%21%29+%7B+picture+%7B+list%28input%3A+%24input%29+%7B+info+%7B+...FacetCollectionInfo+%7D+items+%7B+...PictureStandardWithoutAlbum+%7D+%7D+%7D+%7D+fragment+FacetCollectionInfo+on+FacetCollectionInfo+%7B+page+has_next_page+has_previous_page+total_items+total_pages+items_per_page+url_complete+%7D+fragment+PictureStandardWithoutAlbum+on+Picture+%7B+__typename+id+title+description+created+like_status+number_of_comments+number_of_favorites+moderation_status+width+height+resolution+aspect_ratio+url_to_original+url_to_video+is_animated+position+tags+%7B+category+text+url+%7D+permissions+url+thumbnails+%7B+width+height+size+url+%7D+%7D+&variables=%7B%22input%22%3A%7B%22filters%22%3A%5B%7B%22name%22%3A%22album_id%22%2C%22value%22%3A%22348936%22%7D%5D%2C%22display%22%3A%22rating_all_time%22%2C%22page%22%3A1%7D%7D" download(url) print('done!') Result  629 total views

Problem Over a few years there are large number of photos in facebook. The MPP want to copy all the photos in facebook to his own gallery on his website. The gallery on the site is using plugin - Envira Gallery. Normally, we create a gallery by drag-drop photos one by one, then place the shortcode on to a post or page. Now we have created all the gallery, the next step is to create posts to acommodate these galleries. The problems left are create posts for each gallery how to please handreds of gallery posts to the menu add featured image to each post Basic informaiton Envira Gallery stores gallery in wp_posts table with post_type = 'envira'. Regular posts are stored in the same table with post_type='post'. The data for the photos in a gallery is stored in wp_postmeta table with meta_key = '_eg_gallery_data' Category information is stored in wp_term_replationships table with object_id = post_id, term_taxonomy_id identifies its category, for gallery term_taxonomy_id=32. Solution Create post list using plugin Advanced Post List. Add posts for each gallery in batch List gallery title, gallery ID in excel in order to generate sql statement to create posts. The formula for cell D3 as following. =$D$1&"(1,'"&YEAR(C3)&"-"&MONTH(C3)&"-"&DAY(C3)&"', '"&YEAR(C3)&"-"&MONTH(C3)&"-"&DAY(C3)&"','[envira-gallery id=\"""&A3&"\""]','"&B3&"','','publish','closed', 'closed','','"&SUBSTITUTE(LOWER(B3)," ","-")&"','','','"&YEAR(C3)&"-"&MONTH(C3)&"-"&DAY(C3)&"','"&YEAR(C3)&"-"&MONTH(C3)&"-"&DAY(C3)&"','',0,'http://domain.com/?page_id=', 0,'post');" The formula on cell C3 as following. ="2018-"&IFERROR(MONTH(LEFT(RIGHT(B3,6),3)&" 1"),MONTH(LEFT(RIGHT(B3,5),3)&" 1"))&"-"&RIGHT(RIGHT(B3,6),2) Change create date for galleries. This is not necessory, but to do this we can use this script in excel cell F3. ="update wp_posts set post_date='"&YEAR(C3)&"-"&MONTH(C3)&"-"&DAY(C3)&"' where id="&A3&";" Set Featured Image to posts Using the first photo in gallery as the featured image.…

Featured Image Featured images are stored in meta_value with meta_key='_thumbnail_id' in wp_postmeta table. SELECT * FROM wp_postmeta WHERE meta_key='_thumbnail_id' and post_id=@post_id Add featured image direct into database Find out the id of the image that you want to be a featured image in wp_post table, then insert a record into wp_postmeta table with meta_key='_thumbnail_id' @image_id = select id from wp_post where post_type='attachment' and post_mime_type='image/jpeg' insert into wp_postmeta (post_id,meta_key,meta_value) values (147, '_thumbnail_id',@image_id); Gallery Photos Photos in Envira Gallery are stored in meta_value with meta_key='_eg_gallery_data' in wp_postmeta table. select * from wp_postmeta where meta_key='_eg_gallery_data' Data stored looks like this: a:3:{s:2:"id";i:3972;s:7:"gallery"; a:8:{i:3973;a:7:{ s:6:"status";s:6:"active"; s:3:"src"; s:102:"http://vincentkempp.com/wp-content/uploads/2020/03/85092119_2768984699811558_8547879668101939200_n.jpg"; s:5:"title";s:47:"85092119_2768984699811558_8547879668101939200_n"; s:4:"link";s:102:"http://vincentkempp.com/wp-content/uploads/2020/03/85092119_2768984699811558_8547879668101939200_n.jpg"; s:3:"alt";s:0:"";s:7:"caption";s:0:"";s:5:"thumb";s:0:"";} Get the first image inside the gallery: SELECT p.id gallery_id, substring(meta_value, locate(':"http://', m.meta_value)+2, locate('";s:5:"title";', m.meta_value)-locate(':"http://',m.meta_value)-2) featured_image FROM wp_posts p JOIN wp_postmeta m on m.post_id=p.id and p.post_type='envira' and p.post_status='publish' and m.meta_key='_eg_gallery_data' ORDER BY p.id Check if the gallery post already has featured image. SELECT m.post_id,post_title, m.meta_value image_id FROM wp_posts p JOIN wp_postmeta m on m.post_id=p.ID and p.post_type='post' and p.post_content LIKE '[envira-gallery id=%' and p.post_status='publish' WHERE meta_key='_thumbnail_id' Get relationship between post_id and gallery_id SELECT p.id,post_title, substring(p.post_content, 21, locate('"]',p.post_content)-21) gallery_id, p.post_content FROM wp_posts p LEFT JOIN wp_postmeta m on m.post_id=p.ID and meta_key='_thumbnail_id' WHERE p.post_type='post' and p.post_content LIKE '[envira-gallery id=%' and p.post_status='publish' and meta_key is null ORDER by p.id Adding featured images INSERT INTO wp_postmeta (post_id,meta_key,meta_value) SELECT p1.id,'_thumbnail_id',img.id FROM ( SELECT p.id,post_title, substring(p.post_content, 21, locate('"]',p.post_content)-21) gallery_id FROM wp_posts p LEFT JOIN wp_postmeta m on m.post_id=p.ID and meta_key='_thumbnail_id' WHERE p.post_type='post' and p.post_content LIKE '[envira-gallery id=%' and p.post_status='publish' and meta_key is null ) p1 -- post_id JOIN ( SELECT p.id gallery_id,…