Jump to content

manage to joining tables in database


Recommended Posts

I manage to fix the first problem i got, but this is another problem.
I have problems to join table and show the result.

This i manage to fix so i can se the variable,

private function paginationYearDataControl($pageId, $limit) {
		$year_list = array();
		$start_from = ($pageId-1) * $limit;
		$sql = 'SELECT ma.makename,mo.modelname,fy.typename,y.year,y.yearid FROM ' 
		._DB_PREFIX_.'caryear y inner join ' 
		._DB_PREFIX_.'carmake ma on y.makeid = ma.makeid inner join ' 
		._DB_PREFIX_.'carmodel mo on mo.modelid = y.modelid inner join '
		._DB_PREFIX_.'cartype fy on fy.typeid = y.typeid ORDER BY ma.makename,mo.modelname,fy.typename,y.year ASC LIMIT '.$start_from.','.$limit;
		if ($results = Db::getInstance()->ExecuteS($sql)){
			foreach($results as $row){
				$year_list[] = array(
					'yearId'			=> $row['yearid'],
					'MakeName'			=> $row['makename'],
					'ModelName'			=> $row['modelname'],
					'Typename'			=> $row['typename'],
					'year'				=> $row['year']
		return $year_list;

But the other in the dropdown i can see, it doesent get the variable´s.
I think this is the problem.
from the tpl file:

<div class="form-group">
      <label for="carType">Type</label>
      <select required="required" id="carType" name="cartype" required>
        <option value="">--Select Type--</option>
    {foreach from=$type_list item=foo}
    <option {if $typeid==$foo.typeid}selected{/if} value="{$foo.typeid}">{$foo.typename}</option>

this is the controller file:

		$year_list = array();
		$make_list = array();
		$type_list = array();
		$total_row = 0;
		$total_page = 1;
		$per_page_count = 25;
		$pageId = 1;
		if($row = Db::getInstance()->getRow('SELECT count(yearid) as total FROM '
		._DB_PREFIX_.'caryear y inner join '
		._DB_PREFIX_.'carmake ma on y.makeid = ma.makeid inner join '
		._DB_PREFIX_.'carmodel mo on y.modelid = mo.modelid inner join '
		._DB_PREFIX_.'cartype fy on y.typeid = fy.typeid ORDER BY ma.makename ASC')){
			$total_row = $row['total'];
		if(Tools::getValue('page') && Tools::getValue('page') != ''){
			$pageId = Tools::getValue('page');

Or my be this is the problem.

//view operation
			if($row = Db::getInstance()->getRow('SELECT * FROM '._DB_PREFIX_.'caryear where yearid='.Tools::getValue('edit'))){
				$this->makeid = $row['makeid'];
				$this->modelid = $row['modelid'];
				$this->typeid = $row['typeid'];
				$this->yearid = Tools::getValue('edit');
				$this->year = $row['year'];
				if ($results = Db::getInstance()->ExecuteS('SELECT * FROM '._DB_PREFIX_.'carmodel where makeid='.(int)$row['makeid'])){
					foreach($results as $row){
						$this->model_list[] = array(
							'modelid'			=> $row['modelid'],
							'modelname'			=> $row['modelname']

I hope someone can help me, im stuck now.


Uppdate: I have now figure out that im missing the type_list that show the typename and type id.

I have for make_list:

$sqlx = 'SELECT * FROM '._DB_PREFIX_.'carmake ORDER BY makename ASC';
        if ($resultsx = Db::getInstance()->ExecuteS($sqlx)){
            foreach($resultsx as $rowx){
                $make_list[] = array(
                    'mid'           => $rowx['makeid'],
                    'name'          => $rowx['makename']

Wondering how i can get type_list in to this.

Edited by spc
first problem solved (see edit history)

Share this post

Link to post
Share on other sites

  • spc changed the title to manage to joining tables in database

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More