select count failing on searching text column

[charismaarts]

Hello:

 

I am having a weird problem with my mysql database that I simply cannot figure out what is wrong.  I have a database setup and I am trying to do a "select count"  query on the column called "username".  this column contains text values. 

 

I got this php code from somewhere and at first I thought it was working fine:

 

$sql = mysql_query(" SELECT count(*) FROM charismos WHERE username = 'will it work' ") or die(mysql_error());

$results = mysql_result($sql, "0");

 

it was counting them fine but then I realized it was only working on the first four rows; no matter what if I try and have it search for a username that is in fifth row or beyond it comes back with '0'; I changed my search to instead search on a "number" column and that works fine beyond the fourth row all he way to the end of the DB.  So I have no idea why the searches for text values are failing beyond the fourth row.  Has anyone seen anything like this before or have any idea what could cause this?

 

Thanks so much,  Gerard

[abdfahim]

From your query, I can't see any problem. If your table is not confidential and also not too big, may be you can attach the sql export here, then I can load in my machine and check.

[charismaarts]

Hello:

 

Thanks for getting back with me.  I am new to all this and have never done an "export" so I do not know the best way to do it.  Hopefully this is what you requested.  I did an export and left all defaults selected and it printed out this:

 

-- phpMyAdmin SQL Dump

-- version 2.11.11.3

-- http://www.phpmyadmin.net

--

-- Host: 50.63.244.142

-- Generation Time: Jul 29, 2012 at 10:34 PM

-- Server version: 5.0.92

-- PHP Version: 5.1.6

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

 

--

-- Database: `db2012gman`

--

 

-- --------------------------------------------------------

 

--

-- Table structure for table `charismos`

--

 

CREATE TABLE `charismos` (

  `userid` int(11) NOT NULL auto_increment,

  `username` longtext,

  `email` varchar(75) NOT NULL default 'a',

  `avatar` varchar(11) default NULL,

  `zenscore` varchar(11) NOT NULL default '0',

  PRIMARY KEY  (`userid`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=59 ;

 

--

-- Dumping data for table `charismos`

--

 

INSERT INTO `charismos` VALUES(1, 'firstuser', 'heisfirst@first.com', '1', '1');

INSERT INTO `charismos` VALUES(2, 'second user', 'sheissecond@second.com', '2', '23');

INSERT INTO `charismos` VALUES(3, 'gerard is the third', 'third@third.com', '3', '55');

INSERT INTO `charismos` VALUES(4, 'fourhguy', 'afteradding@newfield.com', '4', '55');

INSERT INTO `charismos` VALUES(5, ' will it work', 'no_email@submitted.com', '2', '10');

INSERT INTO `charismos` VALUES(57, ' gerard_again', ' gh@we.com ', '3', '2300');

INSERT INTO `charismos` VALUES(58, ' textuser', '  ', '3', '0');

 

[abdfahim]

  Quote

Hello:

 

Thanks for getting back with me.  I am new to all this and have never done an "export" so I do not know the best way to do it.  Hopefully this is what you requested.  I did an export and left all defaults selected and it printed out this:

 

-- phpMyAdmin SQL Dump

-- version 2.11.11.3

-- http://www.phpmyadmin.net

--

-- Host: 50.63.244.142

-- Generation Time: Jul 29, 2012 at 10:34 PM

-- Server version: 5.0.92

-- PHP Version: 5.1.6

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

 

--

-- Database: `db2012gman`

--

 

-- --------------------------------------------------------

 

--

-- Table structure for table `charismos`

--

 

CREATE TABLE `charismos` (

  `userid` int(11) NOT NULL auto_increment,

  `username` longtext,

  `email` varchar(75) NOT NULL default 'a',

  `avatar` varchar(11) default NULL,

  `zenscore` varchar(11) NOT NULL default '0',

  PRIMARY KEY  (`userid`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=59 ;

 

--

-- Dumping data for table `charismos`

--

 

INSERT INTO `charismos` VALUES(1, 'firstuser', 'heisfirst@first.com', '1', '1');

INSERT INTO `charismos` VALUES(2, 'second user', 'sheissecond@second.com', '2', '23');

INSERT INTO `charismos` VALUES(3, 'gerard is the third', 'third@third.com', '3', '55');

INSERT INTO `charismos` VALUES(4, 'fourhguy', 'afteradding@newfield.com', '4', '55');

INSERT INTO `charismos` VALUES(5, ' will it work', 'no_email@submitted.com', '2', '10');

INSERT INTO `charismos` VALUES(57, ' gerard_again', ' gh@we.com ', '3', '2300');

INSERT INTO `charismos` VALUES(58, ' textuser', '  ', '3', '0');

 

 

Okay, locate your problem. You have extra space in front of user name, hence it is not giving anything. If you write like below, it will work (notice an extra space in username, e.g. NOT 'will it work', rather, ' will it work'

SELECT count( * )
FROM charismos
WHERE username = ' will it work'

 

You see, you have same problem after 4th row for all data. That's why you are not getting any result after 4th row. To overcome this prob, always use PHP TRIM function when entering data in database

[charismaarts]

Thank you very much; that definitely was the problem.  But it has exposed another problem.  I am using gravity forms to have new vaues inputed and then passing with a query string to  the next page where the count occurs; and somewhere along the line an extra space is being inserted into the beginning of the field...so I am trying to figure out why; I tried to use that trim function but it did not seem to help; but i may have coded it wrong; I will be checking this alll out and let you know...

 

Thanks!

 

Gerard

[peipst9lker]

If you need help with that, post the relevant code pieces.

Also please put things like sql dumbs or code either in [ code][ /code] or [ php][ /php] forum tags (without space).

[charismaarts]

Thanks to everyone for the help...finally got it figured out...

 

I had to apply the trim in 2 different places and also correct a querystring but it is working well now...

 

All the best,  Gerard