Asked  7 Months ago    Answers:  5   Viewed   51 times

I have and array with two values and I want to use it with sql IN operator in select query.

Here is the structure of my table

id comp_id
1   2
2   3
3   1

I have an array $arr which have two values Array ( [0] => 1 [1] => 2 )

I want to fetch the record of comp_id 1 and comp_id 2. So I wrote the following query.

SELECT * from table Where comp_id IN ($arr)

But it does not return the results.

 Answers

16

Since you have plain integers, you can simply join them with commas:

$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(',', $arr) . ")";

If working with with strings, particularly untrusted input:

$sql = "SELECT * FROM table WHERE comp_id IN ('" 
     . implode("','", array_map('mysql_real_escape_string', $arr)) 
     . "')";

Note this does not cope with values such as NULL (will be saved as empty string), and will add quotes blindly around numeric values, which does not work if using strict mysql mode.

mysql_real_escape_string is the function from the original mysql driver extension, if using a more recent driver like mysqli, use mysqli_real_escape_string instead.

However, if you just want to work with untrusted numbers, you can use intval or floatval to sanitise the input:

$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(",", array_map('intval', $arr)) . ")";
Wednesday, March 31, 2021
 
PHPWDev
answered 7 Months ago
35

implode() can help you do this, though you will need to loop through and apply it to each individual array:

 $resultStrings = array();
 foreach ($array as $key => $values) {
    $subarrayString = "('$key','" . implode($values, "','") . "')";
    $resultStrings[] = $subarrayString;
 }

 $result = implode($resultStrings, ",");
Saturday, May 29, 2021
 
jakubos
answered 5 Months ago
26

If you are using composer to manage your project, you can just change the composer.json file:

"autoload": {
    "psr-4": {
        "": "src/",
        "": "vendor/phpoffice/phpexcel/Classes/"
    },
    "classmap": [
        "app/AppKernel.php",
        "app/AppCache.php"
    ]
},

Then add

use PHPExcel;
use PHPExcel_IOFactory;

to your controller file, and you can use the PHPExcel like this:

$objPHPExcel = new PHPExcel();

Hope it helps.

Sunday, August 8, 2021
 
Taptronic
answered 3 Months ago
25

The first obvious idea is to use your own regexp-matching operator with commuted arguments:

create function commuted_regexp_match(text,text) returns bool as
'select $2 ~* $1;'
language sql;

create operator ~!@# (
 procedure=commuted_regexp_match(text,text),
 leftarg=text, rightarg=text
);

Then you may use it like this:

SELECT '^p' ~!@# ANY(authors) FROM book;

Another different way of looking at it to unnest the array and formulate in SQL the equivalent of the ANY construct:

select bool_or(r) from 
  (select author ~* '^j' as r
    from (select unnest(authors) as author from book) s1 
  ) s2;
Sunday, October 10, 2021
 
Anupam Jain
answered 2 Weeks ago
44

This is the answer:

$sql="SELECT * FROM Blar";
$result = mysql_query($sql);
$combinedResults = array();
while ($row = mysql_fetch_array($result)) {
    $combinedResults[$row['cat']][] = array( 
    'LineID' => $row['lineID'],
    'Qty' => $row['qty'],
    'SetCost' => $row['Setcost'],
    'MonCost' => $row['Moncost'],
    'Postcode' => $row['postcode']
    );
};
//print_r($combinedResults);
print_r($combinedResults['Apps'][0]['Qty']);

Thanks for your help @Dereleases

Saturday, October 23, 2021
 
Puneet
answered 1 Day ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :