visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Languages :: PHP :: Counting Names in MySQL DB
By: Squibi U.S.A.  Date: 08/10/2002 00:00:00  English  Points: 300 Status: Answered
Quality : Excellent
I have a database of over 50,000 names and need a efficient fast way of getting a name count (ie. Smith appeared 2 times, Sweeney appeared 3 times etc.) Right now I am using the following but it is taking way too long to process:

global $dbHost,$db,$dbUserName,$dbPassword;
MySQL_Connect($dbHost,$dbUserName,$dbPassword);
MySQL_Select_DB($db);
$sql = "SELECT LastName FROM Quotes";
$results= MySQL_Query($sql);
$RecordCount = mysql_Numrows($results);
for ($int = 0; $int < $RecordCount; $int++){
$NameArr[$int] = mysql_result($results,$int,"LastName");
}
$NameCountArr = array_count_values($NameArr);

$TempLetter = "A";
$VAR["Letter"] = $TempLetter;
#placed all names into an array
for ($x = 0; $x < $RecordCount+1; $x++){
$LastName = @mysql_result($results,$x,"LastName");
if($LastName !== $TempName){
$TempName = $LastName;
    if(strtoupper($LastName[0]) == $TempLetter){
$VAR["LastName"] = $LastName;
$VAR["Count"] = $NameCountArr[$LastName];
$LetterCount = $AlphaArray[strtoupper($LastName[0])];    
#$LetterCount = times First letter appears

$VAR["LetterHtml"] .=  template($NameHtmlLarge);

                   }else{
$TempLetter = strtoupper($LastName[0]);
                   
$VAR["Results"] .= template("$RowHtml");
$VAR["Letter"] = $LastName[0];
$VAR["LastName"] = $LastName;
$VAR["Count"] = $NameCountArr[$LastName];
$LetterCount = $AlphaArray[$LastName[0]];

#etc. etc.
By: VGR Date: 09/10/2002 00:14:00 English  Type : Comment
1) create temporary table tempo1 select disctinct yourfullname from yourtable where 1;
2) $query="select * from tempo1 where 1 order by yourfullname asc;";
3) $result=mysql_query($query,$linkID) or die ("bad query tempo1 via $query. ".mysql_error());
4) while ($res=mysql_fetch_array($result)) {
 $locval=$res["yourfullname"]; // current name to count occurences of
 $query2="select count(*) as a from yourtable where yourfullname='$locval';";
 $result2=mysql_query($query2,$linkID) or die ("bad query yourtable via $query. ".mysql_error());
 $res2=mysql_fetch_array($result2);
 // here memorize somewhere that $res2["a"] is the count of occurences of fullname $locval
} // while
By: Squibi Date: 09/10/2002 01:32:00 English  Type : Comment
Sorry but I am an idiot, what am I doing wrong?

$sql = 'create temporary TABLE tempo1 SELECT DISTINCT LastName FROM Quotes where 1';
mysql_query($sql);
$query="select * from tempo1 where 1 order by LastName asc;";
$result=mysql_query($query,$linkID) or die ("bad query tempo1 via $query. ".mysql_error());
while ($res=mysql_fetch_array($result)) {
$locval=$res["yourfullname"]; // current name to count occurences of
$query2="select count(*) as a from quotes where LastName='$locval';";
$result2=mysql_query($query2,$linkID) or die ("bad query yourtable via $query. ".mysql_error());
$res2=mysql_fetch_array($result2);

 // here memorize somewhere that $res2["a"] is the count of occurences of fullname $locval
} // while
By: VGR Date: 09/10/2002 01:38:00 English  Type : Comment
change "Yourfullname" to "LastName" in the $locval assignment

I don't knwow which is faster of "select * from tempo1" or "select LastName from tempo1" baceuase you have only one attribute/field/column in tempo1
By: Squibi Date: 09/10/2002 01:44:00 English  Type : Comment
I am getting the following error (after making the correction).

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/famousquotes/html/byLastNamecreate.php on line 82
bad query tempo1 via select * from tempo1 where 1 order by LastName asc;.

