MySQL ODBC Connector - Select ALL rows with WHERE statement

Author

Select ALL rows with WHERE statement

Alvaro Cobo

2005-09-17, 8:23 pm

------ =_NextPart_000_0017_
01C5BB9E.B016AC70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi all:=20

I've searched into the MySQL manual but I can't find the solution to =
this very silly question.=20

I have a select query where I need to filter the data using the WHERE =
statement. But also need to have an option to show all the data in the =
table.=20

For example (something like...):=20

SELECT * FROM `tbl_table1` WHERE field1 =3D 'Test' #To show all the =
rows where field 1=3D 'Test'

But also have the choice to select all the rows using something like:=20

SELECT * FROM `tbl_table1` WHERE field1 =3D * or ALL or % #Select all =
the fields.

Very thankful in advance.=20

Alvaro

PD: System Details: Mysql server version 4.1.11, PHP 4.3.10-2, Debian =
Sarge.
------ =_NextPart_000_0017_
01C5BB9E.B016AC70--

Pooly

2005-09-17, 8:23 pm

2005/9/17, Alvaro Cobo <coboalvaro@gmail.com>:
> Hi all:
>=20
> I've searched into the MySQL manual but I can't find the solution to this=
very silly question.
>=20
> I have a select query where I need to filter the data using the WHERE sta=
tement. But also need to have an option to show all the data in the table.
>=20
> For example (something like...):
>=20
> SELECT * FROM `tbl_table1` WHERE field1 =3D 'Test' #To show all the rows=
where field 1=3D 'Test'
>=20
> But also have the choice to select all the rows using something like:
>=20
> SELECT * FROM `tbl_table1` WHERE field1 =3D * or ALL or % #Select all t=
he fields.
>=20

You need to select all rows, but you have only control on the
condition on flied1, I am right ?
If you can input %, it should do the trick.
select * from table WHERE field1 like '%';
Why can't you do just=20
select * from table ??

--=20
Pooly
Webzine Rock : http://www.w-fenec.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Peter Brawley

2005-09-17, 8:23 pm

--=======AVGMAIL-432C91CD5A27=======
Content-Type: multipart/alternative; boundary=------------ 08000708080908060401
0608

-------------- 08000708080908060401
0608
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Alvaro,

/>I have a select query where I need to filter the data using the WHERE

>statement. But also need to have an option to show all the data in the
>table.
/
You cannot send the query engine an "option". When the program
determines that all rows are to be retrieved, you could set the WHERE
clause to "WHERE 1=1", but why not simply have the program append no
WHERE clause at all?

PB

-----

Alvaro Cobo wrote:

>Hi all:
>
>I've searched into the MySQL manual but I can't find the solution to this very silly question.
>
>I have a select query where I need to filter the data using the WHERE statement. But also need to have an option to show all the data in the table.
>
>For example (something like...):
>
>SELECT * FROM `tbl_table1` WHERE field1 = 'Test' #To show all the rows where field 1= 'Test'
>
>But also have the choice to select all the rows using something like:
>
>SELECT * FROM `tbl_table1` WHERE field1 = * or ALL or % #Select all the fields.
>
>Very thankful in advance.
>
>Alvaro
>
>PD: System Details: Mysql server version 4.1.11, PHP 4.3.10-2, Debian Sarge.
>
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.344 / Virus Database: 267.11.1/104 - Release Date: 9/16/2005
>
>

-------------- 08000708080908060401
0608
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Alvaro, 
 
>I have a select query where I need to filter the data using the
WHERE 
>statement. But also need to have an option to show all the data in
the 
>table. 
 
You cannot send the query engine an "option". When the program
determines that all rows are to be retrieved, you could set the WHERE
clause to "WHERE 1=1", but why not simply have the program append no
WHERE clause at all? 
 
PB 
 
----- 
 
Alvaro Cobo wrote:
<blockquote cite=" mid001a01c5bbc8$9bb3
4e80$0100a8c0@Alvaro
" type="cite">
<pre wrap="">Hi all:

I've searched into the MySQL manual but I can't find the solution to this very silly question.

