filters in the WHERE clause

#21

On Jun 30, 6:56*am, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m>
wrote:

Quote:

Captain Paralytic:

On 30 June, 14:18, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m
wrote:

[...snip valid points against replying...]

Points taken, CP. I'll keep them in mind. But...

Another point you might notice is that her question is
absolutely blatantly obviously a php question, but her
understanding is so low that she asks it in a MySQL newsgroup.

True. But traffic in this group isn't that high, and her question is
not that far off a MySql-topic.
We also have questions about network issues and general data modeling
issues in this group, which strictly aren't on-topic either. Yet, we
tend to discuss them as long as we don't end up talking about Scrabble,
Espresso, Politics or Motorbikes.

But has that point been reached in this thread, yet?
Ahh you think that magically, after 10 years, in this thread she'll
suddenly grasp what it is all about! I admire your optimism, but I
can't see it happening.

:-)
One chance a thread, CP.
Unless such poster turns out to be a troll.

--
Erick

Okay, the confusing error message yesterday was because I made the
correct change on the server with my IDE but they weren't saved on the
server even though
it was saved in my IDE running on the server because they were doing
maintenance I didn't know about..
So I got the error message that didn't make sense.

The nrows function is to get a count. The input of the function is
the $sql.
That result gets assigned to $nRecs so I can use it to display
records in a page.

I got the error message for the double where clause so I took out all
the sqlWhere and concatenated everything to the $sql.
Which works but it is harder to see what the sql is doing. I couldn't
figure out how to get it concatenated together that way.
so I added the first $sqlWhere to the $sql and used the .$sqlWhere
for the filters and it removed the extra Where clause.
I also noticed there was a space after the FROM in the nrows function
strpos line and took it out. and works.
$sql="SELECT COUNT(*) AS cnt " . substr($sql,
strpos($sql,"FROM"));*****

But in order to learn from this I would like to understand the nrows
function.
I wrote a test page using the function.
<?php
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*
*/
$dbname= "pharm_cart_order";
$link = mysql_connect('10.237.82.232', 'root', '');
mysql_select_db($dbname);
if ( !mysql_select_db($dbname,$link) ) {
echo 'Could not select database ';
exit;
}
$sql="SELECT DISTINCT bu, cart_id, nationalDrugCode, error_flag,
item_id, description, upc, ndc_uom, std_uom FROM cart_inventory,
onelink WHERE nationalDrugCode=ndc AND bu ='08010' ORDER BY bu LIMIT
100,100";
$result=mysql_query($sql);
if ( !$result ) {die("Invalid query:" .
mysql_error() . " $sql");}

echo nrows($sql);
function nrows($sql) {
$sql="SELECT COUNT(*) AS cnt " . substr($sql,
strpos($sql,"FROM"));
$result=mysql_query($sql);
if ( !$result ) {die("Invalid query:" .
mysql_error() . " $sql");}
$dbarray = mysql_fetch_array($result);
$nCnt=$dbarray['cnt'];
$cnt=($nCnt==1) ? "1 Record" : "$nCnt";
return $cnt;
}//end nrows
?>

I would like to verify it with a query in the query browser
So I see that the substring with strpos replaces part of the select
with the COUNT(*). But does that give the count of the DISTINCT rows?
I need the count to be verified.
Thanks for your patience.

#22

JRough wrote:

Quote:

On Jun 30, 6:56 am, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m
wrote:
Captain Paralytic:

On 30 June, 14:18, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m
wrote:
[...snip valid points against replying...]

Points taken, CP. I'll keep them in mind. But...

Another point you might notice is that her question is
absolutely blatantly obviously a php question, but her
understanding is so low that she asks it in a MySQL newsgroup.
True. But traffic in this group isn't that high, and her question is
not that far off a MySql-topic.
We also have questions about network issues and general data modeling
issues in this group, which strictly aren't on-topic either. Yet, we
tend to discuss them as long as we don't end up talking about Scrabble,
Espresso, Politics or Motorbikes.

But has that point been reached in this thread, yet?
Ahh you think that magically, after 10 years, in this thread she'll
suddenly grasp what it is all about! I admire your optimism, but I
can't see it happening.
:-)
One chance a thread, CP.
Unless such poster turns out to be a troll.

