Jump to content

select count failing on searching text column


charismaarts

Recommended Posts

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

Link to comment
Share on other sites

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');

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.