Connecting Tech Pros Worldwide Help | Site Map

last inserted row number.

Familiar Sight
 
Join Date: Oct 2008
Posts: 141
#1: Jun 12 '09
hi all !!

i got my results like that

1 user0 55
2 user1 53
3 user2 49
4 user2 48
5 user3 47 <------- last inserted row
6 user4 46

how do i get to echo the position of the last inserted row?

the result need to be "last inserted row is 5"!!


thank you all!!
Markus's Avatar
Moderator
 
Join Date: Jun 2007
Location: York, England, with wolves.
Posts: 4,936
#2: Jun 12 '09

re: last inserted row number.


Quote:

Originally Posted by canabatz View Post

hi all !!

i got my results like that

1 user0 55
2 user1 53
3 user2 49
4 user2 48
5 user3 47 <------- last inserted row
6 user4 46

how do i get to echo the position of the last inserted row?

the result need to be "last inserted row is 5"!!


thank you all!!

There are many possible ways, it depends on how you're storing this data. Give us some more information. Also, wouldn't the last inserted row by #6 ?
prabirchoudhury's Avatar
Familiar Sight
 
Join Date: May 2009
Location: Wellington, New Zealand
Posts: 152
#3: Jun 13 '09

re: last inserted row number.


1. not very much clear abt asking.
2. we could get just inserted using

Expand|Select|Wrap|Line Numbers
  1. after executing insert statement call
  2. $last_inserted_id = mysql_insert_id();
  3.  
  4.  
or explain specific in details


:)
Familiar Sight
 
Join Date: Oct 2008
Posts: 141
#4: Jun 13 '09

re: last inserted row number.


before

count | username | bid-amount
1 user0 55
2 user2 49
3 user2 48
4 user4 46

after

count | username | bid-amount
1 user0 55
2 user2 49
3 user2 48
4 user4 47 <--- last inserted
5 user4 46


bid amount going with order by bid_amount DESC ,so amount 47 was last inserted ,the amount 46 count was 4 ,and after the insert of amount 47 ,amount 46 go down to 5 count!!

im displaying to the user in what place is bid amount are!! ,and this is what im trying to get.

thanks for your replay :)

i hope you got me better now.
prabirchoudhury's Avatar
Familiar Sight
 
Join Date: May 2009
Location: Wellington, New Zealand
Posts: 152
#5: Jun 13 '09

re: last inserted row number.


Option A
1. i dont think it a good idea to update table data for every insert.
2. it is easier to get the details for particular bit (would be a bit)
Expand|Select|Wrap|Line Numbers
  1.  
  2. table bit
  3. ---------------------
  4. | bit_id| details
  5. ---------------------
  6. |1001 | car
  7. |1002 | gold
  8. --------------------
  9.  
  10. add a fild bit_id in your described table
  11.  
  12.  
  13.  
3. when you wanna display current biting status for a particular bit_id, get the query of
Expand|Select|Wrap|Line Numbers
  1. ORDER BY `bid-amount` DESC 
  2.  
option B
4. otherwise you have to check the data in bid-amount for a bit_id(that you doent have) and rearrange with updates.

5. i would prefer option A

:))
Markus's Avatar
Moderator
 
Join Date: Jun 2007
Location: York, England, with wolves.
Posts: 4,936
#6: Jun 13 '09

re: last inserted row number.


Again, you haven't explained how you're storing the data.

But, the last inserted is going to be (providing there was only one insert) array_length - 2.
Familiar Sight
 
Join Date: Oct 2008
Posts: 141
#7: Jun 13 '09

re: last inserted row number.


the count is running and doing the count dynamic ,no change in the database

only the id is changing in the database the id is on auto increment.
when im displaying the results it is going with bid_amount desc ,so the highst bid_amount is allways at the top!!

