create exec command according to condition

[abigail pattugalan]

hello everyone,

I am trying to generate sql statement depending on the field received by php coming from jquery ajax.

function residentSearchPost() {

$searchPosted = array();

$arrayWhere = array();

$searchValue = array();

//get all posted fields

$searchPosted[] = $this->f3->GET('POST.searchtresident_id');

$searchPosted[] = $this->f3->GET('POST.searchtresident_first');

$searchPosted[] = $this->f3->GET('POST.searchtresident_last');

$searchPosted[] = $this->f3->GET('POST.searchtresident_dob');

$searchPosted[] = $this->f3->GET('POST.SearchRefbrgystreet_id');

$searchPosted[] = $this->f3->GET('POST.searchtresident_mobile_1');

$searchPosted[] = $this->f3->GET('POST.searchtresident_mobile_2');

//where conditions

$searchWhere[] = 'tresident_id = :id';

$searchWhere[] = 'tresident_first like :fn';

$searchWhere[] = 'tresident_last like :ln';

$searchWhere[] = 'tresident_dob = :dob';

$searchWhere[] = 'refbrgystreet_id = :strID';

$searchWhere[] = 'tresident_mobile_1 like :mobile1';

$searchWhere[] = 'tresident_mobile_2 like :mobile2';

//values to search

$searchValue[] = array(':id'=>(int)$this->f3->GET('POST.searchtresident_id'));

$searchValue[] = array(':fn'=>"'%" . $this->f3->GET('POST.searchtresident_first') . "%'");

$searchValue[] = array(':ln'=>"'%" . $this->f3->GET('POST.searchtresident_last') . "%'");

$searchValue[] = array(':dob'=>"'" . $this->f3->GET('POST.searchtresident_dob') . "'");

$searchValue[] = array(':strID'=>(int)$this->f3->GET('POST.SearchRefbrgystreet_id'));

$searchValue[] = array(':mobile1'=>"'%" . $this->f3->GET('POST.searchtresident_mobile_1') . "%'");

$searchValue[] = array(':mobile2'=>"'%" . $this->f3->GET('POST.searchtresident_mobile_2') . "%'");

//create where statement based on the values of posted fields

//where statement that will be generated will based on posted fields. if posted field is empty it will not be included in where statement

$strSearchWhere = '';

for($i=0;$i<count($searchPosted);$i++) {

if (!empty($searchPosted[$i])) {

$arrayWhere[] = $searchValue[$i];

$strSearchWhere = $strSearchWhere . $searchWhere[$i] . ' and ';

}

}

$strSearchWhere= rtrim($strSearchWhere, ' and ');

$strSearchWhere = 'SELECT * FROM tresident where ' . $strSearchWhere;

$search = $this->db->exec($strSearchWhere, $arrayWhere);

die;

}

I am receiving an error message

