[SOLVED] Need help with PHP/mySQL problem.

Chesso · May 16, 2007

Hello, I may need help with similar things like this in the future concerning PHP/mySQL as I have some doubt about certain ways I have done things in the past (I am trying to build things as small/dynamic and efficient as I can).

Right now I have a little quarm about performing 2 queried loops of mysql, when I think with a bit of help/thought I may be able to pull it off somehow with just 1.

Right now for my topic_display page (where I display forum topics for a category), I do something along these lines:

$result = mysql_query("SELECT id, topic_id, title, creator, posts, last_post, last_post_by, locked, pinned FROM forum_threads ORDER BY thread_standing DESC LIMIT " .$i. ",$start_from");
  while ($r = mysql_fetch_array($result))
  {
   if ($r['topic_id'] == $forum_topic_id) 
   {
// If locked, then show locked image, if not then null value.
if ($r['locked'] == "true") { $temp_locked = "<img src=\"" .image_url. "locked_image.gif\" border=\"0\" />  "; }
else { $temp_locked = ""; }

    // here I echo out the actual thread data etc.
   }
  }

This basically grabs all threads within a certain limit (for paging) and displays them (title, total posts, last post user/time/date), and if it's status is locked I insert a little locked image.

I wish to do the same for pinned topics, however pinned topics should obviously show above everything else in a descending (most recent displayed first) fashion.

I could do this easy right now by doing an extra looped query like this one first and tell it to ignore those specific entries on purpose. But is there perhaps a better way to go about this?

phast1 · May 16, 2007

If your first query already has a "pinned" field that contains a 1 or 0 depending on whether or not the thread is "pinned", then you should be able to just add to the ORDER BY part of the query to cause it to first sort by "pinned" and then sort by "thread_standing" afterwards, such as:

$result = mysql_query("SELECT id, topic_id, title, creator, posts, last_post, last_post_by, locked, pinned FROM forum_threads ORDER BY pinned, thread_standing DESC LIMIT " .$i. ",$start_from");

That should do what you want without needing any extra queries..

radar · May 16, 2007

Not a bad query -- If on my site i were using even a single piece of pre-written code i would have to steal that query and use it for my site...

but yeah not too shabby there, definately should work...

Chesso · May 16, 2007

With that addition to the query, the pinned topic is pushed to the very bottom.

Is there a modification to reverse this?

I'd assume using ASC rather than DESC would reverse that, but I would need ASC for pinned and DESC for the other.

So far most of my queries have been considerably simple (which I try to keep it that way if I can), so I'm not too experience with building slightly more complex queries like this.

radar · May 16, 2007

here is how my friend does it -- don't ask me to help with understanding cuz i sure don't understand it lol -- of course he's been programming for like 30 years so yeah..

<?php
/ If store_reverse, then first obtain topics, then stickies, else the other way around...
// Funnily enough you typically save one query if going from the last page to the middle (store_reverse) because
// the number of stickies are not known
$sql = $db->sql_build_query('SELECT', $sql_array);
$sql = str_replace('{SQL_TOPIC_TYPE}', ($store_reverse) ? POST_NORMAL : POST_STICKY, $sql);
$result = $db->sql_query_limit($sql, $sql_limit, $sql_start);

$shadow_topic_list = array();
$num_rows = 0;
while ($row = $db->sql_fetchrow($result))
{
if ($row['topic_status'] == ITEM_MOVED)
{
	$shadow_topic_list[$row['topic_moved_id']] = $row['topic_id'];
}

$rowset[$row['topic_id']] = $row;
$topic_list[] = $row['topic_id'];
$num_rows++;
}
$db->sql_freeresult($result);

// Active topics?
if ($s_display_active && sizeof($active_forum_ary))
{
	// Generate topic forum list...
	$topic_forum_list = array();
	foreach ($rowset as $t_id => $row)
	{
		$topic_forum_list[$row['forum_id']]['forum_mark_time'] = ($config['load_db_lastread'] && $user->data['is_registered'] && isset($row['forum_mark_time'])) ? $row['forum_mark_time'] : 0;
		$topic_forum_list[$row['forum_id']]['topics'][] = $t_id;
	}

	if ($config['load_db_lastread'] && $user->data['is_registered'])
	{
		foreach ($topic_forum_list as $f_id => $topic_row)
		{
			$topic_tracking_info += get_topic_tracking($f_id, $topic_row['topics'], $rowset, array($f_id => $topic_row['forum_mark_time']), false);
		}
	}
	else if ($config['load_anon_lastread'] || $user->data['is_registered'])
	{
		foreach ($topic_forum_list as $f_id => $topic_row)
		{
			$topic_tracking_info += get_complete_topic_tracking($f_id, $topic_row['topics'], false);
		}
	}

	unset($topic_forum_list);
}
else
{
	if ($config['load_db_lastread'] && $user->data['is_registered'])
	{
		$topic_tracking_info = get_topic_tracking($forum_id, $topic_list, $rowset, array($forum_id => $forum_data['mark_time']), $global_announce_list);
		$mark_time_forum = (!empty($forum_data['mark_time'])) ? $forum_data['mark_time'] : $user->data['user_lastmark'];
	}
	else if ($config['load_anon_lastread'] || $user->data['is_registered'])
	{
		$topic_tracking_info = get_complete_topic_tracking($forum_id, $topic_list, $global_announce_list);

		if (!$user->data['is_registered'])
		{
			$user->data['user_lastmark'] = (isset($tracking_topics['l'])) ? (int) (base_convert($tracking_topics['l'], 36, 10) + $config['board_startdate']) : 0;
		}
		$mark_time_forum = (isset($tracking_topics['f'][$forum_id])) ? (int) (base_convert($tracking_topics['f'][$forum_id], 36, 10) + $config['board_startdate']) : $user->data['user_lastmark'];
	}
}