--
Erick

Okay, the confusing error message yesterday was because I made the
correct change on the server with my IDE but they weren't saved on the
server even though
it was saved in my IDE running on the server because they were doing
maintenance I didn't know about..
So I got the error message that didn't make sense.

The nrows function is to get a count. The input of the function is
the $sql.
That result gets assigned to $nRecs so I can use it to display
records in a page.

I got the error message for the double where clause so I took out all
the sqlWhere and concatenated everything to the $sql.
Which works but it is harder to see what the sql is doing. I couldn't
figure out how to get it concatenated together that way.
so I added the first $sqlWhere to the $sql and used the .$sqlWhere
for the filters and it removed the extra Where clause.
I also noticed there was a space after the FROM in the nrows function
strpos line and took it out. and works.
$sql="SELECT COUNT(*) AS cnt " . substr($sql,
strpos($sql,"FROM"));*****

But in order to learn from this I would like to understand the nrows
function.
I wrote a test page using the function.
?php
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*
*/
$dbname= "pharm_cart_order";
$link = mysql_connect('10.237.82.232', 'root', '');
mysql_select_db($dbname);
if ( !mysql_select_db($dbname,$link) ) {
echo 'Could not select database ';
exit;
}
$sql="SELECT DISTINCT bu, cart_id, nationalDrugCode, error_flag,
item_id, description, upc, ndc_uom, std_uom FROM cart_inventory,
onelink WHERE nationalDrugCode=ndc AND bu ='08010' ORDER BY bu LIMIT
100,100";
$result=mysql_query($sql);
if ( !$result ) {die("Invalid query:" .
mysql_error() . " $sql");}

echo nrows($sql);
function nrows($sql) {
$sql="SELECT COUNT(*) AS cnt " . substr($sql,
strpos($sql,"FROM"));
$result=mysql_query($sql);
if ( !$result ) {die("Invalid query:" .
mysql_error() . " $sql");}
$dbarray = mysql_fetch_array($result);
$nCnt=$dbarray['cnt'];
$cnt=($nCnt==1) ? "1 Record" : "$nCnt";
return $cnt;
}//end nrows
?

I would like to verify it with a query in the query browser
So I see that the substring with strpos replaces part of the select
with the COUNT(*). But does that give the count of the DISTINCT rows?
I need the count to be verified.
Thanks for your patience.

No, it does not give the count of distinct rows. Additionally, even if
it did, the result could be wrong because you are making two separate
queries and you don't know what another user might have done in the
meantime.

What's wrong with mysql_num_rows?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#23

JRough:

Quote:

But in order to learn from this I would like to understand the nrows
function.
I wrote a test page using the function.
?php
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*
*/
$dbname= "pharm_cart_order";
$link = mysql_connect('10.237.82.232', 'root', '');
mysql_select_db($dbname);
if ( !mysql_select_db($dbname,$link) ) {
echo 'Could not select database ';
exit;
}

In the above, do you see that you select the database twice?

Quote:

$sql="SELECT DISTINCT bu, cart_id, nationalDrugCode, error_flag,
item_id, description, upc, ndc_uom, std_uom
FROM cart_inventory,onelink
WHERE nationalDrugCode=ndc AND bu ='08010'
ORDER BY bu
LIMIT 100,100";

[...]

Quote:

echo nrows($sql);

Quote:

function nrows($sql) {

The function's only parameter is a string, called $sql, containing a
SELECT query.

Quote:

$sql="SELECT COUNT(*) AS cnt " . substr($sql,
strpos($sql,"FROM"));

This is very confusing, because you are using a local variable $sql
with the same name as the function's parameter $sql.
Let's rewrite it as I think you want to use it:

$newSql="SELECT COUNT(*) AS cnt " . substr($sql,strpos($sql,"FROM"));

so

# $newSql will contain
# "SELECT COUNT(*) AS cnt
# FROM cart_inventory,onelink
# WHERE nationalDrugCode=ndc AND bu ='08010'
# ORDER BY bu
# LIMIT 100,100";

I wouldn't know why you are using ORDER BY here, because it doesn't
affect the result. But anyway:

Quote:

$result=mysql_query($sql);

Would now be:
$result=mysql_query($newSql);

Quote:

$dbarray = mysql_fetch_array($result);
$nCnt=$dbarray['cnt'];
$cnt=($nCnt==1) ? "1 Record" : "$nCnt";
return $cnt;
}//end nrows
?

