Return to Question

More searchable title, added a couple more tags

edited Nov 29, 2018 at 12:02

mickmackusa ♦

4.9k
4
20
49

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

edited Feb 8, 2018 at 17:03

109
8

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

edited Feb 5, 2018 at 7:35

109
8

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

edited Feb 4, 2018 at 18:56

109
8

Loading

added 383 characters in body

edited Feb 4, 2018 at 17:24

109
8

Loading

asked Feb 4, 2018 at 13:46

109
8

Loading