seeking php help, foreach with destinct selection [ sorted ]

[Vaupell]

got a table similar to this, just much larger
colum = autoinc for table
columa and columb = unknown data
columc is known data i can select with.

But if i just SELECT * FROM my table WHERE columc=knownvalue
the result will be an array i also need to run through a loop to display

im going to print/echo via templates, <tables> and the template_bit
ill be using will be something like
<tr><td>{vb:raw DataG}</td><td>{vb:raw DataX}

colum|columa|columb|columc
-------+--------+--------+-------
idfield| data1| dataG | data3
idfield| data1| dataF | data2
idfield| data1| dataH | data1
idfield| data2| dataG | data3
idfield| data2| dataF | data2
idfield| data2| dataH | data1
-------------------------------------

Now i want to display DataG from both places (or many more) where DataG is present
but only once pr unique columA

so first i get the unique columa's with sql

$getunique = $vbulletin->db->query("SELECT DISTINCT columa FROM ".TABLE_PREFIX."mytable");

and now im stuck,,
so im thinking maybe assign each unique id's with a id and then
run a loop that collects the data for display

forach ($getunique, ??what value??)
{
-- run the templater registers and renders..
}

i hope its clear what im trying to do :confused:

--------------- Added 1264287288 at 1264287288 ---------------

adding code snipittet.. hope it helps to make sence what im trying to do


$getapplicants = $vbulletin->db->query_read("SELECT DISTINCT userid FROM ". TABLE_PREFIX ."ewapplication");
while($row=$vbulletin->db->fetch_array($getapplicants))
{
$geta1=$vbphrase[ewow_app_main_name];
$getcharname=$vbulletin->db->query("SELECT * FROM ".TABLE_PREFIX."ewapplication WHERE question='".$geta1."'");
$loadcharname = $db->fetch_array($getcharname);
$charactername = $loadcharname['ansver'];

$templater = vB_Template::create('ew_applicant_list_bit');
$templater->register('charname', $charactername);
$applicantbit .= $templater->render();
}

result :

character name :
testcharlie
testcharlie

but the second user, should have been testjohn
and if more was added then ofcourse they should appear.

[Auran]

I have a couple of questions - I think I understand what you want to happen.

I'm presuming you're running on vB4.
* Whereabouts would this template/php run from?
* Are you aware of the vb:foreach function available in templates?
* Are you sure your SQL query is working correctly? Do you have any access to something like phpMyAdmin to be able to test your query?

An abridged version of the code I wrote to pull all unread posts from subscribed threads is below - followed by the template code which displays the posts in forum sidebar.

$posts = $this->registry->db->query_read_slave("
SELECT * //query was much more specific...
FROM " . TABLE_PREFIX . "post AS post
JOIN " . TABLE_PREFIX . "thread AS thread ON (thread.threadid = post.threadid)
JOIN " . TABLE_PREFIX . "subscribethread AS subscribethread ON (subscribethread.threadid = thread.threadid AND subscribethread.userid = " . $this->registry->userinfo[userid] . ")
JOIN " . TABLE_PREFIX . "threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = " . $this->registry->userinfo[userid] . ")
JOIN " . TABLE_PREFIX . "user AS user ON (user.userid = post.userid)

WHERE 1 = 1
AND post.threadid = subscribethread.threadid
AND post.dateline > threadread.readtime
AND subscribethread.userid = " . $this->registry->userinfo[userid] . "

ORDER BY post.dateline DESC
");

$postcount = 0;

while ($post = $this->registry->db->fetch_array($posts))
{
$post['title'] = fetch_trimmed_title($post['title'], $this->config['newposts_titlemaxchars']);

$post['url'] = fetch_seo_url('thread', $post, array('p' => $post['postid'])) . '#post' . $post['postid'];
$post['newposturl'] = fetch_seo_url('thread', $post, array('goto' => 'newpost')) . '#post' . $post['postid'];

$post['date'] = vbdate($this->registry->options['dateformat'], $post['dateline'], true);
$post['time'] = vbdate($this->registry->options['timeformat'], $post['dateline']);

$post['message'] = $this->get_summary($post['message'], $this->config['newposts_messagemaxchars']);

// user info
$linkinfo = array('postuserid' => $post['userid'], 'postusername' => $post['username']);
$post['memberurl'] = fetch_seo_url('member', $linkinfo, null, 'postuserid', 'postusername');

$this->fetch_avatarinfo($post);

$postarray[$post['postid']] = $post;

$postcount++;
}

$message = "";

if ($postcount != 0) {
$message .= "<br />Note: updates occur every five minutes.";
} else {
if ($this->registry->userinfo[userid] == 0)
{
$message .= "This feature is only available for logged in members.";
} else {
$message .= "There are currently no posts to display.";
}
}

$templater = vB_Template::create('block_nfposts');
$templater->register('blockinfo', $this->blockinfo);
$templater->register('posts', $postarray);
$templater->register('message', $message);
return $templater->render();

<vb:each from="posts" key="postid" value="post">
<li class="avatarcontent floatcontainer">
<a class="smallavatar">
<vb:if condition="$post['showavatar']">
<img width="30" title="" src="{vb:raw post.avatarurl}" alt=""/>
<vb:else />
<img width="30" title="" src="{vb:stylevar imgdir_misc}/unknown.gif" alt=""/>
</vb:if>
</a>
<div class="smallavatartext">
<p><a href="{vb:raw post.url}" class="title">{vb:raw post.title}</a></p>
<p>{vb:raw post.message}</p>
<div class="meta">
by <a href="{vb:raw post.memberurl}">{vb:var post.username}</a> at <span class="time">{vb:raw post.time}</span> {vb:raw post.date}
</div>
</div>
</li>
</vb:each>

So as you can see, my query in the PHP returns data into an array which I then pass as a registered variable into the template and use the vB built-in template call to iterate through the results (and accessing specific known named variables inside them - ex. {vb:raw post.title}).

Have you considered this sort of approach? I'm happy to give you a better look at my code and how I implemented it if you PM me.

[Carnage]

Whatever you do about this PLEASE try not to put database queries in while/for/foreach loops. Performance of the code will be really awful.

I'm not entirly sure what it is you are trying to do; could you explain your application better?

[Vaupell]

changed my table's instead to work better..
and solved it that way.

just some huge query with 35 colums, hehe
before i was running 35 querys where each was a row :D