close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

MySQLI Nested Repeat

Thread began 6/17/2015 8:16 pm by JPowell | Last modified 4/17/2017 2:01 pm by Ray Borduin | 966 views | 9 replies |

JPowell

MySQLI Nested Repeat

I've done nested repeat regions before, but that was many years ago and never with MySQLI - I was hoping for some help. I have two tables. 1) Articles which include fields id, adate, name, article. 2) Image with fields id, fk (foreign key back to articles), name. Each article can have multiple images.

Query for articles:
<?php
$rsArticle = new WA_MySQLi_RS("rsArticle",$connPIW2015_i,10);
$rsArticle->setQuery("SELECT article.id, article.adate, article.name FROM article");
$rsArticle->execute();
?>

I created a query for images, a repeat region for the articles, another repeat region for the images, then manually moved the images query and repeat regions into the articles repeat region, so the code looks like this.

<?php
while(!$rsArticle->atEnd()) {
?>
<?php
$rsImages = new WA_MySQLi_RS("rsImages",$connPIW2015_i,0);
$rsImages->setQuery("SELECT images.id, images.fk, images.name FROM images");
$rsImages->execute();
?>
<div><?php echo($rsArticle->getColumnVal("adate")); ?>,
<?php echo($rsArticle->getColumnVal("name")); ?>

<?php
while(!$rsImages->atEnd()) {
?>
<p><?php echo($rsImages->getColumnVal("name")); ?></p>
<?php
$rsImages->moveNext();
}
$rsImages->moveFirst(); //return RS to first record
?>
</div>
<?php
$rsArticle->moveNext();
}
$rsArticle->moveFirst(); //return RS to first record
?>

Not sure where to go from here. The rsImages query needs some sort of WHERE statement, but not sure what that is.

Please help,
Thanks - Jeff.

Sign in to reply to this post

Ray BorduinWebAssist

It sounds like you would need to filter the images table using the fk column and compare it to the value from the article recordset. Something like:

php:
<?php

$rsImages 
= new WA_MySQLi_RS("rsImages",$connPIW2015_i,0);
$rsImages->setQuery("SELECT images.id, images.fk, images.name FROM images WHERE fk = ?");
$rsImages->bindParam("i""".($rsArticle->getColumnVal("id"))  ."""-1"); //LoopParam
$rsImages->execute();
?>
Sign in to reply to this post

JPowell

Thanks, you guys are awesome as usual. Needed to change $rsImages in the bindParam line to $rsArticle, and it works perfectly.

Sign in to reply to this post

info298111

Error on example

HI, I have looked and tried to work this, but I get this error "Parse error: syntax error, unexpected '""' (T_CONSTANT_ENCAPSED_STRING)"
in the line with the bindParam:
<?php
$sponsoren_img = new WA_MySQLi_RS("sponsoren_img",$miedema_i,0);
$sponsoren_img->setQuery("SELECT * FROM sponsoren_img WHERE id_alb = ? ORDER BY volgorde ASC");
$rsImages->bindParam("i", "".($sponsoren_alb->getColumnVal("id_alb"))  ."", "-1");
$sponsoren_img->execute();
?>

Sign in to reply to this post

Ray BorduinWebAssist

I feel like the error might be coming from another part of the page. I don't see an issue in the code you have provided.

Sign in to reply to this post

info298111

Error

I checked again, but could not find any issue either, but the error perstists. The use code is below:
<?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;
}
}

$hoofdfoto = new WA_MySQLi_RS("hoofdfoto",$miedveld,1);
$hoofdfoto->setQuery("SELECT * FROM hoofdfoto");
$hoofdfoto->execute();?>
<?php
$sponsor_main = new WA_MySQLi_RS("sponsor_main",$miedema_i,1);
$sponsor_main->setQuery("SELECT * FROM sponsor_main");
$sponsor_main->execute();?>
<?php
$sponsoren_alb = new WA_MySQLi_RS("sponsoren_alb",$miedema_i,1);
$sponsoren_alb->setQuery("SELECT * FROM sponsoren_alb ORDER BY volgorde ASC");
$sponsoren_alb->execute();?>
<?php
$sponsoren_img = new WA_MySQLi_RS("sponsoren_img",$miedema_i,0);
$sponsoren_img->setQuery("SELECT * FROM sponsoren_img WHERE id_alb = ? ORDER BY volgorde ASC");
$rsImages->bindParam("i", "".($sponsoren_alb->getColumnVal("id_alb"))  ."", "-1");
$sponsoren_img->execute();
?>

Sign in to reply to this post

Ray BorduinWebAssist

Please attach a copy of the page itself. I think the error may be lower on the page? Maybe it is in an include file.

I'd debug this by removing sections of the code until the error disappears. That will allow you to narrow down the error to a particular line of code and then it will become easier to look for the problem once you have the line narrowed down.

Sign in to reply to this post

info298111

Error

Removed almost everything. Still no joy. File is attached. Any assistance in this would be much appreciated.

Sign in to reply to this post

Ray BorduinWebAssist

I'd need FTP access to debug.

You remove all of the server code and the page still won't open? Same error?

Sign in to reply to this post

info298111

I redid it like this:
<?php
$sponsoren_img = new WA_MySQLi_RS("sponsoren_img",$miedema_i,0);
$sponsoren_img->setQuery("SELECT * FROM sponsoren_img WHERE id_alb = ?");
$sponsoren_img->bindParam("i", "".($sponsoren_alb->getColumnVal("id_alb")) ."", "-1"); //colname
$sponsoren_img->execute();
?>

And now I get no errors anymore

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...