now if second user place a bid and it is not in the top(the bid amount is less then the first user, then the count is just displaying in what line he his!!

all i need is to show the user in what position he his ,like:

echo hello $username ."you are in $position position";

i hope this time you get what i need!!

thanks
Member
 
Join Date: Jun 2008
Posts: 97
#8: Jun 13 '09

re: last inserted row number.


i think your problem will be solved using simply

Expand|Select|Wrap|Line Numbers
  1. ORDER BY `bid_amount` DESC 
  2.  
prabirchoudhury's Avatar
Familiar Sight
 
Join Date: May 2009
Location: Wellington, New Zealand
Posts: 152
#9: Jun 13 '09

re: last inserted row number.


1. insert a new bid . (without count) [but no duplicate bit-amount.]

Expand|Select|Wrap|Line Numbers
  1. 2. select latest details
  2.  
  3. $sql= "select * from table ORDER BY bit-amount DESC";
  4. $csr = mysql_query($sql);
  5. $count = 1;
  6. while ($row=mysql_fetch_array($csr)){
  7.  
  8. update count with $count where id = '".$row[id]."' ";
  9.  
  10. $count++
  11. }
  12.  
  13.  
  14.  
then it would update the current bit status


:)
Familiar Sight
 
Join Date: Oct 2008
Posts: 141
#10: Jun 13 '09

re: last inserted row number.


i got a table returning all the data from the database ,one row showing the bid_amount!!


all i want is to tell the user in what position he his!

the table have a dynamic count with count++ !!


all i want is to tell the user the row number he is!! thats it!!

i cannot get that!
thank you!
n8kindt's Avatar
Familiar Sight
 
Join Date: Mar 2008
Location: Southern California
Posts: 221
#11: Jun 13 '09

re: last inserted row number.


Quote:
before

count | username | bid-amount
1 user0 55
2 user2 49
3 user2 48
4 user4 46

after

count | username | bid-amount
1 user0 55
2 user2 49
3 user2 48
4 user4 47 <--- last inserted
5 user4 46
the title of this post is misleading. you're not looking for the last inserted id but rather the current rank of a user who placed a bid.

what i would do is make a query that sorts the users by bid amount, retrieve the results using mysql_fetch_assoc and then loop thru the results.

something like
Expand|Select|Wrap|Line Numbers
  1. $target_username = "user4"; //username we need to match
  2. $username_matched = false; //we have not matched a username yet
  3. $q = "SELECT * FROM `bid-history` ORDER BY `bid-amount` ASC";
  4. $r = mysql_query($r);
  5. $count = 1; //set current
  6. while($w = mysql_fetch_assoc($r) && !$username_matched){
  7.   if($w['username']==$target_username ){
  8.    $username_matched=true;
  9.    $user_position = $count;
  10.    $user_bidamount = $w['bid-amount'];
  11.   }
  12.  $count++;
  13.  
  14.  
  15. }
  16. echo "User \"$target_username\" is number $user_position in line with a bid amount of $$user_bidamount";
  17.    //output: User "user4" is number 4 in line with a bid amount of $47
  18.  
Familiar Sight
 
Join Date: Oct 2008
Posts: 141
#12: Jun 13 '09

re: last inserted row number.


where does it get the last bid_amount from?

there is bids from the same user again and again!!

your code is exactly what i need but need some fixes to fit!!

thank you!!!
n8kindt's Avatar
Familiar Sight
 
Join Date: Mar 2008
Location: Southern California
Posts: 221
#13: Jun 13 '09

re: last inserted row number.


if you look, i set it up so that the while loop only runs when $username_matched equals false. when the user is matched the variable is set to true thus killing the loop
Familiar Sight
 
Join Date: Oct 2008
Posts: 141
#14: Jun 13 '09

re: last inserted row number.


Expand|Select|Wrap|Line Numbers
  1. $q = "SELECT * FROM `bidding_details` where bid_id=$bid_id ORDER BY id desc limit 1";
i want to limit the SELECT to find only the id of the last inserted id!

now i want to start count++ and see if the $username match the highest ID,

if there is a match between the $username and the Highst ID then display
the $count!!

thanx!
Familiar Sight
 
Join Date: Oct 2008
Posts: 141
#15: Jun 14 '09

re: last inserted row number.


