Solved

php /mysql count statement...

Posted on 2014-12-05
8
463 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
0
Comment
Question by:aej1973
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40483901
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
0
 

Author Comment

by:aej1973
ID: 40483930
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
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 250 total points
ID: 40483955
Use a 'LIKE' statement in the WHERE clause.

WHERE column LIKE '%Chair%' AND column LIKE '%Footrest%' AND column LIKE '%armchairs%' AND 

Open in new window

You don't actually need the last one because 'armchairs' already contains the word 'chair'.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40484050
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.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40484079
That should probably be an OR statement.
WHERE column LIKE '%Chair%' OR column LIKE '%Footrest%' OR column LIKE '%armchairs%'

Open in new window

0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 40484112
@Dave: I think the "default" installation of MySQL makes the character set case-insensitive.  So your earlier comment about Chair being a subset of armchairs is exactly right.  This WHERE clause would also be expected to find them all:

WHERE column LIKE '%cHAIr%' OR column LIKE '%FooTREst%'

Open in new window

0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40484167
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".
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40484522
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
database connection error mysql stops 7 79
php hashing methods 3 45
Can't find an open element in HTML 5 51
Load string Array from file 23 37
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question