I have a select query where I need to filter the data using the WHERE statement. But also need to have an option to show all the data in the table.

For example (something like...):

SELECT * FROM `tbl_table1` WHERE field1 = 'Test' #To show all the rows where field 1= 'Test'

But also have the choice to select all the rows using something like:

SELECT * FROM `tbl_table1` WHERE field1 = * or ALL or % #Select all the fields.

Very thankful in advance.

Alvaro

PD: System Details: Mysql server version 4.1.11, PHP 4.3.10-2, Debian Sarge.
</pre>
<pre wrap="">
<hr size="4" width="90%">
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.1/104 - Release Date: 9/16/2005
</pre>
</blockquote>
</body>
</html>

-------------- 08000708080908060401
0608--
--=======AVGMAIL-432C91CD5A27=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.1/104 - Release Date: 9/16/2005

--=======AVGMAIL-432C91CD5A27=======
Content-Type: text/plain; charset=us-ascii

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
--=======AVGMAIL-432C91CD5A27=======--

Alvaro Cobo

2005-09-18, 3:23 am

Thanks Pooly and Peter:

The problem is that in the same php page I would like to choose either the
calculation from an especific year or the calculation from the whole table
using URL.

An example:

I have a table which shows how much money somebody has spent each year and
there is the choice to know how much money they have spent from the begining
of the data input (fields: id, person, expenses, year).

I have built a dynamic page which shows a link with the years in the
database, and an aditional link which selects "all" records. So when people
chooses one of the year links, it sends an URL variable to the browser,
which is collected by the sql script to the WHERE statement and then it
filters the data and sum all the data from this specific year. But If they
choose the "all" link, the filter just disapears, and the sql query makes a
sum of the whole data.

I guess, once the years are fixed data, Poolys approach could be very
useful, because, I can configure the "all" link to send an URL variable with
the '%' wildcard, and the year links could still send their data (2005, for
example) which defines univocally each one of the records in the year field
in the budget table.

At the moment I was building two separate pages, so when people want the
infiltered data, the hyperlink directs then to another page, but it is much
better to have all the information in only one page, to make it easy the
mainteinance.

Thanks a lot guys. It was a great help from you.

Very best regards,

Alvaro.

PD: Sorry, but my English is not very good this time.

----- Original Message -----
From: "Pooly" <pooly7@gmail.com>
To: <mysql@lists.mysql.com>
Sent: Saturday, September 17, 2005 3:53 PM
Subject: Re: Select ALL rows with WHERE statement

2005/9/17, Alvaro Cobo <coboalvaro@gmail.com>:
> Hi all:
>
> I've searched into the MySQL manual but I can't find the solution to this
very silly question.
>
> I have a select query where I need to filter the data using the WHERE
statement. But also need to have an option to show all the data in the
table.
>
> For example (something like...):
>
> SELECT * FROM `tbl_table1` WHERE field1 = 'Test' #To show all the rows
where field 1= 'Test'
>
> But also have the choice to select all the rows using something like:
>
> SELECT * FROM `tbl_table1` WHERE field1 = * or ALL or % #Select all the
fields.
>

You need to select all rows, but you have only control on the
condition on flied1, I am right ?
If you can input %, it should do the trick.
select * from table WHERE field1 like '%';
Why can't you do just
select * from table ??

--
Pooly
Webzine Rock : http://www.w-fenec.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...ma
il.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jasper Bryant-Greene

2005-09-18, 3:23 am

Alvaro Cobo wrote:
> Thanks Pooly and Peter:
>
> The problem is that in the same php page I would like to choose either the
> calculation from an especific year or the calculation from the whole table
> using URL.

The same page can easily handle both.

<?php
$sql = "SELECT * FROM mytable";

if(isset($_GET['year']) && !empty($_GET['year'])) {
$sql .= " WHERE year=" . intval($_GET['year']);
}

$result = mysql_query($sql);
?>

If year is passed as a GET variable then it will retrieve only records
with that year in the table. If it's not passed then it will retrieve
the whole table by omitting the WHERE clause.

