SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member nickthibert's Avatar
    Join Date
    May 2004
    Location
    Ottawa
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question selecting certain rows only

    what im trying to do here is add the ability to make certain galleries public, and some not. i have added a column to my database called 'public' witha set value of yes or no. now, what im trying to do is modify this code to select only the galleries that have 'yes' in the public column. this code is from the tutorial http://www.sitepoint.com/article/php...stem-minutes/1

    here's the code:

    PHP Code:
     if( empty($cid) && empty($pid) )
     {
      
    $number_of_categories_in_row 1;
      
    $result mysql_query"SELECT c.category_id,c.category_name,COUNT(photo_id)
          FROM gallery_category as c
          LEFT JOIN gallery_photos as p ON p.photo_category = c.category_id
          GROUP BY c.category_id" 
    );
      while( 
    $row mysql_fetch_array$result ) )
      {
       
    $result_array[] = "<a href='view_memb_fros.php?cid=".$row[0]."'>".$row[1]."</a> "."(".$row[2].")";
      }
      
    mysql_free_result$result ); 
      
    $result_final "<tr height='20' width='300' align='center' valign='middle'>";
      foreach(
    $result_array as $category_link)
      {
       if(
    $counter == $number_of_categories_in_row)
       { 
        
    $counter 1;
        
    $result_final .= "</tr><tr height='20' width='300' align='center' valign='middle'>";
       }
       else
       
    $counter++;
       
    $result_final .= "<td height='20' align='center' valign='middle'>".$category_link."</td>";
      }
      if(
    $counter)
      {
       if(
    $number_of_categories_in_row-$counter)
       
    $result_final .= "<td height='20' align='center' valign='middle'>&nbsp;</td>";
       
    $result_final .= "</tr>";
      }
     } 
    any ideas anyone? whenever i try to to add in a WHERE statement in the mysql query, i get different errors rturned (mostly invalid array functions).

    any help is greatly appreciated!

  2. #2
    With More ! for your $ maxor's Avatar
    Join Date
    Feb 2004
    Location
    Scottsdale, Arizona
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The easiest way to do it would be to add

    Code:
    WHERE tablename.public = yes
    Or 'no' if you want only the private galleries.

  3. #3
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    maxor's suggestion is definitley the easiest route just make your query this:

    PHP Code:
    SELECT c.category_id,c.category_name,COUNT(photo_id)
          
    FROM gallery_category as c
          LEFT JOIN gallery_photos 
    as p ON p.photo_category c.category_id
         WHERE gallery
    .public = 'yes'
          
    GROUP BY c.category_id 
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  4. #4
    With More ! for your $ maxor's Avatar
    Join Date
    Feb 2004
    Location
    Scottsdale, Arizona
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could also avoid changing the query and use PHP if else logic to sort through your results, although that really isn't the best method programtically speaking since you're querying the DB inefficiently.

  5. #5
    SitePoint Member nickthibert's Avatar
    Join Date
    May 2004
    Location
    Ottawa
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i added the WHERE line in, and get these errors still (which were the ones i was getting before)

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\view_memb_fros.php on line 25

    Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in D:\view_memb_fros.php on line 29

    Line 25:
    while( $row = mysql_fetch_array( $result ) )

    Line 29:
    mysql_free_result( $result );

  6. #6
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ran into that problem before as well. To solve it, we used mysql_fetch_assoc()

    You could also take the advice of maxor and use the ifelse logic, but like he said it really isn't the best method.
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  7. #7
    SitePoint Member nickthibert's Avatar
    Join Date
    May 2004
    Location
    Ottawa
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i had to change it from

    WHERE gallery.public = 'yes'

    to

    WHERE c.public = 'yes'

    and now it works!

    thank you very much!

  8. #8
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Glad it's working for you!!
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!


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
  •