By grumble on

I'm new to CMSs and am trying to find out how each CMS manages database connections, so I can decide which CMS to use. I want to construct a page that displays the result of a SELECT statement. Eg. Displays a list of products, stored in a database (a search function would also be useful). I have done a bit of a search of the Drupal documentation without much success. Pages such as
http://drupal.org/node/52526
Look like they might be useful, and I'm quite interested in the
"Forms API Quickstart Guide"
link, for example, but when I try and follow it, I just get an advertising page. The explicit link
http://drupaldocs.org/api/head/file/contributions/docs/developer/example...
also leads me to this page.
Can anyone either describe how database connections are handled, or tell me what is going on with the documentation and direct me to a link that works?

Cheers,

Comments

scoutbaker’s picture

scoutbaker commented

Hmmm...a simple search turned up this link for the Form API Quickstart Guide.

As for your main question, I'm not sure I understand exactly what you are asking for. What exactly are you trying to measure to make this determination? If you can provide more detail, perhaps someone will know more about it and be able to answer the question.

dman’s picture

dman commented

I see that link on that page is over three years old and does seem to now be taken by a domain squatter.
That's a pain.
All the real docs are under api.drupal.org.

.dan. is the New Zealand Drupal Developer working on Government Web Standards

mradcliffe’s picture

mradcliffe
he/him
Primary language English
commented

The api is on api.drupal.org.

However I suggest that you use a contributed module to handle these things for you so you don't have to update or maintain your own code. The views module lets you create custom lists and representations of node, user, taxonomy, og, activity, flag, etc... data in a variety of ways.

For products, if you need additional ecommerce support, there are several e-commerce options.

grumble’s picture

grumble commented

Hi Guys,

Thanks for all your suggestions and comments. To clarify my scenario:
My client has an extensive product list, that he manages using a database.
I want to construct pages that display queries to that database. First a series of "catalog" pages, that simply display all his products (in alphabetical order), then a "search" page, that allows users to search for the products they want. It would also be nice to have some "update" pages, where my client could log in and update his catalog (without needing to know SQL).
I am sure this is a very common scenario.
I have observed lots of confusion around this issue. Most big WCMSs use a database to manage and generate webpages. My scenario is different however, I don't want to use the Drupal database (I **think** the view module does this), rather, I want to use my clients database.
I guess the most straightforward way of doing this is to access the page source code, and directly insert php code to query the database. If someone could suggest something like: "use X template, go ... click ... open window ... insert your php code here" that would be cool.
The form API at http://drupal.org/node/36050 and discussed at http://drupal.org/node/68159#comment-129394 looks like it might provide a "better" solution (albeit more complicated). I'd want to look into that when I get the hang of Drupal a bit more.

grumble’s picture

grumble commented

Looking into the Form API, I **think** we are still querying the Drupal database here. Looks like we're building a "form" by interacting with the Drupal database directly, rather than through a GUI. Is this correct? BTW, it is _really_ off-putting to a new user for there to be links in the documentation that don't work.

dman’s picture

dman commented

For now, learning FormAPI at the same time as this will be frustrating.
I'd suggest getting to the point where you can embed your own code that you are familiar with first. You can add the super-powers of FormAPI later. FAPI can be used with external DBs, but best to learn those two concepts independently first.

Searching a little* (on "external database" ) produces
A useful handbook page and
what looks like a code template for connecting to, querying and returning from another DB
It correctly uses Drupals db_set_active() switch - which is the magic.

That should give you a good starting point.

On the internet, sometimes a user comment in a three year old thread may point to an external site that no longer has the information where it was. That's just life. Confusing that with the actual maintained docs will leave you ... confused.
If you do find a dead link in real documentation however, please raise it or fix it (you have the power). The real page you want can often be located by a simple search* based on the keywords of the dead link.

*see a pattern here?

.dan. is the New Zealand Drupal Developer working on Government Web Standards

grumble’s picture

grumble commented

Thanks for that dman. Those links you sent look like they fit the bill quite nicely (will experiment later).

I guess I found those broken links by typing something into the search box, while browsing the documentation. Took me to the "forums" without realising it. A special "documentation search" box/button is a good idea I think.

Also, I would never have thought I'd find what I was looking for in the "Developing for Drupal » Module developer's guide"! Surely this should be a (basic) tutorial or something.

BTW. I hate to be a traitor after your excellent help here, but while I was waiting for your reply, I downloaded Joomla. The reason? I found what I was looking for in 3 "obvious" clicks from their documentation home page:
Developers->Database->How to connect to an external database
http://docs.joomla.org/How_to_connect_to_an_external_database
easy!

In drupal its:
Get started developing for Drupal here->Module developer's guide->Module HowTos->How to connect to multiple databases within Drupal
http://drupal.org/node/18429
Rather less intuitive. It was hard for me to locate, even knowing it was there!

Easy to use = good documentation + good interface

dman’s picture

dman commented

