Dynamic select mysqli query with dynamic parameters returns error doesn’t match number of bind variables [duplicate]

by

Because:

  1. You are using user-supplied data, you must assume that your query is vulnerable to a malicious injection attack and
  2. the amount of data that is to be built into the query is variable/indefinite and
  3. you are only writing conditional checks on a single table column

You should use a prepared statement and merge all of the WHERE clause logic into a single IN statement.

Building this dynamic prepared statement is more convoluted (in terms of syntax) than using pdo, but it doesn’t mean that you need to abandon mysqli simply because of this task.

$mediaArray ='Facebook,Twitter,Twitch,';
$otherMedia="House";

$media = array_unique(explode(',', $mediaArray . $otherMedia));
$count = count($media);

$conn = new mysqli("localhost", "root", "", "myDB");
$sql = "SELECT * FROM mediaservices";
if ($count) {
    $stmt = $conn->prepare("$sql WHERE socialmedianame IN (" . implode(',', array_fill(0, $count, '?')) . ")");
    $stmt->bind_param(str_repeat('s', $count), ...$media);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    $result = $conn->query($sql);
}
foreach ($result as $row) {
    // access values like $row['socialmedianame']
}

For anyone looking for similar dynamic querying techniques:

More Related Contents:

  1. mysqli or PDO – what are the pros and cons? [closed]
  2. Object of class mysqli_result could not be converted to string
  3. mysqli_stmt::bind_result(): Number of bind variables doesn’t match number of fields in prepared statement
  4. SELECT COUNT(*) AS count – How to use this count
  5. Mysqli update throwing Call to a member function bind_param() error [duplicate]
  6. mysqli bind_param() expected to be a reference, value given
  7. Should I manually check for errors when calling “mysqli_stmt_prepare”?
  8. mysqli_real_escape_string() expects exactly 2 parameters, 1 given
  9. parameters in MySQLi
  10. Retrieving Multiple Result sets with stored procedure in php/mysqli
  11. PHP Commands Out of Sync error
  12. Mysql No connection could be made because the target machine actively refused it
  13. How to connect to MySQL database in PHP using mysqli extension?
  14. How do I count query result rows?
  15. Bind Param with array of parameters
  16. How do you use IN clauses with mysqli prepared statements [duplicate]
  17. What to do with mysqli problems? Errors like mysqli_fetch_array(): Argument #1 must be of type mysqli_result and such
  18. Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message ‘No index used in query/prepared statement’
  19. bind_param Number of variables doesn’t match number of parameters in prepared statement
  20. How to fetch all in assoc array from a prepared statement?
  21. Fatal error: Call to undefined function mysqli_result()
  22. Fatal error: Call to undefined method mysqli_stmt::fetch_array() [duplicate]
  23. Why I am getting an error when using w3school tutorial? [duplicate]
  24. Using wildcards in prepared statement
  25. mysqli_connect(): (HY000/2002): No connection could be made because the target machine actively refused it
  26. How to remove the fatal error when fetching an assoc array
  27. Fatal error: Call to undefined method mysqli_stmt::get_result()
  28. PHP MySQLi Multiple Inserts
  29. Fatal error: Call to a member function prepare() on a non-object in
  30. Strange problem with the length of the field gotten from database [duplicate]

Leave a Comment