limit problem when using update. | Familiar Sight | | Join Date: Oct 2008
Posts: 141
| |
im trying to update my results and i get error:
this is the code: -
this is the select :
-
SELECT * FROM `bidding_details` where `bid_id`='$bid_id' and `sortbid` = '1' order by bid_price asc limit 0, 50
-
-
this is the update:"
-
update bidding_details set sortbid = '0', rank = '0' where `bid_id`='$bid_id' and sortbid = '1' and `bid_price`='$remove_bid' LIMIT 0, 50
-
if i do LIMIT 50 ,it's working ,when im puting 0, 50 ,i get error!
i need update the results from the first 50 rows.
what can be the error
|  | Moderator | | Join Date: Jun 2007 Location: York, England, with wolves.
Posts: 4,936
| | | re: limit problem when using update.
What is the error text? In future, please remember to give us all of the information - it helps us help you.
Good day,
Mark.
| | Familiar Sight | | Join Date: Oct 2008
Posts: 141
| | | re: limit problem when using update.
sorry :)
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 50' at line 1
|  | Member | | Join Date: Mar 2008 Location: here
Posts: 107
| | | re: limit problem when using update.
http://dev.mysql.com/doc/refman/5.0/en/update.html
Single-table syntax: -
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
-
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
-
[WHERE where_condition]
-
[ORDER BY ...]
-
[LIMIT row_count]
-
You can use LIMIT row_count to restrict the scope of the UPDATE. A LIMIT clause is a rows-matched restriction. The statement stops as soon as it has found row_count rows that satisfy the WHERE clause, whether or not they actually were changed.
If I understood you correctly (and I am not sure I have) Quote:
Originally Posted by canabatz  i need update the results from the first 50 rows. Than LIMIT 50 will do exactly what you need.
| | Familiar Sight | | Join Date: Oct 2008
Posts: 141
| | | re: limit problem when using update.
can i do something like this: - $sql_update_bids11111="select * form `bidding_details` where (update bidding_details set sortbid = '0', rank = '0' where `bid_id`='$bid_id' and sortbid = '1') limit 0, 50 ";
thanx
| | Moderator | | Join Date: Jun 2007 Location: York, England, with wolves.
Posts: 4,936
| | | re: limit problem when using update.
Where clause syntax looks like: WHERE x = 1 AND x = 2 AND x = 3 ...
You should be reading the MySQL manual for these questions as they're well covered.
| | Familiar Sight | | Join Date: Oct 2008
Posts: 141
| | | re: limit problem when using update.
i try and try but cant figure this out!!
please help!
this is the code i have: - $query909 = mysql_query("SELECT * 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['username']){
-
$cubid++;
-
$bid_position[$cubid] = $line['bid_price'];
-
} //end of if
-
else {
-
$last = $line['username'];
-
$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'";
-
mysql_query($sql_update_bids11111)or die(mysql_error());
-
-
$remove_bid_count++;
-
}
-
}
the problem is if i got 70 rows ,the code starting from the last row and go up 50 rows and not effecting the first 20 rows ,i want the code to effect only the 50 rows ,can some one direct me to the right way!!
thanx
| | Member | | Join Date: Mar 2008 Location: here
Posts: 107
| | | re: limit problem when using update.
Are you trying to update only selected rows?
| | Familiar Sight | | Join Date: Oct 2008
Posts: 141
| | | re: limit problem when using update.
im updating only one record from the total rows ,but i need the update to look only in the first 50 rows ,not to start from the last row and go up 50!
thanx
| | Moderator | | Join Date: Jun 2007 Location: York, England, with wolves.
Posts: 4,936
| | | re: limit problem when using update.
I'm afraid I cannot understand your problem. Please try to explain better.
| | Member | | Join Date: Mar 2008 Location: here
Posts: 107
| | | re: limit problem when using update.
Quote:
Originally Posted by Markus I'm afraid I cannot understand your problem. Please try to explain better. Same here. Reading it over and over again but don't understand.
| | Familiar Sight | | Join Date: Oct 2008
Posts: 141
| | | re: limit problem when using update.
ok ,i give example!
i got 80 rows
i want to select only the first 50 rows ,i want my update code to look only
at the first 50 rows ,if it finds the result it looks for ,then he will update that row!
bid_price is going from high price to low price ,my select query is bid_price ASC
and i put it in LIMIT 50 ,but it is not starting from row 50 ,it's starting from row 80, row 80 is the lower price!
what i need is the query to start from the lower price in row 50!!
i hope im better in explaining now!
my english is not the best ,sorry!! :)
thanx
| | Member | | Join Date: Mar 2008 Location: here
Posts: 107
| | | re: limit problem when using update.
Quote:
Originally Posted by canabatz ok ,i give example!
i got 80 rows
i want to select only the first 50 rows ,i want my update code to look only
at the first 50 rows ,if it finds the result it looks for ,then he will update that row!
So you are updating only within selected rows. You can do that by storing selected rows Id's and than updating with where clause of your SQL command like this: -
WHERE id IN ($id[0], $id[1], .....)
-
| | Familiar Sight | | Join Date: Oct 2008
Posts: 141
| | | re: limit problem when using update.
can you please show example on this row how i do that: - SELECT * FROM `bidding_details` where `bid_id`='$bid_id' and `sortbid` = '1' order by bid_price asc limit 50
im kind of new to php!
thanx
| | Familiar Sight | | Join Date: Oct 2008
Posts: 141
| | | re: limit problem when using update.
some body?
please ,i didnt understand the last post ,how to do that?
thanx
| | Member | | Join Date: Mar 2008 Location: here
Posts: 107
| | | re: limit problem when using update.
I'll try.
You have in your line 5: -
-
-
.....
-
-
while($line = mysql_fetch_assoc($query909)) {
-
....
-
Every time that loop happens $line stores one record from your table. $line is an assoc array and I will assume that records id name is Id (this could be different).
So every iteration you select one record from the table and its id is held in $line['Id']. All you have to do is to store it every time and keep it for later. There is few ways you can do that but I would store it within array like this: -
-
-
-
$ids = array();
-
while($line = mysql_fetch_assoc($query909)) {
-
-
$ids[] = $line['Id'];
-
-
......
-
}
-
Once the looping is over $ids holds all id's you need to check. To use them in your WHERE clause you can implode array like this: -
$ids_str = implode(",", $ids);
-
finally your sql update would look something like this. -
UPDATE ........whatever you need to update ........ WHERE Id IN ($ids_str)
-
I hope this helps. Good luck.
| | Familiar Sight | | Join Date: Oct 2008
Posts: 141
| | | re: limit problem when using update.
thanx alot Zorgi!!!!
i will try that ,and come back with the solution!!
thanx!!!!
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,272 network members.
|
