Oh no! Where's the JavaScript?
Your Web browser does not have JavaScript enabled or does not support JavaScript. Please enable JavaScript on your Web browser to properly view this Web site, or upgrade to a Web browser that does support JavaScript.
Not a member yet? Click here to register.
Forgot Password?

Help Needed - Combine 2 Scripts Into One

Asked Modified Viewed 3,493 times
G
Grimloch
G
Energy can neither be created nor destroyed; only transformed !
  • Senior Member, joined since
  • Contributed 722 posts on the community forums.
  • Started 141 threads in the forums
  • Started this discussions
  • Answered 2 questions
asked
Senior Member

Hey guys; it's the old man again. I need some help trying to combine two different scripts into one. The first one displays main topics:
$result = dbquery("SELECT * FROM ".DB_GRIMS_BLOG_TOPICS." WHERE topic_sub='0' ORDER BY topic_title ASC");
if (dbrows($result)) {
$cnt = 0;
   while($data = dbarray($result)) {
      $id = $data['topic_id'];
      $title = $data['topic_title'];
         $result1 = dbquery("SELECT * FROM ".DB_GRIMS_BLOG_POST." WHERE topic_id='$id'");
         $num_rows1 = dbrows($result1);
echo "<tr><td><a class='lnk-side' href='".BASEDIR."grims_blog/topics_page.php?topic_id=".$id."'>$title</a><span class='lnk-side'>($num_rows1)</span></td></tr>\n";
   }
$cnt++;
}

The second one displays sub-topics:
$result = dbquery("SELECT * FROM ".DB_GRIMS_BLOG_TOPICS." WHERE topic_sub='1' ORDER BY topic_parent_title ASC");
if (dbrows($result)) {
$cnt = 0;
   while($data = dbarray($result)) {
      $id = $data['topic_id'];
      $title = $data['topic_title'];
      $parent = $data['topic_parent_title'];
         $result1 = dbquery("SELECT * FROM ".DB_GRIMS_BLOG_POST." WHERE topic_id='$id'");
         $num_rows1 = dbrows($result1);
echo "<tr><td><a class='lnk-side' href='".BASEDIR."grims_blog/topics_page.php?topic_id=".$id."'>$parent >$title</a><span class='lnk-side'>($num_rows1)</span></td></tr>\n";
   }
$cnt++;
}

The only difference between the 2 scripts is the 'topic_sub' field.
This first image is the way I need it to be. The second image is the way it is now.
Edited by Grimloch on 05-05-2021 14:45,
Grimloch attached the following image:
Image not found Image not found
0 replies

15 posts

G
Grimloch
G
Energy can neither be created nor destroyed; only transformed !
  • Senior Member, joined since
  • Contributed 722 posts on the community forums.
  • Started 141 threads in the forums
  • Started this discussions
  • Answered 2 questions
answered
Senior Member

I've been thinking about this since I posted it. Since there are 2 and only 2 conditions/results of the query I think I can do this with an if else setup. Can't wait to get home tonight and try it.

UPDATE: Well I tried it and it won't work. It'll show all topics and sub-topics just fine but I cannot figure out how to order the output to the desired result.

Sure would be nice to get some input from some of you php experts. :|
Edited by Grimloch on 05-05-2021 14:51,
0 replies
G
Grimloch
G
Energy can neither be created nor destroyed; only transformed !
  • Senior Member, joined since
  • Contributed 722 posts on the community forums.
  • Started 141 threads in the forums
  • Started this discussions
  • Answered 2 questions
answered
Senior Member

I am really disappointed that no one will even take a stab at this problem to help me out.
0 replies
R
Anonymous User
R
Anonymous User 367
  • Veteran Member, joined since
  • Contributed 939 posts on the community forums.
  • Started 2 threads in the forums
  • Answered 20 questions
answered
Veteran Member

Well, I'm focusing only on issues related to core.


Anyway, did you tried LEFT JOIN and nested loops?
1 reply
G
Grimloch
G
Energy can neither be created nor destroyed; only transformed !
  • Senior Member, joined since
  • Contributed 722 posts on the community forums.
  • Started 141 threads in the forums
  • Started this discussions
  • Answered 2 questions
answered
Senior Member

Come on guys please help me out with this. I just cannot get a handle on doing a JOIN or INNER JOIN on the same table to get the desired results.
Here is the script that produces the output as currently seen on my blog at https://blog.whisperwillow.net/grims_.../index.php
<?php
echo "<div class='col-sm-12'>\n";
echo "<table width='100%' border='0'><tr><td><span class='hdspan2'><b>".$locale['gb_810']."</b></span></td></tr></table>\n";
echo "<table align='center' width='80%' border='0'>\n";
$result = dbquery("SELECT * FROM ".DB_GRIMS_BLOG_TOPICS."");
   while($data = dbarray($result)) {
      $id = $data['topic_id'];
      $title = $data['topic_title'];
      $sub = $data['topic_parent'];
         $result1 = dbquery("SELECT * FROM ".DB_GRIMS_BLOG_POST." WHERE topic_id='$id'");
         $num_rows = dbrows($result1);
if($sub>0) {
echo "<tr><td width='15'></td><td>»» <a class='lnk-side' href='".BASEDIR."grims_blog/topics_page.php?topic_id=".$id."'>$title</a><span style='font-size:11px;color:white;'> [$num_rows posts]</span></td></tr>\n";
} else {
echo "<tr><td colspan='2'><a class='lnk-side' href='".BASEDIR."grims_blog/topics_page.php?topic_id=".$id."'>$title</a><span style='font-size:11px;color:white;'> [$num_rows posts]</span></td></tr>\n";
   }
}
echo "</table><p></div>\n";
?>

