putting results in array

[canabatz]

i want to put results in array ,and count each array

i want to count how much result users have

example:

 

i do SELECT for users in a tabel with order by:

 

user1

user1

user2

user2

user1

user3

user3

user3

 

i need a code to count the users that have 3 records in a row!

 

in my example user3 got 3 records, i want the code to tell me which user got 3 records in a row and tell

me the array number that is record 3 from the same user!

 

my problem is that i dont know how to get results and put the in array.

 

thanx for any help and way to achieve this .

[canabatz]

i got for example this code:

$query="SELECT * FROM `my_table` where id='1233'  LIMIT 10";
$result=mysql_query($query) or die('Failed selecting applications: ' . mysql_error());



while($row = mysql_fetch_array($result)) 
{

$first_name=$row['first_name'];

$a = array("$first_name");

print_r($a); echo "<br />";




}

 

the result is :

Array ( [0] => user1 ) 
Array ( [0] => user1 ) 
Array ( [0] => user2 ) 
Array ( [0] => user3 ) 
Array ( [0] => user3 ) 
Array ( [0] => user5 ) 
Array ( [0] => user6 ) 
Array ( [0] => user7 ) 
Array ( [0] => user7 ) 
Array ( [0] => user7 )

 

 

how do get it like this:

Array ( [0] => user1 ) 
Array ( [1] => user1 ) 
Array ( [0] => user2 ) 
Array ( [0] => user3 ) 
Array ( [1] => user3 ) 
Array ( [0] => user5 ) 
Array ( [0] => user6 ) 
Array ( [0] => user7 ) 
Array ( [1] => user7 ) 
Array ( [2] => user7 )

 

 

thanx

[DavidAM]

Your code is not collecting the users in an array, so I'm not sure what you mean by

i want the code to tell me which user got 3 records in a row and tell

me the array number that is record 3 from the same user!

 

But this should show which users have 3 entries in a row:

$query="SELECT * FROM `my_table` where id='1233'  LIMIT 10";
$result=mysql_query($query) or die('Failed selecting applications: ' . mysql_error());

$lastU = '';
$inRow = 0;
while($row = mysql_fetch_array($result))
{
  if ($lastU == $row['first_name']) {
    $inRow++;
  } else {
    $lastU = $row['first_name'];
  }
  if ($inRow == 3) {
    echo $lastU . ' 3 in a row';
  }
}

[canabatz]

thats great!!

 

now i want to update the row number 3!

 

user1

user1

user1 <= how i chose this row to update?

 

i want to update this row ,how do i tell the update that i want to update exactly this row?

 

 

thanx

[DavidAM]

You will have to determine is unique about the row.  Then you can do an update with a WHERE clause specifying the uniqueness of the row.  I don't know what your table looks like, so i can't tell you how to do that.  What is the table structure? and what are you trying to update?

[Zane]

Wouldn't something like this work?

SELECT first_name, COUNT(*) as count FROM `my_table` where id='1233 GROUP BY first_name

 

Although it wouldn't put them into an array, $row['count'] would hold the quantity of user3, user2, etc, etc.

 

while($row = mysql_fetch_array($result)) {
     echo $row['first_name'] . " appears " . $row['count'] . "times";
}

 

[DavidAM]

@zanus:

The SELECT COUNT(*) solution would be better if he was looking for people with 3 entries; but the post indicated he was looking for 3 consecutive entries, and I couldn't see a way to do that in a query.

 

@canabatz:

By the way, I did not think about this before, but that query may not return what you expect.  I have not really read through the complete specs for mySql, but without an ORDER BY clause, the order of the rows may be random.  What I'm saying is that you may get 3 consecutive entries with the query, but the rows may not actually be consecutive in the database.  I'm still not sure what you are trying to accomplish, but if you have a create_date column or an auto_increment column, you should probably be ordering the results by that column to make sure you get the same sequence as when the rows went into the database.

[canabatz]

this is the code i was using before ,it is working but my problem is that the limit for only 50 rows is not

working ,it is going over 50 rows ,i want my code to select only the first 50 rows!

i was thinking about maybe put the results in array then i can skip the update limit 1!

$query909 = mysql_query("SELECT reg_id, bid_price FROM `bidding_details` where `bid_id`='$bid_id' and `sortbid` = '1' order by bid_price asc limit 50") or die(mysql_error());
$last = '';
$cubid = 0;
$remove_bid_count = 0;
while($line = mysql_fetch_assoc($query909)) {
  if($last == $line['reg_id']){ 
  $cubid++;
  $bid_position[$cubid] = $line['bid_price'];
}
  else {
    $last = $line['reg_id'];
    $cubid = 1;
    $remove_bid_count = 1;
    $bid_position[$cubid] = $line['bid_price'] ;
  }
  if($cubid > 3) { 
$remove_bid = $bid_position[$remove_bid_count];
$sql_update="update bidding_details set sortbid = '0', rank = '0' where `bid_id`='$bid_id' and sortbid = '1' and `bid_price`='$remove_bid' order by rank desc limit 1";
mysql_query($sql_update)or die(mysql_error());
$remove_bid_count++;
}
}

 

