LITTLEBLACKDOG.COM

Posted: Wed Feb 20, 2008 8:37 am Post subject: Fun with SQL code

Hey all

I'm having a bit of fun trying to call all the information that I need from some DB tables that I'm using. when I execute the following code:

Code: Select all




SELECT `team_info`.*, `team_info`.`team_info_id`, `country`.*, `manager_info`.`manager_name`, `manager_info`.`manager_nickname`, `manager_info`.`manager_country`


FROM `team_info`


 LEFT JOIN `country` ON `team_info`.`team_country` = `country`.`country_id` 


 LEFT JOIN `manager_info` ON `team_info`.`team_manager` = `manager_info`.`manager_id` 


 [b]LEFT JOIN `country` ON `manager_info`.`manager_country` = `country`.`country_id` [/b]


WHERE (`team_info`.`team_info_id` =22)

It gives "#1066 - Not unique table/alias: 'country' " as an error. Now for thos wondering why I have the bolded line in this, it is to try and get the country id linked to the corresponding field, as I have it linked to the coutnry id.

Hopefully this makes sense, and that its' jsut an easy fix so that I can call this information. Any questions please ask away.

Posted: Wed Feb 20, 2008 9:16 am Post subject: Re: Fun with SQL code

creed wrote:
Hey all

I'm having a bit of fun trying to call all the information that I need from some DB tables that I'm using. when I execute the following code:

Code: Select all
SELECT `team_info`.*, `team_info`.`team_info_id`, `country`.*, `manager_info`.`manager_name`, `manager_info`.`manager_nickname`, `manager_info`.`manager_country` FROM `team_info` LEFT JOIN `country` ON `team_info`.`team_country` = `country`.`country_id` LEFT JOIN `manager_info` ON `team_info`.`team_manager` = `manager_info`.`manager_id` [b]LEFT JOIN `country` ON `manager_info`.`manager_country` = `country`.`country_id` [/b] WHERE (`team_info`.`team_info_id` =22)

It gives "#1066 - Not unique table/alias: 'country' " as an error. Now for thos wondering why I have the bolded line in this, it is to try and get the country id linked to the corresponding field, as I have it linked to the coutnry id.

Hopefully this makes sense, and that its' jsut an easy fix so that I can call this information. Any questions please ask away.

I'd say the obvious problem is the double instances of the "country" table. The question remains - do you need two instances of that table? If you need the "ON" clause to check two different things (from two different tables), you can use the AND qualifier between them.

If you DO need both country tables, the easy solution would be to create an alias for one or both of the tables. Something like:

SELECT *
FROM COUNTRY C1
INNER JOIN COUNTRY C2
ON ... and so on

Posted: Wed Feb 20, 2008 9:21 am Post subject: Re: Fun with SQL code

Alphi wrote:
creed wrote:
Hey all

I'm having a bit of fun trying to call all the information that I need from some DB tables that I'm using. when I execute the following code:

Code: Select all
SELECT `team_info`.*, `team_info`.`team_info_id`, `country`.*, `manager_info`.`manager_name`, `manager_info`.`manager_nickname`, `manager_info`.`manager_country` FROM `team_info` LEFT JOIN `country` ON `team_info`.`team_country` = `country`.`country_id` LEFT JOIN `manager_info` ON `team_info`.`team_manager` = `manager_info`.`manager_id` [b]LEFT JOIN `country` ON `manager_info`.`manager_country` = `country`.`country_id`[/b] WHERE (`team_info`.`team_info_id` =22)

It gives "#1066 - Not unique table/alias: 'country' " as an error. Now for thos wondering why I have the bolded line in this, it is to try and get the country id linked to the corresponding field, as I have it linked to the coutnry id.

Hopefully this makes sense, and that its' jsut an easy fix so that I can call this information. Any questions please ask away.

I'd say the obvious problem is the double instances of the "country" table. The question remains - do you need two instances of that table? If you need the "ON" clause to check two different things (from two different tables), you can use the AND qualifier between them.

If you DO need both country tables, the easy solution would be to create an alias for one or both of the tables. Something like:

SELECT *
FROM COUNTRY C1
INNER JOIN COUNTRY C2
ON ... and so on

Let me give two examples, to better explain it:

Option 1:

Code: Select all




