help with join or nested query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

PhilPond
Forum Newbie
Posts: 7
Joined: Sat Oct 11, 2003 3:00 pm

Post by PhilPond »

JAM:-

Nah -- Should never more than 100 links. Here is my code so far (it uses arrays):

Code: Select all

<?php

  class portfolio &#123;


    // Initialize object varibles

    var $curUserID = 0;
    var $curLinkID = 0;
    var $userCount = 0;
    var $linkCount = 0;
    var $users = array();
    var $links = array();



    // call to initilize the object

    function init() &#123;
      $this->buildLinkArray();
      $this->buildUserArray();
      $this->userCount = count( $this->users );
      $this->linkCount = count( $this->links );
    &#125;



    // call to reset the object

    function reset() &#123;
      $this->curUserID = 0;
      $this->curLinkID = 0;
      unset($this->users);
      unset($this->links);
    &#125;



    // gets all links from database and assigns values to array "$links"
    // Used by init() function

    function buildLinkArray() &#123;
      $result_links = mysql_query("select gallery_links.* from gallery_links");
      while( $res = mysql_fetch_assoc( $result_links ) ) &#123;
        $data&#1111;] = $res;
      &#125;
       $this->links = $data;
    &#125;



    // gets all users from database and assigns values to array "$users"
    // Used by init() function

    function buildUserArray() &#123;
      $result_links = mysql_query("select gallery_people.* from gallery_people");
      while( $res = mysql_fetch_assoc( $result_links ) ) &#123;
        $data&#1111;] = $res;
      &#125;
      $this->users = $data;
    &#125;


    // Show user details

    function UserDetails($arg="") &#123;
      switch($arg) &#123;
        case "id":
          return($this->users&#1111;$this->curUserID]&#1111;"ID"]);
          break;
        case "name":
          return($this->users&#1111;$this->curUserID]&#1111;"name"]);
          break;
        case "email":
          return($this->users&#1111;$this->curUserID]&#1111;"email"]);
          break;
        default:
          return($this->users&#1111;$this->curUserID]&#1111;"ID"]);
          break;
      &#125;
    &#125;


  &#125;

?>
... now I'm back to my original problem (sigh). I want to write a function that will return a users links. Only this time, I have an multidimensional array populated with all the links and another with all the users. Printing out the users is no problem.

I tried using a while loop to iterate through the links array and check for the appropriate ID, but I am not satisfied with that solution. What I would really like to do is return a smaller array containing just the links for that user so that I can loop through the array and format as I wish without hardcoding any html into my class.

So, essentially, I am trying to build a smaller multidimensional array out of a larger multidimensional array by extracting the values (and keys) that match the supplied user id.

Whew...

Phil
Stoneguard
Forum Contributor
Posts: 101
Joined: Wed Aug 13, 2003 9:02 pm
Location: USA

Post by Stoneguard »

Sounds like what you want is a 'where' on the select

Code: Select all

$sql = "select gallery_links.* from gallery_links WHERE id=" $userid;
And the same for the second query. By the way, when you join the tables, you will get some duplicate information, but you can set an identifier in the while so that you can change over when the id changes.

Code: Select all

<?php
$id = -1;

while ($res = mysql_fetch_assoc( $result_links ) ) {
   if ($res['id'] != $id) {
      $data[$id] = $links;
      unset($links);
   }

   $links[] = $res; // actually, just pull out the needed columns here
}
?>
PhilPond
Forum Newbie
Posts: 7
Joined: Sat Oct 11, 2003 3:00 pm

Post by PhilPond »

Stoneguard:-

Thanks -- but I will need the entire recordset here, otherwise I will be querying the database for each user. I am looking for output something like:


Username
link
link
link

Username
link
link

Username
link
link
link

...

Too bad we can't run SQL querries on an array.

Phil
Post Reply