Fetch() working but not when AND added

multichild · January 12, 2018, 3:39pm

I have the following generated query below which when using this code, works fine

$r->execute();
$numrows = $r->fetch();
$numrows = $numrows[0]; 

WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like :keyword) select COUNT(*) from LIMIT WHERE Contract_Number like :keywordB

But the user has a multiple choice system and then when they use the second choice it does make things more difficult but I feel that I have managed it pretty well and seems to make sense.

So this is now what the query looks like with the second choice added

WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like :keyword AND Contract_Status = :keyword1) select COUNT(*) from LIMIT WHERE Contract_Number like :keywordB AND Contract_Status = :keywordB1

And here is the whole code that builds up to the count part not working.

$query = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})  
select COUNT(*) from LIMIT WHERE  {limit clause b}";

    if ($i === 0) {
	$limitClause .= " " . $field_name . " " . $operator . " :keyword";	
	$limitClauseB .= " " . $field_name . " " . (${'operator' . 'B'} = $operator) . " :keywordB";
} else {
	$limitClause .= " AND " . $field_name . " " . (${'operator' . $i} = $operator) . " :keyword".$i;	
	$limitClauseB .= " AND " . $field_name . " " . (${'operatorB' . $i} = $operator) . " :keywordB".$i;
}

$query = str_replace('{limit clause}', $limitClause, $query);
$query = str_replace('{limit clause b}', $limitClauseB, $query);

$r=$conn->prepare($query);

if (strpos($query, ":keyword") !== false) {
	if ($operator == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keyword', $keywordb);
	} else {
		$r->bindParam(':keyword', $keyword);
		}
}
	
if (strpos($query, ":keyword1") !== false) {
	if ($operator1 == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keyword1', $keywordb);	
	} else {
		$r->bindParam(':keyword1', $keyword);
		}
}

if (strpos($query, ":keywordB") !== false) {
	if ($operatorB == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keywordB', $keywordb);
	} else {
		$r->bindParam(':keywordB', $keyword);
	}
}

if (strpos($query, ":keywordB1") !== false) {
	if ($operatorB1 == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keywordB1', $keywordb);
	} else {
		$r->bindParam(':keywordB1', $keyword);
	}
}

if (strpos($query, ":contractLike") !== false) {$r->bindParam(':contractLike', $contractLike); }

$r->execute();
//echo ($r->queryString);
$numrows = $r->fetch();
$numrows = $numrows[0];

So like I say its all good when only one choice is made but when the user adds to the search if you like building the query up, it seems I cant get $numrows to show the correct count, in fact it shows 0, so its not counting anything.

droopsnoot · January 12, 2018, 6:57pm

Is there a potential for some confusion between ‘:keywordB’ and ‘:keywordB1’, and between ‘:keyword’ and the others, in your strpos() calls? It strikes me that the first call to strpos() will return true in any case, because all the parameter names start with ‘:keyword’, but does it matter if you then call bindParam() on a parameter that isn’t in the query?

Presumably the query works correctly if you run it directly?

multichild · January 15, 2018, 9:03am

Hi droopsnoot,

Well there are I think 15 of these to think about so I had to somehow make every :keyword token unique, and with the loop adding adding an incremental value to each to create those :keyword variations, I thought that would do it.

So in the case each one is unique as you have :keyword, :keyword1, :keywordB and :keywordB1

Then the bindParams are in if statements, so if any of those above doesn’t exist then the bindParam that’s missing wont be called, but there is a specific order, and the bindParams are in order, its just a case of they exist when the script is run.

The one thing that did worry me as its the first time I have done it this way, is am I allowed to use numerical digits within the tokens, but if I am then each one I thought would ultimately be unique and so it would work.

multichild · January 15, 2018, 9:35am

I have just found an issue after testing and echoing values out.

When the second choice is made the first operator is showing the wrong value, so that’s why this isn’t working, because it should be a ‘like’ when its coming back as a ‘=’ which wont work, and thus zero results.

So there a conflict somewhere that means on multiple selections the first $operator is not what it should be.

droopsnoot · January 15, 2018, 10:28am

That’s true, and I was struggling a bit in my head, but when you check:

