homepage Welcome to WebmasterWorld Guest from 54.227.25.58
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
sql join with tables that have some same field names
sql join with tables that have some same field names
drooh




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

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

 

drooh




msg:4151553
 3:59 am on Jun 12, 2010 (gmt 0)

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 />

LifeinAsia




msg:4151669
 2:37 pm on Jun 12, 2010 (gmt 0)

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 />";

LifeinAsia




msg:4151672
 2:40 pm on Jun 12, 2010 (gmt 0)

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

rocknbil




msg:4151714
 4:51 pm on Jun 12, 2010 (gmt 0)

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.

drooh




msg:4152378
 3:25 pm on Jun 14, 2010 (gmt 0)

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

rocknbil




msg:4152403
 4:15 pm on Jun 14, 2010 (gmt 0)

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');

drooh




msg:4152411
 4:37 pm on Jun 14, 2010 (gmt 0)

darn, i was getting excited

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved