i have a query that displays list of certain users
steveable
David
hallaa
moymaker
Haye
// Retrieve bidder who choice t1
$query = "SELECT b.*, a.id, a.team1, u.name, u.nick, s.team_id, s.teams, uu.nick AS nick_tag FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
LEFT JOIN " . $DBPrefix . "users uu ON (uu.id = b.tagged)
LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = b.willwin)
LEFT JOIN " . $DBPrefix . "auctions a ON (a.id = b.auction)
WHERE b.auction = :id and a.team1 = b.willwin and b.tagged != '0' order by b.tagged desc";
$params = array();
$params[] = array(':id', $id, 'int');
$db->query($query, $params);
i want to update their account balance at once
table: users column:balance
how do i go about it, thanks
what is the query that you tried and what is the problem you observe?
If the balance is the same for all, then it would be plausible with a single query.
But if the balance is different, itâs better to run several queries in a loop.
I hope your homebrewed PDO wrapper supports single prepare multiple execute strategy. Either way I donât know the syntax,so here is how it could be done with vanilla PDO
$stmt = $pdo->prepare("UPDATE users SET balance=? WHERE id=?");
$pdo->beginTransaction();
foreach ($users as $user)
{
$stmt->execute([$balance, $user['id']]);
}
$pdo->commit();
the page just reload without any update done
the users have different balance, but the update is the same amount for all so that means it will be adding to the amount the already have
You would do that exactly like @colshrapnel outlined, only that youâd do the math in SQL.
then it could be done in a single query, like
update users set balance=balance+? where id in (?,?,?);
The actual implementation for vanilla PDO you can see in my article, Prepared statements and IN clause
hello am still on the issue
this is what i could come up with but the update still not working only the display user.
the form below
<form name="update" action="" method="post">
<!-- BEGIN bids -->
<tr>
<td>{bids.W_NICK} </td>
<td><div align="center">{bids.BID}</div></td>
<td><div align="center">{bids.WILLWIN}</div></td>
<td><div align="center">{bids.TAGGED}</div></td>
<td align="left">{bids.BALANCE}</td>
<td align="left">
<div align="center">
</div></td>
</tr>
<!-- END bids -->
<div align="right">
<input type="text" name="balance" size="15" maxlength="10" value="{BALANCE}">
<input type="hidden" name="auction" value="{ID}">
<input type="hidden" name="action" value="update">
<input type="hidden" name="csrftoken" value="{_CSRFTOKEN}">
<input type="submit" name="act" class="centre" value="{L_071}">
</div>
</form>
php code
$id = intval($_REQUEST['id']);
// Retrieve users that bids
$query = "SELECT b.*, u.name, u.nick, u.balance, s.team_id, s.teams, uu.nick AS nick_tag FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
LEFT JOIN " . $DBPrefix . "users uu ON (uu.id = b.tagged)
LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = b.willwin)
WHERE b.auction = :user_id order by b.tagged desc";
$params = array();
$params[] = array(':user_id', $id, 'int');
$db->query($query, $params);
if (isset($_POST['action']) && $_POST['action'] == 'update')
{
// load the user data balance
$query = "UPDATE " . $DBPrefix . "users SET
balance = :balance WHERE id = :user_id";
$params = array();
$params[] = array(':balance', $system->input_money($_POST['balance']), 'float');
$params[] = array(':user_id', $id, 'int');
$db->query($query, $params);
header('location: viewbettors.php?PAGE=' . intval($_POST['offset']));
exit;
}
plz what am i doing wrong here thanks and i dont know pdo like that, so yeahh.
for lack of knowing your DB class we cannot tell.
yesterday peeps where droping pdo format couldnt understand what to do with it
Thatâs because in PHP there are only two database extensions that can connect to MySQL: PDO and mysqli. Anything else that connects to MySQL uses one of the two internally.
Unless it is set somewhere outside the code you showed, I canât see where this
$id = intval($_REQUEST['id']);
is set in your form. I see this line which kind of lines up with what you do with it later:
<input type="hidden" name="auction" value="{ID}">
I also donât understand the presence of the first query, as you donât seem to do anything with the results. You run the query to select an auction, but then you immediately run a different query on the users table to update the balance.
yeah $id = intval($_REQUEST[âidâ]); removed thats for single users edit
<input type="hidden" name="auction" value="{ID}">
now
<input type="hidden" name="offset" value="{OFFSET}">(used for the return page after submit)
note: running single user edit is working below is the code i use
$id = intval($_REQUEST['id']);
// load the user data
$query = "SELECT * FROM " . $DBPrefix . "users WHERE id = :user_id";
$params = array();
$params[] = array(':user_id', $id, 'int');
$db->query($query, $params);
$user_data = $db->result();
if (isset($_POST['action']) && $_POST['action'] == 'update')
{
if (isset($_POST['balance']))
{
$balance_clean = str_replace('-', '', $_POST['balance']);
$query = "UPDATE " . $DBPrefix . "users SET
balance = :balance WHERE id = :user_id";
$params = array();
$params[] = array(':balance', $system->input_money($_POST['balance']), 'float');
$params[] = array(':user_id', $id, 'int');
$db->query($query, $params);
header('location: listusers.php?PAGE=' . intval($_POST['offset']));
exit;
}
}
but when i try it for multiple users i dont get result just a page loading but no update
hi am trying to combine two tables for update to update specific users have been able to update users but its updating all need to pinpoint to make it specific so how do i gp about it
// Retrieve users that bids
$query = "SELECT b.*, u.name, u.nick, u.balance, s.team_id, s.teams, uu.nick AS nick_tag FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
LEFT JOIN " . $DBPrefix . "users uu ON (uu.id = b.tagged)
LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = b.willwin)
WHERE b.auction = :user_id order by b.tagged desc";
$params = array();
$params[] = array(':user_id', $id, 'int');
$db->query($query, $params);
if (isset($_POST['action']) && $_POST['action'] == 'update')
{
$query = "UPDATE u.*, b.* " . $DBPrefix . "users u SET u.balance = :balance WHERE b.auction = :user_id";
$params = array();
$params[] = array(':balance', $system->input_money($_POST['balance']), 'float');
$params[] = array(':user_id', $id, 'int');
$db->query($query, $params);
header('location: viewbettors.php?PAGE=' . intval($_POST['offset']));
exit;
}
hope u get the idea am trying to do in the update part, thanks
hi i need to update only certain row not all eg
âusersâ with id 90 in the âbidsâ table column auction
how do i introduce a where in the statement so only those with id 90 gets updated
UPDATE u.*, b.* " . $DBPrefix . "users u SET u.balance = :balance WHERE b.auction = :user_id
I am no SQL expert, but does that query work if you run it in phpmyadmin, manually, inserting the values that your code will send it? I am wondering specifically about the âu., b.â at the beginning of the query. As far as I can see, youâre trying to update a column in âusersâ based on the auction id in âbâ being equal to 90, but you donât define which table is âbâ, and you donât define the relationship between b and u as far as I can see.
First thing - forget about the PHP code and get the query working. Once the query works, you can add the code to provide the balance and user id or auction id. Unless the query works, of course, in which case ignore this post.
I donât see the relationship between âb.auctionâ and âu.balanceâ.
hi it was an example an idea am trying to make below explains more
hi am having an update issue problem, i have two tables
table 1
users
column id balance
3 675
6 400
8 900
2 450
5 200
table 2
bids
column auction bidder
90 3
90 6
90 8
95 2
95 5
i have this code (below) that display users with selected auction works fine
// Retrieve users that bids
$query = "SELECT b.*, u.name, u.nick, u.balance, s.team_id, s.teams, uu.nick AS nick_tag FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
LEFT JOIN " . $DBPrefix . "users uu ON (uu.id = b.tagged)
LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = b.willwin)
WHERE b.auction = :user_id order by b.tagged desc";
$params = array();
$params[] = array(':user_id', $id, 'int');
$db->query($query, $params);
And i have this code (below) that update users balance but it update all users cause it not connected to bids, trying to update column balance in âusersâ based on the auction id in âbidsâ being equal to auction id
if (isset($_POST['action']) && $_POST['action'] == 'update')
{
$query = "UPDATE " . $DBPrefix . "users SET balance = :balance";
$params = array();
$params[] = array(':balance', $system->input_money($_POST['balance']), 'float');
$db->query($query, $params);
header('location: listusers.php?PAGE=' . intval($_POST['offset']));
exit;
}
hope u understand
So, are you saying that in your example tables above, for example in auction id 90, you would want to update the user balances for anyone who is in the bidder column where the auction id = 90? In this case, you would want to alter the balance of users 3, 6 and 8?
If thatâs what you want, you could use a double query something like:
update table1 set balance = :balance where id in (select bidder from table2 where auction=:auction)
Obviously you need to see how to deal with the balance, you probably would want to alter the balance by a fixed number for each user rather than set it to a specific number. So something like
update table1 set balance = balance - :bidamount where id in (select bidder from table2 where auction=:auction)
I believe you can do it with a JOIN as well.
yeah just thats what i want but i intergrated the codes didint work
// Retrieve users that bids
$query = "SELECT b.*, u.name, u.nick, u.balance, s.team_id, s.teams, uu.nick AS nick_tag FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
LEFT JOIN " . $DBPrefix . "users uu ON (uu.id = b.tagged)
LEFT JOIN " . $DBPrefix . "sports s ON (s.team_id = b.willwin)
WHERE b.auction = :user_id order by b.tagged desc";
$params = array();
$params[] = array(':user_id', $id, 'int');
$db->query($query, $params);
if (isset($_POST['action']) && $_POST['action'] == 'update')
{
$query = "UPDATE users" . $DBPrefix . "SET balance = :balance WHERE 'id' IN (SELECT 'bidder' FROM bids WHERE auction=:auction)";
$params = array();
$params[] = array(':balance', $system->input_money($_POST['balance']), 'float');
$params[] = array(':auction', $id, 'int');
$db->query($query, $params);
header('location: listusers.php?PAGE=' . intval($_POST['offset']));
exit;
}
am writing it correctly right