phpmysqlsqljoomlaselect-query

How to construct a Joomla! query for matching two field values in a table?

i have a table #__newtoys_variants having many fields of which id and v_prod_id are there.

Now although id is unique - the v_prod_id is product id The url displays product information and price

example.com/index.php?option=com_newtoys&id=2&vid=7

here id value is extracted from id and vid is v_prod_id as extracted from db table row against the corresponding

Here is a brief of table for instance

id v_prod_id v_price
1     7       200
2     7       220
3     1       250
4     1       270
5     2       300
6     10      350
7     9       220
8     7       195

Now i intend that 404 error / 500 error / page does not exist should be displayed in front end - if the id and v_prod_id is not matched in front end url

In case a user changes url to say

example.com/index.php?option=com_newtoys&id=2&vid=1

then want 404 error / 500 error / page does not exist to be displayed in front end Here is Table Database enter image description here Can any one help on it to achieve same

Here is a brief function - unsure what exactly should be in sql query or function so that id & v_prod_id should be matched as in array and in case result is zero then error message can be displayed

function loadProduct($id ,$vid){
    $mainframe =JFactory::getApplication();
    $option = JRequest::getCmd('option');
    $db =JFactory::getDBO();
    global $Itemid; 
    $sql = "";

    $db->setQuery($sql);

if ($rows = $db->loadObjectlist()) {
return $rows[0];

} else {
  if ($db->getErrorNum()) {
    JError::raiseError(500, "Something went horribly wrong, the query returned the error ". $db->getErrorMsg());

  } else {
 JError::raiseError(404, "404, Page does not Exists ". $db->getErrorMsg());
  }
}       
    }

can any one help and suggest. Bounty added

Solution

  • Try this:

    function loadProduct($id, $vid){
  $mainframe = JFactory::getApplication();
  $option = JRequest::getCmd('option');
  $db = JFactory::getDBO();
  $query = $db->getQuery(true);
  $query->select('id, v_prod_id');
  $query->from($db->quoteName('#__newtoys_variants'));
  $query->where($db->quoteName('id')." = ".$db->quote($id), 'AND');
  $query->where($db->quoteName('v_prod_id')." = ".$db->quote($vid));
  $db->setQuery($sql);

  if ($rows = $db->loadObjectlist()) {
    return $rows[0];
  } else {
    if ($db->getErrorNum()) {
      JError::raiseError(500,
       "Something went horribly wrong, the query returned the error ". $db->getErrorMsg());
    } else {
      JError::raiseError(404,
       "404, Page does not Exists ". $db->getErrorMsg());
    }
  }
}

    See here for further details:

    https://docs.joomla.org/Selecting_data_using_JDatabase

    UPDATE

    You asked in the comments for a Joomla! translation of the following query:

    Select *, (select prod_name from #__newtoy_products where id=v.id) as prod_name
from #__newtoys_variants AS v Where v.state='1' and v.id = '".$v_prod_id."'

    ...which is basically the same as this:

    SELECT v.*, p.prod_name
FROM #__newtoys_variants AS v
LEFT JOIN #__newtoy_products AS p
ON p.id = v.id
WHERE v.state='1' and v.id = '".$v_prod_id"'

    ...which should map to something like this in Joomla!:

    $query->select(array('v.*', 'p.prod_name'))
      ->from($db->quoteName('#__newtoys_variants', 'v'))
      ->join('LEFT', $db->quoteName('#__newtoy_products', 'p'))
      . ' ON (' . $db->quoteName('p.id') . ' = ' . $db->quoteName('v.id') . ')')
      ->where($db->quoteName('v.state')." = ".$db->quote(1), 'AND')
      ->where($db->quoteName('v.id')." = ".$db->quote($v_prod_id));