LITTLEBLACKDOG.COM

Posted: Wed Feb 20, 2008 1:09 pm Post subject:

What happens if you are less quote-happy? They aren't necessary for field names and table names, IIRC. (Plus it's more readable.)

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"].")";

Posted: Wed Feb 20, 2008 1:18 pm Post subject:

CMTG wrote:
What happens if you are less quote-happy? They aren't necessary for field names and table names, IIRC. (Plus it's more readable.)

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"].")";

Well at least something is being returned now, although we're back to the original problem of the manager_country value being the same as the team_country. I'm thinking it might be something to do with how I'm calling it in the php code when it's gong through the recordset. I have to call country_abr in order to get the team country, but it doesn't seem to also grab the linked info for manager_country as well.

Posted: Wed Feb 20, 2008 1:35 pm Post subject:

Are the results correct in PHPMyAdmin?

As an aside, you can do a:

Code: Select all

print_r($result_ar);

To dump the contents of an object in a human-readable format, if that helps to debug the query.

Posted: Wed Feb 20, 2008 1:38 pm Post subject:

CMTG wrote:
Are the results correct in PHPMyAdmin?

No, it offers the country id for the team, but doesn't have a separate one for the manager

As an aside, you can do a:

Code: Select all

print_r($result_ar);

To dump the contents of an object in a human-readable format, if that helps to debug the query.[/quote]

Tried that, it's how I knew ti wasnt' getting anything for the manager country info.

Posted: Wed Feb 20, 2008 1:41 pm Post subject:

creed wrote:

No, it offers the country id for the team, but doesn't have a separate one for the manager

Right, so at least we know its an SQL problem and not a PHP one.

Are any of the manager fields being populated at all?

While your at it, try this query:

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, country, manager_info WHERE team_info.team_country=country.country_id AND team_info.team_manager=manager_info.manager_id AND team_info.team_info_id=".$_GET["id"];

Posted: Wed Feb 20, 2008 1:54 pm Post subject:

CMTG wrote:
creed wrote:

No, it offers the country id for the team, but doesn't have a separate one for the manager

Right, so at least we know its an SQL problem and not a PHP one.

Are any of the manager fields being populated at all?

Some are. I'm populating them as I get the information, but

While your at it, try this query:

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, country, manager_info WHERE team_info.team_country=country.country_id AND team_info.team_manager=manager_info.manager_id AND team_info.team_info_id=".$_GET["id"];

[/quote]

Retuns the same results, but only if the manager info is populated. If not it craps out (returns nothing)

Posted: Thu Feb 21, 2008 1:13 pm Post subject:

Just bumping this up. hopefully I havent posted a stumper

Posted: Mon Feb 25, 2008 10:58 am Post subject:

creed wrote:

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, country, manager_info WHERE team_info.team_country=country.country_id AND team_info.team_manager=manager_info.manager_id AND team_info.team_info_id=".$_GET["id"];

Retuns the same results, but only if the manager info is populated. If not it craps out (returns nothing)

I think I see what is happening. I might be wrong though.

What's happening is that there is something in team_info.team_manager but no matching rows in manager_info where manager_info.manager_id is equal to team_info.team_manager. This is a data entry error. You shouldn't be allowed to enter a row into team_info without meeting the foreign key constraints. (A foreign key is essentially a reference into another table, which is what team_info.team_manager is.)

In short you are entering data in the wrong order. You shouldn't enter the team without first entering the manager. Or your script should be putting the manager in at the same time as the team, if he doesn't already exist.

If there really isn't a manager, then you probably shouldn't join on the manager's table when team_info.team_manager is null.

Posted: Sun Mar 02, 2008 12:19 am Post subject:

well team manager always has a value (default value is 1, which refers to no manager in the manager table. No record will return as NULL.

Sorry for the delay, damn life.