14 Replies - 4226 Views - Last Post: 25 September 2008 - 02:23 AM Rate Topic: -----

#1 jeffmc21  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 64
  • Joined: 27-November 07

PHP within Coldfusion

Post icon  Posted 22 August 2008 - 12:48 PM

I am curious about using <cfinclude> to include an external file that has a call to a database and the parsing of the results. I'm wanting to do the call and the parse in PHP, but the page that it will be included on is Coldfusion. Is that possible?
Is This A Good Question/Topic? 0
  • +

Replies To: PHP within Coldfusion

#2 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: PHP within Coldfusion

Posted 22 August 2008 - 01:03 PM

View Postjeffmc21, on 22 Aug, 2008 - 12:48 PM, said:

I am curious about using <cfinclude> to include an external file that has a call to a database and the parsing of the results. I'm wanting to do the call and the parse in PHP, but the page that it will be included on is Coldfusion. Is that possible?


I don't think a cfinclude would work because it would not execute the php code, just display it. I don't use php, but my guess would be to try cfhttp instead. Then include the cfhttp results. If you are using CF8, another slightly more complicated option might be:

http://www.houseoffu...id:51958#280022
Was This Post Helpful? 0
  • +
  • -

#3 jeffmc21  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 64
  • Joined: 27-November 07

Re: PHP within Coldfusion

Posted 22 August 2008 - 04:53 PM

I'm actually wanting to execute this connection, SQL call, and results display:

<?php

// Create the connection file with connection information

$host = "hostname";
$username = "username";
$password = "password"; 

// Connect to Database

$connection = @mysql_connect($host,$username,$password);
mysql_select_db(database_name,$connection);

$result = mysql_query("SELECT DATE_FORMAT(date,'%b %d, %Y') as article_day, `article_name`, `article_link`, `article_text` FROM `table_name` WHERE `date` <= CURDATE() ORDER BY event_date LIMIT 3");


 while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo '<div class="cssarticle">
	<div class="headline">'. $row["article_name"] .'</div>
	<div class="articledate">'. $row["article_day"].'</div>
	<div class="articleintro">'. $row["article_text"].'</div>
	<div class="articlelink"><a href="'. $row["article_link"].'">Read More...</a></div>
	</div>';
}

mysql_free_result($result);


?>



I'm extremely, extremely new to Coldfusion, so if someone could help show me how to do this, it'd be better than the initial request of PHP in CF.

Thanks so much!!
Was This Post Helpful? 0
  • +
  • -

#4 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: PHP within Coldfusion

Posted 22 August 2008 - 06:15 PM

View Postjeffmc21, on 22 Aug, 2008 - 04:53 PM, said:

I'm actually wanting to execute this connection, SQL call, and results display:

<?php

// Create the connection file with connection information

$host = "hostname";
$username = "username";
$password = "password"; 

// Connect to Database

$connection = @mysql_connect($host,$username,$password);
mysql_select_db(database_name,$connection);

$result = mysql_query("SELECT DATE_FORMAT(date,'%b %d, %Y') as article_day, `article_name`, `article_link`, `article_text` FROM `table_name` WHERE `date` <= CURDATE() ORDER BY event_date LIMIT 3");


 while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo '<div class="cssarticle">
	<div class="headline">'. $row["article_name"] .'</div>
	<div class="articledate">'. $row["article_day"].'</div>
	<div class="articleintro">'. $row["article_text"].'</div>
	<div class="articlelink"><a href="'. $row["article_link"].'">Read More...</a></div>
	</div>';
}

mysql_free_result($result);


?>



I'm extremely, extremely new to Coldfusion, so if someone could help show me how to do this, it'd be better than the initial request of PHP in CF.

Thanks so much!!



No problem. Yes, it should be easy to convert the php code to ColdFusion.

To run queries against a database you generally create a datasource in the ColdFusion Administrator. The datasource stores all of the settings (db name, username, etc). So whenever you want to run a query, you just supply the datasource name. Technically you could open a dynamic connection to the database, if you want. But it is really more work than is necessary. If you need help with the datasource settings, just ask. (Include your CF and mySQL version too)

To run queries, you use the <cfquery> tag. You just supply a "name" and "datasource", then put your sql statement inside the tags. ColdFusion then populates a variable with the query results.

// NOT TESTED
// Connect to Database
<cfquery name="getArticle" datasource="yourDatasourceNameHere">
SELECT DATE_FORMAT(date,'%b %d, %Y') as article_day, `article_name`, `article_link`, `article_text` 
FROM	`table_name` 
WHERE  `date` <= CURDATE() 
ORDER BY event_date 
LIMIT 3
</cfquery>



