Updating a row

skyhighweb · May 9, 2017, 11:42am

$query = "SELECT b.*, u.nick FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
WHERE b.bidder NOT IN ('b.tagged') and b.tagged IN ('b.bidder') and b.auction = :auc_id ";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$db->query($query, $params);				
// Also update bids table
				$query = "INSERT INTO " . $DBPrefix . "bids VALUES (NULL, :auc_id, :bidder_id, :bid, :time, :qty, :willwin, :tagged, :balance)";
				$params = array();
				$params[] = array(':bid', $bid, 'float');
				$params[] = array(':auc_id', $id, 'int');
				$params[] = array(':bidder_id', $bidder_id, 'int');
				$params[] = array(':time', $NOW, 'int');
				$params[] = array(':qty', $qty, 'int');
				$params[] = array(':willwin', $willwin, 'str');
				$params[] = array(':tagged', $tagged, 'str');
				$params[] = array(':balance', $balance, 'str');
				$db->query($query, $params);


$i = 0;
while ($row = $db->fetch())
{
	$template->assign_block_vars('tag_bidder', array(
			'ID' => $row['bidder'],
			'NAME' => $row['nick'],
			'WILLWIN' => $row['willwin'],
			'TAGGED' => $row['tagged']
			));
	$i++;
}

sorry mod for the multiple post

droopsnoot · May 9, 2017, 12:02pm

OK, so straight after you execute the INSERT query, you need an UPDATE query to set the ‘tagged’ column for the other user.

skyhighweb · May 9, 2017, 12:06pm

yeah that’s where d problem is its not even updating sometimes i get zeros btw bidder n tagged when i tweak the code thats y i gave posted only d select n insert cuz i have no luck on d updating part

droopsnoot · May 9, 2017, 12:11pm

Well, I guess the query would be something like

update bids set tagged = bidder_id where auction = auc_id and bidder = tagged

That presumes that within a single auction (auc_id) the bidder will be unique.

skyhighweb · May 9, 2017, 12:17pm

yeah they are both unique id ,i just tried the code it didnt work (sadface)

skyhighweb · May 9, 2017, 12:58pm

i tried this bids set tagged = (bidder)

the data looks like this

bidder…tagged
david … david
mike … david

instead of

bidder…tagged
david … mike
mike … david

not yet good

what u think is missing

skyhighweb · May 10, 2017, 9:08am

morning all, still on the issue

droopsnoot · May 10, 2017, 10:07am

Show us the current code.

skyhighweb · May 10, 2017, 12:06pm

am still using the above code

skyhighweb
1d1

$query = "SELECT b.*, u.nick FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
WHERE b.bidder NOT IN ('b.tagged') and b.tagged IN ('b.bidder') and b.auction = :auc_id ";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$db->query($query, $params);				
// Also update bids table
				$query = "INSERT INTO " . $DBPrefix . "bids VALUES (NULL, :auc_id, :bidder_id, :bid, :time, :qty, :willwin, :tagged, :balance)";
				$params = array();
				$params[] = array(':bid', $bid, 'float');
				$params[] = array(':auc_id', $id, 'int');
				$params[] = array(':bidder_id', $bidder_id, 'int');
				$params[] = array(':time', $NOW, 'int');
				$params[] = array(':qty', $qty, 'int');
				$params[] = array(':willwin', $willwin, 'str');
				$params[] = array(':tagged', $tagged, 'str');
				$params[] = array(':balance', $balance, 'str');
				$db->query($query, $params);


