Simple join problem not so simple

[brettacvh]

Here is a photo of my 3 tables that I'm trying to join:

http://www.cyber-e-sport.com/cyber_images/tables.jpg

Here is a link to the page that won't work:

http://www.cyber-e-sport.com/cyber_photos/photos4.php?product_ID=2

Can somebody please tell me what's wrong with this query:

SELECT DISTINCT tbl_photos_products.product_ID AS product_ID_1, tbl_photos.*, tbl_products.product_ID, tbl_products.product_name, tbl_photos_products.photos_ID
FROM ((tbl_products LEFT JOIN tbl_photos_products ON tbl_photos_products.product_ID=tbl_products.product_ID) LEFT JOIN tbl_photos ON tbl_photos.photos_ID=tbl_photos_products.photos_ID)
WHERE tbl_products.product_ID = var1

VARIABLE:
name: va1
Type: numeric
Default value: 0
Runtime value: $_GET[' tbl_products.product_ID']

When I give var1 a default value of 6, for example, it works, but when I give it a default value of 0 or -1 nothing is displayed in the web page.

[brettacvh]

Oh yeah. Here's a better set of images for the database:

http://www.cyber-e-sport.com/tables/tables.php

[j_70]

One good way to debug these types of problems is to run the SQL outside of PHP. Have you validated your SQL by running it in one of the MySQL (assuming that is what you are using) GUI tools?

[brettacvh]

yeah, I ran the query in query builder and it returned the results. Perhaps the problem is in my page code???:

<?php require_once('../Connections/dbConn.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$var1_testRS = "6";
if (isset($_GET[' tbl_products.product_ID'])) {
  $var1_testRS = $_GET[' tbl_products.product_ID'];
}
mysql_select_db($database_dbConn, $dbConn);
$query_testRS = sprintf("SELECT DISTINCT tbl_photos.*, tbl_products.* FROM (tbl_products LEFT JOIN tbl_photos ON tbl_photos.product_ID=tbl_products.product_ID) WHERE tbl_products.product_ID = %s", GetSQLValueString($var1_testRS, "int"));
$testRS = mysql_query($query_testRS, $dbConn) or die(mysql_error());
$row_testRS = mysql_fetch_assoc($testRS);
$totalRows_testRS = mysql_num_rows($testRS);

mysql_select_db($database_dbConn, $dbConn);
$query_brandRS = "SELECT * FROM tbl_brands ORDER BY brand_ID ASC";
$brandRS = mysql_query($query_brandRS, $dbConn) or die(mysql_error());
$row_brandRS = mysql_fetch_assoc($brandRS);
$totalRows_brandRS = mysql_num_rows($brandRS);
?>

---

<form action="photos4.php" enctype="application/x-www-form-urlencoded" method="post" name="form" target="_self" id="form">
                          <table width="100%" border="0" cellspacing="0" cellpadding="0">
                          <tr>
                            <td width="3%" align="left" valign="top">&nbsp;</td>
                            <td width="97%" align="left" valign="top">
                              <label></label>
                              <label></label>
                              <label>
                              <select onchange='location.href =&quot;http://www.cyber-e-sport.com/cyber_photos/photos3.php?brand_ID=&quot;+document.form.select3.options[document.form.select3.selectedIndex].value;' name="select3" id="select3">
                                <option selected="selected" value="">Select Brand</option>
                                <?php
do {  

?>
                                <option value="<?php echo $row_brandRS['brand_ID']?>"><?php echo $row_brandRS['brand_name']?></option>
                                <?php
} while ($row_brandRS = mysql_fetch_assoc($brandRS));
  $rows = mysql_num_rows($brandRS);
  if($rows > 0) {
      mysql_data_seek($brandRS, 0);
	  $row_brandRS = mysql_fetch_assoc($brandRS);
  }
?>
                              </select>
                              </label>
                            </td>
                          </tr>
                          <tr>
                            <td align="left" valign="top">&nbsp;</td>
                            <td align="left" valign="top">&nbsp;</td>
                          </tr>
                          <tr>
                            <td colspan="2" align="left" valign="top"><div id="orbita">
                              <table width="903" border="0" cellspacing="0" cellpadding="0">
                                <tr>
                                  <td height="10" colspan="3" align="left" valign="top"><img src="../cyber_images/topgreenstrip.jpg" width="903" height="14" border="0" /></td>
                                </tr>
                                <tr>
                                  <td width="10" align="left" valign="top" background="../cyber_images/left_green_border.jpg"><img src="../cyber_images/left_green_border.jpg" alt="" width="10" height="10" border="0" /></td>
                                  <td width="883" align="left" valign="top" bgcolor="#1F1D1E"><table width="100%" border="0" cellspacing="0" cellpadding="0">
                                      <tr>
                                        <td align="left" valign="top" ><table border="0">
                                            <tr>
                                              <?php
do { // horizontal looper version 3
?>
                                              <td><table cellpadding="13" >
                                                  <tr>
                                                    <td><table border="0" cellspacing="0" cellpadding="0">
                                                        <tr>
                                                          <td align="left" valign="top"><span class="body_copy"><img src="../downloads/<?php echo $row_testRS['image_url']; ?>" alt="<?php echo $row_testRS['alt_tag']; ?>" name="image" width="140" border="0" id="image2" /><br />
                                                                <?php echo $row_testRS['description']; ?><br />
                                                                <?php echo $row_testRS['width']; ?> x <?php echo $row_testRS['height']; ?> <?php echo $row_testRS['units']; ?><br />
                                                                <?php $filetypea = '../downloads/' . $row_testRS['image_url'];
$ext = end(explode('.', $filetypea));
print $ext;
?>
                                                                <br />
                                                                <?php $filename = '../downloads/' . $row_testRS['image_url'];
$imagesize = filesize("$filename");
$convertedsize = $imagesize/1024;
$convertedsizeround = round($convertedsize, 2);
print $convertedsizeround;
?>
                                                            kb</span><br />
                                                            <a href="../downloads/<?php echo $row_testRS['image_url']; ?>" target="_blank" class="greenlink">DOWNLOAD</a></td>
                                                        </tr>
                                                    </table></td>
                                                  </tr>
                                              </table></td>
                                              <?php
$row_testRS = mysql_fetch_assoc($testRS);
    if (!isset($nested_testRS)) {
      $nested_testRS= 1;
    }
    if (isset($row_testRS) && is_array($row_testRS) && $nested_testRS++ % 5==0) {
      echo "</tr><tr>";
    }
  } while ($row_testRS); //end horizontal looper version 3
?>
                                            </tr>
                                          </table>
                                            </td>
                                      </tr>
                                      <tr>
                                        <td align="left" valign="top" class="body_copy"><div align="center"> </div></td>
                                      </tr>
                                  </table></td>
                                  <td width="10" align="left" valign="top" background="../cyber_images/right_green_border.jpg"><img src="../cyber_images/right_green_border.jpg" alt="" width="10" height="10" border="0" /></td>
                                </tr>
                                <tr>
                                  <td width="10" height="10" align="left" valign="top"><img src="../cyber_images/bottom_left.gif" alt="" width="10" height="10" border="0" /></td>
                                  <td align="left" valign="top"><img src="../cyber_images/bottom_green_border.jpg" alt="" width="883" height="10" border="0" /></td>
                                  <td width="10" height="10" align="left" valign="top"><img src="../cyber_images/bottom_right.gif" alt="" width="10" height="10" border="0" /></td>
                                </tr>
                              </table>
                            </div></td>
                            </tr>
                        </table></form>