2

I'm trying to show stuff queried from two tables, but on one html table. Data is shown for the last 30 days, based on which, an html table is being generated. Currently I'm stuck using two queries and generating two html tables:

$query1 = mysqli_query( $con, "SELECT date, stuff* " );
while( $record = mysqli_fetch_array( $query1 ) ){
    echo '<html table generated based on query>';
}

$query2 = mysqli_query( $con, "SELECT date, other stuff*" );
while( $record = mysqli_fetch_array( $query2 ) ){
    echo '<another html table generated based on query2>';
}

Is there a possibility to show both queries on one html table instead?

Note that it gets tricky since we have dates on one table which are not necessarily found in the second table or vice-versa.

Thanks for the support guys. So far I'm stuck at this:

SELECT * FROM user_visit_logs 
LEFT JOIN surfer_stats ON user_visit_logs.date = surfer_stats.date 
UNION 
SELECT * FROM user_visit_logs 
RIGHT JOIN surfer_stats ON user_visit_logs.date = surfer_stats.date

The query completes, but the 2nd table fields are all null: phpmyadmin screenshot

Furthermore, it breaks when I add additional clause like:

WHERE user_id = '{$_SESSION['user_id']}' ORDER BY date DESC LIMIT 30
Improve this question
5
  • Yes, it is possible to show them in a single table. See colspan property of td element. Commented Feb 11, 2016 at 21:26
  • I don't understand how that's relevant to my issue. Basically I have 5 columns from the first db and another 3 columns from the 2nd db. I can't generate them on the same table as formatting breaks since dates from the two db are not necessarily found in both of them. Commented Feb 11, 2016 at 21:29
  • @Ivan - Are you trying to merge rows as well? Or will both of the result sets be completely independent of eachother other than the fact that they'll share a table? Commented Feb 11, 2016 at 21:33
  • Results are independent, except for date column. Commented Feb 11, 2016 at 21:55
  • Please add an screenshot, regarding some part of your initial tables (table1 & table2) Commented Feb 11, 2016 at 22:22

3 Answers 3

Reset to default
2

I think you are after FULL OUTER JOIN concept:

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2)

enter image description here

In which you may use common dates as a shared row.

So the query will get to simple one:

$query = "
    SELECT table1.date, stuff
    FROM table1
    LEFT OUTER JOIN table2 ON table1.date = table2.date
    UNION
    SELECT table2.date, other_stuff
    FROM table1
    RIGHT OUTER JOIN table2
    ON table1.date = table2.date
";
$result = mysqli_query( $con, $query );

while( $record = mysqli_fetch_array( $result ) ){
    echo '<html table generated based on query>';
}

Example

This is an schematic diagram of FULL OUTER JOIN concept:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Mysql does not support full outer join. You need to use union to achive the same.
@Shadow tnx for notable comment dude!
Been trying this with no luck: " Column 'date' in field list is ambiguous"
@Ivan you need to prefix the date field with table names. check update in a minute :)
Solved already. Thanks for the help and getting me on the right track. Much appreciate it!
|
1

After running into quite a few bumps with this one, I finally managed to merge 2 columns from each table and also to use where and sort clauses on them with the following query:

( SELECT user_visit_logs.user_id,user_visit_logs.date,unique_hits,non_unique_hits,earned,sites_surfed,earnings FROM user_visit_logs 
LEFT OUTER JOIN surfer_stats ON user_visit_logs.user_id = surfer_stats.user_id AND user_visit_logs.date = surfer_stats.date where user_visit_logs.user_id = 23 ORDER BY date DESC LIMIT 30 )
UNION 
( SELECT surfer_stats.user_id,surfer_stats.date,unique_hits,non_unique_hits,earned,sites_surfed,earnings FROM user_visit_logs 
RIGHT OUTER JOIN surfer_stats ON user_visit_logs.user_id = surfer_stats.user_id AND user_visit_logs.date = surfer_stats.date where user_visit_logs.user_id = 23 LIMIT 30 )

Simplified, "user_visit_logs" and "surfer_stats" were the 2 tables needed to be joined.

Improve this answer

Comments

0

Absolutely. Just pop them both into a variable:

$data = '';

$query = mysqli_query($con,"SELECT date, stuff* ");
while($record = mysqli_fetch_array($query)) {
$data.= '<tr><td>--Your Row Data Here--</td></tr>';
}

$query2 = mysqli_query($con,"SELECT date, other stuff*");
while($record = mysqli_fetch_array($query2)) {
$data .= '<tr><td>--Your Row Data Here--</td></tr>';
}

echo "<table>$data</table>";

Instead of using echo in your loop, you're just storing the results in $data. Then, you're echoing it out after all data has been added to it.

As for your second point, it's not a big deal if fields don't exist. If they're null, you'll just have a column that doesn't have data in it.

Here's an example with fake column names:

$data = '';

$query = mysqli_query($con,"SELECT date, stuff* ");
while($record = mysqli_fetch_array($query)) {
    $data.= "<tr><td>{$record[id]}</td><td>{$record[first_name]}</td><td>{$record[last_name]}</td></tr>";
}

$query2 = mysqli_query($con,"SELECT date, other stuff*");
while($record = mysqli_fetch_array($query2)) {
    $data .= "<tr><td>{$record[id]}</td><td>{$record[first_name]}</td><td>{$record[last_name]}</td></tr>";
}

    echo "<table><tr><th>ID</th><th>First Name</th><th>Last Name</th></tr>$data</table>";

I have a feeling I may have misunderstood the need. If so, I apologize. If you can elaborate just a bit more I can change my answer :)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.