Welcome to WebmasterWorld Guest from 54.145.222.231

Forum Moderators: open

Message Too Old, No Replies

sql join with tables that have some same field names

sql join with tables that have some same field names

   
3:06 am on Jun 12, 2010 (gmt 0)

5+ Year Member



Ok, this may be easily solved by changing the primary key name but Im just really curious about this

Here are the two tables

table: cms_news_blog_entry
id
category_id
title
content
timestamp

table: cms_news_blog_categories
id
name
weight

You can see that they both have the same field name for their primary key, the generic id

What I want to do is fetch an array that joins the two tables listing all the entries from the cms_news_blog_entry table that have the category_id that comes from $_GET

Can you tell me if this looks correct?

$category_id = $_GET['category_id'];

$sql = "
SELECT * FROM `cms_news_blog_categories`,`cms_news_blog_entry`
WHERE
`cms_news_blog_categories`.`id` = ".$category_id."
AND
`cms_news_blog_entry`.`category_id` = `cms_news_blog_categories`.`id`
ORDER BY `timestamp` DESC
";

$result = mysql_query($sql);

while($row = mysql_fetch_array($result)){
$id = $row['id'];
$title = $row['title'];
$timestamp = $row['timestamp'];
$name = $row['name'];
}

What Im wondering is why the $id = $row['id'] is the id from cms_news_blog_entry and not cms_news_blog_categories ?

Originally I had my query starting with
"SELECT * FROM `cms_news_blog_entry`,`cms_news_blog_categories`

But with that it was giving me the id of cms_news_blog_categories and I could not figure out how to get the id of cms_news_blog_entry
3:59 am on Jun 12, 2010 (gmt 0)

5+ Year Member



Here is another post on the same thing, with these two different examples I am getting different results and Im just wanting to know why

<?
// this ones outputs category_id and id as the same
$sql = "
SELECT * FROM `cms_news_blog_entry`,`cms_news_blog_categories`
WHERE
`cms_news_blog_entry`.`category_id` = `cms_news_blog_categories`.`id`
";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
echo $row['title']."<br />";
echo $row['category_id']."<br />";
echo $row['id']."<br />";

}
?>
<hr />

<?
// this one outputs the id from cms_news_blog_entry and the category id
$sql = "
SELECT * FROM `cms_news_blog_categories`,`cms_news_blog_entry`
WHERE
`cms_news_blog_entry`.`category_id` = `cms_news_blog_categories`.`id`
";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
echo $row['title']."<br />";
echo $row['category_id']."<br />";
echo $row['id']."<br />";

}
?>
<hr />
2:37 pm on Jun 12, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



Because you have 2 fields named id in the query results. Most DBs will handle this by using "id" for the first field it comes across, then generate another name for the second one. You can avoid this with an alias:
SELECT *, cms_news_blog_categories.ID AS CategoriesID
FROM `cms_news_blog_categories`,`cms_news_blog_entry`
WHERE `cms_news_blog_categories`.`id` = ".$category_id."
AND
`cms_news_blog_entry`.`category_id` = `cms_news_blog_categories`.`id`
ORDER BY `timestamp` DESC

Then instead of
echo $row['id']."<br />";
use
echo $row['CategoriesID']."<br />";
2:40 pm on Jun 12, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



Or better yet, just select the actual fields that you are going to output:
SELECT cms_news_blog_entry.Title, cms_news_blog_entry.Category_ID, cms_news_blog_categories.id
4:51 pm on Jun 12, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



I've never done it this way, but my GUESS would be that the second ID can be accessed via an array? (Just a guess, like)

$id = $row['id'];
$id[0] and $id[1]

The alias is the preferred approach for accessing the associative values.

Usually what I do is access these by their index, mysql_fetch_array returns both the associative and indexed items in the array. To avoid being cumbersome, I have a function that stores the field names,

@first_fields = get_field_names('table1');
@second_fields = get_field_names('table2');

This may seem like overhead, but the arrays and their values are used to generate html table headers. Then step through inside the mysql while loop


$f=count(@first_fields);
$s=count(@second_fields);
$total_fields = $f+$s;
while ($row=mysql_fetch_array($results)) {
echo "<tr>";
for ($i=0;$i<$f;$i++) {
echo "<td>" . $row[$i] . "</td>";
}
// Note we SKIP the second id field by starting at $f+1
for ($i=$f+1;$i<$total_fields;$i++) {
echo "<td>" . $row[$i] . "</td>";
}
echo </tr>";
}


(Note: typed out for concept, might have errors . . . )

Though it appears to be working, you SHOULD be able to do this

`cms_news_blog_categories.id`

or even this, backticks are only required for names that conflict with internal data types, functions, or other reserved words,

cms_news_blog_categories.id

instead of

`cms_news_blog_categories`.`id`

Just a few less characters to debug.
3:25 pm on Jun 14, 2010 (gmt 0)

5+ Year Member



Ok, thanks so much for all the info, this is exactly what i needed.

I learned that you can add an alias onto your query by doing

SELECT *, table1.id AS table1_id FROM `table1`,`table2`

And I also learned that it may be better to just specify exactly which fields you want to select instead of all *

SELECT cms_news_blog_entry.id,title,category_id

So lastly, rocknbil, what is the @first_fields , is that some shorthand? Also, my php installation doesn't recognize get_field_names(), is that because that is a function you have written
4:15 pm on Jun 14, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



So lastly, rocknbil, what is the @first_fields



Ugh . . .my apologies . . . that is a fault of working in multiple languages, and switching between the two on various projects throughout the day. In Perl, @ is an array in that context . . . so

@fields = [array list of values] # perl

$fields = [array list of values] # PHP

Change those to $ for PHP. In PHP, @ precedes a function to quiet any errors the function may return, like

@mail('i am an invalid recipient and have missing parameters');
4:37 pm on Jun 14, 2010 (gmt 0)

5+ Year Member



darn, i was getting excited