looking for some mysql help

[gragland]

Hello, I'm not sure how doable this is but here it goes:

I have an sql statement below. The $user_joined variable ends up equaling something like "456, 542, 123, 987". Everything works fine, but what I'd like to do is pass the vote_user_id along to the foreach statement so that I can echo the vote_user_id that gave me the link_id for each loop of the foreach statement. Each vote_user_id may give me any number of link_ids.

$users_joined = join(', ', $users);

$sql = "SELECT link_id FROM links, votes WHERE vote_user_id in ($users_joined) AND vote_link_id=link_id AND link_status !='discard' $order_by LIMIT $rows";
}

do_header($title);

$link = new Link;
$links = $db->get_col($sql);
if ($links) {
foreach($links as $link_id) {
$link->id=$link_id;
$link->read();
echo $link->id;
//here I'd like to echo the vote_user_id that I got the link_id from

}
}

I hope that makes sense! Any help would be greatly appreciated.

[mrboskits]

By adding the vote_user_id to the SELECT part of your SQL you'll have the vote_user_id associated with each link_id

SELECT link_id, vote_user_id FROM links, votes WHERE vote_user_id in ($users_joined) AND vote_link_id=link_id AND link_status !='discard' $order_by LIMIT $rows

Does that answer your question?

[earl-grey]

Where does $users_joined come from?

[gragland]

By adding the vote_user_id to the SELECT part of your SQL you'll have the vote_user_id associated with each link_id

SELECT link_id, vote_user_id FROM links, votes WHERE vote_user_id in ($users_joined) AND vote_link_id=link_id AND link_status !='discard' $order_by LIMIT $rows

Does that answer your question?

Thanks! I think that gets me a bit closer, here's the full code, maybe you can help me with what else I need to change. After the $sql statement (the one you helped me modify) I have this code which I don't fully understand:

$link = new Link;
$links = $db->get_col($sql);
if ($links) {
foreach($links as $link_id) {
$link->id=$link_id;
$link->read();

echo "<track>\n";
echo " <identifier>".get_permalink($link->id)."</identifier>\n";
echo " <annotation><![CDATA[$link->title]]></annotation>\n";
echo " <info>/story_test.php?title=" . $link->title_url . "</info>\n";
echo " <location>$link->url</location>\n";
echo " </track>\n\n";
}
}

The variables in the echo statement above come from the read() function that is called. You provided me with the modification I needed to make the the $sql statement to include vote_user_id, now I need to modify the read function to make it take the vote_user_id's and turn them into variables like those other variables in the echo statements. Here's the read() function:

function read() {
global $db, $current_user;
$id = $this->id;
if(($link = $db->get_row("SELECT links.*, users.user_login, users.user_email FROM links, users WHERE link_id = $id AND user_id=link_author"))) {
$this->author=$link->link_author;
$this->author_email=$link->user_email;
$this->username=$link->user_login;
$this->blog=$link->link_blog;
$this->status=$link->link_status;
$this->votes=$link->link_votes;
$this->randkey=$link->link_randkey;
$this->category=$link->link_category;
$this->url= $link->link_url;
$this->url_title=$link->link_url_title;
$this->title=$link->link_title;
$this->title_url=$link->link_title_url;
$this->tags=$link->link_tags;
$this->content=$link->link_content;
$date=$link->link_date;
$this->date=$db->get_var("SELECT UNIX_TIMESTAMP('$date')");
$date=$link->link_published_date;
$this->published_date=$db->get_var("SELECT UNIX_TIMESTAMP('$date')");
$date=$link->link_modified;
$this->modified=$db->get_var("SELECT UNIX_TIMESTAMP('$date')");
$this->fullread = $this->read = true;
return true;
}
$this->fullread = $this->read = false;
return false;
}

Although I don't fully understand the code, it looks like $sql is used by the read function, so I need to do the modification to the $sql statement you suggested, as well as change the read function a bit. If you could help me with this that would be great.

Thanks,
Gabe

[mrboskits]

function read() {
global $db, $current_user;
$id = $this->id;
if(($link = $db->get_row("SELECT links.*, users.user_login, users.user_email FROM links, users WHERE link_id = $id AND user_id=link_author"))) {
$this->author=$link->link_author;
$this->author_email=$link->user_email;
$this->username=$link->user_login;
$this->blog=$link->link_blog;
$this->status=$link->link_status;
$this->votes=$link->link_votes;
$this->randkey=$link->link_randkey;
$this->category=$link->link_category;
$this->url= $link->link_url;
$this->url_title=$link->link_url_title;
$this->title=$link->link_title;
$this->title_url=$link->link_title_url;
$this->tags=$link->link_tags;
$this->content=$link->link_content;
$date=$link->link_date;
$this->date=$db->get_var("SELECT UNIX_TIMESTAMP('$date')");
$date=$link->link_published_date;
$this->published_date=$db->get_var("SELECT UNIX_TIMESTAMP('$date')");
$date=$link->link_modified;
$this->modified=$db->get_var("SELECT UNIX_TIMESTAMP('$date')");
$this->fullread = $this->read = true;
return true;
}
$this->fullread = $this->read = false;
return false;
}

You should have a bunch of variable declarations for the Link class. Add $vote_user_id to that and then you can set the value of it for the object by adding a line ...

$this->vote_user_id = $link->vote_user_id; 

(making sure that the vote_user_id column is included in your SQL statement)

cheers

[gragland]

Not sure if I understand you but,

Aren't those variables, such as "$link->link_author" coming from this:
if(($link = $db->get_row("SELECT links.*, users.user_login, users.user_email FROM links, users WHERE link_id = $id AND user_id=link_author"))) {

Specifically, the "links.*" part. So would it just be a matter of modifying that sql statement and then also adding:
$this->vote_user_id = $link->vote_user_id; ?

And then also making the original sql change you had suggested.

thanks for the help
-gabe