0


I have a PHP page and try to list my block categories with names instead of id's. Here is my MariaDB output:

select id,name  from url_category_data;
+----+----------------------+
| id | name                 |
+----+----------------------+
|  1 | adv                  |
|  2 | aggressive           |
|  3 | alcohol              |
|  4 | anonvpn              |
|  5 | automobile/bikes     |
|  6 | automobile/boats     |
|  7 | automobile/cars      |
|  8 | automobile/planes    |
|  9 | chat                 |
| 10 | costtraps            |
+----+----------------------+

I feed my block list by using those categories and stores as id. Here is my block category list:

select policyid,category_list from url_category_block where status  = 1 ;
+----------+---------------+
| policyid | category_list |
+----------+---------------+
|        1 | 8,9,10        |
+----------+---------------+

When I try to use echo I can print out 8,9,10

<p> Result is:   
<?php
     if($result_block===true ||   $row_list = $result_block->fetch_assoc()){ 
       echo  $row_list["category_list"];}?>
</p>

Its output il like that:

Result is: 8,9,10

But I want to display name of these values, such as;

Result is :
automobile/planes
chat
costtraps

To show my data, I show list as shortly.
Actually there are nearly 150 category name and id. So, I have to automate this match process in PHP I saw some answer this kind of question, but I am not very familiar with MySQL and PHP, so those answers not clear for me. How can convert those id numbers to print to screen their names by using PHP?

Tevfik Ceydeliler
  • 5
  • 7
  • 2
    You should normalize your database: Use a table to couple policy ID's to category ID's so that you can select what you need with a `JOIN` – jeroen Jun 29 '16 at 12:40
  • Join the table with the `JOIN` command. – node_modules Jun 29 '16 at 12:42
  • try to normalize the table by stoing it in diffrent table or get its result, and then again execute query to those ids only or u can use string function (custom) and use it. i am not sure about its performance. http://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two – Purushottam zende Jun 29 '16 at 12:50

2 Answers2

0

Do what @jeroen sais in his comment.

Create a table (url_category_block), where you have a column policyid and a column category_id. For every policy and category combination you need you will have an entry. Then you can query along these lines:

SELECT url_category_data.name FROM url_category_block LEFT JOIN url_category_data ON url_category_block.category_id = url_category_data.id WHERE policyid = 1;

This query probably doesn't work right away. I didn't test it and every SQL syntax works a little bit different, but a quick Google search (for sql, join) should help you.

Marco7757
  • 735
  • 9
  • 16
  • Is there any way in PHP to do this? – Tevfik Ceydeliler Jun 29 '16 at 12:55
  • Sure! You could, e.g., use [mysqli](http://php.net/manual/en/mysqli.query.php) to query your database. However, I've never worked with MariaDB so far. – Marco7757 Jun 29 '16 at 12:57
  • they are exactly same , mariadb and mysql – Tevfik Ceydeliler Jun 29 '16 at 13:26
  • `SELECT url_category_data.name, url_category_block.category_list FROM url_category_data INNER JOIN url_category_block ON url_category_data.id = url_category_block.category_list` show a name and values. Such as: automobile/planes as name and 8,9.10 as category_list. I think the comma between id's in category_list mess my data – Tevfik Ceydeliler Jun 29 '16 at 13:40
0

The best solution would be to normalise your url_category_block table and store each policy_id - category id pair in its own record. In this case a simple join on the field values will do the trick you are asking.

The quick solution is to use find_in_set() function to join the 2 tables:

select *
from url_category_block a
inner join url_category_data b on find_in_set(b.id, a.category_list)>0
Shadow
  • 33,525
  • 10
  • 51
  • 64