SELECT `team_info`.*, `team_info`.`team_info_id`, `country`.*, `manager_info`.`manager_name`, `manager_info`.`manager_nickname`, `manager_info`.`manager_country` 


FROM `team_info` 


 LEFT JOIN `manager_info` ON `team_info`.`team_manager` = `manager_info`.`manager_id` 


 LEFT JOIN `country` ON `team_info`.`team_country` = `country`.`country_id` AND `manager_info`.`manager_country` = `country`.`country_id` 


WHERE (`team_info`.`team_info_id` =22)

Option 2:

Code: Select all




SELECT `team_info`.*, `team_info`.`team_info_id`, `C1`.*, `manager_info`.`manager_name`, `manager_info`.`manager_nickname`, `manager_info`.`manager_country` 


FROM `team_info` 


 LEFT JOIN `country` C1 ON `team_info`.`team_country` = `C1`.`country_id` 


 LEFT JOIN `manager_info` ON `team_info`.`team_manager` = `manager_info`.`manager_id` 


 LEFT JOIN `country` C2 ON `manager_info`.`manager_country` = `C2`.`country_id`


WHERE (`team_info`.`team_info_id` =22)

Posted: Wed Feb 20, 2008 9:36 am Post subject:

Well your second option works....sort of. When you review the array that's being called from the db with all information, it's jsut pulling the country id for team. It doesnt' fetch the country ID for manager.

For the first example it does neither, all values from the country table are blank.

Posted: Wed Feb 20, 2008 9:55 am Post subject:

creed wrote:
Well your second option works....sort of. When you review the array that's being called from the db with all information, it's jsut pulling the country id for team. It doesnt' fetch the country ID for manager.

For the first example it does neither, all values from the country table are blank.

Well, in the 2nd example, I didn't add any instances of C2.* (or the specific fields) - try that and see if it contains the data you're looking for.

Posted: Wed Feb 20, 2008 9:58 am Post subject:

Alphi wrote:
creed wrote:
Well your second option works....sort of. When you review the array that's being called from the db with all information, it's jsut pulling the country id for team. It doesnt' fetch the country ID for manager.

For the first example it does neither, all values from the country table are blank.

Well, in the 2nd example, I didn't add any instances of C2.* (or the specific fields) - try that and see if it contains the data you're looking for.

Ok I'm going to sound like a knob here, but how do you do that? It's probably a silly question but I'm thinking this is more advanced SQL then I'v played with before.

Posted: Wed Feb 20, 2008 10:36 am Post subject:

creed wrote:
Alphi wrote:
creed wrote:
Well your second option works....sort of. When you review the array that's being called from the db with all information, it's jsut pulling the country id for team. It doesnt' fetch the country ID for manager.

For the first example it does neither, all values from the country table are blank.

Well, in the 2nd example, I didn't add any instances of C2.* (or the specific fields) - try that and see if it contains the data you're looking for.

Ok I'm going to sound like a knob here, but how do you do that? It's probably a silly question but I'm thinking this is more advanced SQL then I'v played with before.

Heh, no big deal. Just reference the alias rather than the table name. Here's the updated 2nd query from my example above:

Code: Select all

SELECT `team_info`.*, `team_info`.`team_info_id`, `C1`.*, `manager_info`.`manager_name`, `manager_info`.`manager_nickname`, `manager_info`.`manager_country` , 'C2'.*


FROM `team_info` 


 LEFT JOIN `country` C1 ON `team_info`.`team_country` = `C1`.`country_id` 


 LEFT JOIN `manager_info` ON `team_info`.`team_manager` = `manager_info`.`manager_id` 


 LEFT JOIN `country` C2 ON `manager_info`.`manager_country` = `C2`.`country_id` 


WHERE (`team_info`.`team_info_id` =22)

Now, one thing to bear in mind - my experience is in MS SQL Server, and I'm a little unsure about the quote marks around everything in your example. So whether you need those or not is something you'll have to determine.

Tell you what, let me make up an example that's slightly simpler, to see if it makes sense to you.

Let's say you have the following tables:

Team
-----------
ID
Name
ManagerID
CountryID

Manager
----------
ID
Name
CountryID

Country
---------
ID
Name

If I understand you correctly, you want both the country name of the team, and the country name of the manager, right?

