joinTable and Undefined variable
8 posts by 2 authors in: Forums > CMS Builder
Last Post: May 25, 2017 (RSS)
By Twocans - May 9, 2017 - edited: May 10, 2017
Hello,
I have been following this post here on using the joinTable
https://www.interactivetools.com/forum/forum-posts.php?postNum=2229299#post2229299
I have created a page similar but no matter what I try I get the error of Notice: Undefined variable
I have created a page here for you to see the results
http://www.camteach.com/results.php?loc=5
I have also added the
<?php foreach ($test_table_twoRecords as $record): ?>
<?php showme($record); ?>
<?php endforeach ?>
==================================================//
The complete page code in itself is
==================================================//
HTML Page Start =====================================//
<?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' => false,
'allowSearch' => false,
'limit' => '1',
));
$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'",
'debugSql' => true,
'allowSearch' => false,
));
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>
<body>
<!-- for each statement start: -->
<?php foreach ($test_table_twoRecords as $record): ?>
<?php showme($record); ?>
<?php endforeach ?>//
<!-- for each statement start: -->
<br>
<br>
<br>
<br>#####################//
<br>
<br><?php echo htmlencode($test_table_twoRecord['tttwo_name']) ?>
<br>
#####################\\
<br>
<br>
<br>
<br>
</body>
</html>
HTML Page End =====================================//
I have tried everything to call the variable but wow, no joy
I have noticed were I to call a variable like this <?php echo $record['tttwo_name']; ?> it will show BUT when I remove the <?php showme($record); ?> then all i get again is a Undefined variable error.
I have also tried <?php echo htmlencode($test_table_two['tttwo_name']) ?> which didn't work either.
Does the joinTable still work, I am using PHP VersionPHP v5.6.30
I would be grateful for any input.
Cheers
Kenny
=====================\\
These are my two tables below
=====================//
CREATE TABLE `cmsb_test_table_one` (
`num` int(10) unsigned NOT NULL AUTO_INCREMENT,
`createdDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`createdByUserNum` int(10) unsigned NOT NULL,
`updatedDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updatedByUserNum` int(10) unsigned NOT NULL,
`test_table_one_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`num`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
CREATE TABLE `cmsb_test_table_two` (
`num` int(10) unsigned NOT NULL AUTO_INCREMENT,
`createdDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`createdByUserNum` int(10) unsigned NOT NULL,
`updatedDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updatedByUserNum` int(10) unsigned NOT NULL,
`tttwo_name` varchar(255) DEFAULT NULL,
`table_two_foreign_key` int(11) DEFAULT NULL,
PRIMARY KEY (`num`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
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!
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.
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!
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