Forums

Home » Liferay Portal » English » Liferay Legacy

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Ramanjaneyulu cheekatla
PHP+MYSQL
October 23, 2007 2:49 AM
Answer

Ramanjaneyulu cheekatla

Rank: New Member

Posts: 11

Join Date: February 22, 2007

Recent Posts

Hi,


I'm trying to connect to mysql in php portlet but its thronging the following error
So Could you please suggest me.

/D:/ZPortal/Liferay 4.3.1/liferay-portal-jboss-tomcat-4.2-4.3.1/server/default/deploy/sample-php-portlet.war/index.php:9: Warning: A link to the server could not be established. url=jdbc:mysql://jdbc:mysql://localhost:3306/ driver=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource com.caucho.quercus.QuercusModuleException: Can't find database for driver 'com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource' and url 'jdbc:mysql://jdbc:mysql://localhost:3306/' [mysql_connect] /D:/ZPortal/Liferay 4.3.1/liferay-portal-jboss-tomcat-4.2-4.3.1/server/default/deploy/sample-php-portlet.war/index.php:12: Warning: A link to the server could not be established. url=jdbc:mysql://localhost:3306/ driver=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource com.caucho.quercus.QuercusModuleException: Can't find database for driver 'com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource' and url 'jdbc:mysql://localhost:3306/' [mysql_error] Cannot connect to the database:

Thanking you,
Ram
Ramanjaneyulu cheekatla
RE: PHP+MYSQL
October 26, 2007 3:42 AM
Answer

Ramanjaneyulu cheekatla

Rank: New Member

Posts: 11

Join Date: February 22, 2007

Recent Posts

Hi,

Could you please help me on PHP + MySQL portlet.
Where should I specify the MySQL connection?
I gone through the following website and made configuration setting accordingly. But still it’s not connecting.
http://www.caucho.com/

Thanking you,
Julien Cornouiller
RE: PHP+MYSQL
October 26, 2007 4:11 AM
Answer

Julien Cornouiller

Rank: Junior Member

Posts: 39

Join Date: September 18, 2007

Recent Posts

where did you download your MySQL Driver (com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource looks bad, i never view this driver before)

please can you try to download MySQL JDBC Driver from official MySQL Web Site http://dev.mysql.com/downloads/connector/j/5.1.html
Ed F.
RE: PHP+MYSQL
February 1, 2008 11:42 AM
Answer

Ed F.

Rank: Expert

Posts: 280

Join Date: June 26, 2006

Recent Posts

Hi all, I am trying to connect php to mysql inside Liferay and ran into a similar problem.

Has anyone found a solution for this?

1/C:/Liferay/ext/servers/tomcat/webapps/sample-php-portlet/index.php:8: Warning: A link to the server could not be established. url=jdbc:mysql://localhost:3306/ driver=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource com.caucho.quercus.QuercusModuleException: Can't find database for driver 'com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource' and url 'jdbc:mysql://localhost:3306/' [mysql_connect] Error connecting to mysql


I've tried:
mysql_connect("localhost", "root", "pw");
mysql_connect("jdbc:mysql://localhost/lportal?user=root&password=pw", "root", "pw");


Any help would be extremely appreciated.
Thanks!
Toni Aho
RE: PHP+MYSQL
February 14, 2008 10:41 PM
Answer

Toni Aho

Rank: New Member

Posts: 13

Join Date: September 20, 2007

Recent Posts

I also have exactly the same problem. I've found some information from caucho bug page:
http://bugs.caucho.com/view.php?id=1859

But i'm not sure, is quercus source code available for my version of liferay (4.3.2). It just needs to update few classes of quercus.jar package...
Toni Aho
RE: PHP+MYSQL
February 15, 2008 12:39 AM
Answer

Toni Aho

Rank: New Member

Posts: 13

Join Date: September 20, 2007

Recent Posts

Ok, i made a little trick and got this working....

I made my own portlet class which extends PHPPortlet. I overrided initQuercus()-method and i created new datasource. With this call you can add your created datasource:

((QuercusServlet)quercusServlet).setDatabase(DATASOURCE);

after calling quercusservlet.init(config)
And in php-code i don't even have to call "mysql_connect()"-method. I can straight go to mysql_query() and everything's fine.
Jerry Niu
RE: PHP+MYSQL
February 15, 2008 9:53 AM
Answer

Jerry Niu

LIFERAY STAFF

Rank: Expert

Posts: 451

Join Date: June 21, 2006

Recent Posts

Thanks for the suggestion Toni.

We will make it easier for php database access in 5.0 as described in LEP-5048 - Easier development of database driven PHP portlets.
Scott Westbrook
RE: PHP+MYSQL
February 15, 2008 12:33 PM
Answer

Scott Westbrook

Rank: New Member

Posts: 14