This should indeed return either "1 Record" or a string containing a
number.

Quote:

I would like to verify it with a query in the query browser
So I see that the substring with strpos replaces part of the select
with the COUNT(*). But does that give the count of the DISTINCT rows?

No. If you want to verify that part of the original query is replaced
by something else, you should simply print the initial string and the
result string. You don't have to really send the query to mysql to see
it.

You could include into your function, after

$newSql="SELECT COUNT(*) AS cnt " . substr($sql,strpos($sql,"FROM"));

the following line:

echo "My original query: ".$sql." ";
echo "and now it is: ".$newSql;
exit;

Quote:

I need the count to be verified.

Verified with what? Do you have a reliable number, that you want to
match the function's return value?

You could take $newSql, feed Query Browser or phpMyAdmin with it, and
get the result.
Then echo the function's output, and see if the result is the same. I
bet it is.

----

Now, you wrote:

Quote:

The nrows function is to get a count. The input of the function is
the $sql.
That result gets assigned to $nRecs so I can use it to display
records in a page.

So, you want the number of returned tuples from the query, which
contains a LIMIT clause, right?
Have you considered using
http://php.net/manual/en/function.mysql-num-rows.php

If you want the total number of records, as if there was no LIMIT
clause, then see
http://dev.mysql.com/doc/refman/5.0/...ion_found-rows

In fact, I don't think you need to write functions that do the
counting, when MySQL will return that number upon request.

--
Erick

#24

On Jun 30, 11:31*am, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m>
wrote:

Quote:

JRough:

But in order to learn from this I would like to understand the nrows
function.
I wrote a test page using the function.
?php
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*
*/
$dbname= "pharm_cart_order";
$link = mysql_connect('10.237.82.232', 'root', '');
mysql_select_db($dbname);
if ( !mysql_select_db($dbname,$link) ) {
* echo 'Could not select database ';
* exit;
}

In the above, do you see that you select the database twice?

$sql="SELECT DISTINCT bu, cart_id, nationalDrugCode, error_flag,

item_id, description, upc, ndc_uom, std_uom

FROM cart_inventory,onelink
WHERE nationalDrugCode=ndc AND bu ='08010' *
ORDER BY bu
LIMIT 100,100";

[...]

echo nrows($sql);
function nrows($sql) {

The function's only parameter is a string, called $sql, containing a
SELECT query.

* * * *$sql="SELECT COUNT(*) AS cnt " . substr($sql,
strpos($sql,"FROM"));

This is very confusing, because you are using a local variable $sql
with the same name as the function's parameter $sql.
Let's rewrite it as I think you want to use it:

* $newSql="SELECT COUNT(*) AS cnt " . substr($sql,strpos($sql,"FROM"));

so

# $newSql will contain
# * *"SELECT COUNT(*) AS cnt *
# * * * *FROM cart_inventory,onelink
# * * * *WHERE nationalDrugCode=ndc AND bu ='08010' *
# * * * *ORDER BY bu
# * * * *LIMIT 100,100";

I wouldn't know why you are using ORDER BY here, because it doesn't
affect the result. But anyway:

* * * *$result=mysql_query($sql);

Would now be:
* * * * *$result=mysql_query($newSql);

* * * $dbarray = mysql_fetch_array($result);
* * * *$nCnt=$dbarray['cnt'];
* * * *$cnt=($nCnt==1) ? "1 Record" : "$nCnt";
* * * *return $cnt;
}//end nrows
?

This should indeed return either "1 Record" or a string containing a
number.

I would like to verify it with a query in the query browser
So I see that the substring with strpos replaces part of the select
with the COUNT(*). *But does that give the count of the DISTINCT rows?

No. If you want to verify that part of the original query is replaced
by something else, you should simply print the initial string and the
result string. You don't have to really send the query to mysql to see
it.

