Sorting a Recordset After Query

[PCHomepage]

At first I tried a joined query which must join between two different databases but, as the site was created by a code generator (CodeCharge Studio) which seems to have its own way of doing things, I was unable to get it to work. Therefor I rewrote it a bit and used a lookup query within the WHILE loop and it is working but I can think of no way to sort the results alphabetically. Any ideas or is there some way to do the joined query? See the red code below, which I'm sure is filled with errors (it was late; I was tired!) but it illustrates what I originally tried to do.

$db1 = new clsDBconn();
$db2 = new clsDBgeoip();

$queryCat = "SELECT DISTINCT Country  
                       FROM guestbook";

/*[COLOR=red]
$queryCat = "SELECT DISTINCT db1.g.Country AS CountryID, db2.c.CountryName AS Country 
             FROM db1.guestbook g, db2.countries c 
             WHERE db1.g.Country = db2.c.ID
             AND db1.g.Country <> '' 
             ORDER BY db2.c.CountryName";
[/color]*/

$db1->query($queryCat);
while ($db1->next_record()) {
     $CountryID= $db1->f("Country");
     $CountryText = CCDLookUp("CountryName","countries","ID=".$CountryID,$db2);
     $CountryLink = "/php/guestbook.php?Country=".$CountryID;
}

 

[jpadie]

Are the two database on different servers?

If so are they federated or in a Cluster or something similar?

 

[PCHomepage]

The two databases are on the same server and have the same login and password so there are no issues in actually reaching them. Since it is on a hosted server, I am not sure of the exact nature of the physical arrangement other than that they are all part of my account. Therefor the code I posted works as it is but it does not list the countries alphabetically and that is the main issue.

 

[jpadie]

Thence not sure why this is a php question. but I will try and help.
Have you tried a proper joIn rather than implied?

For the time being use only a MySQL command line client as I have no idea what abstraction layer you are using nor whether it is limited in any way.

 

[PCHomepage]

I wasn't sure whether to post it here or to the MySQL forum but it seemed more of a PHP issue as it apparently cannot be done directly as a query. The query I can do but the surrounding PHP is the issue so hence, posting it here seemed better. If you like, though, I can post it there instead.

To answer your question, these were originally a "proper join" but that's when they were all in the same database. Having them separate is not generally an issue and I've done it before successfully but what I did then does not work here because of the way CodeCharge Studio does things. It produces copious amounts of code into many separate files and includes and I've not been able to locate the way it puts queries together.

 

[jpadie]

could you try the following on the command line please? and confirm that the query returns what you expect?

SELECT DISTINCT 
	g.Country AS CountryID, 
	IFNULL(c.CountryName, 'Unknown') AS Country
FROM 
	db2.countries g
LEFT OUTER JOIN
	db1.guestbook g
ON
	g.Country = c,ID
WHERE
	g.Country <> ''
GROUP BY
	g.country
ORDER BY 
	c.CountryName ASC

 

[PCHomepage]

At first it gave an error:

SQL Error (1146): Table 'db2.countries' doesn't exist

I tried correcting the alias for db2.countries g to db2.countries c and the comma where there should be a dot for c,ID to c.ID but even with these things corrected, it still gave the same error until I realized that db1 and db2 needed to be the actual names of the databases and they were not. Using the proper names, they work perfectly. Thank you!

Oddly, and the reason I thought it was a PHP question and the reason I used db1 and db2, is that my software seems to require a separate connection resource for each query but it apparently works with only the single one, likely because they both have the same login and password.

 

[jpadie]

mysql_connect() connects to a database host. a server. not a database itself.

mysql_select_db effectively just issues a

USE mydatabase;

command. it sets the default database. You can always still use absolute references to each table, and so never need to use the mysql_select_db() if you don't want to (there is a performance hit, I believe, due to connection level cacheing of the table information).

so yes, db1 and db2 are not connection strings but the names of the databases. I should have realised that was part of your error condition from the code in your first post and the reuse of the [$]db1/2 reference; but i assumed you had used the nomenclature to remind yourself of the database names only.