Sort MySQL data by field in second table

[lmh]

Hi,

Apologies if this is obvious and I'm missing something, but I'm not sure what I should be searching for here...

I've got a table, 'Events', with a series of fields, one of which is 'CompanyID'. This is an integer that refers to another table, 'Company', which is a list of IDs and companies.

Now, is it possible to select from the first table of 'Events' and sort the data by the company name in 'Company' referred to by the 'CompanyID'?

I hope that makes sense, and many thanks in advance.

Lawrence

[guelphdad]

yes. that would be done with a simple join.

show us what you have tried so far.

[lmh]

That's encouraging... I'm on the right lines with join.

What I've got is as follows, but it's currently not working at all. Do I need to define any fields as secondary keys for this to work?

echo "<table>";
echo "<tr><td></td><th>Edit</th><th>Delete</th><th>Move Up</th><th>Move Down</th><th>Live</th></tr>";

$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
$result1 = mysql_query("SELECT campaign.*, data_company.* FROM campaign LEFT JOIN company_data ON campaign.company = data_company.id ORDER BY company");

$return1 = mysql_num_rows($result1);
if (mysql_num_rows($result1) == 0){
}else{
while($row = mysql_fetch_array($result1))
{
$campaign_id = $row["id"];
$campaign_company = $row["company"];
$campaign_issue = $row["issue"];
$campaign_organisation = $row["organisation"];
$campaign_type = $row["type"];
$campaign_successful = $row["successful"];
$campaign_date = $row["date"];
echo "<tr><td>&nbsp; &nbsp; $campaign_company $campaign_issue</td>";
echo "<td><a href=\"?load=articles&task=delete&id=$article_id\"><img src=\"images/symbols/cross.gif\"></a></td><td>";
echo "<a href=\"?load=articles&position=$newupposition&id=$article_id\"><img src=\"images/symbols/up.gif\"></a></td><td>";
echo "<a href=\"?load=articles&position=$newdownposition&id=$article_id\"><img src=\"images/symbols/down.gif\"></a></td>";
echo "<td>";
if($article_live=="1"){ echo "<a href=\"?load=articles&live=1&alteration=0&id=$article_id\"><img src=\"images/symbols/tick.gif\"></a>"; }
if($article_live=="0"){ echo "<a href=\"?load=articles&live=1&alteration=1&id=$article_id\"><img src=\"images/symbols/cross.gif\"></a>"; }
echo "</td></tr>\n";
}
}

[Fumigator]

You have errors in your query. "data_company" or "company_data"? You are using both. Are you sure you want an outer join? (LEFT joins are outer joins)

Here's some advice: Add code to catch query errors. You would never know that your query didn't work correctly because you aren't catching the error.

$query = "SELECT campaign.*, data_company.* FROM campaign LEFT JOIN company_data ON campaign.company = data_company.id ORDER BY company";
$result1 = mysql_query($query);
if (!$result) {
die("QUERY ERROR! <br />query text: $query<br />error text: ".mysql_error());
}

[guelphdad]

even better, test directly in mysql and then code for your additional programming needs, that way you aren't correcting php code when it is mysql code that needs minding.

I do it that way anyway.

[lmh]

Probably best in future, thanks!

For now, the error I'm getting is:

QUERY ERROR!
query text:
error text: Column: 'company' in order clause is ambiguous

[lmh]

Fantastic, it's sorted.

Should have been company.company.

Many thanks!

[guelphdad]

yes, when you have a column with the same name in more than one table of your join you should prefix the table names.

[lmh]

Many thanks for this, it's very helpful. One last problem... When using:

$campaign_id = $row["id"];

It returns the ID column from the company table rather than the campaign table. How can this be altered to display the ID from the campaign?

Thanks!

[guelphdad]

see post 8

[lmh]

I thought it would be:

$campaign_id = $row["campaign.id"];

But that doesn't seem to work...

[Fumigator]

This is one reason why it is better to specify each column name you are selecting rather than using SELECT *. You can apply alias names to columns that are ambiguous.

For your present query, stick a print_r($row) in after the query is executed to see what the associative indices are named.

[PappaJohn]

This is one reason why it is better to specify each column name you are selecting rather than using SELECT *. You can apply alias names to columns that are ambiguous.
Agreed. Or, name your columns in such a way as they aren't ambiquous. For example, prefix the column names in each table: ie:

comp_companyid = the CompanyID field in the 'Company' table
event_companyid = the CompanyID field in the 'Events' table

By prefixing all fields in the 'company' table with 'comp_' and all fields in the 'events' table with 'event_' you can eliminate ambiquity of column names.

[guelphdad]

why bother to do that though? when you select your columns just prefix the table

company.companyid
event.companyid

saves less time typing all the other column name prefixes when they aren't needed.