Table data image and browser output image:
I need to concat the sub-topics to be underneath their associated main topic in the correct order as seen in one of the images at the top of this post. I just cannot figure out how to do it. I think I can figure out the table joins but then how to configure the output from that is beyond my ability. Please Help!
Edited by Grimloch on 16-05-2021 18:57,
Grimloch attached the following image:
Image not found Image not found
0 replies
D
douwe_yntema
D
  • Senior Member, joined since
  • Contributed 667 posts on the community forums.
  • Started 57 threads in the forums
  • Answered 1 question
answered
Senior Member

I.m.o the seconde query inside the loop does nothing more than returning 1 row. Should it not be something like: topic_parent=id?
0 replies
G
Grimloch
G
Energy can neither be created nor destroyed; only transformed !
  • Senior Member, joined since
  • Contributed 722 posts on the community forums.
  • Started 141 threads in the forums
  • Started this discussions
  • Answered 2 questions
answered
Senior Member

Look again; the first query returns topics and sub-topics. The second query returns POSTS by the topic id. That's how I get the post count for each topic/sub-topic.
Edited by Grimloch on 17-05-2021 00:28,
0 replies
D
douwe_yntema
D
  • Senior Member, joined since
  • Contributed 667 posts on the community forums.
  • Started 57 threads in the forums
  • Answered 1 question
answered
Senior Member

Did some googling and tests with the following code:



$result = dbquery("SELECT child.id, child.title as subtitle, parent.title as parenttitle FROM test AS parent JOIN test AS child ON (child.parent = parent.id)");

while ($data = dbarray($result)) {
 echo $data['parenttitle'];
 echo "---".$data['subtitle']."<br>";
}
Edited by douwe_yntema on 17-05-2021 09:34,
douwe_yntema attached the following image:
Image not found Image not found
1 reply
D
douwe_yntema
D
  • Senior Member, joined since
  • Contributed 667 posts on the community forums.
  • Started 57 threads in the forums
  • Answered 1 question
answered
Senior Member

Can you send me a SQL-dump of your database?
0 replies
G
Grimloch
G
Energy can neither be created nor destroyed; only transformed !
  • Senior Member, joined since
  • Contributed 722 posts on the community forums.
  • Started 141 threads in the forums
  • Started this discussions
  • Answered 2 questions
answered
Senior Member

If you look at Post#6 there is an image of the db table.
1 reply
G
Grimloch
G
Energy can neither be created nor destroyed; only transformed !
  • Senior Member, joined since
  • Contributed 722 posts on the community forums.
  • Started 141 threads in the forums
  • Started this discussions
  • Answered 2 questions
answered
Senior Member