There is a button that limits search to handbook pages. I use it all the time. Unfortunately it doesn't LOOK like a button and it's not the default (I think it should be - like on php.net : search functions, then search docs, then search everything)
But hey :-{

It's a fair comment on finding things within the docs. The Drupal handbooks ARE (IMO) too fragmented, and often too deep. There's just too much.
ALL development documentation is within "Module developer guide" as creating trivial modules is the best-practice way of adding code. Arbitrary inline PHP is regarded as a hack. That's just the way it is - and also is why the forums are divided the way they are - there is no generic 'developer' section - just 'module development'

Suggesting and comparing where you expected to find stuff is a valuable contribution to the docs however - many times better than just complaining ;-)

Although connecting to an external database may be the first thing you want to do right now ... I don't think it would be expected to be documented in 'basic' steps in many apps. ..

Best of luck!

.dan. is the New Zealand Drupal Developer working on Government Web Standards

grumble’s picture

grumble commented

I hate to rub salt in the wound, but when I go to http://api.drupal.org/api/ and type "test" in the search box I get the error:

An HTTP error 0 occurred.
http://api.drupal.org/api/autocomplete/6

When I type "external database" I get "No search results found."

manicolaus’s picture

manicolaus commented

OK, assume I can get info from an external db as a new and separate table into the MySQL schema that Drupal uses.

(I want to import a list of meetings in different parts of the world. The list is maintained locally (offline) on an Access db. I've figured out how to get that table converted to MySQL and imported as a new table into the MySQL db that Drupal uses.)

Now, how do I display that data in such a way that it integrates with the rest of the Drupal environment? Can the very useful and lovely Views module be set to process and read this data (which is NOT in the node table or in one of the content_type tables)? Can comments be set up for it?

OR can the Views module and all the rest of the Drupal toolkit only read data that's in the nodes table and in one of the content_type or node-type tables? If so, my task in updating this list, which changes frequently, becomes considerably more difficult. I'm not (yet) a PHP programmer and hope I don't have to become one to make this work. I am able to link the exported nodes table and the content_type table into Access and manipulate them there but it gets very tricky with maintaining the id and vid fields and not overwriting other nodes, inserting new rows, etc. etc. (I am an experienced VBA programmer.) There must be a better way.

Thanks for any thoughts on this.

WorldFallz’s picture

WorldFallz commented

I believe views can be used to access and display external data via the hook_views_data function (see the advanced help and/or api documentation), but using something node specific like the comment module will require the items to be nodes. If you can make the data available via a feed, you can use the http://drupal.org/project/feedapi module to create nodes from it.

manicolaus’s picture

manicolaus commented

Thank, I'll try it.
-- M.

manicolaus’s picture

manicolaus commented

I looked into it but the description in Advanced Help is beyond my horizon. I tried implementing it in various ways and got nothing but syntax errors.

Surely my situation is not so unique that I'm the first person to want to upload data from an externally maintained database into a new table in the MySQL db that Drupal uses by default, and have Views read and display the data.

WorldFallz’s picture

WorldFallz commented

No, but usually someone who wishes to do something along these lines, a somewhat advanced task, also has the skills to implement the views api. I don't mean that to be critical-- it's just a fact. The views api is still beyond me at this point as well.

If you just want to be able to access the data via drupal you could create a cck content type and then import the data into nodes with one of the import modules.

Alternatively, you could also just access the data via drupal db functions and format and display it like normal (as if it weren't part of a drupal site).

rgriffiths’s picture

rgriffiths commented

I'm a dedicated Drupaller when it comes to where it's best,
but I have to admit it seems a sledgehammer to crack a nut here.

In a similar situation to what you are addressing
ie display a catalog, then on to more detailed pages by click/select,
I just use MySQL/PHP/Apache set,
with a minimum of (interesting & fun) coding.

ie .. you can write a few pages of html with embedded php
that will do everything you need, without all the complication of Drupal

The code that you need to access the MySQL database from a web page is easy
three sets of code ..

1. to link up to the database ..
2. select some data
3. display that data in rows of a table, making each key data a hyperlink, eg ..


//open the connection to the database
$connection = mysql_connect("localhost","myname","mydatabase");

//report failure if it doesnt work
if (!$connection)
  {
  die('Could not connect: ' . mysql_error());
  }

//now select the 'mydatabase' database
mysql_select_db("mydatabase", $connection);

//run a query on the database
$result = mysql_query("SELECT name, address, suburb, client_num FROM clients,$connection);

//Start Table output
echo
 	"<table border>   <tr>" .
	"<th>Name         </th>" .
	"<th>Address     </th>" .
	"<th>Suburb        </th>" . 
	"<th>Client          </th>" .
	"</tr>" ;

//Now fetch the current row into the array '$row', while rows remain
while ($row = mysql_fetch_row($result))
{
//start a table row
echo "<tr>";

//then print each element across the table
for ($i=0; $i < mysql_num_fields($result); $i++)
    {     echo "<td>". $row[$i] ."</td>" ; }

//and end the row
echo "\n</tr>";
}

//and when no more rows available, finish the table
echo "\n</table>" ;

//close the connection
mysql_close($connection);

You can make field 4 write as a href, pointing on to the page for that partic client_num from the database

This is allthat Drupal does, but with a heap more elegance and complexity,
which doesn't seem appropriate here

WorldFallz’s picture

WorldFallz commented

Actually, the drupal db functions I linked above even make this a bit easier.