寻找连接不良的表之间的链接（不确定配方）

Question

:

I have to say that I am far from being an expert in PHP and MySQL, so I did not know how to formulate my question. This explains the vagueness of the title. I have two tables which are badly written but which I am not allowed to change. The only thing that they share is a field called url. They have no primary key or indexes. Here is their structure:

Shareholder       |||   tags

url1 |  value1      |||      url1 | tag1 tag2 tag350 tag 400

url2  | value2      |||     url2 | tag8 tag400 tag350

url3  | value1      |||   url3 | tag2 tag1

url4 | value1       |||    url4 | tag5 tag 600

url5 | value2        |||   url5  | tag 60 tag8

From these tables I have to find the average number of tags by shareholder, and the shareholders are, for example, value1, value2.
The expected ouput would be, for example, shareholder1 (value1)

It was rather easy to count the number of tags by url with a temporary array. Then I think that I have to add these values to a related shareholder, but I'm lost. I do not know how to solve this problem and don't even know how to formalize this type of PHP/SQL problem. I have made an attempt which has naturally failed. I paste it below nevertheless. Can someone help me?

$sqlprim = "SELECT DISTINCT content FROM shareholder";
$deletedtermsprim = mysql_query ($sqlprim);

while ($rowprim = mysql_fetch_array($deletedtermsprim))
{
$tagsbyshareholder = array();
foreach ($rowprim as $keys1 => $values1)
{
    $sql = "SELECT url FROM shareholder WHERE content like \"$values1\"";
    $output1 = mysql_query($sql);
    $deletedterms = array("tags", "[", "]", ":");
while ($row = mysql_fetch_array($output1)) {
    foreach ($row as $columnName => $columnUrl) {
        $sql2 = "SELECT content
FROM tags WHERE url like\"$columnUrl\"";
$deletedterms = mysql_query($sql2);
while ($row2 = mysql_fetch_array($deletedterms)) 
{
    foreach ($row2 as $keys2 => $columntags); 
    {
        $preparedtags = str_replace($deletedterms, "", $columntags);
        $temporaryarray = explode(" ", $preparedtags);
        $temporaryarray = array_values(array_filter($temporaryarray));
        $countedtagsbyurl = count($temporaryarray);
        array_push($tagsbyshareholder, $countedtagsbyurl);
        }
}
}
}

}
foreach ($tagsbyshareholder as $countitems => $numberoftagsbyshareholder);
            echo $values1. ";". $numberoftagsbyshareholder;
            echo "<br />";
}

Answer 1

This might help you with the SQL part of your problem and I think it will simplify what you have to do in PHP. Your database may not be properly normalised or have PKs etc, but you can still do joins on the two tables.

For instance, this query:

SELECT s.url, s.content, t.content 
FROM shareholder s
INNER JOIN tags t 
ON s.url = t.url

returns

url1    value1  tag1 tag2 tag350 tag400
url2    value2  tag8 tag400 tag350
url3    value3  tag2 tag1
url4    value4  tag5 tag600
url5    value5  tag60 tag8

on your sample data. This kind of query with an inner join to pull both tables together would surely help to avoid a lot of embedded loops in your PHP. This seems particularly important because you are trying to run a select query for each repetition of the outer loop.