Search tags from MySQL database

[Genosuki]

Hello, so i made table with Tags row which is Text(126).. and idea is to put text like "PHP, MySQL, Forum, Blog" etc etc...

I have problem searching tags my search code:

$requestedtag = $con->real_escape_string($_GET['tag']);

$sql = "SELECT * FROM images WHERE tags = '$requestedtag' ORDER BY ID DESC LIMIT 12";
		$result = $con->query($sql);

		if ($result->num_rows > 0) 
		{
		    while($row = $result->fetch_assoc()) {
		    	$image_id = $row["ID"];
		    	$image_title = $row["Title"];
		    	$image_url = $row["ImgURL"];
		    	$image_tags = $row["Tags"];
		    	echo '<div class="col-3"><div class="thumb"><a href="/image.php?id='.$image_id.'" title="'.$image_title.'"><img src="'.$image_url.'"></img></a></div></div>';
			}
		}

But it doesnt return anything. I think the problem is that row["Tags"]; is divided by "," from tags and i should split it somehow but i dont really know what to do...

[ginerjm]

One of the first rules of database design and normalization is to never put the same data items in one field.  Your tags are each one a separate piece of data.  They are therefore equal.  And because of that they should be stored separately into a separate table as separate rows.

 

So for each combination of Id/Title/Url you should have but one record with a tag.  If there are more tags with those same attributes then you need to have a separate record with each new tag.  Then when you are looking for the tags for a specific combination of Id/Title/Url you run a query that pulls all the rows with those particular values.  You can then loop thru those result rows and capture each of the tags.  Output them as you wish.

[Barand]

So you would have two tables and use a join to match on tag

+-----+-------------+------------+
| ID  | title       | imageURL   |
+-----+-------------+------------+
|  1  |  abc        | /im/abc.jpg|
|  2  |  xyz        | /im/xyz.jpg|
+-----+-------------+------------+
   |
   |
   +---------------+
                   |
                +--------+-------------+
                |imageID |   tag       |
                +--------+-------------+
                |  1     | animal      |
                |  1     | dog         |
                |  1     | snow        |
                |  2     | mountains   |
                |  2     | snow        |
                +--------+-------------+

Example

SELECT i.ID, i.title
FROM images i
  JOIN tags t ON i.ID = t.imageID
WHERE t.tag = 'snow'