You could include into your function, after

* $newSql="SELECT COUNT(*) AS cnt " . substr($sql,strpos($sql,"FROM"));

the following line:

echo "My original query: ".$sql." ";
echo "and now it is: ".$newSql;
exit;

I need the count to be verified.

Verified with what? Do you have a reliable number, that you want to
match the function's return value?

You could take $newSql, feed Query Browser or phpMyAdmin with it, and
get the result.
Then echo the function's output, and see if the result is the same. I
bet it is.

----

Now, you wrote:
The nrows function is to get a count. *The input of the function is
the $sql.
That result gets assigned to $nRecs so I can use it to display
records in a page.

So, you want the number of returned tuples from the query, which
contains a LIMIT clause, right?
Have you considered usinghttp://php.net/manual/en/function.mysql-num-rows..php

If you want the total number of records, as if there was no LIMIT
clause, then seehttp://dev.mysql.com/doc/refman/5.0/en/information-functions.html#fun...

In fact, I don't think you need to write functions that do the
counting, when MySQL will return that number upon request.

--
Erick'
Erick

Thanks. mysql_num_rows would be useful and also the select found
count after the limit will be useful. There is a form for posting
selections in this search box.
The user is editing large numbers of records. The order by is part of
a $_GET
selected after the results are displayed the user can click on the
rows to sort them.
There is a search box that gets posted while the user searches for
filtered columns and does editing.
The sql is pulled after the page number is calculated. There are a
large number of results and I'm only showing 100 at a time
so the number of records has to be calculated first to find the right
page the user is on then the sql is pulled so I don't see how I can
use those two functions here.
As I see it the nrows function is necessary even though it could be
better. I suppose I shouldn't use the same name for the local
variable in the function.
I did see that the concatenation was wrong. So I fixed it. I'm still
having problems though because I have to hard wire the value for
$nRecs to get the page before the LIMIT.
The limit is the page numbers shown.
I don't know how to hard wiire it on the line with the asterisks. If
I can do that someway then I'm done with this.
Thanks for going through nrows function. So it returns either 1
record for a 1 row query or a number if there are more than that.
I will avoid this function in the future. Thanks for being helpful.
echo "---$error_flag---";
$sql="SELECT DISTINCT bu, cart_id, nationalDrugCode, error_flag,
item_id, description, upc, ndc_uom, std_uom ";
$sql.="FROM cart_inventory, onelink ";
$sqlWhere="WHERE nationalDrugCode=ndc ";