if (strpos($query, ":keyword") !== false) {

that will give you a positive result if it finds :keyword, but it will also give you a positive result if it finds :keyword1 or :keywordB or :keywordB1 and not :keyword. It might be the case that there will never be a situation where the query contains one of the suffixed keywords but not the plain unadorned one. It may also be the case that calling bindParam when the token doesn’t exist won’t cause a problem.

One way to be absolutely sure it never occurs is to make sure there’s a space after every keyword as you add them to the query (where white space doesn’t matter) and add the space on the end of the search string in your strpos() call, so search for ":keyword " which will not get confused with the start of any of the others.

(I notice you’ve found the issue, but just trying to clarify what I meant above.)

multichild · January 15, 2018, 2:57pm

Ok I think I know what you mean, I’ll take a look at that next.

I did resolve one issue by adding making sure the lone $operator doesnt get confused and over written by itself it seems, so I have below:

if ($i === 0) {
	$limitClause .= " " . $field_name . " " . (${'operatorA'} = $operator) . " :keyword";	
	$limitClauseB .= " " . $field_name . " " . (${'operatorB'} = $operator) . " :keywordB";
} else {
	$limitClause .= " AND " . $field_name . " " . (${'operatorA' . $i} = $operator) . " :keyword".$i;	
	$limitClauseB .= " AND " . $field_name . " " . (${'operatorB' . $i} = $operator) . " :keywordB".$i;
}

The change is the first ${‘operatorA’} is unique and then I can use that in the binds

if (strpos($query, ":keyword") !== false) {
	echo ($operatorA."a ");
	if ($operatorA == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keyword', $keywordb);
	} else {
		$r->bindParam(':keyword', $keyword);
		}
}

It corrected the issue I thought was the problem as it looks right but still zero results, so will have a look at your idea now

multichild · January 15, 2018, 3:40pm

Do you mean like this

if ($i === 0) {
	$limitClause .= " " . $field_name . " " . (${'operatorA'} = $operator) . " :keyword ";	
	$limitClauseB .= " " . $field_name . " " . (${'operatorB'} = $operator) . " :keywordB ";
} else {
	$limitClause .= " AND " . $field_name . " " . (${'operatorA' . $i} = $operator) . " :keyword".$i." ";	
	$limitClauseB .= " AND " . $field_name . " " . (${'operatorB' . $i} = $operator) . " :keywordB".$i." ";
}

if (strpos($query, ":keyword ") !== false) {
	echo ($operatorA."a ");
	if ($operatorA == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keyword ', $keywordb);
	} else {
		$r->bindParam(':keyword ', $keyword);
		}
}
	
if (strpos($query, ":keyword1 ") !== false) {
    echo ($operatorA1."b ");
	if ($operatorA1 == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keyword1 ', $keywordb);	
	} else {
		$r->bindParam(':keyword1 ', $keyword);
		}
}

if (strpos($query, ":keywordB ") !== false) {
    echo ($operatorB."c ");
	if ($operatorB == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keywordB ', $keywordb);
	} else {
		$r->bindParam(':keywordB ', $keyword);
	}
}

if (strpos($query, ":keywordB1 ") !== false) {
    echo ($operatorB1."d ");
	if ($operatorB1 == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keywordB1 ', $keywordb);
	} else {
		$r->bindParam(':keywordB1 ', $keyword);
	}
}

I’m not sure if that’s what you meant, if it is it has’t fixed it

droopsnoot · January 15, 2018, 6:35pm

Yes, that’s what I was talking about for the specific chance that it might get confused on the keywords.

Is there a loop somewhere that controls the value of $i?

multichild · January 16, 2018, 9:08am

Ye its further up and that seems to be working fine, as when I echo out the values it creates in the strpos if statements they are exactly what they should be.

if (strpos($query, ":keyword ") !== false) {
	echo ($operatorA);

But here it is -

if (isset($_POST['advrows'])) {
    for ($i = 0; $i < sizeof($_POST['field_names']); $i++) {
        if (strlen(trim($_POST['field_names'][$i])) !== 0 && strlen(trim($_POST['operator'][$i])) !== 0)
            {
            if (($_POST['field_names'][$i]) && ($_POST['operator'][$i])) {
                $field_name = ($_POST['field_names'][$i]);
                $operator = ($_POST['operator'][$i]);
                $keyword = ($_POST['keyword'][$i]);

Which then eventually feeds into this

if ($i === 0) {
	$limitClause .= " " . $field_name . " " . (${'operatorA'} = $operator) . " :keyword";	
	$limitClauseB .= " " . $field_name . " " . (${'operatorB'} = $operator) . " :keywordB";
} else {
	$limitClause .= " AND " . $field_name . " " . (${'operatorA' . $i} = $operator) . " :keyword".$i;	
	$limitClauseB .= " AND " . $field_name . " " . (${'operatorB' . $i} = $operator) . " :keywordB".$i;
}

then

$query = str_replace('{limit clause}', $limitClause, $query);
$query = str_replace('{limit clause b}', $limitClauseB, $query);

then

if (strpos($query, ':keyword') !== false) {
	echo ($operatorA);
	if ($operatorA == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keyword', $keywordb);
	} else {
		$r->bindParam(':keyword', $keyword);
		}
}
	
if (strpos($query, ':keyword1') !== false) {
    echo ($operatorA1);
	if ($operatorA1 == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keyword1', $keywordb);	
	} else {
		$r->bindParam(':keyword1', $keyword);
		}
}

if (strpos($query, ':keywordB') !== false) {
    echo ($operatorB);
	if ($operatorB == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keywordB', $keywordb);
	} else {
		$r->bindParam(':keywordB', $keyword);
	}
}

if (strpos($query, ':keywordB1') !== false) {
    echo ($operatorB1);
	if ($operatorB1 == 'like') {
		$keywordb = $keyword.'%';
		$r->bindParam(':keywordB1', $keywordb);
	} else {
		$r->bindParam(':keywordB1', $keyword);
	}
}

$r->execute();
$numrows = $r->fetch();
$numrows = $numrows[0];

multichild · January 16, 2018, 9:17am

I think I have found the issue, or at least the next issue, its $keyword.

Its staying the same value instead of being what it should be.

For instance the keyword I have typed into the first choice is DOM, and then in the second choice is Active, so then you should end up with

WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like DOM AND Contract_Status = Active) select COUNT(*) from LIMIT WHERE Contract_Number like DOM AND Contract_Status = Active

But what I’m getting is -

WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like Active AND Contract_Status = Active) select COUNT(*) from LIMIT WHERE Contract_Number like Active AND Contract_Status = Active

Keyword is always Active in this case, so then the select statement isn’t finding what it needs to be.

I changed the echo to look for $keyword in each strpos as below

if (strpos($query, ':keyword') !== false) {
	echo ($keyword);
...

if (strpos($query, ':keyword1') !== false) {
    echo ($keyword);
...

if (strpos($query, ':keywordB') !== false) {
    echo ($keyword);
...

if (strpos($query, ':keywordB1') !== false) {
    echo ($keyword);
...


...

multichild · January 16, 2018, 9:30am

I think I have it

used the same process as the others, silly really should have seen it

if ($i === 0) {
    $keywordA = $keyword;
	$limitClause .= " " . $field_name . " " . (${'operatorA'} = $operator) . " :keyword";
	$keywordB = $keyword;
	$limitClauseB .= " " . $field_name . " " . (${'operatorB'} = $operator) . " :keywordB";
} else {
    (${'keywordA' . $i} = $keyword);
	$limitClause .= " AND " . $field_name . " " . (${'operatorA' . $i} = $operator) . " :keyword".$i;
	(${'keywordB' . $i} = $keyword);
	$limitClauseB .= " AND " . $field_name . " " . (${'operatorB' . $i} = $operator) . " :keywordB".$i;
}

Which then creates the unique $keyword that can be used in each strpos, just tested it and the count showed the correct number of entries.

droopsnoot · January 16, 2018, 10:15am

Of course! When you use bindParam() to point to a variable, it uses the variable value at the time of the execute(), not at the time of the bindParam(), so by using $keyword and $keywordB all the time, you just end up with the same values repeated through the query. I never spotted that.

You could use bindValue() instead of the way you’re doing it, which would grab the value of $keyword at the point that you bind it, but I’ve never used it myself so I’m not 100% sure.

multichild · January 16, 2018, 10:34am

Cheers droopsnoot,

There other session issues to deal with now, but its because of the changes above so I think I’m on the final straight.

Thanks again for sticking with it with me.

droopsnoot · January 16, 2018, 10:35am

No problem, just kicking myself for not spotting it earlier.

system · April 17, 2018, 5:35pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Multiple Queries Using Prepared SQL Statements PHP	9	15288	October 8, 2014
Why WHILE Loop Fails After Paginated Page 1? PHP	30	3518	October 8, 2020
fetch(PDO::FETCH_OBJ not working PHP	4	2682	May 10, 2018
[Help Req]: MySQL Fetch Problem PHP	2	641	December 5, 2010
mysql_num_rows() problem PHP	26	5049	August 29, 2010

Fetch() working but not when AND added

Related topics