Join Date: February 11, 2008

Recent Posts

You can also use the PDO function in PHP to get access to MySQL databases (I also had to this to get access to MSSQL).

In your PHP code:

$dbh = new PDO('java:comp/env/jdbc/MySQLDatabase');

Create a context.xml file in your portlet's META-INF folder:

<Context path="/FOO" docBase="FOO"
debug="5" reloadable="true" crossContext="true">

<Resource name="jdbc/MySQLDatabase" auth="Container" type="javax.sql.DataSource"
maxActive="10" maxIdle="5" maxWait="10000"
username="user" password="pass" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/MySQLDatabase?useUnicode=true&amp;characterEncoding=UTF-8"/>
</Context>


And finally add a resource-ref to your web.xml file in the portlet's WEB-INF folder:

<web-app>
...
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/MySQLDatabase</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
michael jonsson
RE: PHP+MYSQL
March 6, 2008 6:50 AM
Answer

michael jonsson

Rank: New Member

Posts: 3

Join Date: March 6, 2008

Recent Posts

I have follow your example without any good results. The portlet only shows blank.
I want to connect my php portlet to mysql.

have tried:
1.
try {
$dbh = new PDO('mysql:host=XXXXX; port=XXXX; dbname=poll', "USER","PASSWORD");
foreach ($dbh->query('SELECT * from results') as $row) {
echo "<pre>";
print_r($row);
echo "</pre>";
}
$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();

AND
2.
//Querying the database to get poll results
//connection to the database
$dbhandle = mysql_connect("XXXXX", "USER", "PASSWORD")
or die("Unable to connect to MySQL");

//select a database to work with
$selected = mysql_select_db("poll",$dbhandle)
or die("Could not select examples");

//execute the SQL query and return records
$result = mysql_query("SELECT * FROM results");

$num_poller = mysql_num_rows($result);
$total_votes = 0;

//fetch the data from the database
while ($row = mysql_fetch_array($result)) {
$total_votes += $row{'num_votes'}; //calculating total number of votes
}
...................


so my question is how do I connect to sql ???
Scott Westbrook
RE: PHP+MYSQL
March 6, 2008 8:46 AM
Answer

Scott Westbrook

Rank: New Member

Posts: 14

Join Date: February 11, 2008

Recent Posts

Did you try adding the JNDI resource to your portlet's web.xml and context.xml files?

I had the same problems using PHP's mysql_connect and explicit MySQL connection parameters in the PDO command. I had to create a JNDI resource-ref and then reference that in my PDO command, e.g. $dbh = new PDO('java:comp/env/jdbc/MySQLDatabase') where your web.xml and context.xml contain the connection parameters to use.

This worked successfully for me with LifeRay 4.4.0 and above.
michael jonsson
RE: PHP+MYSQL
March 7, 2008 4:55 AM
Answer

michael jonsson

Rank: New Member

Posts: 3

Join Date: March 6, 2008

Recent Posts

Scott Dale Westbrook:
Did you try adding the JNDI resource to your portlet's web.xml and context.xml files?


I have tried writeing exactly as you posted earlier and when it didn´t work I posted my question. I will try again but if anyone could post there code in: web.xml, context.xml and index.php i would be more then happy.

also I dont get a web.xml after I have installed the portlet? Can I just create one then ? or do I then miss half of the data that should be in it??

my portlet(is called arial):
C:\liferay\webapps\arial\
index.php
  1
  2<?php /*
  3try {
  4   $dbh = new PDO('mysql:host=localhost; port=00; dbname=XXXXl', "XXXX","");
  5   foreach ($dbh->query('SELECT * from results') as $row) {
  6    echo "<pre>";
  7      print_r($row);
  8    echo "</pre>";   
  9   }
 10   $dbh = null;
 11} catch (PDOException $e) {
 12  print "Error!: " . $e->getMessage() . "<br/>";
 13   die();
 14}
 15*/
 16//Querying the database to get poll results
 17//connection to the database
 18$dbhandle = mysql_connect("localhost", "XXXXX", "XXXXXX")
 19  or die("Unable to connect to MySQL");
 20
 21//select a database to work with
 22$selected = mysql_select_db("test",$dbhandle)
 23  or die("Could not select examples");
 24
 25//execute the SQL query and return records
 26$result = mysql_query("SELECT * FROM results");
 27
 28$num_poller = mysql_num_rows($result);
 29$total_votes = 0;
 30
 31//fetch the data from the database
 32while ($row = mysql_fetch_array($result)) {
 33  $total_votes += $row{'num_votes'};  //calculating total number of votes
 34}
 35
 36//nulling the pointer $result
 37mysql_data_seek($result,0);
 38
 39//close the connection
 40mysql_close($dbhandle);
 41//
 42//Tell gd2, where your fonts reside
 43$font = 'arial.ttf';
 44
 45//Set starting point for drawing
 46$y = 50;
 47
 48//Specify constant values
 49$width = 700; //Image width in pixels
 50$bar_height = 20; //Bars height
 51$height = $num_poller * $bar_height * 1.5 + 70; //Calculating image height
 52$bar_unit = ($width - 400) / 100; //Distance on the bar chart standing for 1 unit
 53
 54//Create the image resource
 55$image = ImageCreate($width, $height);
 56
 57//We are making four colors, white, black, blue and red
 58$white = ImageColorAllocate($image, 255, 255, 255);
 59$black = ImageColorAllocate($image, 0, 0, 0);
 60$red   = ImageColorAllocate($image, 255, 0, 0);
 61$blue  = imagecolorallocate($image,0,0,255);
 62
 63//Create image background
 64ImageFill($image,$width,$height,$white);
 65//Draw background shape
 66ImageRectangle($image, 0, 0, $width-1, $height-1, $black);
 67//Output header
 68ImageTTFText($image, 16, 0, $width/3 + 50, $y - 20, $black, $font, 'Projekt status');
 69
 70while ($row = mysql_fetch_object($result)) {
 71  if ($total_votes > 0)
 72    $percent = intval(round(($row->num_votes/$total_votes)*100));
 73  else
 74    $percent = 0;
 75
 76//Output header for a particular value
 77ImageTTFText($image,12,0,10, $y+($bar_height/2), $black, $font, $row->book_type);
 78//Output percentage for a particular value
 79ImageTTFText($image, 12, 0, 170, $y + ($bar_height/2),$red,$font,$percent.'%');
 80
 81$bar_length = $percent * $bar_unit;
 82
 83//Draw a shape that corresponds to 100%
 84ImageRectangle($image, $bar_length+221, $y-2, (220+(100*$bar_unit)), $y+$bar_height, $black);
 85//Output a bar for a particular value
 86ImageFilledRectangle($image,220,$y-2,220+$bar_length, $y+$bar_height, $blue);
 87//Output the number of votes
 88ImageTTFText($image, 12, 0, 250+100*$bar_unit, $y+($bar_height/2), $black, $font, $row->num_votes.' votes cast.');
 89
 90//Going down to the next bar
 91$y = $y + ($bar_height * 1.5);
 92
 93}
 94
 95//Tell the browser what kind of file is come in
 96header("Content-Type: image/jpeg");
 97
 98//Output the newly created image in jpeg format
 99ImageJpeg($image);
100
101//Free up resources
102ImageDestroy($image);
103
104?>

C:\liferay\webapps\arial\META-INF\
context.xml
1
2- <Context path="/FOO" docBase="Foo" debug="5" reloadable="true" crossContext="true">
3  <Resource name="jdbc/MySQLDatabase" auth="Container" type="javax.sql.DataSource" maxActive="10" maxIdle="5"    maxWait="10000" username="user" password="pass" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/MySQLDatabase?useUnicode=true&characterEncoding=UTF-8" />
4  </Context>

C:\liferay\webapps\arial\WEB-INF
web.xml
 1
 2  <?xml version="1.0" encoding="UTF-8" ?>
 3  <!DOCTYPE web-app (View Source for full doctype...)>
 4- <web-app>
 5  <display-name>arial</display-name>
 6- <listener>
 7  <listener-class>com.liferay.portal.kernel.servlet.PortletContextListener</listener-class>
 8  </listener>
 9- <servlet>
10  <servlet-name>arial</servlet-name>
11  <servlet-class>com.liferay.portal.kernel.servlet.PortletServlet</servlet-class>
12- <init-param>
13  <param-name>portlet-class</param-name>
14  <param-value>com.liferay.util.bridges.php.PHPPortlet</param-value>
15  </init-param>
16  <load-on-startup>0</load-on-startup>
17  </servlet>
18- <servlet-mapping>
19  <servlet-name>arial</servlet-name>
20  <url-pattern>/arial/*</url-pattern>
21  </servlet-mapping>
22- <taglib>
23  <taglib-uri>http://java.sun.com/portlet</taglib-uri>
24  <taglib-location>/WEB-INF/tld/liferay-portlet.tld</taglib-location>
25  </taglib>
26- <resource-ref>
27  <description>DB Connection</description>
28  <res-ref-name>jdbc/MySQLDatabase</res-ref-name>
29  <res-type>javax.sql.DataSource</res-type>
30  <res-auth>Container</res-auth>
31  </resource-ref>
32  </web-app>


WHAT IS WRONG ? I´am not sure if all the searh strings are right becuase i have just copied from Scott Dale Westbrook
example.

(Just the index.php works on my local machine. But not when i upload it so it beacome a portal ! )
michael jonsson
RE: PHP+MYSQL
March 11, 2008 1:18 AM
Answer

michael jonsson

Rank: New Member

Posts: 3

Join Date: March 6, 2008

Recent Posts

After readering liferay wiki, forums and every read-&helpfile, I' am now looking for a new open source portal. Liferay looks nice but has to many bugs for me, doesn't feel completed yet. And not the best base for us that want to develop i think.

UPDATE: I came up with the idea of changing the quercus against real php5! Is that possible? We have already got php5 to work with tomcat. But where(what file) do we put that liferay should use php5 instead of quercus???

PLEASE HELP !
Andrew Tillinghast
RE: PHP+MYSQL
December 1, 2009 8:21 AM
Answer

Andrew Tillinghast

Rank: Junior Member

Posts: 60

Join Date: September 5, 2008

Recent Posts

I know this is an old thread but I though I should update seeing as I found the solution.

the web.xml entry is sufficient if you want to access the datasource via PDO:
1
2    <resource-ref>
3        <description>DB Connection</description>
4        <res-ref-name>jdbc/ojrac</res-ref-name>
5        <res-type>javax.sql.DataSource</res-type>
6        <res-auth>Container</res-auth>
7      </resource-ref>


However if you want to use the mysql_connect functions you need to pass the database through to resin, this is also done in web.xml
 1
 2    <servlet>
 3        <servlet-name>TVChannels</servlet-name>
 4        <servlet-class>com.liferay.portal.kernel.servlet.PortletServlet</servlet-class>
 5        <init-param>
 6            <param-name>portlet-class</param-name>
 7            <param-value>com.liferay.util.bridges.php.PHPPortlet</param-value>
 8        </init-param>
 9        <init-param>
10            <param-name>database</param-name>
11            <param-value>java:comp/env/jdbc/ojrac</param-value>
12        </init-param>
13        <load-on-startup>0</load-on-startup>
14    </servlet>


One more issue that might get you, if your running on Jboss you need to expose the JNDI to the war via a jboss.web.xml
 1
 2<?xml version="1.0" encoding="UTF-8"?>
 3<jboss-web>
 4    <resource-ref>
 5    <!-- exposes inside the war as java:comp/env/jdbc/ -->
 6        <res-ref-name>jdbc/ojrac</res-ref-name>
 7        <res-type>javax.sql.DataSource</res-type>
 8        <!-- JNDI as named in jboss -->
 9        <jndi-name>java:/jdbc/ojrac</jndi-name>
10    </resource-ref>
11</jboss-web>


Just for completeness the jar for mysql connector needs to be in your $JBOSS_HOME/server/{Server configuration}/lib directory and your jboss server needs to be restarted before it loads

With the Jar loaded you can create a JNDI datasource in $JBOSS_HOME/server/{Server configuration}/deploy, for example ojrac-ds.xml
 1
 2<?xml version="1.0" encoding="UTF-8"?>
 3<datasources>
 4  <local-tx-datasource>
 5    <jndi-name>jdbc/ojrac</jndi-name>
 6    <connection-url>jdbc:mysql://server.domain.tld:3306/{database name}</connection-url>
 7    <driver-class>com.mysql.jdbc.Driver</driver-class>
 8    <user-name>user</user-name>
 9    <password>pass</password>
10    <min-pool-size>0</min-pool-size>
11    <max-pool-size>20</max-pool-size>
12    <idle-timeout-minutes>5</idle-timeout-minutes>
13    <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
14    <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
15  </local-tx-datasource>
16</datasources>


Hope this helps anyone else that stumbles across this thread.
John Hayward
RE: PHP+MYSQL
February 9, 2011 9:26 AM
Answer

John Hayward

Rank: New Member

Posts: 1

Join Date: December 1, 2010

Recent Posts

For at least tomcat 5.5 according to:
http://tomcat.apache.org/tomcat-5.5-doc/config/context.html#Resource_Definitions
If you create the resource in context.xml it is pretty much equivalent to creating the resource in web.xml
You only need to create it one place not both.

johnh...
Scott Westbrook:
You can also use the PDO function in PHP to get access to MySQL databases (I also had to this to get access to MSSQL).

In your PHP code:

$dbh = new PDO('java:comp/env/jdbc/MySQLDatabase');

Create a context.xml file in your portlet's META-INF folder:

<Context path="/FOO" docBase="FOO"
debug="5" reloadable="true" crossContext="true">

<Resource name="jdbc/MySQLDatabase" auth="Container" type="javax.sql.DataSource"
maxActive="10" maxIdle="5" maxWait="10000"
username="user" password="pass" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/MySQLDatabase?useUnicode=true&amp;characterEncoding=UTF-8"/>
</Context>


And finally add a resource-ref to your web.xml file in the portlet's WEB-INF folder:

<web-app>
...
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/MySQLDatabase</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>