SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2010
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with tables please...

    Hi Everyone...I am a complete newbie... I am sure my questions sounds ridiculous ...

    But I have a simple MySQL database. Here it is..

    Field Type Null Key Default Extra
    photo_id bigint(20) unsigned NO PRI NULL auto_increment
    photo_filename varchar(25) YES NULL
    photo_caption text YES NULL
    photo_description text YES NULL
    photo_keywords text YES NULL
    photo_category bigint(20) unsigned NO 0
    gallery_description text YES NULL

    1.JPG

    Right now the website runs based on photo_id but I would like to base it on the photo_caption. But I am not sure how to establish the connection between photo_id and photo_caption.

    I tried

    Code:
    "SELECT photo_caption, photo_description, photo_filename FROM gallery_photos WHERE photo_caption='". $pid."'"
    but there are no instances where these two connects... how can I connect them or rewrite my table?

    I hope this makes sense.... thank you for any help....

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If you want to extract all rows from the table where the caption has a certain value, then you'll have to use that certain caption value in your WHERE clause. Right now, it looks like you're trying to confront the caption column with a photo id, and that will return no results.

    By the way, are you putting the gallery_description in each photo row? If a gallery can contain more than 1 photo, you should create a galleries table (id, description) and then have a gallery_id column in the photos table that is a foreign key to the galleries table.

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2010
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thank you....

    Guido... I am not sure I understand your answer... sorry... I am a complete newbie... do you mean
    Code:
    "SELECT photo_caption, photo_description, photo_filename FROM gallery_photos WHERE photo_caption=photo_caption"
    this?

    Since the gallery runs based on photo_id ...don't we have to make the connection between photo_id and photo_caption?

    Thank you very much for your patience with me... in advance...

    By the way, are you putting the gallery_description in each photo row? If a gallery can contain more than 1 photo, you should create a galleries table (id, description) and then have a gallery_id column in the photos table that is a foreign key to the galleries table.
    This was my mistake..I need to remove that ...that was an experiment... so I have to remove the "gallery_description" part....
    Last edited by yathrakaaran; Jan 23, 2012 at 06:02. Reason: mistake...

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by yathrakaaran View Post
    Guido... I am not sure I understand your answer... sorry... I am a complete newbie... do you mean
    Code:
    "SELECT photo_caption, photo_description, photo_filename FROM gallery_photos WHERE photo_caption=photo_caption"
    this?
    No, what I mean is that this:
    Code:
    WHERE photo_caption = '" . $pid . "'"
    does not make much sense to me. Unless you put a photo caption in $pid.

    Where does that $pid come from?
    Quote Originally Posted by yathrakaaran View Post
    Since the gallery runs based on photo_id ...don't we have to make the connection between photo_id and photo_caption?
    What do you mean by that? There already is a connection between photo_id and photo_caption: they are in the same row of the photos table.

    What is it you want to do exactly? The user types in some text, and you want to display all the photos that have that text in the caption?

  5. #5
    SitePoint Zealot
    Join Date
    Mar 2010
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    guido2004....you obviously know so much.... thank you for your help..

    Where does that $pid come from?
    This came from the php code of the gallery.. pid is the photo_id (as I understand $pid is the input variable ....)

    What is it you want to do exactly? The user types in some text, and you want to display all the photos that have that text in the caption?
    ... I should have explained this better first... I would like to display the photo_caption as part of the photo link. Right now the link just shows this
    Code:
    viewgallery.php?cid=3&pid=277
    ..So instead I wanted to show the photo caption on the link. So my friend changed the code so that " now the query runs against the photo caption rather than the numeric id" and he tried to use this
    Code:
    $result = mysql_query( "SELECT photo_caption, photo_description, photo_filename FROM gallery_photos WHERE photo_caption='". $pid."'" );
    Here is my entire code for it...
    Code:
    // initialization
    
        $result_array = array();
        $counter = 0;
    
     
    
        $cid = isset($_GET['cid']) ? (int)($_GET['cid']) : 0; 
        
    //--RT: 
        $pid = isset($_GET['pid']) ? ($_GET['pid']) : 0; // now not an interger - now it hold the gallery title
    $pid = str_replace("_", " ", $pid); // remove the _ from the url
    
    
    
    // Category Listing
    
     
    
        if( empty($cid) && empty($pid) )
    
        {
    
            $number_of_categories_in_row = 2;
    
     
    
            $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[] = "<div class=cthumbs><a href='viewgallery.php?cid=".$row[0]."'><br/>
    
    <img src='/photos/categoryimages/category".$row[0].".jpg' height='153px' width='230px' ><br/>
    <span class=cname>".$row[1]."</span></a></div>";
    
            }
    
    
            mysql_free_result( $result );   
     
    
            $result_final = "<div class=cpage><div class=categories><table class=categoryview><tr>\n";
    
    
            foreach($result_array as $category_link)
    
            {
    
                if($counter == $number_of_categories_in_row)
    
                {   
    
                    $counter = 1;
    
                    $result_final .= "\n</tr>\n<tr>\n";
    
                }
    
                else
    
                $counter++;
    
                $result_final .= "\t<td>".$category_link."</td>\n";
    
            }
    
            if($counter)
    
            {
    
                if($number_of_categories_in_row-$counter)
    
                $result_final .= "\t<td colspan='".($number_of_categories_in_row-$counter)."'></td>\n";
    
                $result_final .= "</tr></table></div>
    
    <!-- .cpage --></div>    ";
    
            }
    
        }
    
    
    
     // Thumbnail Listing
    
    else if( $cid && empty( $pid ) )
    
    {
    
      $number_of_thumbs_in_row = 3;
    
    // If current page number, use it 
    
      // if not, set one! 
    
      if(!isset($_GET['page'])){ 
    
          $page = 1; 
    
      } else { 
    
          $page = $_GET['page']; 
    
      } 
    
    // Define the number of results per page 
    
      $max_results = 80; 
    
    
    
    // Figure out the limit for the query based 
    
    // on the current page number. 
    
      $from = (($page * $max_results) - $max_results); 
    
      $result = mysql_query("
        SELECT 
              photo_id
            , photo_caption
            , photo_filename 
            , photo_category
            , gallery_description
        FROM 
            gallery_photos 
        WHERE 
            photo_category='".addslashes($cid)."' 
        LIMIT 
            $from, $max_results
        "); 
    
      $nr = mysql_num_rows( $result );
    
      if( empty( $nr ) )
    
      {
    
       $result_final = "\t<tr><td>No Photos found</td></tr>\n";
    
      }
    
       else
    
      {
    
       while( $row = mysql_fetch_array( $result ) )
    
       {
    
        
    
        $result_array[]= "<a href='viewgallery.php?cid=$cid&pid=".$row[0]."'><img 
    
        src='".$images_dir."/tb_".$row[2]."' alt='".$row[1]."' /></a>";
    
     
    
       }
    
    
    
        $result = mysql_query( "SELECT category_name, gallery_description  FROM gallery_category WHERE category_id='".addslashes($cid)."'" ); 
    
        $nr = mysql_num_rows( $result );
    
        list($category_name, $gallery_description ) = mysql_fetch_array( $result ); 
    
        mysql_free_result( $result );    
     
    
        
        $result_final = "
        
    
    <div class=thumbsPage>
    
    
    <div class=link><a href=viewgallery.php>Albums</a><span class=arrow>&gt;&gt</span><a href=viewgallery.php?cid=$cid>$category_name</a><br><br></div>
    
    <div class=des>$gallery_description<br><br></div>
    
    
    <tr><td align=center valign=middle><div class=thumbs><table><tr>\n";
    
    
               foreach($result_array as $thumbnail_link)
                {
                    if($counter == $number_of_thumbs_in_row)
                    {    
                        $counter = 1;
                        $result_final .= "\n</tr>\n<tr>\n";
                    }
                    else
                    $counter++;
    
                    $result_final .= "\t<td>".$thumbnail_link."</td>\n";
                }
        
                if($counter)
                {
                    if($number_of_thumbs_in_row-$counter)
                $result_final .= "\t<td colspan='".($number_of_thumbs_in_row-$counter)."'>&nbsp;</td>\n";
    
                $result_final .= "</tr></table></td></tr></table>
    </div>    
    </div>    
    
    ";
            
            
    
    
    // Figure out the total number of results in DB: 
    
    $total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM gallery_photos WHERE photo_category=".addslashes
    
    ($cid).""),0); 
    
    
    
    // Figure out the total number of pages. Always round up using ceil() 
    
    $total_pages = ceil($total_results / $max_results); 
    
    if ($total_pages >1)
    
    {  
    
    // build links if more than one page
    
    
    
    // Build Page Number Hyperlinks 
    
    $result_final .=  "<div class=plinks><tr><td colspan='".$number_of_thumbs_in_row."' class='tcat'>\n&nbsp;&nbsp;"; 
    
    // Build Previous Link 
    
    if($page > 1){ 
    
        $prev = ($page - 1); 
    
        $result_final .=  "\n<a href=\"".$_SERVER['PHP_SELF']."?cid=$cid&page=$prev\" title='Previous Page'>&lt;&lt;PREVIOUS PAGE</a>"; 
    
    } 
    
    for($i = 1; $i <= $total_pages; $i++){ 
    
        if(($page) == $i){ 
    
            $result_final .= "&nbsp; $i"; 
    
            } else { 
    
                $result_final .=  "\n<a href=  \"".$_SERVER['PHP_SELF']."?cid=$cid&page=$i\" title='Page ".$i."'class=pagenos>$i</a>"; 
    
        } 
    
    } 
    
    // Build Next Link 
    
    if($page < $total_pages){ 
    
        $next = ($page + 1); 
    
        $result_final .=  "\n<a href=\"".$_SERVER['PHP_SELF']."?cid=$cid&page=$next\" title='Next Page'>NEXT PAGE<span class=arrow>&gt;&gt</span></a>"; 
    
    } 
    
    $result_final .=  "\n</td></tr></div>"; 
    
    }
    
    else
    
    {
    
    $result_final .=  "\n";
    
    }      
    
       }
    
      }
    
    }
    
    // display previous and next links if more than one photo 
    
     else if( $pid ) 
    
        { 
    
    //--RT:  now the query runs against the photo cation rather than the numeric id
            $result = mysql_query( "SELECT photo_caption, photo_description, photo_filename FROM gallery_photos WHERE photo_caption='". $pid."'" ); 
    
            list($photo_caption, $photo_description, $photo_filename) = mysql_fetch_array( $result ); 
    
            $nr = mysql_num_rows( $result ); 
    
            mysql_free_result( $result );     
    
    
    
            //fill pid_array with sorted pids in current category 
    
            $result = mysql_query( "SELECT photo_id FROM gallery_photos WHERE photo_category='".addslashes($cid)."' ORDER BY photo_id" ); 
    
            $ct = mysql_num_rows( $result );     
    
            while ($row = mysql_fetch_array($result)) { 
    
                $pid_array[] = $row[0]; 
    
                 } 
    
            mysql_free_result( $result ); 
    
    
    
            if( empty($nr ) ) 
    
            { 
    
                $result_final = "\t<tr><td>No Photo found</td></tr>\n"; 
    
            } 
    
            else 
    
            { 
    
                $result = mysql_query( "SELECT category_name FROM gallery_category WHERE category_id='".addslashes($cid)."'" ); 
    
                list($category_name) = mysql_fetch_array( $result ); 
    
                mysql_free_result( $result );     
    
    
    
    
    
                $result_final = "
    
    
                <div class=limagePage>
    
                
    
                <div class=llink><a href=viewgallery.php>Albums</a><span class=arrow>&gt;&gt</span><a href=viewgallery.php?cid=$cid>$category_name</a></div>
      
                ";
    
                 
    
                // display previous and next links if more than one photo 
    
                if ($ct > 1) { 
    
                      $key = array_search($pid, $pid_array); 
    
                      $prev = $key - 1; 
    
                      if ($prev < 0) $prev = $ct - 1; 
    
                      $next = $key + 1; 
    
                      if ($next == $ct) $next = 0; 
    
    
    //--RT: changed the $pid_array to replace the spaces with _ 
    
                      $result_final .= "<div class='prevnext'>"; 
                      $result_final .= "<span class='prev'><a href=viewgallery.php?cid=$cid&pid=". str_replace(" ", "_", $pid_array[$next]) ."><img src=photos/assets/left.png  border=0 ></a></span>"; 
                      $result_final .= "<span class='next'><a href=viewgallery.php?cid=$cid&pid=". str_replace(" ", "_", $pid_array[$prev])."><img src=photos/assets/right.png  border=0 ></a></span>"; 
                      $result_final .= "</div>";
    
                      
                }            
    
                }
    
               $result_final .= "<div class=limage><table><tr><td><table class=image><tr>\n\t<td><a href=viewgallery.php?cid=$cid&pid=". str_replace(" ", "_", $pid_array[$next]) ."><img src='".$images_dir."/".$photo_filename."' border='0' alt='".$photo_caption."' /></a>
    
                   
    
                        <div class=caption>".$photo_caption."</div> 
    
                                            
                        <div class='excerpt'>".$photo_description."</div> 
    
                        </td>                    
    
                        </tr></table></td></tr></table><div class=underline></div></div>
    
    <!-- .limagePage --></div>    
    
    ";             
    
                 }

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by yathrakaaran View Post
    I would like to display the photo_caption as part of the photo link. Right now the link just shows this
    Code:
    viewgallery.php?cid=3&pid=277
    ..So instead I wanted to show the photo caption on the link.
    Can you give an example of how the link should look like? Everything from <a ...> until </a> please.

  7. #7
    SitePoint Zealot
    Join Date
    Mar 2010
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Hi Guido...

    Guido 2004...

    If it is possible I would like it to show "http://www.xyz.com/viewgallery.php/Dallas-Divide-Evening/"... (this is just an example...)

  8. #8
    SitePoint Zealot
    Join Date
    Mar 2010
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Hi...

    Can anyone help me with this?

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Ok, I think I understand. The variable is still called $pid, but it doesn't contain the photo id anymore, now it contains the caption. Right?

    Then your query should be correct. Try rewriting this line
    PHP Code:
    $result mysql_query"SELECT photo_caption, photo_description, photo_filename FROM gallery_photos WHERE photo_caption='"$pid."'" ); 
    to
    PHP Code:
    $query "SELECT photo_caption, photo_description, photo_filename FROM gallery_photos WHERE photo_caption='" $pid "'";
    echo 
    "query: $query";
    $result mysql_query($query); 
    See if the query is how you expect it to be, and if it is, copy and paste it in PHPMyAdmin, and see if it returns any results.

  10. #10
    SitePoint Zealot
    Join Date
    Mar 2010
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Hi Guido2004

    I have to admit ..I didn't know how to run that from PHPMyadmin... it was giving me a syntax error. I am sure it because I don't what I am doing... I am only seeing SGL queries can be run from PHPMyadmin. Is that right? So I copy and pasted the code in my php file and I got this...
    Code:
                           
    query: SELECT photo_caption, photo_description, photo_filename FROM gallery_photos WHERE photo_caption='451'
    No Photo found
    Does this mean that there is no 'instance' where photo_caption becomes equal to "pid" ?

    I am also getting an error
    Code:
    Undefined index: in C:\wamp\www\viewgallery.php on line 422
    And the line 422 is
    Code:
    $result_final .= "<div class=limage><table><tr><td><table class=image><tr>\n\t<td><a href=viewgallery.php?cid=$cid&pid=". str_replace(" ", "_", $pid_array[$next]) ."><img src='".$images_dir."/".$photo_filename."' border='0' alt='".$photo_caption."' /></a>
    Thank you for your help...
    Last edited by yathrakaaran; Jan 26, 2012 at 22:04. Reason: Mistakes

  11. #11
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    67
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I think it's a bit dangerous to just switch from photo_id to photo_caption since photo_caption is not unique and in your table can also be NULL. So not a good field to identify your photos with. If you really want the caption in the url i would use both the photo id and the caption in the url so something like this:
    "http://www.xyz.com/viewgallery.php?pid=451#Dallas-Divide-Evening"

    and keep the rest of the script/the queries as they were.

  12. #12
    SitePoint Addict FizixRichard's Avatar
    Join Date
    May 2003
    Location
    UK
    Posts
    372
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yathrakaaran... are you basically wanting to change your link so that it displays a friendly name as opposed to an unfriendly number? And the caption is the closest thing you have to a friendly word???

    Because if thats what you are intending to achieve what you are actually asking for, or should I say need, is a hook mechanism.

    To do that is more complicated than just basing off of "caption" for the reasons already detailed in this thread.

    a nice simple way of adding a hooking mechanism is to add a new column to the database called "hook" and one called "title", then edit the image upload/create function so that hook is populated by a hook that you generate.

    To generate the hook, your form will want a title field (you don't want to use the caption as that could be long) and give the title field it a fairly short length.

    Then on the create/upload image function, have a process where the title is normalised (turn spaces into '-' or strip them entirely and strip out any characters that are invalid in the URL).

    Then prefix this normalised title with the images id (pid).

    So, an image that has a title of "I am an image" and a pid of "55" would have a hook name of "55-i-am-an-image".


    Then in the URL you would use the hook instead of the caption.
    And in the query you would query against hook instead of caption.
    FIZIX - Full Service Digital Agency - Engaging websites, apps and games.
    Follow us @FIZIXAgency

  13. #13
    SitePoint Zealot
    Join Date
    Mar 2010
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thank you guys...

    FizixRichard and brense...thank you so much for your help... you guys really think on a higher plane than I do... I could be asking some stupid questions below...sorry about that... what I really would like to happen is to make the link look like http://www.xyz.com/viewgallery.php/C...rneys/Colorado. In another words www.xyz.com/viewgallery.php/folder-name/image name or title. The hook idea...sounds very interesting. Right now...I cannot connect 'pid and photocaption... there are no instances they come together as equal. What does 'hook' do? what kind of value 'hook' supposed to have?

  14. #14
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    67
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, that shouldn't be a big problem. If by folder-name you mean photo_category.

    All you need to do is substract these values from the url. And change your queries a little bit like so:

    PHP Code:
    // substract values from url
    $url $_SERVER["REQUEST_URI"];
    $url explode('.php'$url);
    $categoy array_shift(explode('/'substr($url[1], 1)));
    $caption array_pop(explode('/'$url[1]));

    // make query
    $query "SELECT photo_caption, photo_description, photo_filename FROM gallery_photos WHERE photo_category = '" $category "' AND photo_caption = '" $caption "'"
    Something like this anyways...

    And ofcourse you must adjust your urls to look like this.


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
  •