Find Most Popular Plugins
Wordpress bloggers are always on the lookout for cool plugins to add extra functionality to their blog. There are so many best wordpress plugin lists online (many including the plugins I use) that it is difficult to assess which are actually the most popular ones. The new Wordpress plugin directory categorizes plugins in way that help you find the most useful plugins for your blog. It is now a central place to find and download WordPress plugins.
Read the rest of this entry »
Creating a Derived Data Column (for efficiency)
Hi
Frequently we query our database for a value but then have to always do the same manipulation on it before we can use it. Well it can often be useful to do this “once” in the database and then store the manipulated value in a separate column of the database.
Here is a very simple example
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*3.14159);
Where col2 is set to the value of col1*pi
You could also do more complex calculation/filtering/shortening in your code eg php,ASP etc
Can you see the power of this technique?
Querying By Date
We are so frequently required to create date based queries, eg how many orders last week, last month, how many orders between two dates. It is necessary to avoid the trap of treating date as a number, instead convert the date into a handy unit such as days
# select all records from today
select * from tbl_orders where TO_DAYS(NOW()) = TO_DAYS(dtmdevdate);
# select all records from last three days
select * from tbl_orders where (TO_DAYS(NOW())-3) > TO_DAYS(orderdate);
MySQL provides a whole range of date functions become familiar with them here
Perl DBI MySQL read a Record
A secret of programming is to have your own personal library of “primitives” I have them for Perl,PHP & ColdFusion MySQL routines. eg for connecting to MySQL, Inserting/Deleting/selecting records, dumping records into hashes or arrays. The following is a simple Perl DBI MySQL function.
Read the rest of this entry »
Test your MySQL queries inside a MySQL GUI before coding them
Before you start coding your application with PHP or whatever remember to test out your MySQL in your MySQL GUI such as SQLYOG or even the CLI
What are the advantages?
- You don’t have to worry about the Script/MySQL syntax clashes (quoting etc)
- You only have to think MySQL and not say PHP as well
- You can see the data produced or not produced
- You can test your concepts
MySQL Standard Query Templates (Aide-Memoire)
Create a grep-able text file with the standard MySQL queries this will help you recall the different query structure of update/insert/select/delete add to your list everytime you write something useful:-
(This wont look too good inside this blog but should copy/paste OK)
template: alter table activity_log add column loginemail varchar(40) first;
template: insert into tbl_xxx (field1) values (’value1′)
template: update employee_data set salary=220000, perks=55000 where title=’ceo’;
template: update tbl_websitepages set page_content=replace (page_content,’http://www.site.co.uk/website/cms/’,'/website/images/cms/’);
template: insert into bad_table2(id,name) select id,name from bad_temp2;
template: into tbl_music_selected (registrant_email,music_id,music_title) select ‘$registrant_email’,music_id,music_title from tbl_music where music_id=’$music_id’
template: select * from tbl_lighter_note ORDER BY RAND() LIMIT 1
template: select * from tbl_orders order by count limit 0,10 (select top ten)
template: update tbl_lighter_note set count=count+1
template: select * from ytbl_region_county_lookup as t1 inner join ytbl_mapregions mp on t1.region_id = mp.intID where t1.region_id = ‘14′ order by t2.DivisionName;
template: update tbl_readings set abc = ucase(reading_title); #will automatically truncate if field shorter
template: update tbl_readings as r1,tbl_readings3 as r3 set r1.reading_content= r3.reading_content,r1.abc=r3.abc,r1.reading_category=r3.reading_category where r1.record_id=r3.record_id; #update taking data from another table (join)
Hello world!
Welcome to WordPress. This is your first post. Edit or delete it, then start blogging!
