pulling data from other table for postbit

[Shepski]

Hiya,

I am working on a hack and I am nowstuck on how to pull data from one field from a different table within my main vb db and then attach that to each user. the other table is created for users who subscribe so I want to add an extra bit to their postbit which displays "subscribed" or something similar. The other table is called adult and has fields called, uid (which contians the users forumid), fname (forum username), email (forum email address) and datesigned (has the date they subscribed). I have played around with the showthread but no success as yet.

any help on where to look appreciated :)

[Gavin B.]

use a join :)

not sure what your query looks like, but it sounds like you need a LEFT JOIN. Should be pretty simple, here's a small article on joins -http://www.devshed.com/Server_Side/MySQL/SQLJoins/

:)

[Shepski]

i had a look at the join statement earlier but could get it to work. this is what i ended up with:

$adult=$DB_site->query("SELECT users.userid FROM users
LEFT JOIN adult ON adult.uid = users.userid, adult.extrapostbit as extrabit WHERE adult.uid = users.userid
");


but i just keep getting mysql errors and alot of annoyed users as they keep trying to post as I make the changes and mess things up!

how close am I? the postbitextra is the bit i want to pull into the postbit and display for each user in the thread view which I presume will end up being something like $adult[extrabit] in the postbit template in-order to display it.

[Gavin B.]

in showthread.php at around line 400ish


$posts=$DB_site->query("
SELECT
post.*,post.username AS postusername,post.ipaddress AS ip,user.*,userfield.*,adult.*,".iif($forum[allowicons],'icon.title as icontitle,icon.iconpath,','')."
attachment.attachmentid,attachment.filename,attach ment.visible AS attachmentvisible,attachment.counter
".iif($avatarenabled,",avatar.avatarpath,NOT ISNULL(customavatar.avatardata) AS hascustomavatar,customavatar.dateline AS avatardateline","")."
FROM post
".iif($forum[allowicons],'LEFT JOIN icon ON icon.iconid=post.iconid','')."
LEFT JOIN user ON user.userid=post.userid
LEFT JOIN userfield ON userfield.userid=user.userid
".iif ($avatarenabled,"LEFT JOIN avatar ON avatar.avatarid=user.avatarid
LEFT JOIN customavatar ON customavatar.userid=user.userid","")."
LEFT JOIN attachment ON attachment.attachmentid=post.attachmentid
LEFT JOIN adult ON adult.uid=user.userid
WHERE $postids
ORDER BY dateline $postorder
");


should do it, I haven't (can't really) tested it... but I would say it should work.

You can refer to your fields as normal in your template :)

[Shepski]

edit: oops! my bad!

just realised that adult.uid should actually be adult.fid! it now runs but not sure if it is working. I am calling $adult[extra] in the postbit but its not pulling the info in. Am i right in calling $adult[extra]?

[Gavin B.]

You should be able to do it with $post[extra] :)

[Shepski]

duh! now you see what your working with...a complete numpty! That all works a sweet as a nut now :)

My next little challenge is to get it to display this extra field in the user info. I have looked in member.php and in the start get info section added the following:

$extrabit=$DB_site->query("SELECT extra FROM adult WHERE fid = '$userid'");

as there wasnt a statement in their to JOIN to but as you have already guessed, its doesnt appear to work. Also, I am not sure how to add some code to that so if the fid != userid to do nothing and not fall over with an error.

Any clues on this?