• From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Topic: query help

I made the following query for a mod:

$result = $db->query("SELECT poster, poster_id, message, topic_id FROM ".$db->prefix."posts WHERE deleted=1");

I don't really have much experience with querys so could somebody please post a query that also fetches the topic subject from the "topics" table for the topic_id mentioned here?
Thanks

FluxBB - v1.4.8

lie2815's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: query help

$result = $db->query("SELECT p.poster, p.poster_id, p.message, p.topic_id, t.subject FROM ".$db->prefix."posts AS p INNER JOIN ".$db->prefix."topics AS t ON t.id=p.topic_id WHERE p.deleted=1");
  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: query help

Sweet, thanks. What is that "p." all the time?

FluxBB - v1.4.8

lie2815's Website

  • Registered: 2004-05-24
  • Posts: 449

Re: query help

p. means a parameter taken from the "posts" table, infact you can see "posts AS p" at some point in the string. and t.subject is the subject field from the topics table (topics AS t). A mysql's handbook or a real programmer would explain better smile

5 Reply by lie2815 (edited by lie2815 2007-05-12 14:25)

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: query help

Thanks. That's good enough. wink
Anyways, I have one more question:
In viewtopic.php, how can I edit the main query so that it only fetches/shows a post when "deleted=0"?

EDIT: "deleted" exists in the following three tables: subscriptions, topics, and posts.

FluxBB - v1.4.8

lie2815's Website

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: query help

anybody?

FluxBB - v1.4.8

lie2815's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: query help

$result = $db->query('SELECT u.email, u.title, u.url, u.location, u.use_avatar, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'users AS u ON u.id=p.poster_id INNER JOIN '.$db->prefix.'groups AS g ON g.g_id=u.group_id LEFT JOIN '.$db->prefix.'online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0) WHERE p.topic_id='.$id.' AND p.deleted=0 ORDER BY p.id LIMIT '.$start_from.','.$pun_user['disp_posts'], true) or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());
  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: query help

sweeeeeet. i think i forgot the "p." in front of deleted when I tried it.

FluxBB - v1.4.8

lie2815's Website