Tech Musings

Thursday, December 15, 2005

PHP queries on MySQL databases

Can you build a query on two MySQL databases?
I posted this question in the PHP Freaks Newbie Forum. Read about it here:
http://www.phpfreaks.com/forums/index.php?showtopic=77038&hl=

Apparently, you only connect to one MySQL "server" using mysql_connect. You then build your queries on multiple databases.

Can you use boolean fulltext searching on two tables at once?
Here's a query I found, don't know if it works yet.
SELECT *
FROM catalogue
JOIN manufacturer
ON catalogue.manufacturer_id = manufacturer.manufacturer_id
WHERE MATCH (model,engine,year,size,part,add_info) AGAINST ('$searchtext')
OR MATCH (manufacturer_make) AGAINST ('$searchtext')


I ran into a bit of trouble today as I was implementing the search feature for the teacher_exchange. I was getting repeating records returned, insufficient information returned, etc. It turns out I had to use DISTINCT in the query, and that I had to call each field individually rather than through the SELECT * like I usually do. This fulltext search queries two distinct tables in on database.

$query_search = "SELECT DISTINCT object_id, object_stamp, object_title, object_url, object_url_type, object_description, object_size, object_type, object_keywords, content.content_name, subject.subject_name, object_grade1, object_grade2, object_grade3, object_grade4 FROM objects left join content on objects.content_id = content.content_id left join subject on objects.subject_id = subject.subject_id left JOIN people on objects.people_number = people.people_number WHERE MATCH (object_title,object_description,object_keywords) AGAINST ('$colname_search' IN BOOLEAN MODE) OR MATCH (people.people_lastname) AGAINST ('$colname_search' IN BOOLEAN MODE)";

>>>>>>ADDED 5/24/06 >>>>>>>
I thought I'd list an example of how to perform a query on TWO 2 databases at one time on the same MySQL server (computer). This one turned out to be easier than I thought. Maybe that's why I couldn't find much information on how to do it! I'm still not sure how to do to do it if the two databases reside on separate or different servers (computers).

Of course, you will need to open connections to both databases first.
$db1_connection = mysql_pconnect('hostname', 'username', 'password');
$db2_connection = mysql_pconnect('hostname', 'username', 'password');

Then you can peform a query similar to this:
$query = mysql_query("SELECT * FROM db1.table, db2.table WHERE db1.table.field = 'something' AND db2.table.field = 'something'") or die(mysql_error());
$row_query = mysql_fetch_assoc($query);
$totalRows_query = mysql_num_rows($query);

One of the things that was causing me confusion was the mysql_select_db($db1_connection) statement I normally use before coding my query statements. PHP will set or use the current active database for this value, or the last link opened by mysql_pconnect. Thus, I do not make any references to either connection when coding these queries.

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home