Jump to content

[SOLVED] Comparing data from 2 tables.

Ramacide

By Ramacide
in MySQL Help

 Share

Recommended Posts

Ramacide Newbie

Ramacide

Posted
Posted

I am trying to make a link appear if the mechanism is listed under the selected "product" and "style". The mechanism is called from 2 places because I want it to list all available mechanisms and provide links only for the ones that are actually available on that product and style. what would be the best way to have it check if mechansim.mechanism_name is also listed in products.mechanism_name i've tried various scenarios? The closest I have gotten is it highlights the first qty of matches not actual matches.

 

<?php
$style = $_GET["style"];
$type = $_GET["type"];
mysql_select_db($database_ioa, $ioa);
$sql = "SELECT style_type.image, style_type.style_type FROM style_type WHERE product_type='$type'";
$mechsql = "SELECT mechanism.mechanism_name, mechanism.product_type, mechanism.mechanism_description FROM mechanism WHERE product_type='$type'";
$stylesql ="SELECT products.style_type, products.mechanism_name FROM products WHERE style_type='$style'";
$result = mysql_query($sql) or die(mysql_error());
$result_mech = mysql_query($mechsql) or die(mysql_error());
$result_style = mysql_query($stylesql) or die(mysql_error());
$num_rows = mysql_num_rows($result); 
$mech_num_rows = mysql_num_rows($result_mech);
?>
<?php require_once('nav_top.php'); ?>
<table width="800" border="0">
  <tr>
    <td width="330" valign="top">
    <table width="292" border="0" cellspacing="0" cellpadding="10">
<tr>
  <?php
  $numColumns = 3; 
  if ($numColumns < 10){
  $numColumns = 0 . $numColumns;
  }
  $extraCells = $numColumns - ($num_rows %  $numColumns) ; 
  $resultingRows =  substr($num_rows /  $numColumns,0,2)  ;
  for($i=0; $i < $num_rows; $i++) {  
  $row = mysql_fetch_array($result);
  if (substr($i/$numColumns,0,1)  != $resultingRows){ 
      if ($i % $numColumns != $numColumns - 1){  
        echo '<td align="center"><span class="image_menu"><div>','<a href="style_type.php?type=',$type,'&style=',$row['style_type'],'" target="_self" class="highlight"><img src="',$row['image'],'" height="84" border="0"> <br />',$row['style_type'],'</a></div></span></td>';
	      }else{                     
        echo '<td align="center"><span class="image_menu"><div>','<a href="style_type.php?type=',$type,'&style=',$row['style_type'],'" target="_self" class="highlight"><img src="',$row['image'],'" height="84" border="0"> <br />',$row['style_type'],'</a></div></span></td></tr>';      }
      }else{                       
          echo '<td align="center"><span class="image_menu"><div>','<a href="style_type.php?type=',$type,'&style=',$row['style_type'],'" target="_self" class="highlight"><img src="',$row['image'],'" height="84" border="0"> <br />',$row['style_type'],'</a></div></span></td>';
  }
  if ($i==$num_rows - 1){
        for($j=0; $j < $extraCells; $j++) { 
        echo "<td>&nbsp</td>\n";

}
echo"</tr>\n";

  }
}
    
  ?>
</table>
</td>
    <td width="460" valign="top">
    <table width="460" border="0" cellspacing="0" cellpadding="10" class="image_menu">
<tr>
  <?php
  $numColumns = 2; 
  if ($numColumns < 10){
  $numColumns = 0 . $numColumns;
  }
  $extraCells = $numColumns - ($mech_num_rows %  $numColumns) ; 
  $resultingRows =  substr($mech_num_rows /  $numColumns,0,2)  ;
  for($i=0; $i < $mech_num_rows; $i++) {  
  $row2 = mysql_fetch_array($result_mech);
  $rowstyle = mysql_fetch_array($result_style);
  if (substr($i/$numColumns,0,1)  != $resultingRows){ 
      if ($i % $numColumns != $numColumns - 1){  
      if(/*$rowstyle['style_type'] == $style && */$row2['mechanism_name'] == $rowstyle['mechanism_name']){
	  echo '<td align="left"><a href="yes" class="highlight">',$row2['mechanism_name'],'</a></td>'; 
	  }else{
        echo '<td align="left">',$row2['mechanism_name'],'</td>';
	  }
      }else{
      if(/*$rowstyle['style_type'] == $style && */$row2['mechanism_name'] == $rowstyle['mechanism_name']){                 
	  echo '<td align="left"><a href="yes" class="highlight">',$row2['mechanism_name'],'</a></td>'; 
	  }else{
        echo '<td align="left">',$row2['mechanism_name'],'</td></tr>';
	  }
      }
      }else{
  	  if(/*$rowstyle['style_type'] == $style && */$row2['mechanism_name'] == $rowstyle['mechanism_name']){                        
	echo '<td align="left"><a href="yes" class="highlight">',$row2['mechanism_name'],'</a></td>'; 
	  }else{
        echo '<td align="left">',$row2['mechanism_name'],'</td>';
  	  }
      }
  if ($i==$mech_num_rows - 1){
        for($j=0; $j < $extraCells; $j++) { 
        echo "<td>&nbsp</td>\n";

}
echo"</tr>\n";

  }
}
    
  ?>
