CODEWITHSUNDEEP
phpmysql
user2915507
user2915507

Reputation: 15

show results from multiple mysql tables using php

I have multiple mysql tables with the same structure. i am trying to show all the results from all tables with a single query. I have been searching online now for a few hours and this is what i came up with.

    $videos_query = mysql_query("SELECT 
                    table1.title,
                    table1.url,
                    table1.image,
                    table2.title,
                    table2.url,
                    table2.image,
                    table3.title,
                    table3.url,
                    table3.image
                 FROM
                    table1
                 INNER JOIN table2 ON table1.url = table2.url
                 INNER JOIN table3 ON table1.url = table3.url");
while($video_info = mysql_fetch_array($videos_query)){

However, using the above code in PHP im getting the error "Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given"

Upvotes: 1

Views: 86

Answers (3)

Legionar
Legionar

Reputation: 7597

You have an error in your sql query - table1 has to be table2:

$videos_query = mysql_query("SELECT 
                    table1.title,
                    table1.url,
                    table1.image,
                    table2.title,
                    table2.url,
                    table2.image,
                    table3.title,
                    table3.url,
                    table3.image
                 FROM
                    table1
                 INNER JOIN table2 ON table1.url = table2.url
                 INNER JOIN table3 ON table1.url = table3.url");

if (mysql_num_rows($videos_query) > 0) {
    while ($video_info = mysql_fetch_array($videos_query)) {
        // process your data here
    }
}

If you want to do union selects:

$videos_query = mysql_query("
  SELECT title, url, image
  FROM table1
  UNION
  SELECT title, url, image
  FROM table2
  UNION
  SELECT title, url, image
  FROM table3
");

if (mysql_num_rows($videos_query) > 0) {
    while ($video_info = mysql_fetch_array($videos_query)) {
        // process your data here
    }
}

Also, maybe you want to order by some column - so then add there ORDER BY + column_name.

Upvotes: 1

Aditya Jhunjhunwala
Aditya Jhunjhunwala

Reputation: 635

change 

 INNER JOIN table2 ON table1.url = table1.url

to 

INNER JOIN table2 ON table1.url = table2.url

Upvotes: 1

Joke_Sense10
Joke_Sense10

Reputation: 5402

While checking ON you are comparing table1.url = table1.url which is incorrect.

Here is the correct code:

$videos_query = mysql_query("SELECT 
                table1.title,
                table1.url,
                table1.image,
                table2.title,
                table2.url,
                table2.image,
                table3.title,
                table3.url,
                table3.image
             FROM
                table1
             INNER JOIN table2 ON table1.url = table2.url
             INNER JOIN table3 ON table1.url = table3.url");
while($video_info = mysql_fetch_array($videos_query)){

Upvotes: 0

Related Questions