Line 82 =
$result2=mysql_query($query2,$linkID) or die ("bad query yourtable via $query. ".mysql_error());
By: VGR Date: 09/10/2002 01:57:00 English  Type : Comment
Sorry, but have you done your mysql_connect beforehand ? (or _pconnect) ???

$linkID=mysql_connect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
mysql_select_db($dbName,$linkID) or die ("bad select DB ".mysql_error());
By: VGR Date: 09/10/2002 02:00:00 English  Type : Comment
AH . I saw that you use "implicit" linkIDs in your mysql_query() calls. In this case, just remove the $linkID references everywhere and it should work.
By: Squibi Date: 09/10/2002 02:16:00 English  Type : Comment
It is running but incredibly slow. I am trying to run this as a cron command and it keeps timing out. I need a way to do this more efficiently.
By: VGR Date: 09/10/2002 02:30:00 English  Type : Answer
add an index for a start
By: Squibi Date: 09/10/2002 02:38:00 English  Type : Comment
add an index?
By: TheFalklands Date: 09/10/2002 04:03:00 English  Type : Comment
First off, it would certainly make sense to create an independant table with the details of the client (ie, firstname, lastname, address, tel, fax etc) and then use the index id in the quotes table. This is the database normalisation mentioned by redcircle at the top.

Second, yes, you might want to look at creating an index to allow mySQL to perform its lookups a bit more efficiently:

ALTER TABLE tempo1 ADD INDEX LastNameIdx (LastName);

But having said all that, to help towards an answer for your initially enquiry, try this SQL instead:

SELECT LastName, count(LastName) as frequency FROM Quotes GROUP BY LastName ORDER BY LastName ASC;

That will give you a row for each unique last name and the number of times that it occurs and eliminates the need for a temporary table.

Regards, T
By: VB guy Date: 09/10/2002 06:27:00 English  Type : Comment
Heres a one-liner which could be the thing you need ;-)

SELECT LastName,count(LastName) from Quotes group by LastName having count(LastName) > 1;
By: digitaltree Date: 09/10/2002 19:16:00 English  Type : Comment
What kind of database structure are you using?

I would make sure your database is normalized for efficiency.

To see what I mean see this site <A HREF="http://www.phpbuilder.com/columns/barry20000731.php3">http://www.phpbuilder.com/columns/barry20000731.php3</A>

In your situation I would make a table with the occurances so anytime I need to know how many times what occured all I have to do is look at that table instead of processing all the data everytime.
By: VGR Date: 09/10/2002 21:31:00 English  Type : Comment
yo

Why don't you do simply select count(*) from ... where name like '$firstLetter%';  (in a loop on $firstLetter for instance ?)

I never count occurences the way you do. I always use count()

rely on the mysqld for being optimized ;-)
By: Squibi Date: 09/10/2002 22:24:00 English  Type : Comment
How do I get the value of the count method?
By: VGR Date: 09/10/2002 22:31:00 English  Type : Comment
right. Forgot it. Write :

select count(*) as a from ...

$res["a"] will give you the answer
By: VGR Date: 09/10/2002 22:37:00 English  Type : Comment
don't forget to follow, if possible, the advice of digitaltree. You would thereafter only process NEW lines to UPDATE counts, not process them all again.

This technique is especially useful if you periodically export/backup/delete old "activity table" rows when it grows too large. Your "old lines" analysis in terms of occurences would be already saved in a persistent table called "stats"
By: Squibi Date: 09/10/2002 23:48:00 English  Type : Comment
I don't really follow you, about only processing new lines. I need some way to get a count of how many times each last name appears in the database when records (and new last names) are being added daily. I could easily get a count and store it in the db but how would I ensure that in the future I was only processing the lines that hadn't been processed before?

Also, the count example given works great for counting occurances of the first letter but I also need to count the occurances of the full last names.
By: sumotimor Date: 13/10/2002 03:11:00 English  Type : Comment
My esteemed friend VBguy has suggested the method, which I was going to write, anyway its correct. The GROUP BY clause does the work
By: sumotimor Date: 13/10/2002 03:20:00 English  Type : Comment
Oops ! soz !! Actually I wanted to write that as a comment. Squibi, please don't award any point to me, but to VBguy or someone else who meets your requirement.

thanks

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 512.265920 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page