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

Question

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 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

Answer 1

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));

Answer 2

Example Code to check the v_prod_id Matches

<?php
//connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
if(isset($_GET['id']) && ($_GET['vid'])){
//sanitize the id and vid i didnt sanitized
$id = $_GET['id'];
$vid = $_GET['vid'];
//Coded by Ajmal PraveeN
                                        $sqlq = $conn->prepare("SELECT id, v_prod_id FROM __newtoys_variants WHERE id= :id");
                                        $sqlq->bindValue(':id', $id);
                                        $sqlq->execute();
                                        $row = $sqlq->fetch(PDO::FETCH_ASSOC);
//if you want to check the id whether it matches? remove the comment line and check the id matches
if(/*($id !== $row['id']) && */($vid !== $row['v_prod_id'])){
echo 'v prod id error';
}
?>