After you run the query, you can display the results using the <cfoutput> tag. Just provide the name you used for the query. CF will then iterate through the results. Inside the loop, the query columns are available as local variables. To output the value of a variable, you use the syntax: #variableName#. The # signs tell CF to evaluate what's inside.

So your while loop would be replaced with something like this:
<cfoutput query="getArticle">
   <div class="cssarticle">
	  <div class="headline"> #article_name#</div>
	  <div class="articledate">#article_day#</div>
	  <div class="articleintro">#article_text#</div>
	  <div class="articlelink"><a href="'#article_link#">Read More...</a></div>
   </div>
</cfoutput>



You might also want to check out livedocs (online documentation). The descriptions and examples are very helpful. It is the best reference for CF syntax. I know CF pretty well, but I use it all the time!
http://livedocs.adob...cs/00000316.htm

HTH

This post has been edited by sansclue: 22 August 2008 - 06:17 PM

Was This Post Helpful? 0
  • +
  • -

#5 jeffmc21  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 64
  • Joined: 27-November 07

Re: PHP within Coldfusion

Posted 22 August 2008 - 08:52 PM

That sounds, and look, exactly like what I'm wanting to accomplish. I'm operating a Cartweaver site and don't actually have CF installed on my machine, so I'm not sure about creating datasources, etc. How would I go about dynamically connecting to the database using CF?
Was This Post Helpful? 0
  • +
  • -

#6 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: PHP within Coldfusion

Posted 22 August 2008 - 10:10 PM

View Postjeffmc21, on 22 Aug, 2008 - 08:52 PM, said:

That sounds, and look, exactly like what I'm wanting to accomplish. I'm operating a Cartweaver site and don't actually have CF installed on my machine, so I'm not sure about creating datasources, etc. How would I go about dynamically connecting to the database using CF?


I would ask whomever is hosting the site to advise you on setting up a datasource. Datasources are really the way to go for a number of reasons, and you only have to do it once.

Personally I would not recommend using dynamic connections unless you know your way around CF and java and are aware of the pros and cons. Dynamic connections are more involved, so there is greater room for error. Plus they lack some of the performance enhancements of datasources, like connection pooling.

http://tutorial402.easycfm.com/
Was This Post Helpful? 0
  • +
  • -

#7 jeffmc21  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 64
  • Joined: 27-November 07

Re: PHP within Coldfusion

Posted 23 August 2008 - 09:23 AM

View Postsansclue, on 22 Aug, 2008 - 10:10 PM, said:

View Postjeffmc21, on 22 Aug, 2008 - 08:52 PM, said:

That sounds, and look, exactly like what I'm wanting to accomplish. I'm operating a Cartweaver site and don't actually have CF installed on my machine, so I'm not sure about creating datasources, etc. How would I go about dynamically connecting to the database using CF?


I would ask whomever is hosting the site to advise you on setting up a datasource. Datasources are really the way to go for a number of reasons, and you only have to do it once.

Personally I would not recommend using dynamic connections unless you know your way around CF and java and are aware of the pros and cons. Dynamic connections are more involved, so there is greater room for error. Plus they lack some of the performance enhancements of datasources, like connection pooling.

http://tutorial402.easycfm.com/


Does it matter at all that I'm connecting to a database that is hosted by a different company, under a different domain? So it would be a remote connection. Is a datasource still the best approach or does that matter?
Was This Post Helpful? 0
  • +
  • -

#8 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: PHP within Coldfusion

Posted 23 August 2008 - 09:46 AM

View Postjeffmc21, on 23 Aug, 2008 - 09:23 AM, said:

Does it matter at all that I'm connecting to a database that is hosted by a different company, under a different domain? So it would be a remote connection. Is a datasource still the best approach or does that matter?


I have rarely used remote connections. But I don't know if it makes a difference here. IIRC the connection string (or settings) would be the same whether you are using a datasource or a dynamic connection. In cases where there is no difference, a datasource is usually preferable.
Was This Post Helpful? 0
  • +
  • -

#9 jeffmc21  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 64
  • Joined: 27-November 07

Re: PHP within Coldfusion

Posted 25 August 2008 - 01:42 PM

I went back and now I'm using the same server for the database table that I am for the rest of the site (my hosting co wouldn't allow remote connections).
So I added the necessary table to my already existing database.

So, using the CF code from your earlier posts, how would I connect to the database/table? The SQL isn't my problem (yet). I'm getting the error at line 3, because I don't know what to put for my datasource name. I already have a datasource that connects to the database to display and operate the rest of the site. It's specified in my Application.cfm file. How do I connect this call to the database?
Was This Post Helpful? 0
  • +
  • -

#10 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: PHP within Coldfusion

Posted 26 August 2008 - 04:42 AM

