MySQL Query Using an Array

MySQL query using an array

The second query should use $thelist not $row, and it should be outside of the while loop. The foreach loop is unnecessary when processing a single row. You can access the name in $row with a simple $row[0]. Something like this (untested):

$query1 = "SELECT name FROM clients WHERE sector = '$sectorlink'";
$clientresult = mysql_query($query1, $connection) or trigger_error("SQL", E_USER_ERROR);

while($row = mysql_fetch_array($clientresult)){
    $temp[] = '"'.$row[0].'"';
}

$thelist = implode(",",$temp);
$query = "SELECT count(*) FROM studies WHERE client IN ($thelist) ORDER BY (date) desc";
$result = mysql_query($query, $connection) or trigger_error("SQL", E_USER_ERROR);

Caution: Please be aware that your code is highly vulnerable to SQL injection attacks. It's fine for testing or internal development but if this code is going to be running the Fort Knox web site you're going to want to fix it up quite a bit. Just an FYI. :-)

Mysql where id is in array

$string="1,2,3,4,5";
$array=array_map('intval', explode(',', $string));
$array = implode("','",$array);
$query=mysqli_query($conn, "SELECT name FROM users WHERE id IN ('".$array."')");

NB: the syntax is:

SELECT * FROM table WHERE column IN('value1','value2','value3')

MySQL query in PHP with an array in the condition

You cannot pass the array to the query. You can use IN to pass different, comma separated values to the query.
So first you have to take each array entry and concatenate it with commas:

//initialize the list
$countries = "";
foreach($country as $a){
    //add each country wrapping it in single quotes
    $countries .= "'".$a."',";
}
//remove the last comma that is not necessary
rtrim($countries,",");
//build the query
$sql = "SELECT * FROM table1 WHERE table1.country IN ($countries)";
//run the query
$result = mysqli_query($con,$sql);

Note that building $countries I have put single quotes around each element. The reason is that I am passing strings to the database. This would not be necessary in case of integers

How to pass an array to a MySQL query using multiple WHERE clauses

Here's a solution that uses only positional parameters:

$active = 1;
$in = join(',', array_fill(0, count($arrList), '?'));
$sql = "SELECT * FROM `table` WHERE active=?                 
                AND postID IN ($in)
                ORDER BY postID DESC";
$stmt = $pdo->prepare($sql);
// make an ordinal array for all the positional parameters
// with $active as the first element.
$params = array_values($arrList);
array_unshift($params, $active);
$stmt->execute($params);
$output = $stmt->fetchAll(PDO::FETCH_OBJ);

How can I execute a MySQL query using an array as an input instead of just a single value?

You would OR your conditions together.

SELECT * 
FROM test_table 
WHERE
mz1_shifted BETWEEN (1000 - 1) AND (1000 + 1)
OR
mz1_shifted BETWEEN (1100 - 1) AND (1100 + 1)
OR
mz1_shifted BETWEEN (1200 - 1) AND (1200 + 1)

Pass array in Mysql query with nodejs

The syntax of the IN() predicate does not use =.

WHERE (table1.col2) IN = (?,?,?,?,?)

should be

WHERE table1.col2 IN (?,?,?,?,?)

Tip: you can (and should) check syntax yourself in the documentation, so you can get answers more easily than posting to Stack Overflow.

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_in

Using Array Values in Mysql Query

You can convert the array to a string and use that in your query. Please note that the below assumes that $categories are already safe and wont contain malicious input. If this is not the case you'll need to clean the input.

$categoriesClause = implode(",",$categories);

$query = $this->db->query ("
    SELECT 
        p.product_id, 
        p.quantity 
    FROM {$this->prefix}product p 
    LEFT JOIN {$this->prefix}product_to_category pc 
        ON (p.product_id = pc.product_id) 
    WHERE pc.category_id IN ($categoriesClause)
    AND p.status = '1' 
    ORDER BY p.quantity DESC 
    LIMIT 0, 4");

Using an array to put into where condition MySQL query

Let's say that $array contains the synonymous categories from wordNet. To turn that into a string which you can use in your query, do this:

$category_string = '"' . implode('","',$array) . '"';

This will turn your array into a string and separate the elements by ",". The string will have a " at the end and the beginning, too, so it can be inserted into the SQL IN statement.

Your SQL command needs to JOIN the three tables (products, categories and product_categories) together and set a WHERE filter for the desired category names. The SELECT command should look similar to this:

SELECT * FROM products, categories, product_categories WHERE products.product_id = product_categories.product_id AND product_categories.category_id = categories.category_id AND categories.category_name IN (INSERT CATEGORY NAMES)

If your SELECT command was to be run with PHP, it would have to look like this:

$sql = 'SELECT * FROM products, categories, product_categories WHERE products.product_id = product_categories.product_id AND product_categories.category_id = categories.category_id AND categories.category_name IN (' . $category_string . ')';

---

Related Topics