Page 1 of 1

limit problem

Posted: Sat Nov 28, 2009 2:31 pm
by canabatz
Hi all
it as been 6 months that im trying to figure out this problem!
and i cannot solve it ,here is my code:

Code: Select all

$sql_f="SELECT reg_id, count(bid_price) as cnt, min(bid_price) as low FROM `bidding_details` where bid_id='$bid_id' and sortbid = '1' group by reg_id HAVING COUNT(reg_id) > 9  limit 50";
$results=mysql_query($sql_f)or die(mysql_error());
$row = mysql_fetch_assoc($results);
$num = $row['low'];
$cnt = $row['cnt'];
 
if($cnt > 9) {
 
$sql_u="update bidding_details set sortbid = '0' ,rank = '0' where bid_id='$bid_id' and bid_price='$num'";
mysql_query($sql_u)or die(mysql_error());
 
  }
i tryd million combinations with no lock!

maybe there is other way to do it?

what im trying to do is:

i want to limit users to have maximum 9 records in the first 50 records, if user got 10 ,then this record will be updated to SORTBID='0'

my problem now is that the limit is not working ,there results are taken from more then 50 rows!!

please please help me with that!!

thanx

Re: limit problem

Posted: Sat Nov 28, 2009 8:38 pm
by Robert07
It looks to me like you'll need to split up your query into 2 pieces, because you seem to be mixing grouped and nongrouped data. Can you post the DDL of the bidding_details table please? You can see that by executing this command in mysql (assuming that's the db you're using):
select create table bidding_details;

Re: limit problem

Posted: Sun Nov 29, 2009 3:49 am
by canabatz
hi Robert!

im using Mysql ,what is the exact command for mysql i need to execute?

thanx

i tryd:

select create table bidding_details;

i get error!

thanx

Re: limit problem

Posted: Sun Nov 29, 2009 3:53 am
by phoenixrises
Believe you're looking for SHOW CREATE TABLE tablename; or DESC tablename; ?

Re: limit problem

Posted: Sun Nov 29, 2009 11:11 am
by Robert07
Yes, sorry it's show create table not select create table...

Re: limit problem

Posted: Sun Nov 29, 2009 12:35 pm
by canabatz
CREATE TABLE `bidding_details` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(30) NOT NULL default '',
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`city` varchar(30) NOT NULL,
`bid_id` int(11) NOT NULL default '0',
`bid_status` varchar(15) NOT NULL default '',
`bid_price` decimal(10,2) NOT NULL default '0.00',
`bid_max_price` decimal(10,2) NOT NULL default '0.00',
`bid_credit` int(11) NOT NULL default '0',
`delmark` smallint(1) NOT NULL default '0',
`sortbid` int(2) NOT NULL default '1',
`hazibid` int(5) NOT NULL default '0',
`rank` int(11) NOT NULL default '0',
`reg_id` varchar(30) NOT NULL,
PRIMARY KEY (`id`),
KEY `bid_id` (`bid_id`),
KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=43707 DEFAULT CHARSET=latin1

Re: limit problem

Posted: Sun Nov 29, 2009 3:38 pm
by Robert07
ok, so you want to do this:
i want to limit users to have maximum 9 records in the first 50 records, if user got 10 ,then this record will be updated to SORTBID='0'
How do you define the first 50 records? I don't see an order by clause in your query. You are currently pulling data for up to 50 different reg_ids with your group by clause. Does a reg_id correspond to a user? It seems to me like this is how the code should work:
1) pull the first 50 records (however you define the first 50)
2) loop through the resultset, counting up the records for each user (by reg_id I assume?) in a multidimensional array
3) if at any point the count for one user goes above 9, then run an update on that record before moving on through the 50 results

Regards,
Robert

Re: limit problem

Posted: Sun Nov 29, 2009 4:06 pm
by canabatz
Ho ,thats easy :) ,not for me! ,im noob!

i wish i knew how to do that ,i dont understans s....t in multi dimensional array!!

any small example how to get results into multi... array?

please ,and thanx Robert!! for pointing me!!

Re: limit problem

Posted: Sun Nov 29, 2009 4:17 pm
by Robert07
If you would answer my questions above I would better be able to give you a working example you can play with.

Re: limit problem

Posted: Sun Nov 29, 2009 4:31 pm
by canabatz
reg_id is user register id.

every user got is reg_id like '112321'.

thanx allot robert!

Re: limit problem

Posted: Sun Nov 29, 2009 5:32 pm
by Robert07
You may want to change the update query because I am not sure if I understand how you want that done, but this is the idea (and I guess you don't need a multidimensional array after all):

Code: Select all

 
$sql_f="SELECT id,reg_id, bid_price FROM `bidding_details` where bid_id='$bid_id' and sortbid = '1'  limit 50";
$results=mysql_query($sql_f)or die(mysql_error());
//Loop through the 50 results
while($row = mysql_fetch_assoc($results)) {
  extract($row);
 
  //Increment a count for this user
  if (isset($regCounts[$reg_id])) $regCounts[$reg_id]++;
  else $regCounts[$reg_id]=1;
 
  //If this user has over 9 records then modify this record
  if ($regCounts[$reg_id]>9) {
    $query = "update bidding_details set sortbid = '0' ,rank = '0' where id='$id'";
    mysql_query($sql_u)or die(mysql_error());
  }
}
 

Re: limit problem

Posted: Sun Nov 29, 2009 5:35 pm
by Robert07
oops, the last part should look like this:

Code: Select all

if ($regCounts[$reg_id]>9) {
  $query = "update bidding_details set sortbid = '0' ,rank = '0' where id='$id'";
  mysql_query($query)or die(mysql_error());
}

Re: limit problem

Posted: Mon Nov 30, 2009 1:59 am
by canabatz
THANX A MILLION ROBERT!!!

that did it!!!

there was missing the order only!

here is the working code after month's im trying!!

Code: Select all

$sql_f="SELECT id, reg_id, bid_price FROM `bidding_details` where bid_id='$bid_id' and sortbid = '1' order by bid_price desc  limit 50";
$results=mysql_query($sql_f)or die(mysql_error());
//Loop through the 50 results
while($row = mysql_fetch_assoc($results)) {
  extract($row);
 
  //Increment a count for this user
  if (isset($regCounts[$reg_id])) $regCounts[$reg_id]++;
  else $regCounts[$reg_id]=1;
 
  //If this user has over 9 records then modify this record
  if ($regCounts[$reg_id]>9) {
$queryz = "update bidding_details set sortbid = '0' ,rank = '0' where id='$id' and bid_id='$bid_id'";
mysql_query($queryz)or die(mysql_error());
}
}
You are the best!!!

thank you thank you thank you!!