Field value picked from wrong table due to same field name in different tables

Question

This is a code am using. However facing an issue due to common id field in all tables

Problem vid='.$row->id.' - in url The id field in the url is showing from #__usedcar_products table, while i want that id should be picked from #__usedcar_variants table

See Url in End of full code

<a href="index.php?option=com_usedcar&view=product&pid='.$row->v_prod_id.'&vid='.$row->id.'">

Below is Full Code

    function loadMainCat($brand,$Carmodel,$minprice,$maxprice,$reg_year, $reg_city, $kms_min,$kms_max,$location){
    $mainframe =JFactory::getApplication();
    $option = JRequest::getCmd('option');
            $database =JFactory::getDBO();
            global $Itemid; 

            /////////////My Sql Condition Start//////////////
            $cond="";
            if($brand){ $cond.=" and cc.id= ".$brand."";}
            if($Carmodel){ $cond.=" and cv.v_prod_id= ".$Carmodel."";}
            if($minprice!="" and $maxprice!=""){
            $cond.=" and (cv.v_price between ".$minprice." and ".$maxprice.")";
            }elseif($minprice){ $cond.=" and cv.v_price >= ".$minprice."";
            }elseif($maxprice){ $cond.=" and v_price <= ".$maxprice."";}

            if($reg_year){ $cond.=" and cv.registration_year =".$reg_year."";}

            if($reg_city){ $cond.=" and cv.registration_city ='".$reg_city."'";}

            if($kms_min!="" and $kms_max!=""){ $cond.=" and (cv.kms_run between ".$kms_min." and ".$kms_max.")";
            }elseif($kms_min){ $cond.=" and cv.kms_run >= ".$kms_min."";
            }elseif($kms_max){ $cond.=" and cv.kms_run <= ".$kms_max."";}

            if($location){ $cond.=" and cv.v_location='".$location."'";}
            /////////////My Sql Condition End//////////////

            //////////Paging////
            jimport('joomla.html.pagination');
            $limit      = $mainframe->getUserStateFromRequest( 'global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int' );
            $limit      = 2;
            $limitstart = $mainframe->getUserStateFromRequest( 'limitstart', 'limitstart', 0, 'int' );
            $sql = "select * from #__usedcar_variants cv inner join #__usedcar_products cp  inner join #__usedcar_categories cc on  cc.id=cp.prod_cat_id and cp.id=cv.v_prod_id and cv.id=cv.id and cv.state='1' where  1 $cond order by cv.id desc";


            $database->setQuery($sql);
            $total = $database->loadResult();
            $pageNav = new JPagination( $total, $limitstart, $limit );
            $database->setQuery( $sql, $this->getState('limitstart'), $this->getState('limit') );
            $database->getQuery($sql);
            $rows = $database->loadObjectList();

            $list="";
            $flg=0;
            if($rows){
            $list.=' <table width="100%" border="0" cellspacing="0" cellpadding="0">
                                          <tr>
                                            <td>
                                                <!--listing start header-->
                                                                                    </td>
                                          </tr>';
                foreach($rows as $row){
                if($row->v_small_img!=""){
                        $img = "/".$row->v_small_img;
                }else
                        $img ='#';

                    $list.='<tr>
                                <td>

     <div class="technology">'.$row->cat_name.'</div>

<p><a href="index.php?option=com_usedcar&view=product&pid='.$row->v_prod_id.'&vid='.$row->id.'"><b>Used</b></a></p>

Answer 1

Assuming you can't change the field name in table, you can select only the fields you need:

$sql = "select cv.*,cc.cat_name,cc.v_small_img from #__usedcar_variants cv inner join #__usedcar_products cp  inner join #__usedcar_categories cc on  cc.id=cp.prod_cat_id and cp.id=cv.v_prod_id and cv.id=cv.id and cv.state='1' where  1 $cond order by cv.id desc";

I don't know in which table fields actually are but you can select them like this:

cv.* - gets all fields from cv table

cv.id as newname - get id field but assigns another namespace (newname)

In this way accessing $row->newname will actually return the cv.id field.

Answer 2

in select query you requested data from table #__usedcar_variants, so you should not have id from another table, isn't it. also you need to fix typo where it .. cv.id=cv.id -> cv.id=cp.id

Answer 3

Regarding your issue, when you have duplicate column names in your resultset, all later columns overwrite the earlier columns. To make a distinction between the columns, you must explicitly add the table name before the column (as you were doing elsewhere in your code).

Beyond that little hiccup, there are many, many ways to refine your snippet which will improve readability and security. While I cannot be completely certain of your how effective/efficient your query is without your database structure and sample data, I think many of my suggestions will serve to optimize your code by virtue of "best practice".

Code (without pagination lines -- because I am not yet familiar with Joomla's pagination methods):

$db = JFactory::getDbo();
try {
    // build array of conditions to be glued together with AND
    $where = array("cv.state = 1");  // no longer in ON clause
    if ($brand) {
        $where[] = "cc.id = " . $db->q($brand);
    }
    if ($Carmodel) {
        $where[] = "cv.v_prod_id = " . $db->q($Carmodel);
    }
    if ($minprice !== "" && $maxprice !== "") {
        $where[] = "cv.v_price BETWEEN " . (int)$minprice . " AND " . (int)$maxprice;
    } elseif ($minprice) {
        $where[] = "cv.v_price >= " . (int)$minprice;
    } elseif ($maxprice) {
        $where[] = "cv.v_price <= " . (int)$maxprice;
    }
    if ($reg_year) {
        $where[] = "cv.registration_year = " . (int)$reg_year;
    }
    if ($reg_city) {
        $where[] = "cv.registration_city = " . $db->q($reg_city);
    }
    if ($kms_min !== "" && $kms_max !== "") {
        $where[] = "cv.kms_run BETWEEN " . (int)$kms_min . " AND " . (int)$kms_max;
    } elseif ($kms_min) {
        $where[] = "cv.kms_run >= " . (int)$kms_min;
    } elseif ($kms_max) {
        $where[] = "cv.kms_run <= " . (int)$kms_max;
    }
    if ($location) {
        $where[] = "cv.v_location=" . $db->q($location);
    }

    $query = $db->getQuery(true)
                ->select(
                    array("cv.v_small_img", "cv.cat_name", "cv.v_prod_id", "cv.id")
                  )
                ->from("#__usedcar_variants cv")
                ->innerJoin("#__usedcar_products cp ON cp.id = cv.v_prod_id")
                ->innerJoin("#__usedcar_categories cc ON cc.id = cp.prod_cat_id")
                ->where($where)
                ->order("cv.id DESC");

    //echo $query->dump();
    $db->setQuery($query);
    $resultset = $db->loadObjectList();
    if (!$resultset) {
        $output = "No Rows In Resultset";
    } else {
        $output = '<table width="100%" border="0" cellspacing="0" cellpadding="0">';
        $output .= '<tr><th>[Header Text]</th></tr>';
        foreach ($resultset as $row) {
            $output .= '<tr><td>';
            $output .= '<div class="technology">' . $row->cat_name . '</div>';
            $output .= '<p><a href="index.php?option=com_usedcar&view=product&pid=' . $row->v_prod_id . '&vid=' . $row->id . '"><b>Used</b></a></p>';
            $output .= '<img src="' . ($row->v_small_img != '' ? $row->v_small_img : '#') . '">';
            $output .= '</td></tr>';
        }
        $output .= '</table>';
    }
} catch (Exception $e) {
    $output = "Syntax Error "; // . $e->getMessage();
}
return $output;

1. Use JDatabase query methods -- this is what they were designed for.
2. Use a try {} catch {} block to help you to debug when things go wrong.
3. Build your WHERE conditions as an array, because the where() method is happy to receive an array of conditions and assumes AND as the glue.
4. When checking the price and kms values, be sure to use !== '' so that 0 values are not mistaken for an empty value.
5. Avoid the use of * in your SELECT clause -- only ask for what you intend to use and explicitly name the tables that your desired columns come from.
6. Write the ON clause conditions to the right of the tables that are being joined.
7. The state clause is not literally a JOIN condition and is more logically placed in the WHERE clause.
8. Use ->dump() when you want to see the generated query. This can be helpful when you want to experiment by transferring the query to phpMyAdmin or WorkBench.
9. Writing MySQL KEYWORDS as all uppercase will improve the readability of your query.
10. Use $db->q() to single-quote-wrap all variables that contain string values. Use (int) to cast integer values from string type to integer type as a security precaution. If your numeric values may contain decimal points, use (float) instead of (int).
11. $row->v_small_img was not used in your posted snippet, so I added an <img> tag to receive the value.
12. Declaring a variable in the function scope with global is sometimes considered bad practice; where as passing the variable as an argument is preferred.
13. And finally, be generous with spacing. Omitting spaces to save a few characters offers no noticeable benefit and certainly impacts readability.