--
Jasper Bryant-Greene
Freelance web developer
http://jasper.bryant-greene.name/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Pooly

2005-09-18, 7:24 am

2005/9/18, Jasper Bryant-Greene <jasper@bryant-greene.name>:
> Alvaro Cobo wrote:
the[color=darkred]
ble[color=darkred]
>=20
> The same page can easily handle both.
>=20
> <?php
> $sql =3D "SELECT * FROM mytable";
>=20
> if(isset($_GET['year']) && !empty($_GET['year'])) {
> $sql .=3D " WHERE year=3D" . intval($_GET['year']);
> }
>=20
> $result =3D mysql_query($sql);
> ?>

Well, if you have control on everything, this approach would be the
best, no trick, easy to understand and maintain.

--=20
Pooly
Webzine Rock : http://www.w-fenec.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Peter Brawley

2005-09-18, 8:23 pm

--=======AVGMAIL-432DC3990736=======
Content-Type: multipart/alternative; boundary=------------ 03070901010400020500
0502

-------------- 03070901010400020500
0502
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Alvaro,

/>The problem is that in the same php page I would like to choose either the
>calculation from an especific year or the calculation from the whole table
>using URL./

That needn't raise a problem, especially in PHP. If the user chooses a
year, have the page construct the appropriate WHERE clause. If she
chooses "all", have the page remove any WHERE clause from the SQL
statement.

PB

-----

Alvaro Cobo wrote:

>Thanks Pooly and Peter:
>
>The problem is that in the same php page I would like to choose either the
>calculation from an especific year or the calculation from the whole table
>using URL.
>
>An example:
>
>I have a table which shows how much money somebody has spent each year and
>there is the choice to know how much money they have spent from the begining
>of the data input (fields: id, person, expenses, year).
>
>I have built a dynamic page which shows a link with the years in the
>database, and an aditional link which selects "all" records. So when people
>chooses one of the year links, it sends an URL variable to the browser,
>which is collected by the sql script to the WHERE statement and then it
>filters the data and sum all the data from this specific year. But If they
>choose the "all" link, the filter just disapears, and the sql query makes a
>sum of the whole data.
>
>I guess, once the years are fixed data, Poolys approach could be very
>useful, because, I can configure the "all" link to send an URL variable with
>the '%' wildcard, and the year links could still send their data (2005, for
>example) which defines univocally each one of the records in the year field
>in the budget table.
>
>At the moment I was building two separate pages, so when people want the
>infiltered data, the hyperlink directs then to another page, but it is much
>better to have all the information in only one page, to make it easy the
>mainteinance.
>
>Thanks a lot guys. It was a great help from you.
>
>Very best regards,
>
>Alvaro.
>
>PD: Sorry, but my English is not very good this time.
>
>----- Original Message -----
>From: "Pooly" <pooly7@gmail.com>
>To: <mysql@lists.mysql.com>
>Sent: Saturday, September 17, 2005 3:53 PM
>Subject: Re: Select ALL rows with WHERE statement
>
>
>2005/9/17, Alvaro Cobo <coboalvaro@gmail.com>:
>
>
>very silly question.
>
>
>statement. But also need to have an option to show all the data in the
>table.
>
>
>where field 1= 'Test'
>
>
>fields.
>
>
>
>You need to select all rows, but you have only control on the
>condition on flied1, I am right ?
>If you can input %, it should do the trick.
>select * from table WHERE field1 like '%';
>Why can't you do just
>select * from table ??
>
>
>--
>Pooly
>Webzine Rock : http://www.w-fenec.org/
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql? unsub...ma
il.com
>
>
>
>

-------------- 03070901010400020500
0502
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Alvaro, 
 
>The problem is that in the same php page I would like to choose
either the 
>calculation from an especific year or the calculation from the
whole table 
>using URL. 
 
That needn't raise a problem, especially in PHP. If the user chooses a
year, have the page construct the appropriate WHERE clause. If she
chooses "all", have the page remove any WHERE clause from the SQL
statement. 
 
PB 
 
----- 
 