the result im geting now for the example is:

 

user1 10.10

user1 10.09

user1 10.08

user2 10.06

user2 10.04

user3  9.12

user1  9.08

user1  9.07

 

now if i got 100 rows the limit 1 is starting from row 100 insted of row 50 ,or if it find same user that got over 3 result

in consecutive order.

 

the rule in my site is that you cannot have more then 3 consecutive bids ! ,if you got 4 ,then it's over the rule ,and the small amount

of the bid is geting changing to be SORTBID 0 ,and geting out from the list.

 

if i got my result like that:

 

user1 10.10

user1 10.09

user1 10.08

user1 10.07 <=--- this one is over the rule and will be updated.

user2 10.06

user2 10.04

user3  9.12

user1  9.08

user1  9.07

 

i hope i explaind it better now.

 

thanx allot

[Zane]

the rule in my site is that you cannot have more then 3 consecutive bids ! ,if you got 4 ,then it's over the rule ,and the small amount

of the bid is geting changing to be SORTBID 0 ,and geting out from the list.

Just a quick question of logic here.

 

If your rule is to have max 3 bids, then why would you insert the 4th bid? And then turn around and try to check for it.  That's just like the "honor system" with a candy jar.  Putting up a sign saying "Only three pieces per person".  And then having a security guard to watch by and tackle down whomever he can.. that he believes took another piece.

 

I would simply query the database before every inserting a single bid.  Run that count method I showed you earlier to check.  If it's three then don't add a damned thing and give them an error message.

[canabatz]

that is the game , if you want to put higher bid ? ,then you do it even if you know that the lowest bid will

be burnt!

 

this is auction site ,the highest unique bid is the winner! ,so when the time comes ,you are going to put more bids

to win!!

 

[DavidAM]

First, I agree with Zanus on this one.  If the rule says you can't have more than 3 bids in a row, then you check when they enter the bid, and you reject it if it violates the rule. (Select the highest three bids in the table, if they all have the same user as the one placing the bid, they are trying to break the rule and you reject the bid).

 

I'm really confused by the logic here.  Why would user1 enter a higher bid when he has the highest bid?  He's just raising the price for himself.  Is this some kind of multi-winner auction?  The top 5 bids ALL win, but a specific user can only have 3 winning bids?

 

If that is the case, I can understand the problem. However, if there is only 1 winning bid (as your last post seems to state) then why would a user out-bid himself.  If I put in the highest bid, at say $12 why would I then bid $13?  I just increased the cost to myself since I already have the highest bid.  And why do you care how many bids I put in a row if the only bid that matters is the absolute highest bid?

[canabatz]

ok!

im going to explain the logic here!

 

first of all it is auction site!

the winner is the one that bid the high'st bid (there is a maximum bid_price that you allow to bid)

users bids are hidden(you cannot see others bids)

 

if the max bid is 99.99 then you cannot bid over 99.99(you cannot bid on 100.00)

the rules are:

1. users cannot have 4 consecutive bids ,only 3 ,in the first 50 rows!

2. users can have maximum 9 bids in the first 50 rows!

 

-------------------

 

if two users got the same amount of bid l( if two users bid on 97.98) both of them are burnt!

if more users bid on 97.98 ,they are burnt also!

 

the winning bid can be as low as 95.04!! because all the bids higher then this bid is duplicate bids!!

 

and please don't ask for logic when people are looking for help ,if some one needs help in something

it is because he need it! ,and he couldn't successd doing it!!

 

don't think people are doing stupid things!

 

thanx ,and if some one wants to help ,then help , don't ask for logic !

 

 

one more thing ,users paying for any bid!

 

[DavidAM]

I apologize if you were offended by my questions.  I did not intend to imply you were doing something stupid.  I was offering help (which is what we do here) in the form of an alternative solution.  If our alternative solution was acceptable to you, then your problem would be solved.  But, we don't know the logic of your application, so we did not know the alternative was not acceptable.  Sometimes, people do not ask the right question; sometimes you have to "think outside of the box"; sometimes a new perspective on a problem produces an elegant solution.  Without understanding the problem, we are offering band-aids for bullet-holes at best.  When the issues are not understood, it is possible to provide a "solution" that creates more problems later or just does not fit with the "logic" of the application.  So we ask questions. 

 

But hey, if you don't want a "real" solution, then I'll stick to your last question (which I did not fully understand, but I am not allowed to ask questions ...):

 

this is the code i was using before ,it is working but my problem is that the limit for only 50 rows is not

working ,it is going over 50 rows ,i want my code to select only the first 50 rows!

i was thinking about maybe put the results in array then i can skip the update limit 1!

$query909 = mysql_query("SELECT reg_id, bid_price FROM `bidding_details` where `bid_id`='$bid_id' and `sortbid` = '1' order by bid_price asc limit 50") or die(mysql_error());

...

the result im geting now for the example is:

 

user1 10.10

user1 10.09

user1 10.08

user2 10.06

user2 10.04

user3  9.12

user1  9.08

user1  9.07

 

