By mauszozo on

Basically, what I'm trying to do (and I may be heading down the wrong path here..) is creating a page in drupal with a search form that people can use to query a separate mysql database and display the results of the query. It doesn't seem like an unusual task, but I haven't yet found any documentation that's laid out how to do this yet.

Is there a module, or a tutorial or documentation that anyone knows of that would give me more info on this topic?

I read http://drupal.org/node/18429 which tells how to connect to a different mysql database, and how to switch back to the drupal db. I'm still trying to figure out if I can create a form in a "Page" and show the results in the same page or not.

Any thoughts? Sign posts? Constructive ridicule?

Thanks for your time!

⋅ Categories: Drupal 6.x

Comments

mauszozo’s picture

mauszozo commented

I'm a little bit further. I found this tutorial: http://drupal.org/node/262422 that shows how to create a custom module that creates a form. Now I just need to run a query with input from the form and figure out how to display the results.

bruce168’s picture

bruce168 commented

Hi mate, I was wondering if you have any live example of the finished search form that I can take a look at ? I've been searching for answers on how to do this as well for a few months. Thanks in advance !

mauszozo’s picture

mauszozo commented

Phew! I got it working. Just in case anyone else is working on something similar, here's what I did:

1. Followed the instructions here http://drupal.org/node/18429 to add another db connection.
2. Followed the instructions here http://drupal.org/node/262422 to create a custom form module.
3. Floundered around in the drupal api documentation until I came up with these (simplified) functions:

function booksearch_form() {
	$output .= drupal_get_form('booksearch_my_form');
	$output .= booksearch_query();
	return $output;
}

function booksearch_query() {

	$sql = "SELECT * FROM bookshelf"; //your uber cool query goes here
	
	db_set_active('booksearch'); //my separate db connection specified in modified settings.php
	$result = pager_query($sql,10);
	db_set_active('default'); //my default drupal db connection specified in modified settings.php
	
	$output = '<table>'; //I know, tables bad, theming inside a data function bad..
	
	while ($row = db_fetch_array($result)) {
		$output .= '<tr><td>'.$row[isbn];
		$output .= '</td><td>'.$row[author];
		$output .= '</td><td>'.$row[title];
		$output .= '</td><td>'.$row[genre];
		$output .= '</td></tr>';
	}
	$output .= '</table>';
	return $output;	
}
mokargas’s picture

mokargas commented

I don't see anything wrong with that, you could always make a theme function available in your module if you're really fussy :)

DigOurGame’s picture

DigOurGame commented

mauszozo,
Thank you very, very, very much.

Rodney

jeverist’s picture

jeverist commented

Actually, you are using tables exactly the way they are intended to be used - to display tabular data - you know, something from a spreadsheet or database table. As opposed to using them for layout.

BTW, thanks for this.

DigOurGame’s picture

DigOurGame commented

Greetings,
Again thank you for sharing this. I have tried doing this many different ways and failed. I do have two questions though. 1)Is there a way to list the title of the rows? 2)Also is there a way to diplay these sorted?
example 1
Top five strongest players going by experience

Player XP
tony 100
jill 83
mike 12
ripe 7
lost 2

Example 2
Top five wealthiest players going by money

Player money
alice 123
ann 84
mike 33
dee 12
sam 5

I tried a few ways I know using mysql, php, table data and I just can't get it to title the rows or sort with out goofing up the work you showed us how to do.

czeky’s picture

czeky commented

Need something similar, good poit to start, thanx

urlstwohim3’s picture

urlstwohim3 commented

Can you show all your code and show where the code is located?