if (isset($bu)){ $sqlFilter="AND bu ='$bu' ";}
if (isset($cart_id)) { $sqlFilter.="AND cart_id='$cart_id' ";}
if (($zoneid !='') && ($zonenm!='')) { $sqlFilter.="AND zoneid
='$zoneid'AND zonenm='$zonenm' ";}
if ( $error_flag !=''){$sqlFilter.="AND error_flag='$error_flag'
";}
$sqlOrder="ORDER BY $order ";

if( $page=='' ){
$nRecs=nrows("$sql ."" $sqlWhere ."". $sqlFilter"); ******
echo $nRecs ."number of Recs";
$_SESSION['recs']=$nRecs;
$page =1;

// echo "" . "nRecs:".$nRecs . "\n";

$startPage=100;
}else{
$startPage=$page *100;
}

$sqlLimit="LIMIT $startPage,100 ";
$sql= $sql ." ".$sqlWhere ." ". $sqlFilter." ".$sqlOrder . " " .
$sqlLimit;
echo ($sql);

#25

Quote:

Which works but it is harder to see what the sql is doing. I couldn't
figure out how to get it concatenated together that way.
so I added the first $sqlWhere to the $sql and used the .$sqlWhere
for the filters and it removed the extra Where clause.

I also noticed there was a space after the FROM in the nrows function
strpos line and took it out. and works.
$sql="SELECT COUNT(*) AS cnt " . substr($sql,
strpos($sql,"FROM"));*****

Note that this is going to malfunction for certain queries which
contain the string "FROM" before the actual FROM keyword, such as:

SELECT FROMAGE, EMAIL_ENVELOPE_FROM_ADDRESS, AFROMAN
FROM CHEESES WHERE ... ;

#26

Gordon Burditt:

Quote:

$sql="SELECT COUNT(*) AS cnt " . substr($sql,
strpos($sql,"FROM"));*****

Note that this is going to malfunction for certain queries which
contain the string "FROM" before the actual FROM keyword, such as:

SELECT FROMAGE, EMAIL_ENVELOPE_FROM_ADDRESS, AFROMAN
FROM CHEESES WHERE ... ;

That's nitpicking: nobody will ever assign attribute 'afroman' to
entity 'cheeses'.

;-p

--
Erick

#27

In article <a88dd019-9719-4ee4-a8cb-52a893149b18 (AT) k39g2000yqd (DOT) googlegroups.com>, JRough <jlrough (AT) yahoo (DOT) com> wrote:

Quote:

But in order to learn from this I would like to understand the nrows
function.

This is the most puzzling part of the entire thread. Why do you need help "to
understand the nrows function"? It's *your* function, isn't it??

#28

On 1 July, 12:12, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:

Quote:

In article <a88dd019-9719-4ee4-a8cb-52a893149... (AT) k39g2000yqd (DOT) googlegroups.com>, JRough <jlro... (AT) yahoo (DOT) com> wrote:

But in order to learn from this I would like to understand the nrows
function.

This is the most puzzling part of the entire thread. Why do you need help "to
understand the nrows function"? It's *your* function, isn't it??

I can't see why you should think this puzzling, the OP is JRough.
'Nuff said.

#29

On Jul 1, 12:39*am, gordonb.jh... (AT) burditt (DOT) org (Gordon Burditt) wrote:

Quote:

Which works but it is harder to see what the sql is doing. *I couldn't
figure out how to get it concatenated together that way.
so I *added the first $sqlWhere to the $sql and used the .$sqlWhere
for the filters and it removed the extra Where clause.
I also noticed there was a space after the FROM *in the nrows function
strpos line and took it out. and *works.
$sql="SELECT COUNT(*) AS cnt " . substr($sql,
strpos($sql,"FROM"));*****

Note that this is going to malfunction for certain queries which
contain the string "FROM" before the actual FROM keyword, such as:

SELECT FROMAGE, EMAIL_ENVELOPE_FROM_ADDRESS, AFROMAN
FROM CHEESES WHERE ... *;

thanks for all the helpful input. I got it working. I did try it
with the SQL_CALC_FOUND_ROWS and found_Rows() function and it did
work except the limit because the limit has to be calculated first so
unfortunately I have to pull the query twice but it was a good
exercise. I think on this conversion the word FROM in the queries
won't be a problem but I won't use that function anymore. I apologize
for not getting the question more specific earlier.

#30

JRough:

Quote:

I rewrote it with SQL_CALC_FOUND_ROWS and SELECT_FOUND_ROWS()
function.

Good!

Quote:

Where do I pull the sql query with the limit?

This must be a language thing (english is not my native tongue). What
does this sentence mean? How do you 'pull' a query? And how do you pull
a query 'at a certain location'?

Quote:

I did it here with the limit in the first $result.

Your piece of code looks (more or less) OK. You simply send the query
with the LIMIT clause to mysql, and you get your results as desired.

Quote:

Then as I understand it the 2nd result is without the limit using
FOUND_ROWS()?

Exactly. It shows the total number of records that would be returned
(if you had no LIMIT clause).

Quote:

The other question is the $sql query gets a syntax error because it
has a minus sign displaying at the end of the query where the LIMIT
should be
ie, LIMIT -100,100 instead of LIMIT 100,100.
How to get rid of the minus sign?

It looks like you are missing a couple of possibilities in your logic:

Quote:

$page_limit=100;
$page=$_GET['page'];
if($_POST['search_bu'] && $page=='' ){$page=1;}

What if $page=="helloWorld"?
What if $page=="" and $_POST['search_bu']===false?

Quote:

$limit_start= ($page * $page_limit) -$page_limit;

You simply can't be sure that $page is an integer, as you intend. If
you don't get the desired result, echo the values of the two fields
above, and see what's going on.

--
Erick