The query is ordering by bid_price asc so it did NOT produce those results.  Actually, the code had no output statements so it did not produce those results AT ALL.  If you only want the highest 50 bids, you need to sort DESCENDING not ASCENDING.

 

now if i got 100 rows the limit 1 is starting from row 100 insted of row 50 ,or if it find same user that got over 3 result in consecutive order.

LIMIT 1 does not start at row 100 and should not start at row 50; so I don't know what you are saying here.

[canabatz]

Thats ok ,i understand some times you need to explain better to get better results!!

 

if i ORDER BY DESC then the highst bid will be updated ,and what i need is the lowest one from the 4!

 

i did try allot of combinations! ,my problem is that i want only the code to check for the first 50 rows!

 

when im ORDERING BY DESC the limit 50 is working! but it's not solving my issue .

 

when im ordering by ASC then the code is starting from the last record (if i got 100 rows then it starts from row 100)

 

thanx David!

[DavidAM]

But, in the select you are ordering ASC so you are NOT getting the top 50 bids.  You are getting the bottom 50 bids that are still open.  When you the update them, you still have bids in the top 50 that need to be considered.

 

Even if you do the first select DESC, when you mark some bids as not valid, that will allow other bids to float up into the now top 50 that may need to be considered.

 

If bid_details had a unique column (such as bid_detail_id integer auto_increment) the whole process would be simple.  Is that a possibility?

 

Without that, you probably need to mark everything that is not in the top 50 as not valid, but that would leave you with less than 50 valid bids after marking duplicates as not valid.

[canabatz]

there is id auto_incerment ,the new id can be in any row becuse of the order by bid_price!

there is RANK also.

there is SORTBID ,that it is 1 when inserted ,and got update to 0 when it is over 3!

 

i want to SELECT with DESC LIMIT 50  ,but i got the high'st bid updated not the low'st

 

tahnx

 

 

[DavidAM]

If id is a unique column in bidding_details, then we can update using that column's value:

 

// Get the top 50 valid bids sorted from highest to lowest
$query="SELECT id, reg_id, bid_price FROM `bidding_details` where `bid_id`='$bid_id' and `sortbid` = '1' order by bid_price DESC limit 50";
$result=mysql_query($query) or die('Failed selecting applications: ' . mysql_error());

// Walk the result set to check for users with more than 3 in a row
$lastU = '';  // The last user we found in the result
$inRow = 0; // How many this user has in a row
$reject = array();  // ID's of entries to be rejected
while($row = mysql_fetch_array($result))
{
  if ($lastU == $row['reg_id']) { // If same user as last row increment count
    $inRow++;
    if ($inRow >= 3) {  // If the user has more than 3 in a row, we reject the ID
      $reject[] = $row['id'];
    }
  } else {  // Not same user, so change the last user we saw
    $lastU = $row['reg_id'];
    $inRow = 1;
  }
}

// If any entries were put into the reject array, update them all at once.
if (count($reject) > 0) {
  $sqlU = "UPDATE bidding_details SET sortbid = '0', rank = '0' WHERE id IN (" . implode(',', $reject) . ")";
  mysql_query($sqlU);
}

This collects the unique id's of the third (or fourth or fifth, etc) in a row and then updates them at the end of the loop.

[canabatz]

David!! you are the best!!

 

thanx allllllllot!

 

i was working on it for long long time!!

 

my other code was working but the limit didnt!!

and some times i was trying to figure this out ,with no seccess !!

 

but here you gave me the exactly how it needs to be!!!

 

 

thanx a million!

[canabatz]

hi all!

 

After the help of David and help me out fixing my problem! ,im trying to modify is code to a new code to do something that i need to do too!

 

the fixed  code is checking if user got 3 records in consecutive order ,and the user cannot have 4 ,if the user got 4 ,then the

lowest bid is updated! ,im using this code in my production site!

 

now i need a code allmost the same but with some modifications!

 

 

here is the working code from David :

$query909 = mysql_query("SELECT reg_id, bid_price FROM `bidding_details` where `bid_id`='$bid_id' and `sortbid` = '1' order by bid_price asc") or die(mysql_error());
$last = '';
$cubid = 0;
$remove_bid_count = 0;
while($line = mysql_fetch_assoc($query909)) {
  if($last == $line['reg_id']){ 
  $cubid++;
  $bid_position[$cubid] = $line['bid_price'];
} //end of if 
  else {
    $last = $line['reg_id'];
    $cubid = 1;
    $remove_bid_count = 1;
    $bid_position[$cubid] = $line['bid_price'] ;
  }
  if($cubid > 3) { 
$remove_bid = $bid_position[$remove_bid_count];
$sql_update_bids11111="update bidding_details set sortbid = '0', rank = '0' where `bid_id`='$bid_id' and sortbid = '1' and `bid_price`='$remove_bid' order by rank desc ";
mysql_query($sql_update_bids11111)or die(mysql_error());
$remove_bid_count++;
}
}

 

im trying to modify this code to check in the first 50 result if user got more then 9 rows ,not in consecutive order like before!

 

i need the code to update record number 10 that is the lowest BID_PRICE from the same user !

 

thanx in advanced for the helpers!