Help with update query!

Question

So i am making a script which updates users position depending on what records exist in another table, I have the query working fine to calculate the user position and i am able to calculate and display for each user. However i am having trouble with the update query so i can write the $userposition to the database. I currently have

<?php

if ($countofone >= '1'){
  $userposition= '1'; }
elseif ($countoftwo >= '6'){
   $userposition='2'; }
elseif ($countofthree >= '1'){
    $userposition='3';}
elseif ($countoffour >= '1'){
    $userposition='4';}
elseif ($countoffive >= '1'){
    $userposition='5';}
 elseif ($countofsix >= '1'){
    $userposition='6';}
  elseif ($countofseven >= '1'){
    $userposition='7';}


$db = JFactory::getDbo();
$query = $db->getQuery(true);
// Fields to update.
$fields = array(
    $db->quoteName('a.userposition') . ' = ' . $userposition
);

// Conditions for which records should be updated.
$conditions = array(
    $db->quoteName('a.id') . ' = ' . $db->quoteName('b.user') 
);

$query->update($db->quoteName('zw9ki_users'))->set($fields)->where($conditions);

$db->setQuery($query);
$result = $db->execute();



?>

I need the join to exist between the tables, a=users and b=usersrecords. The column b.user relates to the a.id

Can someone help me with this please? I just need the join added to the query please! Any help much appreciated! <3<3<3

Answer 1

I've made some updates to your database query, defining the table name correctly (using the prefix #__) and also defined the column names correctly.

// Define a default
$userposition = 1;

if ($countofone >= 1)
{
    $userposition = 1;
}
elseif ($countoftwo >= 6)
{
    $userposition = 2;
}
elseif ($countofthree >= 1)
{
    $userposition = 3;
}
elseif ($countoffour >= 1)
{
    $userposition = 4;
}
elseif ($countoffive >= 1)
{
    $userposition = 5;
}
elseif ($countofsix >= 1)
{
    $userposition = 6;
}
elseif ($countofseven >= 1)
{
    $userposition = 7;
}

$db         = JFactory::getDbo();
$fields     = array($db->qn('userposition') . ' = ' . (int)$userposition);
$conditions = array($db->qn('id') . ' = ' . $db->q('user'));

$query = $db->getQuery(true);
$query->update($db->qn('#__users'))->set($fields)->where($conditions);
$db->setQuery($query);

$result = $db->execute();