Can't get a variable to echo out

[jeff5656]

I have a table called sheet2 and I can't get this to echo out when I choose the field cpt:

 

$query2 = "select * from sheet2 where cpt = '9921X' ";
$res2=mysql_query($query2) or die(mysql_error());
while ($rvu_array = mysql_fetch_assoc($res2)){

echo $rvu_array['cpt']." ".$rvu_array['rvu']."<br>";
}

 

And 9921X DOES exist as one of the records in that table.

 

If I do anything else like:

$query2 = "select * from sheet2 where rvu = '0.18' ";
$res2=mysql_query($query2) or die(mysql_error());
while ($rvu_array = mysql_fetch_assoc($res2)){

echo $rvu_array['cpt']." ".$rvu_array['rvu']."<br>";
}

it DOES echo out the records where the rvu field has 0.18 as a value.

Why on earth would this not work?  I searched for cpt as a reserved word but I didn't see that as a reserved word anywhere.

here's my table structure:

CREATE TABLE IF NOT EXISTS `sheet2` (

  `cpt` varchar(7) NOT NULL default '',

  `Descript` varchar(640) default NULL,

  `prof_charge` int(3) default NULL,

  `rvu` decimal(3,2) default NULL,

  `Medicare_Prof` decimal(16,2) default NULL,

  `BX_ Prof` decimal(17,2) default NULL,

  PRIMARY KEY  (`cpt`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

[PFMaBiSmAd]

How did you get the data into the table? If you did some kind of csv import, you likely have some non-printing characters, such as a space or a new-line, as part of the data and you won't be able to do an exact match with it without stripping the excess characters.

[WebStyles]

as far as I know cpt is NOT a reserved word.

 

I know you said you did, but because nobody can confirm it here, are you SURE you have that value in that field? (sorry for asking)

Check if field has a space before or after 9921X

 

or try something:

 

$query2 = "select * from sheet2 where cpt like '%9921X%' ";

 

 

Hope this helps

[jeff5656]

Yes I did in fact import that from excel (I cant remember if I did it as xls or csv).  How can I examine the cpt field to see if there are hidden characters?

[WebStyles]

you can do a script to select everything, trim() each filed and update database.

[jeff5656]

You guys are smart - I looked, and there is a leading space in front of each value on the cpt field!

[PFMaBiSmAd]

You can trim spaces from the field by executing the following query using your favorite database management tool (phpmyadmin or similar) -

 

UPDATE sheet2 SET cpt = TRIM(cpt)

[jeff5656]

Ok this is weird I used the above:

UPDATE sheet2 SET cpt = TRIM(cpt)

and it worked.  But then I went to another table which also has leading zeros:

 

UPDATE procedures SET cpt = TRIM(cpt)

and it said zero rows effected.  I then went in and the leading spaces are still there for all the cpt entries!

[jeff5656]

I even tried this inside php but the leading spaces are still there:

$query = "update procedures set cpt = trim(cpt)";
mysql_query($query) or die(mysql_error());

[PFMaBiSmAd]

Are you sure they are spaces? If they are tabs or new-lines, the mysql TRIM() function would need to be told which characters to specifically trim -

 

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

 

Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

 

mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'

 

[jeff5656]

Yep I was beginning to suspect that the "space" that I see is not a space.  Here's one of the records:

 

INSERT INTO `procedures` (`id`, `cp`, `Descript`, `prof_charge`, `RVU`, `Medicare_Prof`, `BX_ Prof`) VALUES

(1, ' 92953', 'Temporary transcutaneous pacing', 70.00, 0.23, 12.56, 16.13)

 

the 92953 SEEMS to have  aleading space, but since trim doesn't work, it must be another character. 

Do you know how to write the query to trim everything that is not a number or a letter (since I don't know what that character is I can't specify what to trim)