1. Home
2. Questions
3. Tags
5. Users
6. Jobs
7. Companies
8. Unanswered
Teams

Ask questions, find answers and collaborate at work with Stack Overflow for Teams.
Try Teams for free Explore Teams
Teams
Ask questions, find answers and collaborate at work with Stack Overflow for Teams. Explore Teams

Return to Question

More searchable title, added a couple more tags

Link

edited Nov 29, 2018 at 12:02

4.8k
4
18
48

How to SET variables ina user-defined MySQL variable and increment it to produce ranking values with JDatabaseQuery?

mysql jdatabase sql variable

Bumped by Community user

occurred Nov 27, 2018 at 9:02

Bumped by Community user

occurred Oct 28, 2018 at 9:00

Bumped by Community user

occurred Sep 28, 2018 at 8:01

Bumped by Community user

occurred Aug 29, 2018 at 8:01

Bumped by Community user

occurred Jul 30, 2018 at 7:01

Bumped by Community user

occurred Jun 30, 2018 at 6:34

deleted 6 characters in body

Source Link

edited Feb 8, 2018 at 17:03

109
7

I'm trying to write this query:

SET @rownumber = 0;
UPDATE #__hikers SET rank = (@rownumber:=@rownumber+1)
ORDER BY score DESC;

like JDatabaseQuery, but still got an error. Please help

UPDATE: I tried this code 1:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber = 0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('#__rank''rank') . ' = ' .$db->quote(@rownumber:=@rownumber+1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

code 2:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber).' = '.$db->quote (0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('#__rank''rank') .' = '.$db->quote(@rownumber).' = '. $db->quote(@rownumber).' + '. $db->quote(1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

I'm trying to write this query:

SET @rownumber = 0;
UPDATE #__hikers SET rank = (@rownumber:=@rownumber+1)
ORDER BY score DESC;

like JDatabaseQuery, but still got an error. Please help

UPDATE: I tried this code 1:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber = 0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('#__rank') . ' = ' .$db->quote(@rownumber:=@rownumber+1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

code 2:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber).' = '.$db->quote (0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('#__rank') .' = '.$db->quote(@rownumber).' = '. $db->quote(@rownumber).' + '. $db->quote(1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

I'm trying to write this query:

SET @rownumber = 0;
UPDATE #__hikers SET rank = (@rownumber:=@rownumber+1)
ORDER BY score DESC;

like JDatabaseQuery, but still got an error. Please help

UPDATE: I tried this code 1:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber = 0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('rank') . ' = ' .$db->quote(@rownumber:=@rownumber+1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

code 2:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber).' = '.$db->quote (0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('rank') .' = '.$db->quote(@rownumber).' = '. $db->quote(@rownumber).' + '. $db->quote(1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

deleted 1 character in body

Source Link

edited Feb 5, 2018 at 7:35

109
7

I'm trying to write this querryquery:

SET @rownumber = 0;
UPDATE #__hikers SET rank = (@rownumber:=@rownumber+1)
ORDER BY score DESC;

like JDatabaseQuery, but still got an error. Please help

UPDATE: I tried this code 1:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber = 0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('#__rank') . ' = ' .$db->quote(@rownumber:=@rownumber+1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

code 2:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber).' = '.$db->quote (0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('#__rank') .' = '.$db->quote(@rownumber).' = '. $db->quote(@rownumber).' + '. $db->quote(1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

I'm trying to write this querry:

SET @rownumber = 0;
UPDATE #__hikers SET rank = (@rownumber:=@rownumber+1)
ORDER BY score DESC;

like JDatabaseQuery, but still got an error. Please help

UPDATE: I tried this code 1:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber = 0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('#__rank') . ' = ' .$db->quote(@rownumber:=@rownumber+1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

code 2:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber).' = '.$db->quote (0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('#__rank') .' = '.$db->quote(@rownumber).' = '. $db->quote(@rownumber).' + '. $db->quote(1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

I'm trying to write this query:

SET @rownumber = 0;
UPDATE #__hikers SET rank = (@rownumber:=@rownumber+1)
ORDER BY score DESC;

like JDatabaseQuery, but still got an error. Please help

UPDATE: I tried this code 1:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber = 0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('#__rank') . ' = ' .$db->quote(@rownumber:=@rownumber+1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

code 2:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->set($db->quote(@rownumber).' = '.$db->quote (0));
$query->update($db->quoteName('#__hikers'));
$query->set($db->quoteName('#__rank') .' = '.$db->quote(@rownumber).' = '. $db->quote(@rownumber).' + '. $db->quote(1));
$query->order('score DESC');
$db->setQuery($query);
$result = $db->execute();

added 415 characters in body

Source Link

edited Feb 4, 2018 at 18:56

109
7

Loading

added 383 characters in body

Source Link

edited Feb 4, 2018 at 17:24

109
7

Loading

Source Link

asked Feb 4, 2018 at 13:46

109
7

Loading