counting rows and limiting users!!

[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?

 

what i want to do after is to limit users to have maximum 4 results in a row!

i they get 5 in a row it will display a massage and ignore the last post!!

 

here is example:

 

the yellow is the last inserted row so it is not good!! there is over 4 results!!

 

thanx in advanced!!

[.josh]

count() should get you started.

[canabatz]

i have this ,but it's limiting to have maximum 4 results ,from the LIMIT 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;
  }
}

 

thanx

 

 

[canabatz]

this the only thing i got to solve ,and then open my site!!

 

please help!!

[canabatz]

me too i want to make it SOLVED

 

Please any one!

 

thanx

[Adam]

As Crayon Violet said, look into using count() within your MySQL query..

 

Adam

[unkwntech]

I'm gonna' give you the answer on this cause I'm in an oddly good mood.

 

 

but... you should learn the mysql count() function that I used in the query so here's the link

 

 

<?php
$query = mysql_query("SELECT COUNT(*) FROM `bidding_details` where bid_id='$bid_id' and username='$username'") or die(mysql_error());
$count = mysql_result($query, 0);

  if($count == 4) {
    header("location:product_detailframe.php?msg=11&&bid_id=$bid_id");
    exit;
  }
?>

[canabatz]

i try your code ,but it's not working!!

 

it's not limiting  ,i can bid as much as i want!!

 

i changed it to

if($count > 4)

then it is limiting me to 4 results maximum!!

 

what i need is that user can have maximum 4 result in a row ,like groups of 4 results ,and they cannot have 5 results in a row!! and they can have few groups of 4!!

 

im happy that you are in a good mood!! so lets try fix it

[unkwntech]

try:

<?php
$query = mysql_query("SELECT COUNT(*) FROM `bidding_details` where bid_id='$bid_id' and username='$username'") or die(mysql_error());
$count = mysql_result($query, 0);

  if($count < 5) {
    header("location:product_detailframe.php?msg=11&&bid_id=$bid_id");
    exit;
  }
?>

[canabatz]

the same ,i can bid as much as i want

[gaza165]

<?php
$query = mysql_query("SELECT COUNT(*) FROM `bidding_details` where bid_id='$bid_id' and username='$username'") or die(mysql_error());
$count = mysql_result($query, 0);


echo $count;

  //if($count < 5) {
   // header("location:product_detailframe.php?msg=11&&bid_id=$bid_id");
   // exit;
  //}
?>

 

echo out your count to see how many rows you are getting before the header executes... let me know what it gives out

[canabatz]

it's giving me the number of rows by current user.

[canabatz]

Hey gaza ,What now

[PFMaBiSmAd]

Just using count is going to give all the rows by each id, not the most recent consecutive rows.

 

The most straight forward way that I can see to do this will require that you add a column to hold the consecutive bid count. When you get a new bid, check if the last bid was by the same id. If the id of the last bid is different than the current bid, you allow the bid and save a 1 in the consecutive bid column. If the id of the last bid is the same as the current bid and the consecutive bid column is less than three, you allow the bid and store the next number in the consecutive bid column. If the id of the last bid is the same as the current bid and the consecutive bid column is three, you don't allow the bid. You will either need to use nested queries or lock the table to insure that the last bid information does not change while you are accessing it.

[PFMaBiSmAd]

Edit to the above, change all references of "three" to "four". You want to allow four, but not five. What I wrote would allow three but not four.

[PFMaBiSmAd]

This thread might help as well - http://forums.mysql.com/read.php?10,223385,223385#msg-223385

[sasa]

how you calculate the place where data go

[PFMaBiSmAd]

Here is an alternative way using some of the ideas at that link (temporary variables instead of actually having a column that you store the value in) -

 

<?php
$query = "set @last_bid_id := '', @num := 1";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
$query = "select id, bid_id, @num := if(@last_bid_id = bid_id, @num + 1, 1) as consec_bid, @last_bid_id := bid_id as dummy from test";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
mysql_data_seek($result, mysql_num_rows($result) - 1);
$row = mysql_fetch_assoc($result);
echo "<pre>",print_r($row,true),"</pre>";
?>

 

This code operates on the actual result set, so you cannot use ORDER BY or LIMIT to just retrieve the last row to get how many consecutive bids there currently are. You need to use the mysql_data_seek function to get to the last row and then fetch it.

 

For some test data, the whole result set would look like this -

 

id bid_id consec_bid dummy

0  987  1  987

1  987  2  987

2  987  3  987

3  345  1  345

4  345  2  345

5  345  3  345

6  345  4  345

7  123  1  123

8  123  2  123

9  123  3  123

10  123  4  123

 

The data_seek/fetch gives -

 

Array

(

    [id] => 10

    [bid_id] => 123

    [consec_bid] => 4

    [dummy] => 123

)

[PFMaBiSmAd]

Actually the method above is kind of crappy because you must query for at least the number of rows that you are testing for (4 in this case) to be able to get the logic to count the consecutive bids.

 

Here is code using the method where you actually have a column to hold the consecutive bids -

 

<?php
$new_bid_id = 123; // some new bid

$max_consec_bid = 4; // the maximum number of consecutive bids

$query = "LOCK TABLES $table_name[0] WRITE"; // lock table so only this script can (read) and write
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());

$query = "SELECT * FROM $table_name[0] ORDER BY id DESC LIMIT 1"; // get the newest row
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());

$row['bid_id'] = ''; // default value
$row['consec'] = 0; // default value
if(mysql_num_rows($result) > 0){
$row = mysql_fetch_assoc($result);
}

// check if new bid can or cannot be accepted
if($new_bid_id == $row['bid_id'] && $row['consec'] >= $max_consec_bid){
echo "Cannot accept bid from: $new_bid_id, because you already have {$row['consec']} consecutive bids<br />";
} else {
// the bid can be accepted
$new_consec = 1; // default value when there is a different bid_id
if($new_bid_id == $row['bid_id']){
	$new_consec = $row['consec'] + 1; // if a consecutive bid, count up
}
$query = "INSERT INTO $table_name[0] (id,bid_id,consec) VALUES(NULL,$new_bid_id,$new_consec)";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
}
$query = "UNLOCK TABLES";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
?>