View Postjeffmc21, on 25 Aug, 2008 - 01:42 PM, said:

I went back and now I'm using the same server for the database table that I am for the rest of the site (my hosting co wouldn't allow remote connections).
So I added the necessary table to my already existing database.

So, using the CF code from your earlier posts, how would I connect to the database/table? The SQL isn't my problem (yet). I'm getting the error at line 3, because I don't know what to put for my datasource name. I already have a datasource that connects to the database to display and operate the rest of the site. It's specified in my Application.cfm file. How do I connect this call to the database?


Yes, some hosting companies won't allow remote connections.

If all of your tables are in the same database, and you already have a datasource for that database, just use that datasource name in the cfquery.

<cfquery name="getArticle" datasource="yourDatasourceNameHere">

Or if you're storing the datasource in a variable, use that variable name

<cfquery name="getArticle" datasource="#variableNameHere#">

I cannot be more specific because I don't know what kind of variable you are using to store datasource name. ie You could be using an application variable (#application.datasourceName#), request variable (#request.datasourceName#), etc...
Was This Post Helpful? 1
  • +
  • -

#11 jeffmc21  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 64
  • Joined: 27-November 07

Re: PHP within Coldfusion

Posted 26 August 2008 - 09:18 AM

I've had to reorganize my SQL call because its MSSQL (my first time with it), and several of the commands I was using don't work in MSSQL. Specifically, the LIMIT command doesn't work, so I've had to use

SELECT TOP 3 * FROM table_name


This works fine now, because there aren't more than three rows in the database. However, as the database populates (from an online form), I will actually want to get the three most recent entries (the bottom 3). Are there MSSQL commands to accomplish this?
Was This Post Helpful? 0
  • +
  • -

#12 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: PHP within Coldfusion

Posted 26 August 2008 - 09:37 AM

View Postjeffmc21, on 26 Aug, 2008 - 09:18 AM, said:

I've had to reorganize my SQL call because its MSSQL (my first time with it), and several of the commands I was using don't work in MSSQL. Specifically, the LIMIT command doesn't work, so I've had to use

SELECT TOP 3 * FROM table_name


This works fine now, because there aren't more than three rows in the database. However, as the database populates (from an online form), I will actually want to get the three most recent entries (the bottom 3). Are there MSSQL commands to accomplish this?



Yes, just add back the ORDER BY clause and use DESC to get the three records with the most recent dates.


SELECT TOP 3 *
FROM table_name
ORDER BY event_date DESC
Was This Post Helpful? 1
  • +
  • -

#13 jeffmc21  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 64
  • Joined: 27-November 07

Re: PHP within Coldfusion

Posted 28 August 2008 - 08:57 PM

Everything you've said has been perfect and exactly what I needed. One last question: How would I strip the time from the datetime column called article_date? its the one we are ordering by, and I want the date portion to display but I get a timestamp of 00:00:00 because in the building of the db, I separated the date and time with some code. How can I remove the time from the call and subsequent display of results?
Was This Post Helpful? 0
  • +
  • -

#14 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: PHP within Coldfusion

Posted 29 August 2008 - 07:57 AM

View Postjeffmc21, on 28 Aug, 2008 - 08:57 PM, said:

Everything you've said has been perfect and exactly what I needed. One last question: How would I strip the time from the datetime column called article_date? its the one we are ordering by, and I want the date portion to display but I get a timestamp of 00:00:00 because in the building of the db, I separated the date and time with some code. How can I remove the time from the call and subsequent display of results?


Yes, datetime objects are always returned in standard format, which includes time. Even if it is only 00:00:00. There are two options. But the simplest is to let CF handle the formatting.

- Leave the query as is and format the datetime value in your <cfoutput> using the DateFormat function. You can use whatever mask you want like "mm/dd/yyyy".

<cfoutput query="getArticle">
	<!--- not sure if the column name is "article_day" or ""article_date" --->
	<div class="articledate">#DateFormat(article_date, "mm/dd/yyyy")#</div>
</cfoutput>



That said, you could also format the value in your sql using MS SQL's convert() function. However, that would require converting the value to a string / varchar. That impacts sorting and may affect how CF interprets the value. So IMO, option #1 is the better option.
Was This Post Helpful? 0
  • +
  • -

#15 Nikimathew  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 25-September 08

Re: PHP within Coldfusion

Posted 25 September 2008 - 02:23 AM

Sun has provided a number of JSR-223 ScriptEngine and ScriptEngineFactory implementations and there are Java implementations of a number of languages.
Combine that with a simple custom tag and the code you see above becomes a trivial extension for ColdFusion 8!
------------------
Nikimathew
word of mouth marketing
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1