</table>
    </td>
  </tr>
</table>
<?php
        echo 'mech.mechname = ',$row2['mechanism_name'];
	echo '<br>product.mechname =',$rowstyle['mechanism_name'];
?>
<?php require_once('nav_bottom.php'); ?>

Link to comment
Share on other sites
Ramacide Newbie

Ramacide

Posted
  • Author
Posted

Mechanism

+------+------------------+-----------+

|image | mechanism_name  | description |

+------+------------------+-----------+

|  img1 |          mech  1      |      des 1  |

|  img2 |          mech  2      |      des 2  |

|  img3 |          mech  3      |      des 3  |

+------+------------------+-----------+

 

 

Products

+-----+-----------------+-----+

| sku  |mechanism_name | dims |

+-----+-----------------+-----+

|  619 |          mech 2    |  2  |

|  623 |          mech 4    |  3  |

+-----+-----------------+-----+

 

With the above tables it should list mech 1, mech 2, and mech 3. However mech 2 should be a link because it is also in products table. The below statement I thought would do this but it doesn't.

 

if($row2['mechanism_name'] == $rowstyle['mechanism_name']){
	  echo '<td align="left"><a href="yes" class="highlight">',$row2['mechanism_name'],'</a></td>'; 
	  }else{
        echo '<td align="left">',$row2['mechanism_name'],'</td>';
	  }

 

$mechsql = "SELECT mechanism.mechanism_name, mechanism.product_type, mechanism.mechanism_description FROM mechanism WHERE product_type='$type'";
$stylesql ="SELECT products.style_type, products.mechanism_name FROM products WHERE style_type='$style'";

 

 

$result_mech = mysql_query($mechsql) or die(mysql_error());
$result_style = mysql_query($stylesql) or die(mysql_error());

  $row2 = mysql_fetch_array($result_mech);
  $rowstyle = mysql_fetch_array($result_style);

Link to comment
Share on other sites
Ramacide Newbie

Ramacide

Posted
  • Author
Posted

Thank You, I will try to figure out how to use LEFT JOIN to do that. I tinkered with JOIN last night but its completely new to me and I wasn't sure it was the correct route, so in essence I didn't have very much success. Doesn't help I've been kinda drained the past few days from Motorcycle course.  I do have a question, by joining will it match by name or number? I was afraid it would join the 2 tables in the order they were and not match up specific information. I hope that makes sense. Going to try now either way.

Link to comment
Share on other sites
Ramacide Newbie

Ramacide

Posted
  • Author
Posted

Well after ALOT of tries I still have not gotten results.

 

These are my sql statements...

$sql = "SELECT style_type.image, style_type.style_type FROM style_type WHERE product_type='$type'";
$mechsql = "SELECT mechanism.mechanism_name, mechanism.product_type, mechanism.mechanism_description FROM mechanism LEFT JOIN products ON products.mechanism_name=mechanism.mechanism_name";

This is my If statement

if($row2['products.mechanism_name'] != null){
		echo '<td align="left"><a href="',$row2['mechanism.mechanism_name'],'" class="highlight">',$row2['mechanism.mechanism_name'],'</a></td>';
		}else{
    echo '<td align="left">',$row2['mechanism_name'],'</td>';}
      }

 

and this is the error I am getting.

 

Notice: Undefined index: products.mechanism_name in style_type.php on line 63

Notice: Undefined index: products.mechanism_name in style_type.php on line 68

Notice: Undefined index: products.mechanism_name in tyle_type.php on line 63

Notice: Undefined index: products.mechanism_name in style_type.php on line 68

Notice: Undefined index: products.mechanism_name in style_type.php on line 74

 

Any suggestions?

Link to comment
Share on other sites
Ramacide Newbie

Ramacide

Posted
  • Author
Posted

I just solved it, don't know if they have to be different values but I changed products.mechanim_name to products.pmechanism_name. Then did as you suggested and checked for null value.

 

$mechsql = "SELECT mechanism.mechanism_name, mechanism.product_type, mechanism.mechanism_description, products.pmechanism_name FROM mechanism LEFT JOIN products ON products.pmechanism_name=mechanism.mechanism_name";

 

if($row2['pmechanism_name'] != null){
		echo '<td align="left"><a href="',$row2['mechanism_name'],'" class="highlight">',$row2[								               'mechanism_name'],'</a></td>';
		}else{
    echo '<td align="left">',$row2['mechanism_name'],'</td>';
}

 

Thank You Fenway for getting me on the right track. Do you suggest keeping row names seperate as the example above or is this not a very common problem?  The rest of my database structure is setup like that.

Link to comment
Share on other sites
fenway Advanced Member

fenway

Posted
Posted

I just solved it, don't know if they have to be different values but I changed products.mechanim_name to products.pmechanism_name. Then did as you suggested and checked for null value.

That's because the LEFT JOIN makes checking any rows from that table meaningless... when you switched it the checking the non-joined table, it was fine.

 

Thank You Fenway for getting me on the right track. Do you suggest keeping row names seperate as the example above or is this not a very common problem?  The rest of my database structure is setup like that.

Separate? please cxplain.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.