Learn computer science
Quering Mysql Column if matching Word - Printable Version

+- Learn computer science (https://discuss.computersciencewiki.org)
+-- Forum: Questions and Answers (https://discuss.computersciencewiki.org/forum-1.html)
+--- Forum: Questions and answers (https://discuss.computersciencewiki.org/forum-2.html)
+--- Thread: Quering Mysql Column if matching Word (/thread-53.html)



Quering Mysql Column if matching Word - LamaPijeon - 02-14-2021

I am querying a MySQL table, and want it only to output items that match the word but can have other words as well. I am sort of looking for the equivalence of 
PHP Code:
strpos string $haystack string $needle
in MySql.


[Image: Screenshot-2021-02-14-at-11-02-41.png]
Here is an image of my phpMyAdmin table. I am trying to search the table if the rightmost column, the `archetype` column. I am trying to find all results that match one word like Voltron, but the problem is that for example item #11, also has the word 'Keyword' in that column so it won't show.


[Image: Screenshot-2021-02-14-at-11-02-46.png]
This is an example of what I want to search by. The last column, the archetype column is the variable. I want to query MySql for all items that were Archetype like Tribal, or like Voltron, but can contain other 'Archetypes' as well.
When I query by this card, item number 3,9,10,11, and 15 should be outputed. 

PHP Code:
$aType $_GET['archetype'];
$aType explode(" "$aType);
        $arrayNum $aType[0];  
        
foreach ($aType as $value){
          //This Decides the Cards.  
              $query_all_items mysqli_query($connect"SELECT * FROM `edh__cardDatabase` WHERE `name` NOT LIKE '$name' AND `archetype` LIKE '$arrayNum' ORDER BY `name` ASC;");
              $arrayNum $aType[+1];
          //This Decides the Cards.  
            while ($row mysqli_fetch_array($query_all_items)) { 

And finally, this is what I have as the code, which right now doesn't work. $aType is an array with all of the Archetypes, and $arrayNum is a string with one of them. It all works, but I'm just curious if there is a way to find results that contain at least $arrayNum, but can contain more.


RE: Quering Mysql Column if matching Word - bmackenty - 02-14-2021

(02-14-2021, 10:14 AM)LamaPijeon Wrote: I am querying a MySQL table, and want it only to output items that match the word but can have other words as well. I am sort of looking for the equivalence of 
PHP Code:
strpos string $haystack string $needle
in MySql.


[Image: Screenshot-2021-02-14-at-11-02-41.png]
Here is an image of my phpMyAdmin table. I am trying to search the table if the rightmost column, the `archetype` column. I am trying to find all results that match one word like Voltron, but the problem is that for example item #11, also has the word 'Keyword' in that column so it won't show.


[Image: Screenshot-2021-02-14-at-11-02-46.png]
This is an example of what I want to search by. The last column, the archetype column is the variable. I want to query MySql for all items that were Archetype like Tribal, or like Voltron, but can contain other 'Archetypes' as well.
When I query by this card, item number 3,9,10,11, and 15 should be outputed. 

PHP Code:
$aType $_GET['archetype'];
$aType explode(" "$aType);
        $arrayNum $aType[0];  
        
foreach ($aType as $value){
          //This Decides the Cards.  
              $query_all_items mysqli_query($connect"SELECT * FROM `edh__cardDatabase` WHERE `name` NOT LIKE '$name' AND `archetype` LIKE '$arrayNum' ORDER BY `name` ASC;");
              $arrayNum $aType[+1];
          //This Decides the Cards.  
            while ($row mysqli_fetch_array($query_all_items)) { 

And finally, this is what I have as the code, which right now doesn't work. $aType is an array with all of the Archetypes, and $arrayNum is a string with one of them. It all works, but I'm just curious if there is a way to find results that contain at least $arrayNum, but can contain more.


This is one of the best questions I've ever seen. You rock. I think we can do this with the IN function in MYSQL. 

1.  convert the array to a comma-delimited list: 
PHP Code:
$array_in_strings implode(",",$arrayNum); 

2. And now change your MYSQL a bit: 
Code:
$query_all_items = mysqli_query($connect, "SELECT * FROM `edh__cardDatabase` WHERE `name` NOT LIKE '$name' AND `archetype` IN '$array_in_strings' ORDER BY `name` ASC;");

This is untested, but give it a go. Really love your question.


RE: Quering Mysql Column if matching Word - LamaPijeon - 02-14-2021

(02-14-2021, 03:47 PM)bmackenty Wrote: This is one of the best questions I've ever seen. You rock. I think we can do this with the IN function in MYSQL. 

1.  convert the array to a comma-delimited list: 
PHP Code:
$array_in_strings implode(",",$arrayNum); 

2. And now change your MYSQL a bit: 
Code:
$query_all_items = mysqli_query($connect, "SELECT * FROM `edh__cardDatabase` WHERE `name` NOT LIKE '$name' AND `archetype` IN '$array_in_strings' ORDER BY `name` ASC;");

This is untested, but give it a go. Really love your question.



Hello Mr. Mackenty. Assuming that you made a mistake and meant...
PHP Code:
$array_in_strings implode(",",$aType);       instead of.        $array_in_strings implode(",",$arrayNum); 
because $arraruNum is a string and not an array, I can say that this still doesn't work. I'll try messing around a little bit and see if I can fix anything.