making a like query on an int

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
neridaj
Forum Commoner
Posts: 40
Joined: Fri Jan 05, 2007 9:55 pm

making a like query on an int

Post by neridaj »

Hello,

I'm having problems returning the correct number of records from a column. When I run the statement in the mysql monitor I get the correct number of records returned but when the query is run from within my script it is returning the wrong amount i.e., 1. I tried using = instead of like but I need to use a wildcard % and I'm not sure I can use that on an int.

I need the count of how many records are in the table that begin with the current year, the records are in the form of YYYYMMDDNN, where NN is the count returned from running the query added to the date("Ymd"). Do I need to change the column type to string to accurately use %?

Code: Select all

function get_invoice_number()
{
    $year = (int)date("Y");
    $today = (int)date("Ymd").'00';
    $conn = db_connect();
    $count = $conn->query("select count(inv_number) from invoice where inv_number like '$year%'");
    if(!$count)
        return false;
    else
        $invnum = $today+$count;
        return $invnum;
}
Thanks for any help,

Jason
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: making a like query on an int

Post by VladSun »

Some of the LIKE operators are internally substituted by the SQL engines with less and greater operators, so indexies are available:
[sql]name LIKE 'b%'[/sql]
=>
[sql]name >= 'b' AND name<'c'[/sql]

Use this approach or use properly formatted date field.

PS: Don't use substring functions - it will be slower because no indexies could be used.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply