I have two tables, one with films, other with books. I managed to get two local webpages with films and their keywords, and with books and their keywords, so that clicking to a keyword I can see all my films with that keyword, or all my books with that keyword.
Now, I am wondering if I could merge books and films having the same keyword (i.g. “violence”), so that in the resulting local webpage I can see both (films and books). The column title, content and keyword (“key-libere”) have identical names, the column author and movie director have different names (“autore” and “regia”).
I tried unsuccessfully with this code:
$query = "SELECT * FROM bibliografie__letture_fatte LEFT JOIN films__olon ON bibliografie__letture_fatte.key-libere = films__olon.key-libere WHERE `key-libere` LIKE '%".$tag."%'";
First of all: never use select *. Always select the columns you need
To achieve your target you canuse union all
Select title, content, keyword, author as source
From yourBookTable
Where …
Union all
Select title, content, keyword, director as source
From yourMovieTable
Where…
If you need special order you can just wrap it by another select
Select title, content, keyword, source
From
(Select title, content, keyword, author as source
From yourBookTable
Where …
Union all
Select title, content, keyword, director as source
From yourMovieTable
Where…) as s
Order by ….
Thank you. I tried according (at least so seems to me) with your suggestions, this code:
$query = "SELECT title, content, key-libere, autore AS source FROM bibliografie__letture_fatte WHERE `key-libere` LIKE '%".$tag."%' UNION ALL SELECT title, content, key-libere, regia AS source FROM films__olon WHERE `key-libere` LIKE '%".$tag."%' ORDER by date";
But unsuccessfully (no errors, but “no data found”).
But I guess that the title I have chosen for this thread is misleading: rather than “merge” I should write “add (/combine)(the results)” from two tables. Because there is no one writer who is also a director (and viceversa). Nor my aim is to merge book and films with the same title.
The only common thing are the keywords, the hashtags.
So what I want is add rather than merge.
I mean, if in the working books hashtag page I get 12 items, and in the working films hashtag page I get 8 items (films), with that hashtag (violence), in the “merged” page my aim is to get 20 items (books + films).
The query should exactly do what you need. It will return all books and movies which keywords contain the content of the tag variable.
If you get no result there must be some other issue
In your php loop you are asking for keys like titulo and imagelink etc. this are of course empty because you do not select them in the query. Maybe this is your problem?
I don’t know…
I add the whole code. This is the code of hashtag-letture.php:
<?php
//hashtag.php
if(isset($_GET["tag"]))
{
$tag = preg_replace("/(?<!\S)#([0-9a-zA-Z]+)/", '', $_GET["tag"]);
$title=$tag;
include "$root/intell/header-intell.inc";
$connect = mysqli_connect("localhost", "myuser", "mypsw", "bibliografia");
mysqli_set_charset($connect, 'utf8mb4'); // procedural style
/*$query1 = "SELECT * FROM bibliografie__letture_fatte LEFT JOIN films__olon ON bibliografie__letture_fatte.key-libere = films__olon.key-libere WHERE `key-libere` LIKE '%".$tag."%'";*/
$query = "SELECT titolo, contenuti, imagelink, data, key-libere, autore AS source FROM bibliografie__letture_fatte WHERE `key-libere` LIKE '%".$tag."%' UNION ALL SELECT titolo, contenuti, imagelink, data, key-libere, regia AS source FROM films__olon WHERE `key-libere` LIKE '%".$tag."%' ORDER by date";
$result = mysqli_query($connect,$query);
if(mysqli_num_rows($result) > 0)
{
include "hashtag-communia.inc";
}
else
{
echo '<p>No Data Found</p>';
}
}
?>
And this is the content of hasgtag-communia.inc (which is called by hashtag-letture.php):
Maybe you should start to first check the query in you database management console like phpmyadmin or mysqlworknench. There you can test the query and see the results
Perfect! Excellent! The problem was key-libere : it works with ‘key-libere’ .
EDIT
ops… there is another, smaller problem: the resulting page shows correctly all desired items, but as hashtags it shows only one hashtag, named always key-libere It seems that the command
It should be pointed out that your DB “design” is flawed. You should learn about “Database Normalization”. (Guilty Admission: I started out doing tables the same exact way before I learned better.)
