Getting data from 2 related and 1 unrelated MySQL tables with one query?

Question

I am trying to create an archives page for my blog, which is also still being built. I have three different MySQL tables I need to pull data from and 1 of them is unrelated from the other 2. I found a similar post here but seeing as how I am not very experienced with PHP I am not able to figure out how to convert that to what I need. I know this can easily be done by executing three different queries, but that will put an unnecessary load on the server and slow down page load times and everything else.

The 2 related categories are "blogs" and "categories" while the third is for a different application built into the blog called the "Brain Link"

Here are the two queries for the related tables:

    $blogQuery = mysqli_query($link, "SELECT * FROM pub_blogs ORDER BY id DESC") or die ("Could not access DB: " . mysqli_error($link));

    $row = mysqli_fetch_assoc($blogQuery);  
    $blogID = $link->real_escape_string($row['id']);
    $title = $link->real_escape_string($row['title']);
    $date = $link->real_escape_string($row['date']);
    $category = $link->real_escape_string($row['category']);
    $content = $link->real_escape_string($row['content']);

    $blogID = stripslashes($blogID);
    $title = stripslashes($title);
    $date = stripslashes($date);
    $category = stripslashes($category);
    $content = stripslashes($content);

    $catQuery = mysqli_query($link, "SELECT * FROM categories ORDER BY id DESC") or die ("Could not access DB: " . mysqli_error($link));

    $row = mysqli_fetch_assoc($catQuery);
    $catID = $link->real_escape_string($row['id']);
    $catName = $link->real_escape_string($row['name']);
    $description = $link->real_escape_string($row['descriptions']);

    $catID = stripslashes($catID);
    $catName = stripslashes($catName);
    $description = stripslashes($description);

and here is the third unrelated query:

    $brainQuery = mysqli_query($link, "SELECT * FROM brain_links ORDER BY id DESC") or die ("Could not access DB: " . mysqli_error($link));

    $row = mysqli_fetch_assoc($brainQuery);
    $brainID = $link->real_escape_string($row['id']);
    $site_name = $link->real_escape_string($row['site_name']);
    $site_url = $link->real_escape_string($row['site_url']);
    $post_date = $link->real_escape_string($row['post_date']);

Is it possible to get data from all three of these with one query? If not can someone point me in the direction of somewhere that will tell me how to join the two related ones?

The blog table saves the category ID under the 'category' column to identify which category it belongs to

I appreciate any help or suggestions!

can you put the table structure on sql fiddle and please define third tables columns — Muhammad Raheel, Sep 28 '12 at 06:19

score 0 · Answer 1 · answered Sep 28 '12 at 06:22

This will bring the data from two tables. How ever, from the third tables data can't be get

SELECT 
        * 
FROM pub_blogs 
LEFT JOIN categories on categories.id = pub_blogs .category
ORDER BY id DESC

Ajnd for the third table you can not get it because you can not make a link to any other table. If you change your table structure you can do it.

score 0 · Answer 2 · answered Sep 28 '12 at 06:36

No you cant get data from 3 rd table using same query . Mysql wants a mapping between two tables .

You can join the blog and category but not the brain links

For the first two tables do :

select * , c.id , c.name , c.descriptions from pub_blogs p  join categories c on c.id = p.category order by id desc ;

For the third table as there is no mapping you cant join it .

Happy Coding :)

Getting data from 2 related and 1 unrelated MySQL tables with one query?

2 Answers2