Re: unique strings from string column type in mySQL

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 */