On Thursday 28 February 2002 00:09, Mike Krisher wrote:
> I have a column in a database with a column type of string that holds
> catalog numbers. They can be in two formats.
>
> 1: 410998
> 2: 555336-18
>
> The 18 in the dash number represents a length. So there could be multiple
> entries with the same base, just a different int after the dash.
>
> I am experiencing two issues: First, when using the catalognumbers in a
> SELECT statement, only those without dashes return anything. Is there
> something I need to do with my datatype in order to use:
>
> $item = "555336-18";
> $sql = "SELECT * FROM products WHERE catalognumber = $item";
As your column type is a string your select statement should read:
SELECT * FROM products WHERE catalognumber = '$item'
>
> Second question, when I strip off the dash and following int(s), my select
> statement will work, even though the subtring I am then searching for
> doesn't really exist in the database. And if I have two numbers with that
> same subtring base they are both returned. For Example:
>
> $item1 = "555336-18";
> $item2 = "555336-7";
> $item = substr($item,0,6);
> $sql = "SELECT * FROM products WHERE catalognumber = $item";
>
> Both items will be be returned, even without using %LIKE%. I'm guessing
> this all stems from my column type. Should I change it to something else,
> and if so what? Can anyone help out?
It's probably because by not using single-quotes around $item, you're forcing
your column, which is a string, into a number. The DB does its best and takes
as many leading characters as it can to make this number. It stops when it
gets to the hyphen as it's not a valid digit thus you end up with 555336, and
hence it matches.
--
Jason Wong -> Gremlins Associates -> www.gremlins.com.hk
/*
A sine curve goes off to infinity, or at least the end of the blackboard.
-- Prof. Steiner
*/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
