Re: [PHP-DB] Re: Database Problems
On Mon, Jun 25, 2012 at 9:46 PM, tamouse mailing lists wrote: > On Mon, Jun 25, 2012 at 7:57 AM, Peter Beckman wrote: >> I'll save you some time, it's not the "where 1" part of the query: >> >> mysql> select count(*) from numbers where 3 and num like '1212%'; > > The only time the where clause fails (rightly so) with a single number > like what is when you do "where 0". > > mysql> select count(*) from quotes where 0; > +--+ > | count(*) | > +--+ > | 0 | > +--+ > 1 row in set (0.00 sec) > > mysql> select count(*) from quotes where 1; > +--+ > | count(*) | > +--+ > | 727 | > +--+ > 1 row in set (0.00 sec) > > Note that the where clause failing does NOT mean the select failed. It > correctly returned 0 rows. Ooops, I mean *1* row with value zero. /o\ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
On Mon, Jun 25, 2012 at 7:57 AM, Peter Beckman wrote: > I'll save you some time, it's not the "where 1" part of the query: > > mysql> select count(*) from numbers where 3 and num like '1212%'; The only time the where clause fails (rightly so) with a single number like what is when you do "where 0". mysql> select count(*) from quotes where 0; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.00 sec) mysql> select count(*) from quotes where 1; +--+ | count(*) | +--+ | 727 | +--+ 1 row in set (0.00 sec) Note that the where clause failing does NOT mean the select failed. It correctly returned 0 rows. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
On Mon, 25 Jun 2012, B. Aerts wrote: On 17/06/12 21:06, Ethan Rosenberg wrote: Dear List - I have a database: +-+ | Tables_in_hospital2 | +-+ | Intake3 | | Visit3 | +-+ Hey Ethan -- Remember that your posts are archived likely forever, and using dummy data like "Bongish" and being critical of people's weight probably isn't going to help you when you decide to seek a job somewhere. Also, for people replying, before offering advice about MySQL on a PHP list, it is good practice to actually run your queries to verify your suggestions. Most of the replies thus far have been conjecture, with only one or two reasonable data-backed voices. Those voices asked: "What are you doing with $result?" How you answer is important. If you are simply printing it with hopes that $result contains data, the suggestion to "RTFM" is vital, as that would be wrong. Those voices also stated "'where 1' just evaluates to true." They are correct and proven here (42 intentional :-) ): mysql> select count(*) from numbers where 1=1 and num like '1212%'; +--+ | count(*) | +--+ | 42 | +--+ 1 row in set (0.26 sec) mysql> select count(*) from numbers where 1 and num like '1212%'; +--+ | count(*) | +--+ | 42 | +--+ 1 row in set (0.02 sec) mysql> select count(*) from numbers where num like '1212%'; +--+ | count(*) | +--+ | 42 | +--+ 1 row in set (0.02 sec) So Ethan, if your query works on the command line, but not in your code, where do you think the issue might exist? http://us3.php.net/manual/en/mysqli.query.php Return Values Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE. Granted, the PHP manual doesn't include *how* to loop through return data. http://us3.php.net/manual/en/class.mysqli-result.php Ethan, your question was: why does this work on the command line, and not through PHP. The remarks by other posters still stand - you don't show anything that leads us to discriminate wether the fault lies in the query, in the API's or in the code. If you're not going to show it, maybe try the following 2 tips : 1) try to modify the query: "SELECT . . . WHERE 1=1 ; " : this formulation removes any doubt wether it is a filtering statement, or a result limiting statement 2) see if " SELECT . . . WHERE 2" or " SELECT . . . WHERE 3 " yields respecively 2 or 3 result rows. If not, the problem is NOT with the API's. I'll save you some time, it's not the "where 1" part of the query: mysql> select count(*) from numbers where 3 and num like '1212%'; +--+ | count(*) | +--+ | 42 | +--+ Beckman --- Peter Beckman Internet Guy beck...@angryox.com http://www.angryox.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Database Problems
On 17/06/12 21:06, Ethan Rosenberg wrote:
Dear List -
I have a database:
+-+
| Tables_in_hospital2 |
+-+
| Intake3 |
| Visit3 |
+-+
mysql> describe Intake3;
++-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++-+--+-+-+---+
| Site | varchar(6) | NO | PRI | | |
| MedRec | int(6) | NO | PRI | NULL | |
| Fname | varchar(15) | YES | | NULL | |
| Lname | varchar(30) | YES | | NULL | |
| Phone | varchar(30) | YES | | NULL | |
| Height | int(4) | YES | | NULL | |
| Sex | char(7) | YES | | NULL | |
| Hx | text | YES | | NULL | |
++-+--+-+-+---+
mysql> describe Visit3;
++--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-++
| Indx | int(4) | NO | PRI | NULL | auto_increment |
| Site | varchar(6) | YES | | NULL | |
| MedRec | int(6) | YES | | NULL | |
| Notes | text | YES | | NULL | |
| Weight | int(4) | YES | | NULL | |
| BMI | decimal(3,1) | YES | | NULL | |
| Date | date | YES | | NULL | |
++--+--+-+-++
mysql> mysql> select * from Intake3 where 1 AND (Site = 'AA') AND (Sex =
'Male') ;
+--++-+---+--++--+---+
| Site | MedRec | Fname | Lname | Phone | Height | Sex | Hx |
+--++-+---+--++--+---+
| AA | 10003 | Stupid | Fool | 325 563-4178 | 65 | Male | Has been
convinced by his friends that he is obese. Normal
BMI = 23. |
| AA | 1 | David | Dummy | 845 365-1456 | 66 | Male | c/o obesity.
Various treatments w/o success |
| AA | 10001 | Tom | Smith | 984 234-4586 | 68 | Male | BMI = 20. Thinks
he is obese. |
| AA | 10007 | Foolish | Fool | 456 147-321 | 60 | Male | Thinks he is
thin. BMI = 45 |
| AA | 10005 | Tom | Obstinant | 845 368-2244 | 66 | Male | Insists that
he is not fat. Becomes violent. Psych involved. |
| AA | 10015 | Dim | Wit | 321 659-3111 | 70 | Male | Very Tall |
| AA | 10040 | Bongish | Bongish | 123 456-7890 | 50 | Male | Bong |
+--++-+---+--++--+-
The same query in a PHP program will only give me results for MedRec 10003
$allowed_fields = array
( 'Site' =>$_POST['Site'], 'MedRec' => $_POST['MedRec'], 'Fname' =>
$_POST['Fname'], 'Lname' => $_POST['Lname'] ,
'Phone' => $_POST['Phone'] , 'Sex' => $_POST['Sex'] , 'Height' =>
$_POST['Height'] );
if(empty($allowed_fields))
{
echo "ouch";
}
$query = "select * from Intake3 where 1 ";
foreach ( $allowed_fields as $key => $val )
{
if ( (($val != '')) )
{
$query .= " AND ($key = '$val') ";
}
$result1 = mysqli_query($cxn, $query);
}
Ethan
MySQL 5.1 PHP 5.3.3-6 Linux [Debian (sid)]
Hello all,
Ethan, your question was: why does this work on the command line, and
not through PHP.
The remarks by other posters still stand - you don't show anything that
leads us to discriminate wether the fault lies in the query, in the
API's or in the code.
If you're not going to show it, maybe try the following 2 tips :
1) try to modify the query: "SELECT . . . WHERE 1=1 ; " : this
formulation removes any doubt wether it is a filtering statement, or a
result limiting statement
2) see if " SELECT . . . WHERE 2" or " SELECT . . . WHERE 3 " yields
respecively 2 or 3 result rows. If not, the problem is NOT with the API's.
Regards,
Bert
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
