SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Converting a query from ColdFusion to PHP

    Someone helped me with this query in ColdFusion in the forums a while ago(Thread is here). and I'm now trying to use this query in PHP and I can't seem to get it to work. the tables are exactly the same in the database.

    ColdFusion:
    Code:
    <cfquery name="qGetPhoto" datasource="#variables.dsn#">
        SELECT       p.photo_id
        ,            p.photo_file
        ,            p.photo_title
        ,            p.photo_description
        ,            p.photo_keywords
        ,            p.photo_date
        ,            p.photo_views
        ,            a.album_id
        ,            a.album_title
        ,            ( SELECT MIN(photo_id) FROM tbl_photos WHERE photo_album = p.photo_album AND photo_id > p.photo_id ) AS prev_id
        ,            ( SELECT MAX(photo_id) FROM tbl_photos WHERE photo_album = p.photo_album AND photo_id < p.photo_id ) AS next_id
        FROM         tbl_photos AS p
        INNER JOIN   tbl_albums AS a ON p.photo_album = a.album_id
        WHERE        p.photo_id = <cfqueryparam value="#arguments.pid#" cfsqltype="cf_sql_integer" />
    </cfquery>
    How would I write this in PHP?

    Thanks!

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pretoria, South Africa
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    First of all, you need to connect to the mysql server with (I usually have a function to connect to the database in an external file):
    PHP Code:
    function db_connect(){
    //function to connect to the MySQL database for a full connection
    //I think that your "#variables.dsn#" contains the info needed here
    $databaseURL "the-server-to-connect-to";
    $databaseUName "the-username";
    $databasePWord "the-password";
    $databaseName "the-database-name";
    $connection mysql_pconnect($databaseURL,$databaseUName,$databasePWord);
        
    mysql_select_db($databaseName,$connection);//selects the database to use

    Now when you need the connection, just call this function
    Then you need a query (I usually store this inside a variable for easy recall):
    PHP Code:
    $qGetPhoto mysql_query("
    SELECT MIN( photo_id )
    FROM tbl_photos
    WHERE photo_album = p.photo_album
    AND photo_id > p.photo_id
    ) AS prev_id, (

    SELECT MAX( photo_id )
    FROM tbl_photos
    WHERE photo_album = p.photo_album
    AND photo_id < p.photo_id
    ) AS next_id
    FROM tbl_photos AS p
    INNER JOIN tbl_albums AS a ON p.photo_album = a.album_id
    WHERE p.photo_id = 
    $pid
    "
    ); 
    (I entered your query in phpmyadmin's query page and let it output the query again so if it does not return all the fields you require, this might be the problem)
    I guess that the <cfqueryparam value="#arguments.pid#" cfsqltype="cf_sql_integer" /> contains a variable to select the rows that match and I replaced this with a php var $pid (the $ is the sign for var). Just make sure that you have a defined $pid before this query gets called.
    Where you need the query to be executed, just call $qGetPhoto.

    I hope that this could help

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I got the blow code to work for my query:

    Code:
    $query_rsNav = "SELECT ImageDate      , ( SELECT MAX(ImageID)             FROM PhotoGallery            WHERE ImageDate < T.ImageDate ) AS next_id      , ( SELECT MIN(ImageID)             FROM PhotoGallery            WHERE ImageDate > T.ImageDate ) AS Prev_id   FROM PhotoGallery as T   WHERE ImageID = 118";
    $rsNav = mysql_query($query_rsNav, $TNT) or die(mysql_error());
    $row_rsNav = mysql_fetch_assoc($rsNav);
    $totalRows_rsNav = mysql_num_rows($rsNav);
    (Sorry that the above code is displaying a bit messy, when I copy and paste it that's how it's showing up for some reason)

    Where I have the ImageID as "118" I need to actually output ImageID from another query on the page. That query is named rsImage. I'm not sure how to output results from another query within this query. I keep getting errors when trying to do so. I know how to do this in ColdFusion, just not PHP. Can someone help me out with that please? Thanks a ton!

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I figured it out. Thanks!

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One more question about this.

    I have this query working when I test the query:
    PHP Code:
    $query_rsNav "SELECT ImageDate , ( SELECT MAX(ImageID) FROM PhotoGallery WHERE ImageDate < T.ImageDate ) AS next_id , ( SELECT MIN(ImageID) FROM PhotoGallery WHERE ImageDate > T.ImageDate ) AS Prev_id FROM PhotoGallery as T WHERE ImageID = $row_rsImage[ImageID] AND CatID = $row_rsImage[CatID]"
    However, when I try to output the data with the following code, it doesn't display anything.
    PHP Code:
    <?php echo $row_rsNav['prev_id']; ?>
    <?php 
    echo $row_rsNav['next_id']; ?>
    Am I doing something wrong when I try to output the data?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    maybe the query is returning 0 rows

    why do you have this compound WHERE clause?
    Code:
    WHERE ImageID = $row_rsImage[ImageID] AND CatID = $row_rsImage[CatID]"
    that's not the same logic as the query in post #1 (which looks amazingly familiar )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh wow, I had actually posted the wrong code in the first post. I had meant to post this:
    Code:
    SELECT ImageDate
         , ImageName
         , ThumbName
         , ( SELECT MAX(ImageID) 
               FROM JamesTattoos 
              WHERE ImageDate < T.ImageDate ) AS prev_id
         , ( SELECT MIN(ImageID) 
               FROM JamesTattoos 
              WHERE ImageDate > T.ImageDate ) AS next_id
      FROM JamesTattoos as T
     WHERE ImageID = 159
    Which is code I had gotten from you r397. (Thanks worked brilliantly!)

    Let me post a more simple version with some ID's actually in there. This does return results when tested in phpmyadmin.

    PHP Code:
    $query_rsNav "SELECT ImageDate , ( SELECT MAX(ImageID) FROM PhotoGallery WHERE ImageDate < T.ImageDate ) AS next_id , ( SELECT MIN(ImageID) FROM PhotoGallery WHERE ImageDate > T.ImageDate ) AS Prev_id FROM PhotoGallery as T WHERE ImageID = 117 AND CatID = 1"
    but when I try to output it with the following code nothing displays.

    PHP Code:
    <?php echo $row_rsNav['next_id']; ?>
    <?php 
    echo $row_rsNav['prev_id']; ?>
    I'm a bit confused as to why when I test it there are results, but I can't output them.

  8. #8
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it all figured out...
    Thanks for the responses!

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by sikk66 View Post
    Got it all figured out...
    that's twice you've posted that

    this time, would you mind telling people what it was?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, no problem.

    My prev_id needed to start with a uppercase letter. (i.e. Prev_id) and next_id started with lovercase and I switched it start with uppercase as well to keep it consistent. that solved that problem.

    Then I noticed the problem with my AND statement(Which I believe you referred to in an earlier post r937) as it was pulling all the ID's regardless of the category(catID) that was selected.

    After some tinkering, I came up with this:

    Code:
    SELECT ImageDate (
    
    SELECT MAX( ImageID )
    FROM PhotoGallery
    WHERE ImageDate < T.ImageDate
    AND CatID = $row_rsImage[CatID]
    ) AS Next_id, (
    
    SELECT MIN( ImageID )
    FROM PhotoGallery
    WHERE ImageDate > T.ImageDate
    AND CatID = $row_rsImage[CatID]
    ) AS Prev_id
    FROM PhotoGallery AS T
    WHERE ImageID = $row_rsImage[ImageID]
    And this seems to be doing just what I need. Only selecting the ID's based on the selected category(CatID).

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sweet
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, so I thought I had this figured out. When I use this code(it navigates through photos in a gallery) The next link seems to sometimes skip over some images within that category, but the previous link does not skip any at all...

    Do I have an obvious error in there?

    Code:
    SELECT ImageDate (
    
    SELECT MAX( ImageID )
    FROM PhotoGallery
    WHERE ImageDate < T.ImageDate
    AND CatID = $row_rsImage[CatID]
    ) AS Next_id, (
    
    SELECT MIN( ImageID )
    FROM PhotoGallery
    WHERE ImageDate > T.ImageDate
    AND CatID = $row_rsImage[CatID]
    ) AS Prev_id
    FROM PhotoGallery AS T
    WHERE ImageID = $row_rsImage[ImageID]

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i guess maybe i should have noticed the problem, but you said the query worked, so i did not look at it closely...

    the problem is, you are taking the MAX and MIN of the image_id, but you are using the sequence determined by the image date

    assuming you still want sequencing by date, you'll have to change the query substantially

    what is the datatype of the imagedate column? DATE, DATETIME, or INTEGER?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again for your response. ImageDate is a timestamp.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by sikk66 View Post
    ImageDate is a timestamp.
    so it gets updated automatically whenever you update anything about the image, including fixing spelling errors in the description?

    if that's true, then maybe you'd rather not use it as the sequencing column for your images
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, I just checked and the timestamp stays the same, even if I update the description.

  17. #17
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    BUT, I just realized that like a week ago I had gone in and manually changed the time in one of the ImageDate entries to change the order of where that image appeared and that was what seemed to be causing the problem I was having. I deleted that entry and now the navigation seems to be working ok again.

    Maybe there still is a better way to be doing this?

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    timestamp as in TIMESTAMP, or a timestamp INTEGER as in 1252468937?

    in both cases, you should consider changing it to DATETIME

    and yes, if you use DATETIME, it is unlikely that two images will share the same value, so you can quite easily change the sequence based on these values

    however, if you think there's a chance that you might wish to re-sequence the images, it would be silly to have to adjust datetime values for this purpose... why not just use an integer called "sort-seq" or something?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you so much! You are always very helpful and I certainly do appreciate it!
    I will try out your suggestions, and if I run into any problems I will post back.
    Thanks again!


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
  •