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';
}


Click here Free PHP/MySQL/cPanel Webhosting No ads no catches, I didn't believe it either!

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)