this code is working for me ,but i want it to display only the last inserted row ,position ,now i can display all the rows and the position ,but i need to show the user only his last bid position ,please help me figure this one out!!

Expand|Select|Wrap|Line Numbers
  1. $q = "(SELECT * FROM `bidding_details` where bid_id=$bid_id and sortbid = '1' order by bid_price desc) union all (SELECT * FROM `bidding_details` where bid_id=$bid_id and sortbid = '0' order by bid_price desc) order by sortbid desc, bid_price desc";
  2. $result=mysql_query($q) or die('Failed selecting applications: ' . mysql_error());
  3. $num1=mysql_num_rows($result);
  4. $rown = 0; 
  5. while($row = mysql_fetch_assoc($result)) 
  6.     $rown++; 
  7.     echo $row['bid_price']. ' is pos: ' . $rown . '.<br>'; 
  8.  
  9. }
thank you
n8kindt's Avatar
Familiar Sight
 
Join Date: Mar 2008
Location: Southern California
Posts: 221
#16: Jun 14 '09

re: last inserted row number.


Quote:

Originally Posted by canabatz View Post

this code is working for me ,but i want it to display only the last inserted row ,position ,now i can display all the rows and the position ,but i need to show the user only his last bid position ,please help me figure this one out!!

Expand|Select|Wrap|Line Numbers
  1. $q = "(SELECT * FROM `bidding_details` where bid_id=$bid_id and sortbid = '1' order by bid_price desc) union all (SELECT * FROM `bidding_details` where bid_id=$bid_id and sortbid = '0' order by bid_price desc) order by sortbid desc, bid_price desc";
  2. $result=mysql_query($q) or die('Failed selecting applications: ' . mysql_error());
  3. $num1=mysql_num_rows($result);
  4. $rown = 0; 
  5. while($row = mysql_fetch_assoc($result)) 
  6.     $rown++; 
  7.     echo $row['bid_price']. ' is pos: ' . $rown . '.<br>'; 
  8.  
  9. }
thank you

ok, you're going to need to add a column to your table and designate it as a timestamp. when you create a new column in phpmyadmin it will give you the option of designating it as a timestamp and the mysql server will generate the current timestamp for you when a new row is inserted-just like the auto increment method you use for your primary key. you can also manually generate it in php using time() (or mktime() if not using the current date). once that is set up correctly, just sort your results by the timestamp field.
Familiar Sight
 
Join Date: Oct 2008
Posts: 141
#17: Jun 14 '09

re: last inserted row number.


this code i posted is giving me this result

6.00 is pos: 1.
5.00 is pos: 2.
4.05 is pos: 3.
4.04 is pos: 4.
4.03 is pos: 5.
4.02 is pos: 6.

4.05 is the last bid inserted ,the id of this bid is the highest bid !

what im try to solve is to display to the user this:

your bid price of 4.05 is at position 3.

i dont want to display all the results from all the users! only the last inserted!!

thanx
Familiar Sight
 
Join Date: Oct 2008
Posts: 141
#18: Jun 14 '09

re: last inserted row number.


please ,please ,someone?

this is the only thing i got left to fix to open my site!!
Dormilich's Avatar
Moderator
 
Join Date: Aug 2008
Location: Leipzig, Germany
Posts: 3,635
#19: Jun 14 '09

re: last inserted row number.


what about assigning a user id to the bid?
Familiar Sight
 
Join Date: Oct 2008
Posts: 141
#20: Jun 15 '09

re: last inserted row number.


there is ,reg_id ,username ,bid_price , bid_date ,an email .

i want the code to do like that:

select from the table for all the result.

start COUNT from the top ORDER by BID_PRICE DESC.

find the SESSION_USERNAME and look if the highest ID of the last bid_price entered by him

if the Highest ID == session_username stop count!

echo $count $username!

thats it!!
Familiar Sight
 
Join Date: Oct 2008
Posts: 141
#21: Jun 18 '09

re: last inserted row number.


i searched all the web and none have any idea how to solve this!!

can some one find a way to get the effected row numbe from a select query?

take it as a challenge ,maybe it will help others !!

please help ,than you!!
Reply