Mysql explode string avoiding text within html tags

PHP
1

I have a table with films and in my php page I get the film title, film content, film date, film keywords and film actors, exploding values comma separated.
For example in Some like it hot I have, in actors column, Marilyn Monroe,Tony Curtis,Jack Lemmon, and I get their names in my php page so that clicking on each of them I open a page with all the films where they were actors.
But I wish add the role in a film as well, i.g. “Marilyn Monroe” as “Zucchero Kandinsky”. I noticed that if I write in the mysql actors column something like <span title="as Zucchero Kandinsky">Marilyn Monroe</span>, I get in php page this whole html code, so that it becomes impossible to link towards other films with Marilyn Monroe.
Therefore, my question is: it would be possibile to do what I wish? That is is possible to explode something avoinding what is within an html tag (<...>), but at the same time keeping this html code in the resulting php page? Indeed I wish in the php page <span title="as Zucchero Kandinsky">Marilyn Monroe</span>, so that with mouse over I can see the role of an actor in that film.
My present code is:

echo "<p class=\"actors\">";
$keywordsa = $row['attori'];
if(empty($row["attori"])){echo "";} else{echo "<p class=\"keywords\"><b>attori</b>:";}
foreach (explode(',', $keywordsa) as $keya) {
    if(empty($row["attori"])){echo "";} else{echo "<span><a href=\"hashtag-actors.php?tag=$keya\">{$keya}</a></span>";}
}
echo "</p>";

Thank you.

2

Do not store csv data in a column. You need to learn about Database Normalization. Each piece of data should be in it’s own row.

And you usually should not be storing HTML in the database.

There are many movie database schema examples out there you can learn from. This one will probably be of help.

https://www.w3resource.com/sql-exercises/movie-database-exercise/index.php

Also, in your code snippet you create a variable for nothing but still use the original variable as well. Putting your code on single lines is discouraged and is harder to read and debug. Take a look at the PSR-2 Coding Style Guide.

5 Likes
3

I’m not entirely clear about the set up you have. But I get the feeling your database needs a redesign.
Possibly not just a “Films” table, but also an “Actor” table, plus a Look-Up Table to join actors to films, and maybe also to join a “Character” table.

By separating this data you will be able to have not just a page per film listing actors/characters, but a page per actor listing their films/characters. Even a page per character, listing actors and films that portrayed them.
I would avoid putting CSV or HTML into table columns. Use a number of relational tables to organise data properly.

3 Likes
4

Thank you. Your tips need a time to study, probably I will see.
But at the present, given that my films table works already pretty good, there would be another way, less “revolutionary”?

5

What you are attempting will be very difficult using a single table.
A proper relational database with multiple linked tables is the way forward.
Continuing with the single table with multiple values in columns is a dark path to misery and coding nightmares.
No one wants to show you how to do it the wrong way. :slightly_smiling_face:

3 Likes
6

The further you go down this rabbit hole, the harder it will be to get out of it. It’s always better to get out while you still can.

6 Likes
7

Well, I will see to learn the way you suggest. Thank you!

8

I’m trying to do what suggested by the above link (#2). In particular at this url: https://www.w3resource.com/sql-exercises/movie-database-exercise/sql-exercise-movie-database-14.php.
I have created two new tables films__cast and films__actors.
The following query (or similar) however doesn’t get the expected result:

SELECT f.titolo, f.data, f.mov_id
FROM  films__olon f 
NATURAL JOIN films__cast
NATURAL JOIN films__actors
WHERE f.mov_id='1';

I get an empty result ( MySQL returned an empty result set (i.e. zero rows)).

Same result with:

SELECT f.titolo, f.data, f.mov_id, a.cognome, a.nome, a.act_id, c.mov_id, c.act_id
FROM  films__olon f 
NATURAL JOIN films__cast c
NATURAL JOIN films__actors a
WHERE f.mov_id=1;
9

If your going to follow a tutorial I would suggest you do exactly as it tells you before you start making your own changes. Once you finish it and have it working you should have a grasp on how to do your database.

2 Likes
10

Thank you. Few seconds ago I found this code working:

SELECT f.titolo, f.data, a.cognome, a.nome
FROM films__cast c 
JOIN films__olon f
ON f.mov_id=c.mov_id
JOIN films__actors a
ON a.act_id=c.act_id
WHERE c.mov_id=1

I got, as expected:

titolo                      data  cognome	 nome	
Good Will Hunting 1997 Damon Matt
Good Will Hunting 1997 Williams Robin

I will do further attempts to perfect my query.

EDIT

BTW, before proceeding, what about a view instead of a table for films__actors? I mean a view from another table with all kind of people (politicians, philosophers, scientists and so on).

1 Like
11

I was thinking about this following my previous answer.

In hindsight, it may be that the lines blur between how an individual is involved in film making. It is common enough for actors become directors, producers, writers and even characters. A film maker may become a character in a biographical film, or when someone appears as themself.
It may be better to have a generic table of “people” and look-ups to define their involvement in any films, as actor, director, producer, character, etc.

A table of “people” may extend to include people beyond the film world, as part of a larger database.

Though they will be sharing the table with fictional characters, and not all characters are human.
But of course real life politicians, philosophers and scientists have been portrayed as characters in films many times, so it may work.

1 Like
12

Thank you very much! I will do so.

13

@SamA74 is right about the people table. It may be more advanced for you right now, but look into the “Party Model”. It is infinity scalable for whatever you are wanting to do with this. It will take some time to learn and understand but it will really bump up your database design skills.

2 Likes
14

Thank you. I will see.
Meanwhile, I noticed that merging actors into a bigger table arises a problem: the bigger table has an ID (the same used as act_id) as primary a.i. key, and I sometimes change it to re-order by date the table after adding new rows (not cronologically ordered). As a result I would have a mess in the above actors mysql query: the changed act_id would get the wrong actors.
Therefore I have, it seems, two ways: 1) make ID non primary (so that I could keeping it, when I re-order the table) or 2) in movie cast table (films__cast) put actor’s name and surname as identifier of an actor, instead of its act_id.
It seems that both the ways are possible: what about the best between the two? Or about another way?

The “Party Model” could be an answer to this question?

15

Or 3: don’t re-order the table, just use ORDER BY when retrieving the data.

1 Like
16

Yes, this could be a third option. But there are relevant cons against my ones?
In particular, I know I’m going off topic, a primary key is really useful (or even necessary), working only in localhost?

17

Why would the location of the database make any difference to how it is structured?

1 Like
18

OK, let me cut “in localhost” (I thought about security issues, but so I was wrong?).

19

Oh, I see what you mean. I don’t see that security is relevant to whether or not you should re-order tables. I like things to look “neat”, but the only place that the table is viewed in “native” form is in phpmyadmin, so I can’t see why re-ordering the table is necessary, which then means you can use the ID as suggested.

1 Like
20

Well, coming back to the code, I managed to get a first step, with the following code:

$query = "
SELECT f.titolo, f.titolo_italiano, f.imagelink, f.data, f.regia, f.regia_nome, f.contenuti, f.key_libere, a.cognome, a.nome, c.role, c.mov_genre
FROM films__olon f
JOIN films__cast c
ON c.mov_genre=f.genere
JOIN films__actors a
ON a.act_id=c.act_id
WHERE c.mov_id=f.mov_id AND f.genere LIKE '%giovan%'
ORDER by DATA";
$result = mysqli_query($db, $query);
while ($row = mysqli_fetch_array($result)){ 
echo "<h2>$row[titolo]"; 
if($row['imagelink']!=''){echo "<img src=\"$row[imagelink]\" height=\"200px\" class=\"fr\" />";}
echo "</h2>";
echo "<p>data:<b>$row[data]</b> - regista: $row[regia_nome] <a href=\"hashtag-registi.php?tag=$row[regia]\">$row[regia]</a></p>";
echo "<blockquote><p>"."$row[contenuti]</p></blockquote>";
echo "<p class=\"actors\">";
echo "<span><a href=\"hashtag-actors.php?tag=$row[cognome]\">$row[nome] $row[cognome]</a> (as $row[role])</span>";
echo "</p>";
$keywords = $row['key_libere'];
if($row['key_libere'] !=''){echo "<p class=\"keywords\"><b>temi</b>:";}
foreach (explode(',', $keywords) as $key) {
if($row['key_libere'] !=''){echo "<span><a href=\"hashtag-films.php?tag=$key\">{$key}</a></span> ";}
}
echo "</p>";}

It is only an attempt, partly successful, but partly not. Indeed I get as many films as many are their actors, and not, as desired, one films with several actors.
I copy only the text, if it’s not a problem:

Good Will Hunting

data: 1997 - regista: Gus Van Sant

Matt Damon (as Will Hunting (ragazzo geniale))

temi :condizione giovanile

Good Will Hunting

data: 1997 - regista: Gus Van Sant

Robin Williams (as Sean Maguire (psicologo))

temi :condizione giovanile

How should I change the code?
I suppose I should use foreach, shouldn’t I? But how?
Thank you!

EDIT

I tried with this code (foreach added)

$query = "
SELECT f.titolo, f.titolo_italiano, f.imagelink, f.data, f.regia, f.regia_nome, f.contenuti, f.key_libere, a.cognome, a.nome, c.role, c.mov_genre
FROM films__olon f
JOIN films__cast c
ON c.mov_genre=f.genere
JOIN films__actors a
ON a.act_id=c.act_id
WHERE c.mov_id=f.mov_id AND f.genere LIKE '%giovan%'
ORDER by DATA";
$result = mysqli_query($db, $query);
while ($row = mysqli_fetch_array($result)){ 
echo "<h2>$row[titolo]"; 
if($row['imagelink']!=''){echo "<img src=\"$row[imagelink]\" height=\"200px\" class=\"fr\" />";}
echo "</h2>";
echo "<p>data:<b>$row[data]</b> - regista: $row[regia_nome] <a href='hashtag-registi.php?tag=$row[regia]'>$row[regia]</a></p>";
echo "<blockquote><p>"."$row[contenuti]</p></blockquote>";
echo "<p class=\"actors\">";
$actors = $row['cognome'];
foreach (explode($actors) as $actor) {
echo "<span><a href=\"hashtag-actors.php?tag=$row[cognome]\">$row[nome] $row[cognome]</a> (as $row[role])</span>";
}
echo "</p>";
$keywords = $row['key_libere'];
if($row['key_libere'] !=''){echo "<p class='keywords'><b>temi</b>:";}
foreach (explode(',', $keywords) as $key) {
if($row['key_libere'] !=''){echo "<span><a href='hashtag-films.php?tag=$key'>#{$key}</a></span> ";}
}
echo "</p>";}

But nothing changed.