Skip to main content

Trick: Use Microsoft Excel to produce bulk sets of SQL Commands for your database

VMware Fusion vs. Parallels Desktop Excel
VMware Fusion vs. Parallels Desktop Excel (Photo credit: Wikipedia)
Our example is about UPDATE statements but you will see that its easy to form any sql statement instead of our update. Furhermore, in this example, we will use one table from the standard  Joomla database which is  mySQL  and  phpmyadmin  for browsing our database.

First step is to log into phpmyadmin and find out the records you need to update


 You can get the candidate datase via your own  sql query . For example you may only need to update the sales of March and not the complete set of sales. In this window you will select all the grid with data and copy it to your clipboard ( control + c ). Then you will open a new notepad document and paste in it ( control + v ) the copied data.
   
 Now you will select all here ( contol + a ) and then copy again ( control + c ). We do this intermediate step to clear our data of any  HTML element  that may pass on to the Excel sheet and cripple our effort. Next open a new  MS Excel  document. Paste the copied data in it :
   
 Data here are full of junk coming from phpmyadmin’s  UI  ;so we need to clear them out by deleting some columns and rows that do now contain actual data. The clearing process depends on how you have copied your data from phpmyadmin of some other database browser. Next the clearing process has produced some straightforward data in our excel sheet :
   
 Once we have cleared our data, we only keep the columns we need, not the complete data set. We then create a new column in our sheet and inside it we form our sql statement, to retreive data from the other columns. Mind the syntax inside MS Excel on how to concat strings and values. Keep in mind that those statements  must comply  with your database’s sql syntax.
 Now if we change the values of the second column ( alias ), the statements will change automatically :
   
 Now we are ready to copy the contents of the “commands” column, paste it to our phpmyadmin and then execute :
   
 This little trick will only work on small datasets though. If your want to massivelly edit your database’s records, then you must use standard SQL commands.


Enhanced by Zemanta

Popular posts from this blog

Enable AMD's Radeon Chill feature for all games.

Since 2014 AMD has been following the tradition of releasing new performance and usability features in their December driver, and 2016 was no exception. Among the myriad of new features and enhancements, was also one that passed almost unnoticed, called Radeon Chill.

Stemming from AMD's acquisition of HiAlgo earlier in 2016, it offers the possibility of huge power and thermal savings while gaming, by monitoring screen movement and adjusting the frame rate accordingly. There is even the claim that it could also reduce response times since the GPU is not occupied with rendering as often.

Its only issue: it's not global, and it's based on a white list of applications to function. But fear not, for we are here. Using a trick as old as operating systems themselves, you can try to make it work with every title. Let's see how to do that.
Step One: Activate Radeon Chill. Doing that is fairly easy. Right click on your desktop and select "Radeon Settings":

Then naviga…

[UPDATE] How to move your wordpress site and mysql database from local XAMPP to the web server

Most web developers follow the way of "first local, then on the server" for their website's early development or setup stages in order to gain either speed or freedom in installing pluging and extensions due to servers' permissions for files and folders.

A small problem comes around when you need to move your site from your local environment ( commonly XAMPP ) to your webserver so your website can go "live". This article will let you know the steps you need to follow when it comes to Wordpress and moving if from local to yuor server.

Before we start, you will need to : 1. have the url, username and password of your hosting environment's management panel ( cPanel or Plesk e.t.c. ) and log into it.
2. in your hosting panel, you need to add FTP access to your site, you will need to jot down the IP or domain name ( depending on DNS ), a username and a password
3. you will need to go into your hosting's database management via your management panel, cre…

How to create a simple Facebook page cover photo with Inkscape

Inkscape is a free, open source application via which you can create vectory graphics. You can also use Inkscape to create your facebook page main graphics such as the logo and the cover photo, maybe also some images to highlight and assign as album covers.

In order to create your first Facebook cover photo for your page, first open us inkscape :


Second, go to File-> Document Properties and assign the canvas size to match the Facebook cover photo size in pixels, as below :


Next, you can add a rectangle to your cover and some text, via the tools to your left, the mail tool bar of Inkscape as you can see in the next two images : 
 Once you are done with your cover, you can save it :


Remember that you can save this as png which is something Facebook can "understand" and also it would be wise to save this as "Inkscape SVG" which is a native form of Inkscape file type which you can open again later via Inkscape and be able to edit it. 
Then, you can change your cover…