Couting how many records have the same id.

[son.of.the.morning]

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.

[OOP]

You can use the count and a join sql statement to achieve that

[son.of.the.morning]

have you an exaplme with out the join?

[ddubs]

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

[son.of.the.morning]

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

[KevinM1]

Why don't you show us how your tables are laid out?

[son.of.the.morning]

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.

[ManiacDan]

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.

[Pikachu2000]

SELECT field, COUNT(field) FROM table GROUP BY field

[son.of.the.morning]

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.

[KevinM1]

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).

[son.of.the.morning]

Sorry man.

 

Table

id

title

body

category_id    <<<<< i want to see how many records in this coloum have the same value

date

[KevinM1]

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?

[son.of.the.morning]

how many items share that category_id

[KevinM1]

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

[son.of.the.morning]

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

[ddubs]

Thats the identifier for the query you just ran. Now you need to fetch the results...

 

mysql_fetch_assoc

[KevinM1]

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];

[son.of.the.morning]

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

[ddubs]

Please post the error text.

[ManiacDan]

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)

[son.of.the.morning]

Finally it works, thank you guys! hahaha sorry for the poor explanations!

[ManiacDan]

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.

[son.of.the.morning]

Wow your a bell end!

[ManiacDan]

You're*

 

Thread closed.