So given the tables above, it'd be as simple as:

Code: Select all

SELECT 


    Team.Name as TeamName, 


    CT.Name as TeamCountry, 


    Manager.Name as ManagerName, 


    CM.Name as ManagerCountry


FROM Team


LEFT JOIN Manager


    ON Team.ManagerID = Manager.ID


LEFT JOIN Country CT -- This maps the country table to the team


    ON Team.CountryID = CT.ID


LEFT JOIN Country CM -- This maps the country table again but this time to the manager


    ON  Manager.CountryID = CM.ID

Does that make a little more sense? Note that I also gave the selected columns custom names (aliases) to better define what data they contain.

Posted: Wed Feb 20, 2008 10:40 am Post subject:

Yep that was quite helpful in fact. Now to figure out how to call this with PHP but I figure that I will be able to do so once I figure out how i's being save into the array

Posted: Wed Feb 20, 2008 10:59 am Post subject:

ya I don't think mysql likes that code, or PHP doesn't, or something. It returns recordsets in phpmyadmin, but will return nothing when loaded in the browser. NO array, no error, no indication that it even bothers to process it.

Posted: Wed Feb 20, 2008 11:23 am Post subject:

creed wrote:
ya I don't think mysql likes that code, or PHP doesn't, or something. It returns recordsets in phpmyadmin, but will return nothing when loaded in the browser. NO array, no error, no indication that it even bothers to process it.

What does your web server error log say?

Posted: Wed Feb 20, 2008 11:33 am Post subject:

CMTG wrote:
creed wrote:
ya I don't think mysql likes that code, or PHP doesn't, or something. It returns recordsets in phpmyadmin, but will return nothing when loaded in the browser. NO array, no error, no indication that it even bothers to process it.

What does your web server error log say?

mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

which baffles me since it gives a valid result in PHPmyadmin

Posted: Wed Feb 20, 2008 11:40 am Post subject:

An invalid result resource may occur when the call to mysql_query() fails, you should be checking to see if mysql_query() returns false. Are you sure the user you are logging with in your call to mysql_connect() has select permission on the table?

Posted: Wed Feb 20, 2008 11:42 am Post subject:

CMTG wrote:
An invalid result resource may occur when the call to mysql_query() fails. Are you sure the user you are logging with in your call to mysql_connect() has select permission on the table?

It does. All privileges assigned for this user for the database. The fact that this select statement doesn't work while others do has me thinking tis' not a permissions issue.

Posted: Wed Feb 20, 2008 11:49 am Post subject:

creed wrote:
CMTG wrote:
An invalid result resource may occur when the call to mysql_query() fails. Are you sure the user you are logging with in your call to mysql_connect() has select permission on the table?

It does. All privileges assigned for this user for the database. The fact that this select statement doesn't work while others do has me thinking tis' not a permissions issue.

So to clarify (because you previously only said it worked in PHPMyAdmin, which is what made me think permissions) you are able to successfully make queries from the PHP code, just not this one?

Care to post the line of code containing the query?

Posted: Wed Feb 20, 2008 12:55 pm Post subject:

CMTG wrote:
creed wrote:
CMTG wrote:
An invalid result resource may occur when the call to mysql_query() fails. Are you sure the user you are logging with in your call to mysql_connect() has select permission on the table?

It does. All privileges assigned for this user for the database. The fact that this select statement doesn't work while others do has me thinking tis' not a permissions issue.

So to clarify (because you previously only said it worked in PHPMyAdmin, which is what made me think permissions) you are able to successfully make queries from the PHP code, just not this one?

Care to post the line of code containing the query?

Sure thing

Code: Select all




$query="SELECT `team_info` . * , `team_info`.`team_info_id` , `country`.`country_abr` , `manager_info`.`manager_name` , `manager_info`.`manager_nickname` , `manager_info`.`manager_country` FROM `team_info` LEFT JOIN `country` ON `team_info`.`team_country` = `country`.`country_id` LEFT JOIN `manager_info` ON `team_info`.`team_manager` = `manager_info`.`manager_id` WHERE (`team_info`.`team_info_id` =".$_GET["id"].")";





$data = mysql_query($query);





$result_ar = mysql_fetch_assoc($data);

The connection informatin is brought in from an include file