limit in update ,any way to do that?

[canabatz]

ok , i got this code for limiting users for max 9 records in the first 50 rows .

 

i want to my code  to check only in the first 50 rows .

 

now if i got lets say 70 rows ,the SELECT limit is not working , it starting from row 70 ,and i need it to look only in the 50 first rows.

 

if you got 10 rows from the same user ,the last record will be updated to be bad! (over the rule)

 

my quastion is how can i force this limit to look only in the first 50 rows ,so if a user have 9 rows in the first 50 is ok ,and if he got more rows

from 50 to 70 it's ok then!

 

$query = mysql_query("SELECT reg_id FROM `bidding_details` where bid_id='$bid_id' and sortbid = '1' and username='$username' limit 50") or die(mysql_error());
$last = '';
$count = 0;
while($line = mysql_fetch_assoc($query)) {
  if($last == $line['reg_id']) $count++; 
  else {
    $last = $line['reg_id'];
    $count = 1;
  }
  if($count > 9) {
$sql_delete="update bidding_details set sortbid = '0' ,rank = '0' where bid_id='$bid_id' and username='$username' and  sortbid = '1' order by bid_price asc limit 1";
mysql_query($sql_delete)or die(mysql_error());
  }
} 

[joel24]

i don't entirely understand what you want...

but you should order the select

... do you have a bid date field?

i.e.

add

"ORDER BY DATE(bid_date) ASC" before "limit 50"

 

[joel24]

and why do you have an "order by" clause in the update query??

 

and shouldnt "  if($count > 9) { " be "  if($count < 9) {"

... if you only want the first 9??

 

i'm totally lost as to what you're trying to achieve

[canabatz]

i got auction site ,and the highest unique bid is in the top of the result  ,it is the first record ,the date of the highest bid can be older or newer !

 

so date wont help me.

 

any other way? please

 

thanx

[canabatz]

the order is to update the last row that it is the 10th row!

 

so the order is starting with ASC

 

order by bid_price asc limit 1

 

thanx

[canabatz]

i got rules in the auction ,user cannot have more then 9 records in the first 50 rows

 

if you got 9 and you send one more bid  the the lowest bid is updated to be false!

 

thats it.

[joel24]

what exactly do you want this part of the script to do?

 

get the first 50 rows... then you should order them by price

"ORDER BY bid_price DESC" ??

 

then you want to check if a user has more than 9 bids in those 50?

and if so make the smallest bids false..?

 

whats your primary key for the bids??

[canabatz]

exactly what i need!

 

the primary key is ID.

[joel24]

what is the foreign key for the items..?

i.e. does reg_id associate the bid with an item.. or bid_id?? or something else

 

also what are reg_id and bid_id?

[canabatz]

bid_id is the current auction .

 

reg_id is the user id ,bid_price is the inserted bid.

 

thanx

[joel24]

$query = mysql_query("SELECT id, reg_id FROM `bidding_details` where bid_id='$bid_id' and sortbid = '1' and username='$username' ORDER BY bid_price DESC") or die(mysql_error());
$last = '';
$count = 0;
while($line = mysql_fetch_assoc($query)) {
  if($last == $line['reg_id']) $count++;
  else {
    $last = $line['reg_id'];
    $count = 1;
  }
  if($count > 9) {
$id = $line['id'];
$sql_delete="update bidding_details set sortbid = '0', rank = '0' where id = '$id'";
mysql_query($sql_delete)or die(mysql_error());
  }
} 

 

there would be much cleaner ways to do it, but that should work?

you might be better off just deleting that row? rather than making it "false"..

i.e.

$sql_delete="DELETE FROM bidding_details WHERE id = '$id'";

 

EDIT: i didn't put in a limit 50 on the select because this script will only delete rows if a user has more than 9 entries in that auction, and they should be deleted regardless of if they're in the top 50 or top 200....

[canabatz]

thanx man for your replays!

 

but it's still updating over 50 rows , i need only in the 50 rows to check if the user got over 9 records!

 

your code is working the same as my code!

 

i most limit the code for the first 50 rows!

 

if a user got 9 records in the first 50 thats ok ,if he got more records over the 50 rows thats ok to have!

 

thanx again

[canabatz]

so there is a way to limit to 50 rows only?

 

thanx

[canabatz]

any one?

 

maybe i need different code?

 

thanx

[mikesta707]

add a limit to joels query?

$query = mysql_query("SELECT id, reg_id FROM `bidding_details` where bid_id='$bid_id' and sortbid = '1' and username='$username' ORDER BY bid_price DESC LIMIT 50");

 

I still don't see why your doing this, but I don't care, so whatever

[canabatz]

it's not working this way ,the limit is not working .

 

if i got 70 rows ,it's starting from row 70 ,i want it to check only 50 rows/

 

thanx

[mikesta707]

maybe the query shouldn't return the results in descending order