SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    looking for some mysql help

    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.

  2. #2
    SitePoint Member mrboskits's Avatar
    Join Date
    Jun 2007
    Location
    Sydney, Aus
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  3. #3
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where does $users_joined come from?

  4. #4
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mrboskits View Post
    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

  5. #5
    SitePoint Member mrboskits's Avatar
    Join Date
    Jun 2007
    Location
    Sydney, Aus
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gragland View Post
    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 ...
    PHP Code:
    $this->vote_user_id $link->vote_user_id
    (making sure that the vote_user_id column is included in your SQL statement)

    cheers

  6. #6
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •