SitePoint Sponsor |
|
User Tag List
Results 1 to 19 of 19
-
Jul 2, 2009, 06:45 #1
- 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>
Thanks!
-
Jul 2, 2009, 09:12 #2
- 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
}
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 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
-
Jul 3, 2009, 08:19 #3
- 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);
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!
-
Jul 3, 2009, 09:56 #4
- Join Date
- Dec 2006
- Location
- Atlanta, GA
- Posts
- 134
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I figured it out. Thanks!
-
Jul 3, 2009, 10:11 #5
- 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]";
PHP Code:<?php echo $row_rsNav['prev_id']; ?>
<?php echo $row_rsNav['next_id']; ?>
-
Jul 3, 2009, 10:30 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 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]"
)
-
Jul 3, 2009, 10:42 #7
- 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
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";
PHP Code:<?php echo $row_rsNav['next_id']; ?>
<?php echo $row_rsNav['prev_id']; ?>
-
Jul 3, 2009, 11:52 #8
- 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!
-
Jul 3, 2009, 12:29 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jul 3, 2009, 13:41 #10
- 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]
-
Jul 3, 2009, 13:56 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
sweet
-
Jul 17, 2009, 07:58 #12
- 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]
-
Jul 17, 2009, 09:19 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 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?
-
Jul 17, 2009, 09:31 #14
- 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.
-
Jul 17, 2009, 09:38 #15
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jul 17, 2009, 09:41 #16
- 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.
-
Jul 17, 2009, 09:46 #17
- 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?
-
Jul 17, 2009, 10:02 #18
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 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?
-
Jul 17, 2009, 10:37 #19
- 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