CODEWITHSUNDEEP
phpmysqljoin
CK-idiot
CK-idiot

Reputation: 107

Really confused about MySQL join

I've really been trying to figure this out myself, but all tutorials on the internet either doesn't work, or they are written half... no one explains how you echo out the content.

right now I'm pulling a table of popular searches... I want to display the name, and then the category name underneath it. But problem is, that the searches table stores the categories as ID and not name.

So I need to use a join, that will link the "category_id" column in the "popular_search" table, to the "id" column inside the "categories" table... and then display the "name" column from the "categories" table to display the category name.

No one gives info on how to echo the content, cause both tables have columns with the same name, how do I actually do it? I don't get it...

This is my current query:

SELECT * FROM popular_search ORDER BY `hits` DESC LIMIT 9

And this my current echo's, but I need to the replace the id field with the category name from the other table:

<?php echo $item['name']; ?>
<?php echo $item['category_id']; ?>

Upvotes: 1

Views: 81

Answers (5)

h2ooooooo
h2ooooooo

Reputation: 39532

Using AS should work for you:

SELECT
    ps.*,
    ca.name as `category_name`
FROM
    `popular_search` as `ps`
INNER JOIN
    `categories` as `ca`
ON
    ps.category_id = ca.id
ORDER BY
    ps.hits DESC
LIMIT 9

Now it'll be in your array as category_name.

You can read more here: http://dev.mysql.com/doc/refman/5.0/en/select.html

Upvotes: 5

Chand Priyankara
Chand Priyankara

Reputation: 6784

i guess, you need to know on table alias. So you can use two alias[new names] to two tables and refer them separately in select query. As there are lot of examples, i wont re-write here them. please have a look here: http://www.w3schools.com/sql/sql_alias.asp Table alias

Upvotes: 1

Kuldeep Singh
Kuldeep Singh

Reputation: 471

This is the exact thing that you are looking for http://www.w3schools.com/sql/sql_join_inner.asp

Give it a try ..

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

Specify the column names in the SELECT (rather than SELECT *).

Give the coumn names alias names (ie, SELECT a.Column1 AS Fred , Fred being the alias)

Upvotes: 2

Sashi Kant
Sashi Kant

Reputation: 13465

Try this::

SELECT 
* 
FROM 
popular_search ps
inner join categories c on (ps.category_id = c.id)

ORDER BY `hits` DESC LIMIT 9

Upvotes: 1

Related Questions