son.of.the.morning Posted December 8, 2011 Share Posted December 8, 2011 Is there a php method/function that can count how many records in a table have the same id. For example you have two tables relating to one another there for there is reason for a coloum to have the same id as another. Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/ Share on other sites More sharing options...
OOP Posted December 8, 2011 Share Posted December 8, 2011 You can use the count and a join sql statement to achieve that Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295759 Share on other sites More sharing options...
son.of.the.morning Posted December 8, 2011 Author Share Posted December 8, 2011 have you an exaplme with out the join? Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295760 Share on other sites More sharing options...
ddubs Posted December 8, 2011 Share Posted December 8, 2011 A tables id is usually its primary key and should be unique across that entire table. Some tables may contain an id as a reference back to a "foreign" table, these are foreign keys. So in your case you would have something kinda of like this: table: groups ----------------- id | group ----------------- 0 | root 1 | wheel 2 | nobody table: users ---------------- id | username | group_id ------------------------ 0 | root | 0 1 | admin | 1 2 | michael | 1 3 | gob | 1 4 | buster | 2 So as you can see in users table, group_id can be repeated (and thats fine). To query the DB and return username and groupname of the user, you would do: SELECT group.groups, username.users FROM groups, users WHERE group_id.users = id.groups Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295763 Share on other sites More sharing options...
son.of.the.morning Posted December 8, 2011 Author Share Posted December 8, 2011 this is helpful fo course but it's not what am trying to do. I dont want to show the relation between two tables even though they are. All i want to do if to be able to count how many records have the same content. for example if you have category_id which can have more then one record with the same id in. I want to be able to say how many records in the table have the same category id. Hope this makes sence Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295765 Share on other sites More sharing options...
KevinM1 Posted December 8, 2011 Share Posted December 8, 2011 Why don't you show us how your tables are laid out? Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295767 Share on other sites More sharing options...
son.of.the.morning Posted December 8, 2011 Author Share Posted December 8, 2011 I dont think you understand me. It doesnt matter how many tables are laid out i am only querying one table. I want to count the amount of rows that have the same category_id. Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295770 Share on other sites More sharing options...
ManiacDan Posted December 8, 2011 Share Posted December 8, 2011 SELECT COUNT(*) FROM yourTable WHERE category_id = 4; Unless of course you mean something else, the sentence you said has four possible interpretations, I chose the easiest one because I'm not done with my coffee yet. Show data, and show the kind of result you're looking for. Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295773 Share on other sites More sharing options...
Pikachu2000 Posted December 8, 2011 Share Posted December 8, 2011 SELECT field, COUNT(field) FROM table GROUP BY field Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295775 Share on other sites More sharing options...
son.of.the.morning Posted December 8, 2011 Author Share Posted December 8, 2011 I really dont have a clue about this right now. I need to be able to say how many records in this colum have the value of 2. then i need to get that number by it's self into a var. Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295780 Share on other sites More sharing options...
KevinM1 Posted December 8, 2011 Share Posted December 8, 2011 We can't help you because we're guessing about what you want based on your complete lack of detail and poorly explained problem. So, again: SHOW US YOUR TABLE(S) And tell us explicitly how you want to use the data from which column(s). Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295784 Share on other sites More sharing options...
son.of.the.morning Posted December 8, 2011 Author Share Posted December 8, 2011 Sorry man. Table id title body category_id <<<<< i want to see how many records in this coloum have the same value date Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295786 Share on other sites More sharing options...
KevinM1 Posted December 8, 2011 Share Posted December 8, 2011 What do you mean by 'value' in this context? The value of a different column in the same table, like title or body? Or just how many items share that category_id? Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295788 Share on other sites More sharing options...
son.of.the.morning Posted December 8, 2011 Author Share Posted December 8, 2011 how many items share that category_id Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295789 Share on other sites More sharing options...
KevinM1 Posted December 8, 2011 Share Posted December 8, 2011 Then Dan and Pikachu's replies should work. If you tried one of them, and the results don't look right, then we'll need to know: A. What the results were Compared to: B: What you expected Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295791 Share on other sites More sharing options...
son.of.the.morning Posted December 8, 2011 Author Share Posted December 8, 2011 I tryied the first one and i kept getting "Resource id #7 " thats returned i dont no how to put Pikachu's one in as i dont under stand it i want to specify a number my self to check you see Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295793 Share on other sites More sharing options...
ddubs Posted December 8, 2011 Share Posted December 8, 2011 Thats the identifier for the query you just ran. Now you need to fetch the results... mysql_fetch_assoc Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295796 Share on other sites More sharing options...
KevinM1 Posted December 8, 2011 Share Posted December 8, 2011 Okay, now we're getting somewhere. You need to fetch the results after you run the query, just like normal: $query = "SELECT COUNT(*) FROM table WHERE category_id = $catID"; $result = mysql_query($query); $row = mysql_fetch_array($result); echo "Number of entries: " . $row[0]; Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295797 Share on other sites More sharing options...
son.of.the.morning Posted December 8, 2011 Author Share Posted December 8, 2011 Getting an error now. this is my code $a = mysql_query("SELECT COUNT(*) FROM blog_posts WHERE category_id = 1")or die(mysql_error()); $b = mysql_fetch_assoc($a); echo $b['0']; Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295799 Share on other sites More sharing options...
ddubs Posted December 8, 2011 Share Posted December 8, 2011 Please post the error text. Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295801 Share on other sites More sharing options...
ManiacDan Posted December 8, 2011 Share Posted December 8, 2011 Good thing you actually showed us the error, otherwise we'd be grasping blind! Hint: how is your code different from the code you were provided? (Double hint: there's two key differences) Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295802 Share on other sites More sharing options...
son.of.the.morning Posted December 8, 2011 Author Share Posted December 8, 2011 Finally it works, thank you guys! hahaha sorry for the poor explanations! Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295806 Share on other sites More sharing options...
ManiacDan Posted December 8, 2011 Share Posted December 8, 2011 Finally it works, thank you guys! hahaha sorry for the poor explanations! Honestly, if you really were sorry, you would have fixed them. Read how to write a smart question. You were asked to clarify 3 times and you refused. That's not a "haha sorry" situation. Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295809 Share on other sites More sharing options...
son.of.the.morning Posted December 8, 2011 Author Share Posted December 8, 2011 Wow your a bell end! Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295815 Share on other sites More sharing options...
ManiacDan Posted December 8, 2011 Share Posted December 8, 2011 You're* Thread closed. Link to comment https://forums.phpfreaks.com/topic/252750-couting-how-many-records-have-the-same-id/#findComment-1295844 Share on other sites More sharing options...
Recommended Posts