Home > Backend Development > PHP Tutorial > PHP multi-keyword and multi-field function to generate SQL statements

PHP multi-keyword and multi-field function to generate SQL statements

WBOY
Release: 2016-07-25 08:55:54
Original
1331 people have browsed it
  1. $keyword="1 2 3";
  2. echo $sql=search($keyword,"enter_gongyin_pic","a+b+c"); //Function generation, no LIMIT, no ORDER BY
Copy code

Generate sql statement:

  1. SELECT * FROM `enter_gongyin_pic` WHERE `a` LIKE '%1%' OR `a` LIKE '%2%' OR `a` LIKE '%3%' OR `b` LIKE '%1 %' OR `b` LIKE '%2%' OR `b` LIKE '%3%' OR `c` LIKE '%1%' OR `c` LIKE '%2%' OR `c` LIKE '%3 %'
Copy code

$keyword is obtained by POST or GET, separated by spaces, and can be searched in multiple fields.

Function to generate SQL query statements with multiple keywords and multiple fields:

  1. /**

  2. * Query condition construction statement
  3. * edit: bbs.it-home.org
  4. */
  5. function search($keyword,$table,$field)
  6. {
  7. /**
  8. //Formal parameter description:
  9. //keyword is the keyword, such as "Beijing Capital Direction Train".With spaces or without
  10. //table is the table name, such as enter_gongyin_pic.
  11. //Field is a combination of fields. If you are looking for one field, write the name.
  12. //If you are looking for more than two fields, use name+picdir.
  13. */
  14. //First determine the field.
  15. $new_field=explode("+",$field ); //Press + to strip
  16. $field_count=count($new_field); //The number of results obtained
  17. $newstring=explode(" ",$keyword); //Press space to strip
  18. $newstring2=array() ;
  19. //Remove useless spaces and uncle elements from the string
  20. $i=0;
  21. foreach ($newstring as $key => $value) {
  22. if($value!="")
  23. {
  24. $newstring2 [$i]=$value;
  25. $i++;
  26. }
  27. }
  28. //Remove useless spaces and uncle elements from the string,
  29. $result_count=count($newstring2); //Number of results obtained
  30. / /Generate SQL statement
  31. //* if($field_count==1) //Find 1 field START **
  32. if($field_count==1) //Find 1 field
  33. {
  34. if($result_count==1 ) //Determine if it is a key segment
  35. {
  36. $newstring_search=$newstring2[0];
  37. $sql="SELECT *
  38. FROM `$table`
  39. WHERE `".$new_field[0]."` LIKE '% $newstring_search%'";
  40. }
  41. if($result_count>1) //Judge if there are multiple key segments
  42. {
  43. $sql="SELECT *
  44. FROM `$table`
  45. WHERE ";
  46. $sql_add= "";
  47. foreach ($newstring2 as $key => $value)
  48. {
  49. if($key==0)
  50. {
  51. $sql_add=$sql_add."`".$new_field[0]."` LIKE '%".$value."%'";
  52. }
  53. else
  54. {
  55. $sql_add=$sql_add." OR `".$new_field[0]."` LIKE '%".$value."%'" ;
  56. }
  57. }

  58. $sql=$sql.$sql_add;

  59. }
  60. }

  61. //***** if($field_count= =1) //Find 1 fieldEND ******

  62. //**** if($field_count>1) //Find multiple fields START *****
  63. if($field_count>1) //Find multiple fields. At this time, $new_field is an array. Has multiple fields
  64. {
  65. if($result_count==1) //Determine if it is a key segment
  66. {
  67. $newstring_search=$newstring2[0]; //$newstring_search is a keyword
  68. $sql="SELECT *
  69. FROM `$table`
  70. WHERE ";
  71. $sql_add="";//Newly added field
  72. foreach ($new_field as $key => $value)
  73. {
  74. if($key==0)
  75. {
  76. $ sql_add=$sql_add."`".$value."` LIKE '%".$newstring_search."%'";
  77. }
  78. else
  79. {
  80. $sql_add=$sql_add." OR `".$value."` LIKE '%".$newstring_search."%'";
  81. }
  82. }
  83. $sql=$sql.$sql_add;
  84. }
  85. if($result_count>1) //Judge if there are multiple key segments (multiple key Word) ===
  86. {
  87. $sql="SELECT *
  88. FROM `$table`
  89. WHERE ";
  90. $sql_add="";//Newly added fields
  91. foreach ($new_field as $key => $value)
  92. {
  93. if($key==0) //When encountering $new_field[0] Example: `a` LIKE '%1%' OR `a` LIKE '%2%' OR `a` LIKE '%3 %'
  94. { //Nested foreach
  95. foreach ($newstring2 as $key2 => $value2)
  96. {
  97. if($key2==0)
  98. {
  99. $sql_add=$sql_add."`".$value. "` LIKE '%".$value2."%'";
  100. }
  101. else
  102. {
  103. $sql_add=$sql_add." OR `".$value."` LIKE '%".$value2."%'" ;
  104. }
  105. }
  106. //Nested foreach
  107. }
  108. else
  109. //(If it is multi-field, such as checking the name+picdir table) Start a FOREACH continuous loop, and execute ELSE each time $new_field[1] $new_field[2] $new_field[3].
  110. //The corresponding value is $value
  111. {
  112. //Nested foreach (multiple fields and multiple keywords)
  113. foreach ($newstring2 as $key2 => $value2)
  114. {
  115. if($key2==0)
  116. {
  117. $sql_add=$sql_add." OR `".$value."` LIKE '%".$value2."%'";
  118. }
  119. else
  120. {
  121. $sql_add=$sql_add." OR `". $value."` LIKE '%".$value2."%'";
  122. }
  123. }
  124. //Nested foreach
  125. }
  126. }//end of foreach ($new_field as $key => $value)
  127. $sql=$sql.$sql_add;
  128. }//if($result_count>1) end
  129. }//if($field_count>1) end
  130. //*** if($field_count>1) //Find more fieldsEND ***
  131. return $sql;
  132. }

Copy code


source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template