2

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

4
  • Right now the query is: UPDATE users a SET a.userposition={$userposition} WHERE a.id = b.user If you know the user id, can you just add it to your query instead of trying to join in another table: UPDATE users a SET a.userposition={$userposition} WHERE a.id = {$userid} Dec 15 '17 at 17:30
  • Dont manually define the table prefix (zw9ki). Instead use #__users
    – Lodder
    Dec 15 '17 at 19:12
  • Also, remove the a. and b. as you're not performing any joins.
    – Lodder
    Dec 15 '17 at 19:14
  • Yes i am unsure as to why i thought i needed a join! Thanks for the help!! :D
    – user9460
    Dec 17 '17 at 11:22
0

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();
4
  • Worked a treat! i cannot for the life of me work out why i had thought i needed a join in there! One last thing, i have changed the numbers to descriptive text, i suppose its easier that way. But when i add in my text. if ($countofone >= 1) { $userposition = 'Animal needs collected'; } elseif ($countoftwo >= 6) { $userposition = 'Owner needs contacted'; } elseif ($countofthree >= 1) { $userposition = 'Longer session needed'; } elseif ($countoffour >= 1) { $userposition = 'Cat is hostile'; } And so on.... I have tried to update (int) to (text) but it not work.
    – user9460
    Dec 17 '17 at 10:51
  • @NanRait - Change (int) to (string), or you can just remove (int) completely ;)
    – Lodder
    Dec 17 '17 at 10:58
  • I am getting an error when i remove the int or use anything other than numbers. it works fine with the number value, I may just have to use the numbers and then have corresponding labels. :/
    – user9460
    Dec 18 '17 at 16:25
  • If you use $userposition = 7; which is clearly an integer and you're sure that the rest of them will be an integer too, then you can use (int)$userposition in the database query. If you're using $userposition = 'something here'; which is a string or a mix between this and an integer, then remove the cast (int). If you'd like to post a new question with your updated code, please feel free.
    – Lodder
    Dec 18 '17 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy