Page 1 of 1

making a like query on an int

Posted: Wed Feb 11, 2009 12:24 pm
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

Re: making a like query on an int

Posted: Wed Feb 11, 2009 1:11 pm
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.