Table structure settings

 CREATE TABLE IF NOT EXISTS `settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `value` varchar(255) NOT NULL, `type` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;

Data dump table settings

 INSERT INTO `settings` (`id`, `name`, `value`, `type`) VALUES (1, 'title', 'Qeekly', 'core'), (2, 'description', 'Твой собственный маленький мир', 'core'), (3, 'keywords', 'minecraft,сервер,комплекс,qeekly,майнкрафт', 'core'), (4, 'copyright', '«При копировании материалов с сайта, оставайтесь людьми — указывайте ссылку на источник!» Qeekly ©', 'core'), (5, 'charset', 'UTF-8', 'core'), (6, 'offline', '0', 'core'), (7, 'template', 'default', 'core'), (8, 'support', 'support@qeekly.ru', 'mail'), (9, 'noreply', 'noreply@qeekly.ru', 'mail'), (10, 'news', '3', 'lvl'), (11, 'pages', '4', 'lvl'), (12, 'actions', '4', 'lvl'), (13, 'profile', '2', 'lvl'), (14, 'news', '3', 'publish'), (15, 'pages', '4', 'publish'), (16, 'comments', '0', 'publish');

Now actually to the question. I need in ONE sql query to get all the values ​​of the value field where type=core , then c type=lvl , etc.

I think about everything like this:

 SELECT `value` FROM `settings` WHERE `type` = 'core' as Core AND `type` = 'lvl' as Lvl AND `type` = 'publish' as Publish

I hope clearly explained. Is it possible or should we create several identical queries with different where?

Here is the same code in several sql:

 SELECT `value` FROM `settings` WHERE `type` = 'core' SELECT `value` FROM `settings` WHERE `type` = 'lvl' SELECT `value` FROM `settings` WHERE `type` = 'publish'
  • Comments are not intended for extended discussion; conversation moved to chat . - Nicolas Chabanovsky ♦

1 answer 1

The answer is based on the comments on the question, please kindly redo the question so that he would really ask about what you need :)

 $rename=array('OFFLINE'=>'OFFLINE_STATUS','TEMPLATE'=>'TEMPLATE_NAME'); $result = $mysqli->query('SELECT name, type, value FROM settings'); while($row=$result->fetch_array()) { $name=strtoupper($row['name']); if($row['type']=='lvl' || $row['type']=='publish') { $name=$name.'_'.strtoupper($row['type']); } if(array_key_exists($name, $rename)) $name=$rename[$name]; define($name,$row['value']); }
  • This is certainly not ideal for me, because well, I would not want to write together just a template - template_name in the table and instead of a simple offline - offline_status. However, if there is no other way, I thank you, because this is definitely what I need. - Dmitry Goncharov
  • @DmitryPotter That's why I wrote under the answer and wrote that if some names do not match, then I need to convert them. Again on the client. I added an example of such a conversion to the code. I hope I did not mess up anything, I wrote to my eyes, I do not work for php :) - Mike
  • The decision to put it mildly is not very. Extra code. It is better to live with a curve :-) table than with clumsy code. :-) But yes it should work) - Dmitry Goncharov
  • @DmitryPotter And your previous code you want to say was not clumsy, with explicit $ settings [100,500], the need to properly sort the output from the database (God forbid some record will not turn out, etc.). By the way, no one bothers to get a name translation table in the database, paste it over the left join right in this query and take the name for php from it - Mike
  • My code was written in order for SELF to show what I want, then I turned for help to implement everything correctly, and not leave it as it somehow works. :) - Dmitry Goncharov