Need for more examples using setDbLoop

[NickM]

I would like to use MySQL and VLib to create a simple list e.g.

Main Heading 1
  SubHead1
  SubHead2
  Subhead3

Main Heading 2
  AnotherSubHead1
  AnotherSubHead2
  AnotherSubHead3

I'm stumped. How do you access the Main Headings from one table and then list the SubHeadings below?

I can do it with PHP and print statements, but I want to use setDbLoop (it seems to be the easiest way to get info from tables using VLib).
At the moment I can get a list of all the Main Headings, followed by a list of all the SubHeadings (when using setDbLoop) - obviously not quite what I want.

If someone could provide an example of the Template and the PHP code to do this, it would be absolutely fantastic!

Please don't ask me to go to another forum for the PHP - I've spent all day trying to nut this out (which has made me a little grumpy) - and I believe the answer lies within this forum.

Thanks.

[releasedj]

It's hard to give an example without a general idea of what the table structure is like in MySQL.

Let me know what the tables are like and I'll give it a crack, no problem! :)

Kind regards,

Kelvin

PS: You won't be able to do this with setDbLoop() if there's more than 1 call to the db, unless you know how many Main headings you require.

[RNilsson]

If you want to retrieve info from more then one table in only one sql-query, you can use inner join, left or right join.

I'm no star on complex mysql-stuff, but if you post the dbstructure and how you want to get and print the info, then we most certainly develop a good sql-query...

[NickM]

An example....Here goes:

two tables:

table 1 has 2 fields and some records:
Fields---CODE -------- DESCRIPTION
----------news--------- News Stories
----------sport----------Latest Sports

table2  has three fields and some records
Fields:---PAGE----------HEADLINE-------------STORY TEXT
----------news-----------Man gives birth-------A man today
----------news-----------Fire!-------------------Reports of a fire
----------sport-----------World Champion-----Three days to go
----------sport-----------Baseball is bad-------Scientists believe


Here is what I would like to see:

----News Stories------------------------------
Man gives birth ....link to Story Text
Fire!....................link to Story Text

----Latest Sport------------------------------
World Champion....link to Story Text
Baseball is bad......link to Story Text


In PHP it's a matter of two while loops, one inside the other and a few print statements.
I have a feeling (from your previous post) that setDbLoop can't be used here because there are two calls to the Db?

If you could provide a solution I would be greatful - and it may even become an example for your docs :wink:

[releasedj]

Here's an example of how you can get this to work. (It's probably worth copying this code into a code editor so that you can see it clearly.)

First of all the PHP file:

Code: [Select]




?php

include_once '../vLIB/vlibTemplate.php';



$tmpl = new vlibTemplate'./news.tmpl;



$cnx = mysql_connect 'localhost', ***, ***

mysql_select_db'released_test', $cnx;



$sql1 = 'SELECT code,description FROM table1'; // get the categories

$result1 = mysql_query $sql1, $cnx;



$tmpl-newLoop'NEWS'; // name of outer loop

while $row1 = mysql_fetch_assoc$result1



  //inner loop

  $sql2 = "SELECT page, headline,story FROM table2 WHERE page= ". $row1code.";

  $result2 = mysql_query$sql2, $cnx;

  $innerloop = array;

  while $row2 = mysql_fetch_assoc$result2

   $innerloop = array'page'=$row2'page','headline'=$row2'headline','story'=$row2'story';

  



  // Add row WITH inner loop to vlibTemplate

  $row_to_add = array

      'code'=$row1'code', // from table 1

      'description'=$row1'description', // from table 1

      'stories' = $innerloop // from table 2 innerloop is called stories

      ;

  $tmpl-addRow$row_to_add, 'NEWS';



$tmpl-addLoop'NEWS';



$tmpl-pparse;

?



.. and a very simple template will look like:

Code: [Select]




html

body



tmpl_loop name=NEWS

tmpl_var name=descriptionbr /

------------------------------br /

  tmpl_loop name=stories

   btmpl_var name=headline/b - tmpl_var name=storybr /

  /tmpl_loop

  br /br /

/tmpl_loop



/body

/html





I hope this helps! If not then let me know!

Kind regards,

Kelvin

[NickM]

I'll be the first to admit that the solution is beautifully logical - but I would never have thought of it (my PHP isn't strong enough yet).

[NickM]

It Works, Many thanks.
The solution is a little more involved than I thought.
I'll work thru the code over the next few days, but to start with--In the template you have:
 <tmpl_loop name="stories">
Yet I don't see the "stories" loop anywhere in the PHP.
How do you manage to make it appear in the template without doing what you did for NEWS in the PHP i.e.
$tmpl->newLoop('NEWS');

I hope you don't mind if I pose some -perhaps obvious- questions over the next few days.

[releasedj]

Ask as many questions as you want!

Take a look at the vlibTemplate docs in the TMPL_LOOP section (Look here).

You'll see an example of the loop structure there. If you take time to read through it you should be able to grasp the idea of how a loop is built using a PHP array.

As for the stories loop, you'll see in the PHP code, a line like this:

Code: [Select]


'stories' = $innerloop



.. this is assigning another loop structure to a loop variable and this is what is used to create an inner loop.

Let me know if this still doesn't make sense.

Regards,

Kelvin

[NickM]

Thanks. It's all coming together.
Your examples and docs make a lot more sense now that I can relate it to this example.