$i = 0;
while ($row = $db->fetch())
{
	$template->assign_block_vars('tag_bidder', array(
			'ID' => $row['bidder'],
			'NAME' => $row['nick'],
			'WILLWIN' => $row['willwin'],
			'TAGGED' => $row['tagged']
			));

droopsnoot · May 10, 2017, 12:15pm

No, I meant show the code that you have tried with the “update” query that you said you’d used back in post #25, that produced the results you showed in post #26.

skyhighweb · May 10, 2017, 12:29pm

oh ok

colshrapnel · May 10, 2017, 12:31pm

Looks like it’s the home-brewed wrapper to blame. Making it to keep the state is a sure way to make your code execution a mess.

The preferred remedy would be to throw the whole wrapper away and to use vanilla PDO instead. Or at least to make it return all rows at once instead of fetching rows one by one, if possible.

skyhighweb · May 10, 2017, 12:43pm

// tagged

$query = "SELECT b.*, u.nick, bt.tagged, bt.auction FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
LEFT JOIN " . $DBPrefix . "bids_tagged bt ON (bt.tagged = u.id)
WHERE b.bidder NOT IN ('b.tagged') and b.tagged IN ('b.bidder') and b.auction = :auc_id ";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$db->query($query, $params);
				
if ($bidder = $tagged)
			{
				
				
				// Also insert bids table
				$query = "INSERT INTO " . $DBPrefix . "bids VALUES (NULL, :auc_id, :bidder_id, :bid, :time, :qty, :willwin, :tagged, :balance)";
				
				$params = array();
				$params[] = array(':bid', $bid, 'float');
				$params[] = array(':auc_id', $id, 'int');
				$params[] = array(':bidder_id', $bidder_id, 'int');
				$params[] = array(':time', $NOW, 'int');
				$params[] = array(':qty', $qty, 'int');
				$params[] = array(':willwin', $willwin, 'str');
				$params[] = array(':tagged', $tagged, 'str');
				$params[] = array(':balance', $balance, 'str');
				$db->query($query, $params);
				
					// update bids
		$query = "UPDATE " . $DBPrefix . "bids SET bidder = tagged";
				$params[] = array(':auc_id', $id, 'int');
				$db->direct_query($query);	
			}				
			
$i = 0;
while ($row = $db->fetch())
{
	$template->assign_block_vars('tag_bidder', array(
			'ID' => $row['bidder'],
			'NAME' => $row['nick'],
			'WILLWIN' => $row['willwin'],
			'TAGGED' => $row['tagged']
			));
	$i++;
}

i later realise it only making a copy of a row and insert it on the next thats bidder copies it self and place on the row next to it which is tagged.

skyhighweb · May 10, 2017, 12:44pm

hi thanks for ur reply but am totally lost with what u just wrote, like missed my way.

skyhighweb · May 10, 2017, 3:41pm

updated code with update bids table

// tagged

$query = "SELECT b.*, u.nick FROM " . $DBPrefix . "bids b
LEFT JOIN " . $DBPrefix . "users u ON (u.id = b.bidder)
LEFT JOIN " . $DBPrefix . "bids_tagged bt ON (bt.id = b.bidder)
WHERE b.bidder NOT IN ('b.tagged') and b.tagged IN ('bt.bidder') and b.auction = :auc_id ";
$params = array();
$params[] = array(':auc_id', $id, 'int');
$db->query($query, $params);
				
if ($bidder = $tagged)
			{
				
				
				// Also insert bids_tagged table
				$query = "INSERT INTO " . $DBPrefix . "bids_tagged VALUES (NULL, :auc_id, :bidder_id, :bid, :time, :qty, :willwin, :tagged, :balance)";
				
				$params = array();
				$params[] = array(':bid', $bid, 'float');
				$params[] = array(':auc_id', $id, 'int');
				$params[] = array(':bidder_id', $bidder_id, 'int');
				$params[] = array(':time', $NOW, 'int');
				$params[] = array(':qty', $qty, 'int');
				$params[] = array(':willwin', $willwin, 'str');
				$params[] = array(':tagged', $tagged, 'int');
				$params[] = array(':balance', $balance, 'int');
				$db->query($query, $params);
				
					// update bids
		$query = "UPDATE " . $DBPrefix . "bids SET bidder = (tagged)";
				$params[] = array(':auc_id', $id, 'int');
				$db->direct_query($query);	
			}				
			
$i = 0;
while ($row = $db->fetch())
{
	$template->assign_block_vars('tag_bidder', array(
			'ID' => $row['bidder'],
			'NAME' => $row['nick'],
			'WILLWIN' => $row['willwin'],
			'TAGGED' => $row['tagged']
			));
	$i++;
}

TechnoBear · May 10, 2017, 3:43pm

Off Topic

@skyhighweb: when you post code on the forums, you need to format it so it will display correctly.

You can highlight your code, then use the </> button in the editor window, or you can place three backticks ``` (top left key on US/UK keyboards) on a line above your code, and three on a line below your code. I find this approach easier, but unfortunately some European and other keyboards don’t have that character.

skyhighweb · May 10, 2017, 3:55pm

sorry about that but am using mobile phone how do i do that with phone

TechnoBear · May 10, 2017, 4:07pm

No need to apologise.

Do you have the backtick character ` on your phone? If so, you can use that. You can also touch the “hamburger” menu in the top right corner of the reply window, which will open the toolbar with all the buttons.

skyhighweb · May 10, 2017, 4:46pm

okay seen

droopsnoot · May 10, 2017, 5:19pm

This bit is just not correct:

$query = "UPDATE " . $DBPrefix . "bids SET bidder = (tagged)";
$params[] = array(':auc_id', $id, 'int');
$db->direct_query($query);

You have an update query, but you haven’t restricted it to update only the appropriate record in the bids table, you need to only do the update where the auction id is the same, and the bidder is the one who you tagged. You haven’t specified any values in there, either.

$query = "UPDATE " . $DBPrefix . "bids SET tagged = :tuser where auction = :auc_id and bidder = :tag";
$params[] = array(':tuser', $bidder_id, 'int');
$params[] = array(':auc_id', $id, 'int');
$params[] = array(':tag' , $tagged, 'int');
$db->query($query, $params);

might do it, but I’m not that familiar with the way you use prepared statements. Check the column names and variable names, I’m reasonably sure that’s correct but not completely.