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

Reply via email to