php sprintf + sql like

By Josh Highland in MySQL, php, tutorials

Sometimes I do my best programming when I’m tired. Don’t ask me why, I just do. It’s a skill I picked up in college.

Being tired and producing good code wasn’t the case last night. I was trying to use the sprintf PHP function with a SQL “Like” statement. I made some dumb mistakes that tripped me up for a while. Hopefully someone out there will find this post and help them not make the problems I made, sleepy or not!

Normally the LIKE is used in mysql like this:
SELECT name FROM users WHERE name LIKE 'J%';

That would get all names including: Josh, Jason, Jimi, etc.
In a SQL Like statement the % is a wild card, so the command is to match everything starting with “J”

Now when you use the sprintf() function it looks kind like this:
$query = sprintf("SELECT name FROM users WHERE name='%s'", $searchString);
The %s will be replaced with the value of $searchString

Trying to combined them is where I had some problems….

At first I tried to do something like this:
$query = sprintf("SELECT name FROM users WHERE name LIKE'%s'", $searchString);
didnt return what I was looking for at all, it had no wild cards in it!

Then I tried this:
$query = sprintf("SELECT name FROM users WHERE name LIKE'%s%'", $searchString);
didnt work either, this time it threw errors

But this worked great
$query =sprintf("SELECT name FROM users WHERE name LIKE '%s'", $searchString . "%");

So the moral of the story is, if you want to use a SQL Like statement, appent the wildcard for the Like statement to the string to be inserted by the sprintf funtion.

cpanel broke mysqlhotcopy 1.22 but here is a fix!

By Josh Highland in hacks, linux, MySQL, tutorials

mysql-logo.gif
Yesterday I logged into my server to back up my databases. I back up the mysql databases using a tool called “mysqlhotcoy”. It’s a handy perl application that copies the database files for easy restores, and it also works really quick when you run it.

I ran the mysqlhotcopy command as root, and was greeted with an error similar to this:
Invalid db.table name 'foo.foo`.`bar'

um, no…. this table exists

I have cpanel installed on my server, a lot of server do. cpanel does updates to many application, mysqlhotcopy is one of them. It looks like my version of mysqlhotcopy was updated to 1.22, and there is some major problems with mysqlhotcopy 1.22.

I did some googeling and I found some people talking about the problem. I even found a quick patch for the problem. The problem comes from a host adding the username and an underscore before a database name (example: “username_databasename.table“). mysqlhotcopy only looks for databasename.table

The following patch instructions will fix mysqlhotcopy 1.22

  • Open the perl script for editing. It is located at /usr/bin/mysqlhotcopy
  • find the following subroutine

    sub get_list_of_tables {
    my ( $db ) = @_;

    my $dbh = DBI->connect(“dbi:mysql:${db}${dsn};mysql_read_default_group=mysqlhotcopy”,
    $opt{user}, $opt{password},
    {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
    });

    my @dbh_tables = eval { $dbh->tables() };
    $dbh->disconnect();
    return @dbh_tables;
    }

  • look for this line (mine was link 821):

    my @dbh_tables = eval { $dbh->tables() };

  • immediately after that line add the following:

    map { s/^.*?\.//o } @dbh_tables

Here is my patched subroutine:

sub get_list_of_tables {
my ( $db ) = @_;

my $dbh = DBI->connect(“dbi:mysql:${db}${dsn};mysql_read_default_group=mysqlhotcopy”,
$opt{user}, $opt{password},
{
RaiseError => 1,
PrintError => 0,
AutoCommit => 1,
});

my @dbh_tables = eval { $dbh->tables() };
map { s/^.*?\.//o } @dbh_tables;
$dbh->disconnect();
return @dbh_tables;
}

After I applied the patch, everything was back to working order.

Some people have other approaches that would work also, like downgrading mysqlhotcopy all together.

Personally, I think adding one line of code wasn’t that big of a deal to fix the program

experiments with MySQL FULLTEXT searching

By Josh Highland in MySQL

I just read a great article on FULLTEXT searching in a MySQL database
(http://www.onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html)

I took a few minutes to run some of my own test using two of the largest text fields on the notPopular.com user profile table. I wanted to check out the results of a FULLTEXT search versus a LIKE query using real data in my database.

First I did a search using the LIKE clause looking for “xdeathstarx”


SELECT *
FROM profiles
WHERE approved =1
AND
(interests_general LIKE '%xdeathstarx%'
OR
interests_music LIKE '%xdeathstarx%')

Showing rows 0 – 29 (119 total, Query took 0.0763 sec)

The query was fast, and took under a second to run

Next I created a FULLTEXT index on the “interests_general” and “interests_music” fields. It was really easy to do.


SELECT *
FROM profiles
WHERE approved =1
AND MATCH (interests_general, interests_music)
AGAINST ('xdeathstarx')

Showing rows 0 – 29 (119 total, Query took 0.0016 sec)

Talk about fast! it took 0.0016 seconds

lets compare the differences:
LIKE statements : 0.0763 sec
FULLTEXT search : 0.0016 sec

hands down the FULLTEXT search won, it was about 50x faster. If that isn’t instant results, I don’t know what are. Sure there will be more disk space used on the FULLTEXT index, but disk space is cheap, CPU cycles aren’t.

This makes me want to look more into optimizing the queries I am planning on using for the ever elusive notPop 2.0