Tell me if this will work for the join. Even if it will I have no idea how to gather and display the data below the query. I'm grabbing at straws here folks.
$result = dbquery("SELECT tp.topic_id, tp.topic_title, tp.topic_parent, sp.topic_title, sp.topic_parent FROM ".DB_GRIMS_BLOG_TOPICS." tp
JOIN ".DB_GRIMS_BLOG_TOPICS." sp.topic_id, sp.topic_title, sp.topic_parent WHERE tp.topic_id = sp.topic_parent");
1 reply
G
Grimloch
G
Energy can neither be created nor destroyed; only transformed !
  • Senior Member, joined since
  • Contributed 722 posts on the community forums.
  • Started 141 threads in the forums
  • Started this discussions
  • Answered 2 questions
answered
Senior Member

OK. I tried it your way:
echo "<table align='center' width='80%' border='0'><tr><td>\n";
$result = dbquery("SELECT child.topic_id, child.topic_title as subtitle, parent.topic_title as parenttitle FROM ".DB_GRIMS_BLOG_TOPICS." AS parent
JOIN ".DB_GRIMS_BLOG_TOPICS." AS child ON (child.topic_parent = parent.topic_id");

   while ($data = dbarray($result)) {
       echo $data['parenttitle'];
       echo "---".$data['subtitle']."<br>";
   }

echo "</td></tr></table><p></div>\n";

And here is the result:
[17-May-2021 07:42:05 America/Chicago] PHP Fatal error: Uncaught Error: Call to a member function setFetchMode() on null in /home/whisperw/blog.whisperwillow.net/includes/db_handlers/pdo_functions_include.php:120
Stack trace:
#0 /home/whisperw/blog.whisperwillow.net/grims_blog/include/latest_topics.php(7): dbarray(NULL)
#1 /home/whisperw/blog.whisperwillow.net/grims_blog/include/modules.php(6): include('/home/whisperw/...')
#2 /home/whisperw/blog.whisperwillow.net/grims_blog/index.php(43): include('/home/whisperw/...')
#3 {main}
 thrown in /home/whisperw/blog.whisperwillow.net/includes/db_handlers/pdo_functions_include.php on line 120
[17-May-2021 07:42:07 America/Chicago] PHP Fatal error: Uncaught Error: Call to a member function setFetchMode() on null in /home/whisperw/blog.whisperwillow.net/includes/db_handlers/pdo_functions_include.php:120
Stack trace:
#0 /home/whisperw/blog.whisperwillow.net/grims_blog/include/latest_topics.php(7): dbarray(NULL)
#1 /home/whisperw/blog.whisperwillow.net/grims_blog/include/modules.php(6): include('/home/whisperw/...')
#2 /home/whisperw/blog.whisperwillow.net/grims_blog/index.php(43): include('/home/whisperw/...')
#3 {main}
 thrown in /home/whisperw/blog.whisperwillow.net/includes/db_handlers/pdo_functions_include.php on line 120
[18-May-2021 07:25:37 America/Chicago] PHP Fatal error: Uncaught Error: Call to a member function setFetchMode() on null in /home/whisperw/blog.whisperwillow.net/includes/db_handlers/pdo_functions_include.php:120
Stack trace:
#0 /home/whisperw/blog.whisperwillow.net/grims_blog/include/latest_topics.php(8): dbarray(NULL)
#1 /home/whisperw/blog.whisperwillow.net/grims_blog/include/modules.php(6): include('/home/whisperw/...')
#2 /home/whisperw/blog.whisperwillow.net/grims_blog/index.php(43): include('/home/whisperw/...')
#3 {main}
 thrown in /home/whisperw/blog.whisperwillow.net/includes/db_handlers/pdo_functions_include.php on line 120
0 replies
D
douwe_yntema
D
  • Senior Member, joined since
  • Contributed 667 posts on the community forums.
  • Started 57 threads in the forums
  • Answered 1 question
answered
Senior Member

You have a typo in your query, the closing parenthesis at the end is not good, it should be: )");

See my example
0 replies
G
Grimloch
G
Energy can neither be created nor destroyed; only transformed !
  • Senior Member, joined since
  • Contributed 722 posts on the community forums.
  • Started 141 threads in the forums
  • Started this discussions
  • Answered 2 questions
answered
Senior Member

This almost works but is not complete:
$result = dbquery("SELECT child.topic_id, child.topic_title as subtitle, parent.topic_title as parenttitle FROM ".DB_GRIMS_BLOG_TOPICS." AS parent
JOIN ".DB_GRIMS_BLOG_TOPICS." AS child ON (child.topic_parent = parent.topic_id)");
   while ($data = dbarray($result)) {
      $id = $data['parent(topic_id)'];
      $maintitle = $data['parenttitle'];
      $subtitle = $data['subtitle'];
if ($maintitle) {
       echo "<tr><td colspan='2'><a class='lnk-side' href='".BASEDIR."grims_blog/topics_page.php?topic_id=".$id."'>$maintitle</a></td></tr>\n";
    }
if ($subtitle) {
       echo "<tr><td width='15'></td><td>&raquo;&nbsp;<a class='lnk-side' href='".BASEDIR."grims_blog/topics_page.php?topic_id=".$id."'>$subtitle</td></tr>\n";
   }
}

I need a distinct 'topic_id' for the links. Also I can't seem to get the POST COUNT to work right because of not having a distinct 'topic_id'. Also I need for the main topics which contain posts(with a topic_parent of zero) to be displayed only once with the sub-topics underneath. Here is an image of the current results:
Grimloch attached the following image:
Image not found
0 replies
D
douwe_yntema
D
  • Senior Member, joined since
  • Contributed 667 posts on the community forums.
  • Started 57 threads in the forums
  • Answered 1 question
answered
Senior Member

Because there is always a maintitle and a subtitle, both links are displayed. That's why you have the maintitles twice.
1 reply
G
Grimloch
G
Energy can neither be created nor destroyed; only transformed !
  • Senior Member, joined since
  • Contributed 722 posts on the community forums.
  • Started 141 threads in the forums
  • Started this discussions
  • Answered 2 questions
answered
Senior Member

Thanks for trying to help douwe. Your code actually works just not in the way I need. I guess I'll just stick with the added dbfield and will just have to modify it manually on sub-topic changes.
0 replies

Statistics

  • Views 0 views
  • Posts 15 posts
  • Votes 0 votes
  • Topic users 3 members

3 participants

G
G
Energy can neither be created nor destroyed; only transformed !
  • Senior Member, joined since
  • Contributed 722 posts on the community forums.
  • Started 141 threads in the forums
  • Started this discussions
  • Answered 2 questions
D
D
  • Senior Member, joined since
  • Contributed 667 posts on the community forums.
  • Started 57 threads in the forums
  • Answered 1 question
R
R
Anonymous User 367
  • Veteran Member, joined since
  • Contributed 939 posts on the community forums.
  • Started 2 threads in the forums
  • Answered 20 questions

Notifications

Track thread

You are not receiving notifications from this thread.

Related Questions

Not yet