excute a multidemenisonal sql code in smarty

Question

I have been trying to do this for ages now and I could not find a good solution on how to do it! Smarty is making things harder for me! Nevertheless I want to show it in Smarty because all my programing are in Smarty. I looked everywhere for a solution or something similar to what I want but I could not find any, I am guessing somebody in here can give me an example of what I want or give me a code that works on the example that I am giving. Anyways, let me show you what I have:

I have a table that looks like this (table name: chapter):

date_add    manga_name  manga_title chapter_num 
12/23/2012  One Piece      OnePiece    002
12/23/2012  One Piece      OnePiece    003
12/22/2012  Naruto         Naru        002

in the tpl file lets say that I have this

{section name=chp loop=$chpt}   

{$chpt[chp].date_add}
{$chpt[chp].manga_name}</a>
<a href="{$chpt[chp].manga_title}/{$chpt[chp].chapter_num} ">{"%03d"|sprintf:$chpt[chp].chapter_num} </a>

{/section}

which will show in this way in real life :

12/23/2012  One Piece  [0002][withURL]
12/23/2012  One Piece  [0003][withURL]
12/22/2012  Naruto [0002][withURL]

How I want to show it is like this :

One Piece :
0002[withURL] 12/23/2012
0003[withURL] 12/23/2012
Naruto : 
0002[withURL] 12/22/2012

I tried many ways to execute it but failed! I looked for some examples and could not find any! What I need is an example that is similar to mine and how to do it with smarty! Smarty is making things harder because I do not know how to use multidimensional sections! and what to put for my MYSQL query! There is got to be a way to do it! Here is my PHP code with SQL statment:

$chapter = $db->query("SELECT  date_add,  manga_name, manga_title, chapter_num FROM chapter ORDER BY ch_Id DESC Limit 0,6");
while($chpt = $db->fetch_array($chapter))
{$chps[] = $chpt;}
$smarty->assign('chpt' , $chps);

This is just a sample of what I have, If you can give me a soultion for it then I can understand the rest.

Basically I want to remove duplicated manga_name results and add all the chapter_num that belongs to the manga_name under them.

score 1 · Accepted Answer · edited May 23 '17 at 11:56

I can think of two options for you. One is to format the query results so that chapters are grouped by the manga_name. A further step will be to use stored procedures in mysql to generate and the formatted query results. OR you can use what I call previous/current check to skip same manga name in the loops.

Here is raw untested sample code for the second option

{$prevMangaName = ''}
{foreach $chapters as $chapter}
    {if $chapter.manga_name != $prevMangaName}
        {$chapter.manga_name|capitalize}:<br>
    {/if}        
    {$chapter.chapter_num}[withURL] {$chapter.date_add}[withURL]<br>
    {$prevMangaName = $chapter.manga_name}
{/foreach}

What is happening here is that a record of the previous manga_name is kept and checkd against the current name and is printed only if they are not the same.

OPTION 1 solution. [PHP] First lets get data from DB concatenating all chapter_num(s) into a single string joined by comma. (You could easily avoid this trouble if you properly implement storing chapters as concatenated string directly in the DB Table)

[_PSEUDO_CODE_ SOMEWHERE IN YOUR PHP]
$chapters = $db->query("select manga_name,group_concat(chapter_num separator ',') as chapter_numbers from chapter");

[SOMEWHERE IN YOUR TEMPLATE FILE]
// convert the chapter_numbers string into array, loop over and print results
{foreach $chapters as $chapterRow}
    {$chapterRow.manga_name|capitalize}:<br>
    {$chapterNumberArray = $chapterRow.chapter_numbers|explode:','}
    {foreach $chapterNumberArray as $chapterNumber}
        {$chaperNumber}[withURL] {$chapterRow.date_add}[withURL]<br>
    {/foreach}
{/foreach}

References:

mysql group_concat

Hope it helps

I did not how you got the second idea, but I must confess you gave a very intelligent solution! But I still like to see on how you can do it with the first method — syrkull, Dec 24 '12 at 05:17
The first method is more involving but the overview is something like this. You query the DB for manga name and use a subquery to get all chapter_nums under each manga_name by concatenating the chapter_nums with (lets say) :: then in PHP you will explode that string with the same delimiter [::] to generate the arrays for each manga_name. Hope you get what I am trying to say. If not I could just write a blog and point you to it. — shawndreck, Dec 24 '12 at 05:29
if you have any references or any examples for it, it will really help me. I know what you said, but I do not know how to do it! what to put in PHP what to put in .tpl file.. and so on. — syrkull, Dec 24 '12 at 16:52
I edited the answer to reflect what we have been discussing here. — shawndreck, Dec 25 '12 at 15:56

excute a multidemenisonal sql code in smarty

1 Answers1