Jump to content

Issue with inner join and where statements


Recommended Posts

hello I am looking for help on a sql problem. Not sure if I have a statement mixed up or not but the table simply won't render. Any help would be greatly appreciated. 

Database name = test

I have two tables

table 1 = parts

table 2 = parts_pricing


The main recordset name is RSparts


Please see my current php code -


$maxRows_RSparts = 10;
$pageNum_RSparts = 0;
if (isset($_GET['pageNum_RSparts'])) {
  $pageNum_RSparts = $_GET['pageNum_RSparts'];
$startRow_RSparts = $pageNum_RSparts * $maxRows_RSparts;
$colname1_RSparts = "-1";
if (isset($_GET['figno'])) {
  $colname1_RSparts = $_GET['figno'];
$colname2_RSparts = "-1";
if (isset($_GET['modelno'])) {
  $colname2_RSparts = $_GET['modelno'];
mysql_select_db($database_test, $test);
$query_RSparts = sprintf("SELECT *  FROM parts INNER JOIN partspricing ON partspricing.sellingprice=parts.partID  WHERE figno = %s AND modelno = %s ORDERBY ACS ", GetSQLValueString($colname1_RSparts, "text"),GetSQLValueString($colname2_RSparts, "text"));
$query_limit_RSparts = sprintf("%s LIMIT %d, %d", $query_RSparts, $startRow_RSparts, $maxRows_RSparts);
$RSparts = mysql_query($query_limit_RSRSparts, $test) or die(mysql_error());
$row_RSparts = mysql_fetch_assoc($RSparts;
if (isset($_GET['totalRows_RSparts'])) {
  $totalRows_RSparts = $_GET['totalRows_RSparts'];
} else {
  $all_RSparts = mysql_query($query_RSparts, $test);
  $totalRows_RSRSparts = mysql_num_rows($all_RSparts);
$totalPages_RSparts = ceil($totalRows_RSparts/$maxRows_RSparts)-1;
Table setup;
    <td>Key No.</td>
    <td>Part Number</td>
    <td>S/N Break</td>
  <?php do { ?>
      <td><?php echo $row_RSparts['keyno']; ?></td>
      <td><?php echo $row_RSparts['partno']; ?></td>
      <td><?php echo $row_RSparts['description']; ?></td>
      <td><?php echo $row_RSparts['qty']; ?></td>
      <td><?php echo $row_RSparts['snbreak']; ?></td>
      <td><?php echo $row_RSparts['availability']; ?></td>
       <td><?php echo $row_RSparts['sellingprice']; ?></td>
    <?php } while ($row_RSparts = mysql_fetch_assoc($RSparts)); ?>
If I leave out parts INNER JOIN partspricing ON partspricing.sellingprice=parts.partID   the table works and renders fine or if I leave out WHERE figno = %s AND modelno = %s ORDERBY ACS the table renders fine. 
I am missing something here. If I leave it as is the table will not render at all. Hopefully this is something simple any help would be greatly appreciated! 


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.

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.

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