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.

sub fn_read_record_db()
{
$brochure_id=shift;
$sql=qq|select txtdevname from ytbl_development where id='$brochure_id'|;
$sel = $dbh->prepare($sql);
$sel->execute;
if ($hash_ref=$sel->fetchrow_hashref())
{
return $hash_ref->{'txtdevname'};
}
return 'Not Found';
}

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)

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

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

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?

Recovering a database from the raw .frm .MYI .MYD files on Vista

(All this presupposes you failed to do a recent & proper backup/dump of your data, but that's why you are reading this)

MySQL requires its datafiles ,*.MYI,*.frm,*.MYD to have exactly the right permissions and ownership otherwise MySQL refuses to see them.
Now this is a problem for me on Vista (de-Vista-ted) because I still haven't worked how to change ownership.

Solution:
1) In new Database create a table with same name , populate with a record or two (doesnt have to have same structure)
1a) I didnt need to do this as I used a month old backup
2) Use Windows Explorer to copy your "good" frm,MYI,MYD on top of the dummy files you created
3) They will automatically inherit the correct owner/permissions
4) When I did this, I was saddened to see however that I couldn't see my new records (probably due to different MySQL versions)
5) I did a Table diagnostics and saw there was an error (using SQLYOG)
6) Did a Repair Table in same Table Diagnostics and wow there were my lovely records!
7) Implement an automatic nightly backup of your databases

So it is possible to recover your database from the raw files, but watch out for :-
file permissions & file ownership and try to have the same MySQL version.
You could do all this on a spare PC and then do a proper backup/dump which is then portable!

Technorati the Specialist Blog Search Engine

I'm trying to get this blog registered with Technorati the Specialist Blog Search Engine.

I have to post this to show that I own the Blog

Technorati Profile