Alvaro Cobo wrote:
<blockquote cite=" mid005701c5bc05$c64b
ec50$0100a8c0@Alvaro
" type="cite">
<pre wrap="">Thanks Pooly and Peter:

The problem is that in the same php page I would like to choose either the
calculation from an especific year or the calculation from the whole table
using URL.

An example:

I have a table which shows how much money somebody has spent each year and
there is the choice to know how much money they have spent from the begining
of the data input (fields: id, person, expenses, year).

I have built a dynamic page which shows a link with the years in the
database, and an aditional link which selects "all" records. So when people
chooses one of the year links, it sends an URL variable to the browser,
which is collected by the sql script to the WHERE statement and then it
filters the data and sum all the data from this specific year. But If they
choose the "all" link, the filter just disapears, and the sql query makes a
sum of the whole data.

I guess, once the years are fixed data, Poolys approach could be very
useful, because, I can configure the "all" link to send an URL variable with
the '%' wildcard, and the year links could still send their data (2005, for
example) which defines univocally each one of the records in the year field
in the budget table.

At the moment I was building two separate pages, so when people want the
infiltered data, the hyperlink directs then to another page, but it is much
better to have all the information in only one page, to make it easy the
mainteinance.

Thanks a lot guys. It was a great help from you.

Very best regards,

Alvaro.

PD: Sorry, but my English is not very good this time.

----- Original Message -----
From: "Pooly" <a class="moz-txt-link-rfc2396E" href="mailto:pooly7@gmail.com"><pooly7@gmail.com></a>
To: <a class="moz-txt-link-rfc2396E" href="mailto:mysql@lists.mysql.com"><mysql@lists.mysql.com></a>
Sent: Saturday, September 17, 2005 3:53 PM
Subject: Re: Select ALL rows with WHERE statement

2005/9/17, Alvaro Cobo <a class="moz-txt-link-rfc2396E" href=" mailto:coboalvaro@gm
ail.com"><coboalvaro@gmail.com></a>:
</pre>
<blockquote type="cite">
<pre wrap="">Hi all:

I've searched into the MySQL manual but I can't find the solution to this
</pre>
</blockquote>
<pre wrap="">very silly question.
</pre>
<blockquote type="cite">
<pre wrap="">I have a select query where I need to filter the data using the WHERE
</pre>
</blockquote>
<pre wrap="">statement. But also need to have an option to show all the data in the
table.
</pre>
<blockquote type="cite">
<pre wrap="">For example (something like...):

SELECT * FROM `tbl_table1` WHERE field1 = 'Test' #To show all the rows
</pre>
</blockquote>
<pre wrap="">where field 1= 'Test'
</pre>
<blockquote type="cite">
<pre wrap="">But also have the choice to select all the rows using something like:

SELECT * FROM `tbl_table1` WHERE field1 = * or ALL or % #Select all the
</pre>
</blockquote>
<pre wrap="">fields.
</pre>
<pre wrap="">
You need to select all rows, but you have only control on the
condition on flied1, I am right ?
If you can input %, it should do the trick.
select * from table WHERE field1 like '%';
Why can't you do just
select * from table ??

--
Pooly
Webzine Rock : <a class="moz-txt-link-freetext" href="http://www.w-fenec.org/">http://www.w-fenec.org/</a>

--
MySQL General Mailing List
For list archives: <a class="moz-txt-link-freetext" href="http://lists.mysql.com/mysql">http://lists.mysql.com/mysql</a>
To unsubscribe: <a class="moz-txt-link-freetext" href="http://lists.mysql.com/mysql? unsub=coboalvaro@gma
il.com">http://lists.mysql.com/mysql? unsub...ma
il.com</a>

</pre>
</blockquote>
</body>
</html>

-------------- 03070901010400020500
0502--
--=======AVGMAIL-432DC3990736=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.1/104 - Release Date: 9/16/2005

--=======AVGMAIL-432DC3990736=======
Content-Type: text/plain; charset=us-ascii

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
--=======AVGMAIL-432DC3990736=======--

Sponsored Links