We help IT Professionals succeed at work.

php /mysql count statement...

aej1973
aej1973 asked
on
619 Views
Last Modified: 2014-12-06
Hi, I have a column as shown below;

Chair_2014_03_03
;Chair_2014_03_05
Chair_2014_03_10
Footrest_2013_03_03
Footrest_2014_03_03
armchairs_2014_03_10
armchairs_2012_03_03
;armchairs_2013_03_03

I need to have a count of all chairs, footrest, and armchairs using php/mysql. I am not sure if there is a function in mysql that will give me the count with involving some php code. Can someone let me know how to do this?

Thank you,
A
Comment
Watch Question

Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
If there is nothing else in that column, then COUNT(*) will work.  See here http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html and here http://www.w3schools.com/sql/sql_func_count.asp

Author

Commented:
Hello Dave, I am familiar with the count statement, but I need to be able to strip the "," and "-201..." to be able to get the correct count.


A
Fixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2011
Author of the Year 2014

Commented:
You may want to consider getting the assistance of a professional DBA to help you redesign this table.  You should be able to make queries like this very simply, but it looks like the data structure permits a collection of semi-useless data to invade the rows.

There are regular expressions that can help in the query, but for my money, a PHP script is the way to go.  Make a SELECT that captured all rows and look at each of them.  With a bit of PHP logic you can separate the fields into array collections of the data you want.  Then PHP count() will be your answer.
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
That should probably be an OR statement.
WHERE column LIKE '%Chair%' OR column LIKE '%Footrest%' OR column LIKE '%armchairs%'

Open in new window

Most Valuable Expert 2011
Author of the Year 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
While that is generally true... I wouldn't count on it.  And it is not true for database and table names using MyISAM engine on non-Windows systems because database and table names are also used for file names which are case-sensitive on those systems.  Someone noted recently that phpMyAdmin forces database and table names to be lower case at creation.  I think someone got tired of "answering the phone".
Most Valuable Expert 2011
Author of the Year 2014

Commented:
You're right -- case-insensitivity is not true for database and table names, just MySQL commands and the data that is housed in the database.  And like all things in computers, you can override the standard settings!  In my experience, unless you program it wrong, MySQL will honor the data, byte-by-byte, and will not make any alterations to case upon INSERT.  SELECT can control whether case matters, depending on initialization settings
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.