counting rows

[canabatz]

i got for example this result from data base:

user1
user1
user1
user2
user2
user1
user1

how can i count in php like that:

user 1 have 3 in a row ,then user2 have 2 in a row ,then user1 have 2 in a row?

thanx

[Dormilich]

compare the adjacent values, count up if they are the same and store the count somewhere if the test fails. you have to make up a suited storage system/variable though.

regards

[canabatz]

any short code example for counting like that?

i didn't get you ,sorry :(

tahnx

[Dormilich]

something like:

for ($i=0; $i<$arr_length; $i++) {
// compare this and the next value
// if true increase count
  if ($arr[$i] == $arr[$i+1]) $count++;
  else {
// if false reset count to 1 and save value along with current count
    save_in_storage($arr[$i], $count);
    $count = 1;
  }
}

note that you have to define save_in_storage() and $arr_length yourself. this is not a working example, it is meant to show the principle, you can use it as starting point.

regards

[canabatz]

thanx allot ,im going to try that :)

[pbmods]

Heya, canabatz.

Try adding a DISTINCT to your SELECT query (http://www.w3schools.com/SQL/sql_distinct.asp).

[canabatz]

it is not distinct my problem :) , i need to count the results sent by users ,i need the code to start counting from top of the results and find me results from the same user ,i want to be able to limit results by user ,i want to limit the user to maximum 4 rows in a run if it reaches 5 rows the result number 5 will be deleted or something !

like this situation is good:

user1
user1
user1
user1
user2 <===== there is one result between user1 so its ok
user1

and this way is not good:

user1
user1
user1
user1
user1 <===== there is 5 result from user1 so result No. 5 will be deleted
user2

thanx :)

[canabatz]

i didnt figure this yet ,please help!!

[nathj]

Hi,

Yo could do one of two things.

1. Run a second query using a count() and group by clause to get just the count out.

2. Use the query you have got and the loop through an array keeping count, when the value changes store the count to an associative array and rest the count.

Either of those options should get you going in the right direction.

nathj

[canabatz]

i try so many combinations ,but not results :(

this is the code i'm using right now for testing ,but its limiting the user to maximu 4 results from the total of 50!!

$query = mysql_query("SELECT * FROM `bidding_details` where bid_id='$bid_id' and username='$username' limit 50") or die(mysql_error());
$last = '';
$count = 0;
 
while($line = mysql_fetch_assoc($query)) {
  if($last == $line['username']) $count++;
  else {
    $last = $line['username'];
    $count = 1;
  }
 
  if($count == 4) {
    header("location:product_detailframe.php?msg=11&&bid_id=$bid_id");
    exit;
  }
}

and here is what i need it to do:

green is good it's not over the limit ,red is bad its over the limit ,the yellow row is the last inserted row ,so username canabatz got is bid in 5 rows in a row ,and it will be deleted or just display a massage!!

thanx

[nathj]

Ok, I think I get it now, that picture helped me see what you are after.

Perhaps we need to change approach here. Counting rows is all well and good and perhaps a SQL COUNT() and group by query would work maybe something like:

SELECT username, count(*) FROM biddingdetails group by username having count(*) >=1 ;
 

That should get you a list of each username that has bid at least once and how many bids they have made.

Alternatively it may be that you generate a new table that permanently stores the count and that figure is incremented with every bid. This would give you the bid count and the full bid history.

Have a think around those two options.
nathj

[canabatz]

there is no way to do it like that:

query started

if found user1! start counting
if no more user1 in the list stop counting user1
start counting user2 if finished with this user2 and there is new user
start counting the new user!!

im stuck!! :(

[chelvan]

define an array. its length limit by your database values (distinct). then increase the specific array element.then finally you able to get the result.

regards
chel-1

[Markus]

@chelvan
Care to explain a little more, even I don't understand.