joinTable and Undefined variable

8 posts by 2 authors in: Forums > CMS Builder
Last Post: May 25, 2017   (RSS)

By Twocans - May 10, 2017

I got this working, but it only allows me to pull the data from one table and not both.

this is the page in action, you can see it pulls data from 2 fields in the first table, but it does not allow me pull from second.

http://www.camteach.com/resultsdetailtwo.php?loc=8

If anyone has any input I would be greatful

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
/* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */

// load viewer library
$libraryPath = 'ctcms/lib/viewer_functions.php';
$dirsToCheck = array('/home/camteach/public_html/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

// load record from 'test_table_one'
list($test_table_oneRecords, $test_table_oneMetaData) = getRecords(array(
'tableName' => 'test_table_one',
'where' => whereRecordNumberInUrl(0),
'joinTable' => 'test_table_two',
'loadUploads' => true,
'allowSearch' => true,
'limit' => '1',
'debugSql' => true,
));
$test_table_oneRecord = @$test_table_oneRecords[0]; // get first record
if (!$test_table_oneRecord) { dieWith404("Record not found!"); } // show error message if no record found

$catNum = $test_table_oneRecord['num'];

// load record from 'test_table_two'
list($test_table_twoRecords, $test_table_twoMetaData) = getRecords(array(
'tableName' => 'test_table_two',
'where' => "table_two_foreign_key = '$catNum'",
'loadUploads' => true,
'allowSearch' => true,
'limit' => '1',
'debugSql' => true,
));

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
</head>
<body>


<!-- STEP2: Display Record (Paste this where you want your record to appear) -->

Table One Record Number: <?php echo htmlencode($test_table_oneRecord['num']) ?><br/>
Table One Name: <?php echo htmlencode($test_table_oneRecord['test_table_one_name']) ?><br/><br />
<br />


Table Two Record Number: <?php echo htmlencode($test_table_twoRecord['num']) ?><br/>
Table Two Name: <?php// echo htmlencode($test_table_twoRecord['tttwo_name']) ?><br/><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />

<?php foreach ($test_table_twoRecords as $record): ?>
<?php showme($record); ?>
<?php endforeach ?>

<!-- /STEP2: Display Record -->

<?php // echo htmlencode($record['tttwo_name']) ?>

<?php echo poweredByHTML(); ?>
</body>
</html>

By Dave - May 18, 2017

Hi twocans, 

I saw your post and wanted to follow up.  Did you ever get this sorted out or are you still having issues?

And if so, what were you trying to accomplish? 

Let me know, thanks!

Dave Edis - Senior Developer
interactivetools.com

By Twocans - May 18, 2017

Thank you Dave,


http://www.camteach.com/z4.php

// load record from 'test_table_two'
list($test_table_twoRecords, $test_table_twoMetaData) = getRecords(array(
'tableName' => 'test_table_two',
'where' => "test_table_two.table_two_foreign_key = '6'",
'leftJoin' => array('test_table_one' => 'ON test_table_two.table_two_foreign_key = test_table_one.num'),
'loadUploads' => false,
'allowSearch' => false,
'limit' => '1',
'debugSql' => true, // optional, display SQL query, defaults to no
));
$test_table_twoRecord = @$test_table_twoRecords[0]; // get first record
// $test_table_oneRecord = @$test_table_oneRecords[0]; // get first record
if (!$test_table_twoRecord) { dieWith404("Record not found!"); } // show error message if no record found

I can managed to get records from one table only, as I always get the error no matter what I have tried, I have tried so so so many ways of joining but wow

Test Table One Name: Notice: Undefined variable:

At the end of the day I just want to be competent with the cms so as I can sell more. I have spent a long time going through the fourms here but not always that easy. 

https://www.interactivetools.com/forum/forum-posts.php?postNum=2218681#post2218681

regards

Kenny

By Dave - May 18, 2017

Hey Kenny, 

Yea, SQL, and sql joins in particular, can be tricky.  It might be easier to switch over to a straight MySQL query.  We added that 'leftJoin' feature experimentally a while back but it's not really much easier than learning the actual MySQL.

What are you trying accomplish?

If you can send me CMS/FTP login details and a link to this post to dave@interactivetools.com I can take a look and get that working for you.  Note: Don't post login details to the forum.

Dave Edis - Senior Developer
interactivetools.com

By Twocans - May 22, 2017

Thank You Dave,

I can managed the sql query but, its calling the data to show it in my body section.

I have tried the following and much more to get the data to show. but get the error Notice: Undefined variable

http://www.camteach.com/z6.php

<body>

<?php echo htmlencode($Record['t1.test_table_one_name']) ?><br>
<?php echo htmlencode($Record['test_table_one.test_table_one_name']) ?><br><br>
<?php echo $t2Record['test_table_two.tttwo_name'] ?><br>
<?php echo $t2Record['{$TABLE_PREFIX}test_table_two.tttwo_name'] ?><br>
<?php echo htmlencode($Record['test_table_two.tttwo_name']) ?><br>
<?php echo poweredByHTML(); ?>
</body>

// load record from 'test_table_two'


$query = "SELECT *
FROM `{$TABLE_PREFIX}test_table_two` AS `t2`
LEFT JOIN `{$TABLE_PREFIX}test_table_one` AS `t1` ON t2.table_two_foreign_key = t1.num
WHERE (t2.table_two_foreign_key = '6')
ORDER BY t2.num ASC
LIMIT 1";

$records = mysql_select_query($query);

showme($records);

//exit;
?>

By Dave - May 23, 2017

Hi twocans, 

The fields shown by showme() are all the fieldnames you have available.  The column names from both tables get joined together.  So this will display the output: 

<?php foreach ($records as $record): ?>
<div style="background:#e5e5e5; margin-bottom:40px; padding: 20px;">
  <?php echo htmlencode($record['num']) ?><br>
  <?php echo htmlencode($record['tttwo_name']) ?><br>
  <?php echo htmlencode($record['table_two_foreign_key']) ?><br><br>
  <?php echo htmlencode($record['test_table_one_name']) ?><br><br>
</div>
<?php endforeach ?>

But if you have identical column names in each table you need to assign them aliases to be able to refer to them.  Like this: 

$query = "SELECT *, t2.num as 't2num', t1.num as 't1num'
            FROM `{$TABLE_PREFIX}test_table_two` AS `t2`
       LEFT JOIN `{$TABLE_PREFIX}test_table_one` AS `t1` ON t2.table_two_foreign_key = t1.num
           WHERE (t2.table_two_foreign_key = '6')
        ORDER BY t2.num ASC
           LIMIT 1";

And then they'll show up in the showme() output and you can refer to them in your query results.

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com

By Twocans - May 25, 2017

Thank you very much Dave,

I couldnt understand the logic of having to add the foreach as I wanted to only show one record on the page. Thanks for your help. 

<?php foreach ($records as $record): ?>

cheers

Kenny