{"status":"Internal Server Error","code":500,"text":"PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined","trace":"

i do believe it has something to do with associative array $arrayWhere.

and I also created a test route using the below code, it works perfectly.

$result = $this->db->exec("SELECT * FROM tresident WHERE tresident_id=:id and tresident_first like :firstn and tresident_mobile_1 like :mobile1",array(':id'=>1, ':firstn'=>'%ja%', ':mobile1'=>'%3085%'));

pls help

thank you

[Ysguy]

Best to halt the actual query and echo out the result and get the sql statement and review that instead.

 

//$search = $this->db->exec($strSearchWhere, $arrayWhere);

Die(echo $strSearchWhere.$arrayWhere);

 

Does the output look like:

 

("SELECT * FROM tresident WHERE tresident_id=:id and tresident_first like :firstn and tresident_mobile_1 like :mobile1",array(':id'=>1, ':firstn'=>'%ja%', ':mobile1'=>'%3085%')

 

I’m guessing not. 

[abigail pattugalan]

hello sir,

this is the output

for $strSearchWhere

SELECT * FROM tresident where tresident_first like :fn and tresident_dob = :dob and tresident_mobile_1 like :mobile1

this is expected generating sql statement

unfortunately the error now is in associative array generated

here is the result of associative array

Array

(

    [0] => Array

        (

            [:fn] => '%jun%'

        )

    [1] => Array

        (

            [:dob] => '1971-10-25'

        )

    [2] => Array

        (

            [:mobile1] => '%0552222221%'

        )

)

so it seems to me it is correct, but it keeps on bringing error message as stated previously

anyone can help?

is the creation of associative array correct?

this is the result of what you suggest to check

SELECT * FROM tresident where tresident_first like :fn and tresident_dob = :dob and tresident_mobile_1 like :mobile1Array

yes the associative array is wrong

pls help

thank you

[Yaroslav Beregovoy]

Hi dear abigail. Let me suggest a possible solution. Try to recognise what is the difference between the two SQL queries below:

// Look, this is what you have got now (WRONG!!!):
$this->db->exec(
    [
        "SELECT * FROM `tresident` WHERE `tresident_id` = :id AND `tresident_first` LIKE :fn"
    ],
    [
        // your $arrayWhere has the next construction which is wrong!!!
        [
            ':id' => 'some val',
        ],
        [
            ':fn' => '%val',
        ]
    ]
);
// Should be this (Correct)
$this->db->exec(
    [
        "SELECT * FROM `tresident` WHERE `tresident_id` = :id AND `tresident_first` LIKE :ft"
    ],
    [
        [
            ':id' => 'some val',
            ':fn' => '%val',
        ],
    ]
);

As you can see the problem is within your bind params to the SQL query string... Hope it helps you

--
-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-framework...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en
---
You received this message because you are subscribed to the Google Groups "Fat-Free Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to f3-framework...@googlegroups.com.
To post to this group, send email to f3-fra...@googlegroups.com.
Visit this group at https://groups.google.com/group/f3-framework.
To view this discussion on the web visit https://groups.google.com/d/msgid/f3-framework/59c876bd-935c-4be6-aec1-9dbf55de1c53%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[abigail pattugalan]

for the update:

this is the select statement

$sql = "SELECT * FROM tresident WHERE tresident_id =:id and tresident_first LIKE :fn and tresident_mobile_1 LIKE :mobile1 ";

and associative array as parameters

$aa= 

Array

(

    [:id] => 1

    [:fn] => %ja%

    [:mobile1] => %3085%

)

now when I passed both the sql statement and the parameters

$result = $this->db->exec($sql, $aa);

I am receiving this error message

{"status":"Internal Server Error","code":500,"text":"PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens","trace":

total parameters are the same, there are four in sql and four in array.

as I have said earlier it is working in my test route.

this is the code for test route.

$result = $this->db->exec("SELECT * FROM tresident WHERE tresident_id=:id and tresident_first like :firstn and tresident_mobile_1 like :mobile1",

array(':id'=>1, ':firstn'=>'%ja%', ':mobile1'=>'%3085%'));

where is the error coming from?

pls help huys

thank you so much

On Saturday, June 8, 2019 at 10:39:25 AM UTC+3, aravael wrote:

Hi dear abigail. Let me suggest a possible solution. Try to recognise what is the difference between the two SQL queries below:

// Look, this is what you have got now (WRONG!!!):
$this->db->exec(
    [
        "SELECT * FROM `tresident` WHERE `tresident_id` = :id AND `tresident_first` LIKE :fn"
    ],
    [
        // your $arrayWhere has the next construction which is wrong!!!
        [
            ':id' => 'some val',
        ],
        [
            ':fn' => '%val',
        ]
    ]
);
// Should be this (Correct)
$this->db->exec(
    [
        "SELECT * FROM `tresident` WHERE `tresident_id` = :id AND `tresident_first` LIKE :ft"
    ],
    [
        [
            ':id' => 'some val',
            ':fn' => '%val',
        ],
    ]
);

As you can see the problem is within your bind params to the SQL query string... Hope it helps you

-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-fra...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en

---
You received this message because you are subscribed to the Google Groups "Fat-Free Framework" group.

To unsubscribe from this group and stop receiving emails from it, send an email to f3-fra...@googlegroups.com.

[Yaroslav Beregovoy]

I just made this query to ensure my case.

This one is absolutely correct:

$string = "SELECT * FROM `artist` WHERE id = :id AND name LIKE :like";
$params = ['id' => 93,':like' => '%metallica'];
return $this->db->exec($string,$params);

However, if you still got an  error do please the next. After you made your query and binded params, please add this part of code and show me the output:
print "$string\r\n"; // your main SQL query string
var_dump($aa); // you array with params
exit;

-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-framework...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en

---
You received this message because you are subscribed to the Google Groups "Fat-Free Framework" group.

To unsubscribe from this group and stop receiving emails from it, send an email to f3-framework...@googlegroups.com.

To post to this group, send email to f3-fra...@googlegroups.com.
Visit this group at https://groups.google.com/group/f3-framework.

To view this discussion on the web visit https://groups.google.com/d/msgid/f3-framework/a1f15935-9087-4788-9d37-eaf491610156%40googlegroups.com.

[abigail pattugalan]

hello sir, thank you so much it is working now.

On Saturday, June 8, 2019 at 5:27:55 PM UTC+3, aravael wrote:

I just made this query to ensure my case.

This one is absolutely correct:

$string = "SELECT * FROM `artist` WHERE id = :id AND name LIKE :like";
$params = ['id' => 93,':like' => '%metallica'];
return $this->db->exec($string,$params);

However, if you still got an  error do please the next. After you made your query and binded params, please add this part of code and show me the output:
print "$string\r\n"; // your main SQL query string
var_dump($aa); // you array with params
exit;

[abigail pattugalan]

Hello Everyone,

here am I again, facing the same issue with array in db->exec.

this below will work fine without any issue at all

db->exec('SELECT * FROM vsearchresidentinfo WHERE tresident_first LIKE :tresident_first and tresident_last LIKE :tresident_last', array(':tresident_first'=>'%abi%', ':'=>'%pat%'));

doing the same thing using user generated array such as the below generated by print_r

$arrayWhere = Array 

(

    [':tresident_first'] => '%abi%'

    [':tresident_last'] => '%pat%'

)

when I pass it as second parameter to db-exec like

db->exec('SELECT * FROM vsearchresidentinfo WHERE tresident_first LIKE :tresident_first and tresident_last LIKE :tresident_last', $arrayWhere);

it generates an error msg

"text":"PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"

I dont know what is wrong with $arrayWhere. kindly anyone pls help

thank you

[bcosca]

Have you tried:

$arrayWhere = Array 

([

    ':tresident_first' => '%abi%',

    ':tresident_last' => '%pat%'

])

[abigail pattugalan]

Hello Sir,

sorry for late reply.

Your code does not work too. but removing the brackets will work. 

My issue here is the $arrayWhere is generated according to fields selected by users.

let me give a complete picture of my application.

html

<form>

<select name="tSearchfield[]">

</select>

<input name="tSearchValue[]" >

<select name="tSearchfield[]">

</select>

<input name="tSearchValue[]" >

<select name="tSearchfield[]">

</select>

<input name="tSearchValue[]" >

<button type=submit>submit</button>

</form>

The above html code will let user select what field to search and enter value to search. There are eleven fields to search

now for F3 side

function residentSearchPost() {

$arValuesToSearch = array();

$arrayWhere = [];

$arSearchField = $this->f3->GET('POST.tsearch_field');

$arSearchValue = $this->f3->GET('POST.tresidentValueToSearch');

$arNewSearchValue = array_values($arSearchValue);

$inpCount= count($arSearchField);

$sWhere = "";

for($i=0;$i<$inpCount;$i++) {

//in here I am creating a new associative array using field as the key

$arValuesToSearch["':" .$arSearchField[$i] . "'"] = "'%" . $arNewSearchValue[$i] . "%'";

}

$keys = array_keys($arValuesToSearch);

$find = array_keys($arValuesToSearch);

for($i=0;$i<$inpCount;$i++) {

//$sWhere is the sql statement

$sWhere = $sWhere . $arSearchField[$i] . " LIKE :". $arSearchField[$i] . " and ";

$arrayWhere[$keys[$i]] = "'%" . $arNewSearchValue[$i] . "%'";

}

$sWhere= preg_replace('/(and(?!.*and))/', '', $sWhere);

$sWhere = "SELECT * FROM vsearchresidentinfo WHERE " . $sWhere;

/*when echoing the $sWhere it will produce this sql statement

SELECT * FROM vsearchresidentinfo WHERE tresident_first LIKE :tresident_first and tresident_last LIKE :tresident_last

*

* as for $arrayWhere using print_r function

* it will produce this

Array

(

[':tresident_first'] => '%abi%'

[':tresident_last'] => '%patt%'

)

*/

//now when executing the below command it will produce this error

//text: "PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined"

$result = $this->db->exec($sWhere,$arrayWhere );

jsone_encode($arrayWhere);

die;

}

I am sure error has something to do with the array $arrayWhere, checking the array using print_r it should work.

I am stucked with this issue.

pls help

thank you

[arava el]

Hello. Please, modify you code with this and show me what you got:

//TODO: debug
print "$sWhere\r\n";
var_dump($arrayWhere);
exit;

$result = $this->db->exec($sWhere,$arrayWhere);

jsone_encode($arrayWhere);
die;

On Sun, Jun 16, 2019 at 10:06 AM abigail pattugalan via Fat-Free Framework <f3-framework+APn2wQdlOZiTLUZ9oRT...@googlegroups.com> wrote:

Hello Sir,

sorry for late reply.

Your code does not workin too. but removing the brackets will work.

let me give a complete picture of my application.

html

<form>

<select name="tSearchfield[]">

</select>

<input name="tSearchValue[]" >

<select name="tSearchfield[]">

</select>

<input name="tSearchValue[]" >

<select name="tSearchfield[]">

</select>

<input name="tSearchValue[]" >

<button type=submit>submit</button>

</form>

The above html code will let user select what field to search and enter values to search. There are eleven fields to search

now for F3 side

function residentSearchPost() {

$arValuesToSearch = array();

$arValuesToSearchInWhere = [];

I am sure it has something to do with the array $arrayWhere, checking the array using print_r it should work.

I am stucked with this issue.

pls help

thank you

on php side route called by AJAXOn Sunday, June 16, 2019 at 1:58:56 AM UTC+3, bcosca wrote:

Have you tried:

$arrayWhere = Array 

([

    ':tresident_first' => '%abi%',

    ':tresident_last' => '%pat%'

])

--
-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-framework...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en

---
You received this message because you are subscribed to the Google Groups "Fat-Free Framework" group.

To unsubscribe from this group and stop receiving emails from it, send an email to f3-framework...@googlegroups.com.

To post to this group, send email to f3-fra...@googlegroups.com.
Visit this group at https://groups.google.com/group/f3-framework.

To view this discussion on the web visit https://groups.google.com/d/msgid/f3-framework/ee60e594-da44-4e35-ad68-a5f2f2e1bee9%40googlegroups.com.

[abigail pattugalan]

hello sir,

here is the result

SELECT * FROM vsearchresidentinfo WHERE tresident_first LIKE :tresident_first and tresident_last LIKE :tresident_last  

array(2) {

  ["':tresident_first'"]=>

  string(7) "'%abi%'"

  ["':tresident_last'"]=>

  string(8) "'%patt%'"

}

taking the single quotes from the key will return an empty array and putting single quotes in the key will raise an error

SQLSTATE[HY093]: Invalid parameter number: parameter ws not defined

thank you

On Sunday, June 16, 2019 at 10:25:44 AM UTC+3, arava el wrote:

Hello. Please, modify you code with this and show me what you got:

//TODO: debug
print "$sWhere\r\n";
var_dump($arrayWhere);
exit;

$result = $this->db->exec($sWhere,$arrayWhere);

jsone_encode($arrayWhere);
die;

-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-fra...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en

---
You received this message because you are subscribed to the Google Groups "Fat-Free Framework" group.

To unsubscribe from this group and stop receiving emails from it, send an email to f3-fra...@googlegroups.com.

[arava el]

Isn`t weird the situation with quotes you have

["':tresident_first'"]=>

I am sure it should be 
[':tresident_first']=>  OR  [":tresident_first"]=>

Doesn`t bother you?) Be attentive...

Let me suggest a possible solution 😎
for($i=0;$i<$inpCount;$i++) {
    $arValuesToSearch[":$arSearchField[$i]"] = "%$arNewSearchValue[$i]%";
}

On Sun, Jun 16, 2019 at 10:40 AM abigail pattugalan via Fat-Free Framework <f3-framework+APn2wQdlOZiTLUZ9oRT...@googlegroups.com> wrote:

hello sir,

here is the result

SELECT * FROM vsearchresidentinfo WHERE tresident_first LIKE :tresident_first and tresident_last LIKE :tresident_last  

array(2) {

  ["':tresident_first'"]=>

  string(7) "'%abi%'"

  ["':tresident_last'"]=>

  string(8) "'%patt%'"

}

--
-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-framework...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en

---
You received this message because you are subscribed to the Google Groups "Fat-Free Framework" group.

To unsubscribe from this group and stop receiving emails from it, send an email to f3-framework...@googlegroups.com.

To post to this group, send email to f3-fra...@googlegroups.com.
Visit this group at https://groups.google.com/group/f3-framework.

To view this discussion on the web visit https://groups.google.com/d/msgid/f3-framework/9280fac5-8c0e-4294-8201-d72e72972144%40googlegroups.com.

[abigail pattugalan]

wow gracious Lord thank you brother, it is working now!

once again thank you so much