Having SQL join problems!

Report · Sep 28, 2012

I have got a master recordset and this is the SQL for it:

SELECT fld_contentID, fld_contentPAGE, fld_contentIMGSLDR, fld_contentTITLE, fld_contentTOP, fld_contentPODL, fld_contentPODR, fld_contentBOTTOM, fld_contentBGIMG, fld_contentSHOW, fld_contentDATE

FROM tbl_content

WHERE fld_contentPAGE = '1'

ORDER BY fld_contentDATE DESC

This works fine. I can populate the page with the content that I want.

I have a second table with secondary information within it for promotional needs. This table serves what I am referring to as 'pods' of info. I am trying to pull and join content from the table called tbl_pods using the unique ID of the pod record and joining it with a field in the tbl_content table, and that field is called fld_contentPODL. Here's the code:

SELECT *

FROM tbl_pods

JOIN tbl_content ON tbl_content.fld_contentPODR = tbl_pods.fld_podID

WHERE fld_podID = podrvar

Name: podrvar

Type: Interger

Default value: %

Run-time Value: $_GET['tbl_content.fld_contentPODL']

I have tried every variation of JOIN that I can think of, but it just won't work with a dynamic default value. If I give a specific default value of 1, instead of %, then it works as expected, but only by being specific which means it's not loading the variable dynamically.

Can somebody please highlight the problem with my join statement, cos this is driving me mental!

Is there something wrong with my master table tbl_contents. I can't see that there is as I am using the field fld_contentPODL to hold the unique ID of the pod, so surely I can join the tables on that! Am I passing data incorrectly? I didn't think I was as I am asking to join the data of tbl_pods to the data of tbl_contentPODL, which should work.

Thanks in advance.

Mat

Report · Sep 28, 2012

>I have tried every variation of JOIN that I can think of,

>but it just won't work with a dynamic default value.

Not sure what you mean by 'dynamic default value'. You have assigned the default value as '%'. That value is used only when another value is not assigned at runtime. We really need to see the entire code from your page to tell what's wrong. Most likely you are just not assigning the value from the form (or where ever it is coming from) to the WHERE clause correctly. As a guess, I would say you need to fix this

SELECT *

FROM tbl_pods

JOIN tbl_content ON tbl_content.fld_contentPODR = tbl_pods.fld_podID

WHERE fld_podID = podrvar

because there is no variable used in that SQL statement. Assuming that podrvar is supposed to be a variable, and that you are using VBScript, then you need to write this differently.

"SELECT *

FROM tbl_pods

JOIN tbl_content ON tbl_content.fld_contentPODR = tbl_pods.fld_podID

WHERE fld_podID = " & podrvar

Report · Sep 28, 2012

What I mean by dynamic default value is the content of the field in the record which is effectively the 'master' instruction to load the content of the JIONed table record using the field fld_podID (in tbl_pods). Basically, as you put it, the runtime value. I used % as an example just because I have tried loads of other things! I normally use -1 which DW sets by default.

So, my database is like this:

Main page content is held in a table called: tbl_content

fld_contentID - INT (primary key) - Value eg: 1 to what ever!

fld_contentTITLE - VARCHAR - Value eg: About us

fld_contentTOP - VARCHAR - Value eg: Blah blah blah

fld_contentPODL - INT - Value eg: 1 (or 2 or 3 etc based on how many records I have in tbl_pods.fld_podID)

The 'pods' content is held in a table called: tbl_pods

fld_podID - INT (primary key) - Value eg: 1 to what ever!

fld_podNAME - VARCHAR - Value eg: Book for Xmas

fld_podCONTENT - VARCHAR - Value eg: We're taking bookings now, so get in touch to ensure you don't miss out.

--------

What I am trying to do is JOIN tbl_content.fld_contentPODL to tbl_pod.fld_podID and then display the runtime content associated with the record so I can display 'Book for Xmas, We're taking bookings now...'

--------

Here's my page code:

<?php require_once('Connections/conn_t3pi.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$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;

}

mysql_select_db($database_conn_t3pi, $conn_t3pi);

$query_rs_content = "SELECT * FROM tbl_content WHERE fld_contentPAGE = '1' ORDER BY fld_contentDATE DESC";

$rs_content = mysql_query($query_rs_content, $conn_t3pi) or die(mysql_error());

$row_rs_content = mysql_fetch_assoc($rs_content);

$totalRows_rs_content = mysql_num_rows($rs_content);

$podlvar_rs_podl = "-1";

if (isset($_GET['tbl_pods.fld_podID'])) {

$podlvar_rs_podl = $_GET['tbl_pods.fld_podID'];

}

mysql_select_db($database_conn_t3pi, $conn_t3pi);

$query_rs_podl = sprintf("SELECT * FROM tbl_pods JOIN tbl_content ON tbl_content.fld_contentPODL = tbl_pods.fld_podID WHERE tbl_content.fld_contentPODL = %s", GetSQLValueString($podlvar_rs_podl, "int"));

$rs_podl = mysql_query($query_rs_podl, $conn_t3pi) or die(mysql_error());

$row_rs_podl = mysql_fetch_assoc($rs_podl);

$totalRows_rs_podl = mysql_num_rows($rs_podl);

?>

<!DOCTYPE HTML>

<html>

<head><title>Untitled Document</title></head>

<body>

<?php echo $row_rs_content['fld_contentTOP']; ?>

</body>

</html>

<?php

mysql_free_result($rs_content);

mysql_free_result($rs_podl);

?>

--------

I've made the SQL bold so it's easier to pick out. Yeah, I am at a loss as I can make the correct content appear if I physically type the correct runtime ID, but if I have a runtime that is loaded by the master record, then it doesn't show anything. I can't figure out why! I am sure I just have something switched around either in the JOIN or in the WHERE, but I don't know what though.

Thanks.

Mat

Report · Sep 28, 2012

OK, still trying to understand what you are doing. You've got two recordsets on the page that are currently unrelated. Next, you appear to be getting the filter value for the second recordset from a querystring by using the _GET array - but it looks like you are referencing a table/column within that. What is actually in your querystring? Did you mean to use 'ID' rather than "'tbl_pods.fld_podID'"