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

Guides to do the error. You need check whether the vid=7 value is matched in database(Code sql query to retrieve whether it matches), if not matched you need to show the error.. thats all — Ajmal PraveeN, Sep 12 '17 at 15:43
Yes - i understand that sql query needs to be written to check whether v_prod_id matches with corresponding id field. If not then error function as mentioned above. But am naive in sql queries - so if can assist on same. thanks — Ruchika, Sep 12 '17 at 15:45
Sorry, Ruchika i dont understood. you mean do i wanna write the code for checking the prod_id? — Ajmal PraveeN, Sep 12 '17 at 15:49
i know its not a forum to ask to write code, but yes if can assist to help on same to provide sql query so as to achieve the desired — Ruchika, Sep 12 '17 at 15:55
For me its not a Problem i can re write or code something :) So let me give you a answer in php pdo to query sql to check the vproid matches to the corresponding id or should you wanna show the error? or just ignore? — Ajmal PraveeN, Sep 12 '17 at 16:04

Steve Chambers · Accepted Answer · 2017-09-16T15:06:46.073

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

edited Sep 16 '17 at 15:06

answered Sep 16 '17 at 13:26

Steve Chambers

37,270
24
156
208

Great - i think its almost close, just need some more if to merge above query with this `"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."'"` . How to rewrite query as single merged query. It will help in achieving the desired as what looking for – Ruchika Sep 16 '17 at 14:34
Thanks - its showing now `Parse error: syntax error, unexpected 'v' on this line ->where($db->quoteName('v.state')." = ".$db->quote(1), 'AND')` . Also once this parse error issue resolved can i simply join in both the new query and old query – Ruchika Sep 16 '17 at 15:25
Well I did say "something like this" - was hoping that would be enough to point you in the right direction without a step by step troubleshoot as this would probably be easier for you than me given I don't have Joomla! installed locally... Could maybe ask a separate SO question if the documentation isn't sufficient? The join syntax is described here: https://docs.joomla.org/Selecting_data_using_JDatabase#Selecting_Records_from_Multiple_Tables – Steve Chambers Sep 16 '17 at 15:35
i understand that . You have pointed me in right direction, just a last pointer - why is id coming as unambiguous - `Column 'id' in field list is ambiguous SQL=SELECT id, v_prod_id,v.*,p.prod_name FROM `#__new#__variants`,`#__new#__variants` AS `v` LEFT JOIN `#__new#__products` AS `p` ON (`p`.`id` = `v`.`v_prod_id`) WHERE `id` = '188' AND `v_prod_id` = '12' AND `v`.`state` = '1' AND `v`.`id` = '188'`. What code should i rectify – Ruchika Sep 16 '17 at 15:49
1

am able to solve after using table alias. Thanks - your solution worked perfectly and much thanks for pointing in right direction - it really helped me in learning same too – Ruchika Sep 16 '17 at 16:01
No problem, glad you worked it out and feel free to edit my answer if a correction is needed. – Steve Chambers Sep 16 '17 at 16:05

score 0 · Answer 2 · answered Sep 12 '17 at 16:37

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';
}
?>

sorry doesnt work bind function error then execute error and then fetch error. — Ruchika, Sep 13 '17 at 02:35
Could you please give me the screenshots of errors? I was Given you a example code to check the db prod match from get, you need to implement to your main script as you need. Does your server has pdo driver? if not install it — Ajmal PraveeN, Sep 13 '17 at 07:15

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

2 Answers2