SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP write multiple queries

    I have a search and I want to allow users to search the database but only return data that they have access to. I need help linking all this together..

    1) First query the users name against their user access:
    PHP Code:
    //return all rows where user has access
    $query1 "SELECT 
           u.id
         , u.username
         , p.user_id
         , p.report_id
      FROM user as u
    INNER
      JOIN user_reports as p
        ON p.user_id = u.username
    WHERE username = '
    $username' "
    Now I have the id of all the companies they have access to through the variable "p.report_id" above. I now need to pull all of these report_ids from the JOINED table above, and perform a FULLTEXT search on just the results from the emt_report:
    PHP Code:
    $query2 "SELECT * FROM WHERE id = p.report_id AND MATCH (company, description, keywords) AGAINST ('$search');' 
    How can I perform a query, take the result and use it in another query?

  2. #2
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If your goal is to do the search, then do a sub-query on the (id) in the search query. Other than that, you will have to use (2) queries and use IN() on the id to do your search query.

    Two query method....

    PHP Code:
    $query mysql_query "SELECT u.id, u.username, p.user_id, p.report_id FROM user as u INNER JOIN user_reports as p ON(p.user_id = u.username) WHERE username = '" $username "';" );


    if ( 
    mysql_num_rows $query ) > )
    {
        
    $data = array ();

        while ( 
    $next mysql_fetch_assoc $query ) )
        {
            
    /* not handling all the data, this is a example. */

            
    $data[] = $next['report_id'];
        }

        
    mysql_free_result $query );

        
    $query mysql_query "SELECT * FROM emt_report WHERE id IN('" explode "','"$data ) . "') AND MATCH(company, description, keywords) AGAINST('" $search "');" );

        if ( 
    mysql_num_rows $query ) > )
        {
            while ( 
    $next mysql_fetch_assoc $query ) )
            {
                
    /* do stuff with search results */
            
    }

            
    mysql_free_result $query );
        }


    Single query method

    PHP Code:
    $query mysql_query "SELECT * FROM emt_report WHERE id IN((SELECT p.report_id FROM user as u INNER JOIN user_reports as p ON(p.user_id = u.username) WHERE username = '" $username "')) AND MATCH(company, description, keywords) AGAINST('" $search "');" );

    if ( 
    mysql_num_rows $query ) > )
    {
        while ( 
    $next mysql_fetch_assoc $query ) )
        {
            
    /* do stuff with search results */
        
    }

        
    mysql_free_result $query );



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
  •