$s_type_switch = 0;
foreach ($topic_list as $topic_id)
{
	$row = &$rowset[$topic_id];

	// This will allow the style designer to output a different header
	// or even separate the list of announcements from sticky and normal topics
	$s_type_switch_test = ($row['topic_type'] == POST_ANNOUNCE || $row['topic_type'] == POST_GLOBAL) ? 1 : 0;

	// Replies
	$replies = ($auth->acl_get('m_approve', $forum_id)) ? $row['topic_replies_real'] : $row['topic_replies'];

	if ($row['topic_status'] == ITEM_MOVED)
	{
		$topic_id = $row['topic_moved_id'];
		$unread_topic = false;
	}
	else
	{
		$unread_topic = (isset($topic_tracking_info[$topic_id]) && $row['topic_last_post_time'] > $topic_tracking_info[$topic_id]) ? true : false;
	}

	// Get folder img, topic status/type related information
	$folder_img = $folder_alt = $topic_type = '';
	topic_status($row, $replies, $unread_topic, $folder_img, $folder_alt, $topic_type);

	// Generate all the URIs ...
	$view_topic_url = append_sid("{$root_path}viewtopic.$phpEx", 'f=' . (($row['forum_id']) ? $row['forum_id'] : $forum_id) . '&t=' . $topic_id);

	$topic_unapproved = (!$row['topic_approved'] && $auth->acl_get('m_approve', $forum_id)) ? true : false;
	$posts_unapproved = ($row['topic_approved'] && $row['topic_replies'] < $row['topic_replies_real'] && $auth->acl_get('m_approve', $forum_id)) ? true : false;
	$u_mcp_queue = ($topic_unapproved || $posts_unapproved) ? append_sid("{$root_path}mcp.$phpEx", 'i=queue&mode=' . (($topic_unapproved) ? 'approve_details' : 'unapproved_posts') . "&t=$topic_id", true, $user->session_id) : '';

	// Send vars to template
	$template->assign_block_vars('topicrow', array(
		'FORUM_ID'					=> $forum_id,
		'TOPIC_ID'					=> $topic_id,
		'TOPIC_AUTHOR'				=> get_username_string('username', $row['topic_poster'], $row['topic_first_poster_name'], $row['topic_first_poster_colour']),
		'TOPIC_AUTHOR_COLOUR'		=> get_username_string('colour', $row['topic_poster'], $row['topic_first_poster_name'], $row['topic_first_poster_colour']),
		'TOPIC_AUTHOR_FULL'			=> get_username_string('full', $row['topic_poster'], $row['topic_first_poster_name'], $row['topic_first_poster_colour']),
		'FIRST_POST_TIME'			=> $user->format_date($row['topic_time']),
		'LAST_POST_SUBJECT'			=> censor_text($row['topic_last_post_subject']),
		'LAST_POST_TIME'			=> $user->format_date($row['topic_last_post_time']),
		'LAST_VIEW_TIME'			=> $user->format_date($row['topic_last_view_time']),
		'LAST_POST_AUTHOR'			=> get_username_string('username', $row['topic_last_poster_id'], $row['topic_last_poster_name'], $row['topic_last_poster_colour']),
		'LAST_POST_AUTHOR_COLOUR'	=> get_username_string('colour', $row['topic_last_poster_id'], $row['topic_last_poster_name'], $row['topic_last_poster_colour']),
		'LAST_POST_AUTHOR_FULL'		=> get_username_string('full', $row['topic_last_poster_id'], $row['topic_last_poster_name'], $row['topic_last_poster_colour']),

		'PAGINATION'		=> topic_generate_pagination($replies, $view_topic_url),
		'REPLIES'			=> $replies,
		'VIEWS'				=> $row['topic_views'],
		'TOPIC_TITLE'		=> censor_text($row['topic_title']),
		'TOPIC_TYPE'		=> $topic_type,

		'TOPIC_FOLDER_IMG'		=> $user->img($folder_img, $folder_alt),
		'TOPIC_FOLDER_IMG_SRC'	=> $user->img($folder_img, $folder_alt, false, '', 'src'),
		'TOPIC_ICON_IMG'		=> (!empty($icons[$row['icon_id']])) ? $icons[$row['icon_id']]['img'] : '',
		'TOPIC_ICON_IMG_WIDTH'	=> (!empty($icons[$row['icon_id']])) ? $icons[$row['icon_id']]['width'] : '',
		'TOPIC_ICON_IMG_HEIGHT'	=> (!empty($icons[$row['icon_id']])) ? $icons[$row['icon_id']]['height'] : '',
		'ATTACH_ICON_IMG'		=> ($auth->acl_get('u_download') && $auth->acl_get('f_download', $forum_id) && $row['topic_attachment']) ? $user->img('icon_topic_attach', $user->lang['TOTAL_ATTACHMENTS']) : '',
		'UNAPPROVED_IMG'		=> ($topic_unapproved || $posts_unapproved) ? $user->img('icon_topic_unapproved', ($topic_unapproved) ? 'TOPIC_UNAPPROVED' : 'POSTS_UNAPPROVED') : '',

		'S_TOPIC_TYPE'			=> $row['topic_type'],
		'S_USER_POSTED'			=> (isset($row['topic_posted']) && $row['topic_posted']) ? true : false,
		'S_UNREAD_TOPIC'		=> $unread_topic,
		'S_TOPIC_REPORTED'		=> (!empty($row['topic_reported']) && $auth->acl_get('m_report', $forum_id)) ? true : false,
		'S_TOPIC_UNAPPROVED'	=> $topic_unapproved,
		'S_POSTS_UNAPPROVED'	=> $posts_unapproved,
		'S_HAS_POLL'			=> ($row['poll_start']) ? true : false,
		'S_POST_ANNOUNCE'		=> ($row['topic_type'] == POST_ANNOUNCE) ? true : false,
		'S_POST_GLOBAL'			=> ($row['topic_type'] == POST_GLOBAL) ? true : false,
		'S_POST_STICKY'			=> ($row['topic_type'] == POST_STICKY) ? true : false,
		'S_TOPIC_LOCKED'		=> ($row['topic_status'] == ITEM_LOCKED) ? true : false,
		'S_TOPIC_MOVED'			=> ($row['topic_status'] == ITEM_MOVED) ? true : false,

		'U_NEWEST_POST'			=> $view_topic_url . '&view=unread#unread',
		'U_LAST_POST'			=> $view_topic_url . '&p=' . $row['topic_last_post_id'] . '#p' . $row['topic_last_post_id'],
		'U_LAST_POST_AUTHOR'	=> get_username_string('profile', $row['topic_last_poster_id'], $row['topic_last_poster_name'], $row['topic_last_poster_colour']),
		'U_TOPIC_AUTHOR'		=> get_username_string('profile', $row['topic_poster'], $row['topic_first_poster_name'], $row['topic_first_poster_colour']),
		'U_VIEW_TOPIC'			=> $view_topic_url,
		'U_MCP_REPORT'			=> append_sid("{$root_path}mcp.$phpEx", 'i=reports&mode=reports&f=' . $forum_id . '&t=' . $topic_id, true, $user->session_id),
		'U_MCP_QUEUE'			=> $u_mcp_queue,

		'S_TOPIC_TYPE_SWITCH'	=> ($s_type_switch == $s_type_switch_test) ? -1 : $s_type_switch_test)
	);

	$s_type_switch = ($row['topic_type'] == POST_ANNOUNCE || $row['topic_type'] == POST_GLOBAL) ? 1 : 0;

	if ($unread_topic)
	{
		$mark_forum_read = false;
	}

	unset($rowset[$topic_id]);
}
}
?>

phast1 · May 16, 2007

Try this:

ORDER BY pinned ASC, thread_standing DESC

That should work according to the mySQL manual..

john010117 · May 16, 2007

Also, in the code that radar has posted, there's a missing / on the first comment.

radar · May 16, 2007

yes -- i know that -- originally i didnt have the <?php or ?> for color coding and so when i put it in i guess i deleted one... 'cause it was there..

Chesso · May 16, 2007

Aha I see my problem now.

It wasn't that DESC was wrong, but with that query build-up, the later DESC did not affect the pinned ordering, so it defaulted to ASC, so I just needed to put DESC to explicitely state it for it .

Not sure if there is any possible problems with this, but it seems pretty darn good so far, thanks very much for the help everyone .

I'll be sure to stop by here for help (and try and help of course lol).

Sign In

[SOLVED] Need help with PHP/mySQL problem.

Recommended Posts

Chesso

Link to comment

Share on other sites

phast1

Link to comment

Share on other sites

radar

Link to comment

Share on other sites

Chesso

Link to comment

Share on other sites

radar

Link to comment

Share on other sites

phast1

Link to comment

Share on other sites

john010117

Link to comment

Share on other sites

radar

Link to comment

Share on other sites

Chesso

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information