Function for generating SQL statements using multiple keywords and fields in PHP

Source: Internet
Author: User
Function for generating SQL statements using multiple keywords and fields in PHP

  1. $ Keyword = "1 2 3 ";
  2. Echo $ SQL = search ($ keyword, "enter_gongyin_pic", "a + B + c"); // function generation, no LIMIT, no ORDER

Generate an SQL statement:

  1. SELECT * FROM 'Enter _ gongyin_pic 'WHERE 'A' LIKE' % 100' OR 'A' LIKE '% 100' OR 'A' LIKE' % 100' OR 'B' LIKE '% 100' OR' B 'LIKE' % 100' OR 'B' LIKE '% 100' OR 'C' LIKE' % 100' OR 'C' LIKE '% 100' OR 'C' LIKE '% 3%'

$ Keyword is obtained by POST or GET. it is separated by spaces and can be searched by multiple fields.

Functions for generating SQL query statements with multiple keywords and fields:

  1. /**

  2. * Query condition construction statement
  3. * Edit: bbs.it-home.org
  4. */
  5. Function search ($ keyword, $ table, $ field)
  6. {
  7. /**
  8. // Parameter description:
  9. // Keyword is a keyword, for example, "Beijing Capital direction train ". With or without spaces
  10. // Table is the table name, such as enter_gongyin_pic.
  11. // Field is a combination of fields. for example, you can write the name after you find a field.
  12. // Use name + picdir if you search for more than two
  13. */
  14. // First, confirm the field
  15. $ New_field = explode ("+", $ field); // strip by +
  16. $ Field_count = count ($ new_field); // The number of results.
  17. $ Newstring = explode ("", $ keyword); // strip by space
  18. $ Newstring2 = array ();
  19. // Remove the useless space ancestor element 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 the useless space ancestor element from the string,
  29. $ Result_count = count ($ newstring2); // The number of results.
  30. // Generate an SQL statement
  31. // * If ($ field_count = 1) // find the START Field **
  32. If ($ field_count = 1) // Find 1 field
  33. {
  34. If ($ result_count = 1) // judge 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) // you can specify 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 the END field ******

  62. // ***** If ($ field_count> 1) // find multiple fields START *****
  63. If ($ field_count> 1) // find multiple fields. $ new_field is an array. Has multiple fields
  64. {
  65. If ($ result_count = 1) // judge 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 = ""; // add a new 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) // you can specify multiple key segments (multiple keywords) =
  86. {
  87. $ SQL = "SELECT *
  88. FROM '$ table'
  89. WHERE ";
  90. $ SQL _add = ""; // add a new field
  91. Foreach ($ new_field as $ key => $ value)
  92. {
  93. If ($ key = 0) // in case of $ new_field [0: 'A 'like' % 100' OR 'A' LIKE '% 100' OR 'A' LIKE' % 100'
  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. // (For multi-field queries, such as querying the name + picdir table) start the FOREACH continuous loop, and each execution of ELSE $ new_field [1] $ new_field [2] $ new_field [3].
  110. // The corresponding value is $ value
  111. {
  112. // Nested foreach (multi-field and multi-keyword)
  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. } // Foreach ($ new_field as $ key => $ value) ends
  127. $ SQL = $ SQL. $ SQL _add;
  128. } // If ($ result_count> 1) ends
  129. } // If ($ field_count> 1) ends
  130. // *** If ($ field_count> 1) // find multiple fields 'end ***
  131. Return $ SQL;
  132. }

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.