Converting a query from ColdFusion to PHP

[sikk66]

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:

<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!

[Jacotheron]

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):

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):

$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

[sikk66]

Okay, I got the blow code to work for my query:

$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!

[sikk66]

I figured it out. Thanks!

[sikk66]

One more question about this.

I have this query working when I test the query:

$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 echo $row_rsNav['prev_id']; ?>
<?php echo $row_rsNav['next_id']; ?>

Am I doing something wrong when I try to output the data?

[r937]

maybe the query is returning 0 rows

why do you have this compound WHERE clause?

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 )

[sikk66]

Oh wow, I had actually posted the wrong code in the first post. I had meant to post this:

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.

$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 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.

[sikk66]

Got it all figured out...
Thanks for the responses!

[r937]

Got it all figured out...

that's twice you've posted that

this time, would you mind telling people what it was?

[sikk66]

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:

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).

[r937]

sweet

[sikk66]

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?

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]

[r937]

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?

[sikk66]

Thanks again for your response. ImageDate is a timestamp.

[r937]

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

[sikk66]

No, I just checked and the timestamp stays the same, even if I update the description.

[sikk66]

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?

[r937]

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?

[sikk66]

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!