[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
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
Re: [PHP-DB] Re: Database Problems
On Mon, Jun 25, 2012 at 7:57 AM, Peter Beckman beck...@angryox.com 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, Jun 25, 2012 at 9:46 PM, tamouse mailing lists tamouse.li...@gmail.com wrote: On Mon, Jun 25, 2012 at 7:57 AM, Peter Beckman beck...@angryox.com 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
[PHP-DB] Re: Database Problems
Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... Dear List - The same query in a PHP program will only give me results for MedRec 10003 why the where 1 clause? Do you know what that is for? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
At 03:30 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... Dear List - The same query in a PHP program will only give me results for MedRec 10003 why the where 1 clause? Do you know what that is for? = Dear Jim Thanks As I understand, to enable me to concatenate phases to construct a query. The query does work in MySQL fro the terminal. Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... At 03:30 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... Dear List - The same query in a PHP program will only give me results for MedRec 10003 why the where 1 clause? Do you know what that is for? = Dear Jim Thanks As I understand, to enable me to concatenate phases to construct a query. The query does work in MySQL fro the terminal. Ethan I don't think so. All it does is return one record. The where clause defines what you want returned. A '1' returns one record, the first one. #10003 I wonder why you think where 1 enables concatenation?? A query (IN SIMPLE TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with a WHERE clause to define the criteria to limit the rows, and an ORDER BY to sort the result set. More complex queries can include GROUP BY when you are including summary operators such as SUM(fldname) or MAX(fldname), or a JOIN clause, or a host of other clauses that make sql so powerful. In your case I think you copied a sample query that (they always seem to be displayed with a 'where 1' clause) and left the 1 on it. To summarzie: SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key 100 and a.key = b.key ORDER BY a.fld1 Im no expert, but hopefully this makes it a little less complex for you. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
first rule of computer science: if everything else fails, read the manual!!!or short: RTFM! --- Regards DZvonko Nikolov dzvo...@gmail.com dzvo...@yahoo.com --- The best things are simple, but finding these simple things is not simple. Simplicity carried to the extreme becomes elegance. ---
Re: [PHP-DB] Re: Database Problems
At 04:21 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... At 03:30 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... Dear List - The same query in a PHP program will only give me results for MedRec 10003 why the where 1 clause? Do you know what that is for? = Dear Jim Thanks As I understand, to enable me to concatenate phases to construct a query. The query does work in MySQL fro the terminal. Ethan I don't think so. All it does is return one record. The where clause defines what you want returned. A '1' returns one record, the first one. #10003 I wonder why you think where 1 enables concatenation?? A query (IN SIMPLE TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with a WHERE clause to define the criteria to limit the rows, and an ORDER BY to sort the result set. More complex queries can include GROUP BY when you are including summary operators such as SUM(fldname) or MAX(fldname), or a JOIN clause, or a host of other clauses that make sql so powerful. In your case I think you copied a sample query that (they always seem to be displayed with a 'where 1' clause) and left the 1 on it. To summarzie: SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key 100 and a.key = b.key ORDER BY a.fld1 Im no expert, but hopefully this makes it a little less complex for you. This is a suggestion I received from this list - + $query = select * from Intake3 where ; Maybe I missed it, but you need to have a 1 after the where part in your select. So... $query = SELECT * FROM Intake3 WHERE 1 ; I must stress that the query works from the terminal, and fails in the PHP code. My question is why? Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
On Sun, Jun 17, 2012 at 10:21 PM, Jim Giner jim.gi...@albanyhandball.com wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... At 03:30 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... Dear List - The same query in a PHP program will only give me results for MedRec 10003 why the where 1 clause? Do you know what that is for? = Dear Jim Thanks As I understand, to enable me to concatenate phases to construct a query. The query does work in MySQL fro the terminal. Ethan I don't think so. All it does is return one record. The where clause defines what you want returned. A '1' returns one record, the first one. #10003 I wonder why you think where 1 enables concatenation?? A query (IN SIMPLE TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with a WHERE clause to define the criteria to limit the rows, and an ORDER BY to sort the result set. More complex queries can include GROUP BY when you are including summary operators such as SUM(fldname) or MAX(fldname), or a JOIN clause, or a host of other clauses that make sql so powerful. In your case I think you copied a sample query that (they always seem to be displayed with a 'where 1' clause) and left the 1 on it. To summarzie: SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key 100 and a.key = b.key ORDER BY a.fld1 Im no expert, but hopefully this makes it a little less complex for you. Right, Why would WHERE 1 return only 1 record? That makes no sense and, no offense, it sounds like you really don't know where you're talking about. WHERE 1 is just a useless statement, but in his case makes it easier to concatenate multiple AND ... statements. As to the original problem, I guess that there might be something wrong with your code later on that parses the result? Did you try to use echo mysqli_num_rows($result1), just after the query to see how many rows it returned? - Matijn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
Matijn Woudt tijn...@gmail.com wrote in message news:cac_gtup8fjuv7jyut89w2491nm-7zno8mrj3w0mep6totm2...@mail.gmail.com... On Sun, Jun 17, 2012 at 10:21 PM, Jim Giner jim.gi...@albanyhandball.com wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... At 03:30 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... Dear List - The same query in a PHP program will only give me results for MedRec 10003 why the where 1 clause? Do you know what that is for? = Dear Jim Thanks As I understand, to enable me to concatenate phases to construct a query. The query does work in MySQL fro the terminal. Ethan I don't think so. All it does is return one record. The where clause defines what you want returned. A '1' returns one record, the first one. #10003 I wonder why you think where 1 enables concatenation?? A query (IN SIMPLE TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with a WHERE clause to define the criteria to limit the rows, and an ORDER BY to sort the result set. More complex queries can include GROUP BY when you are including summary operators such as SUM(fldname) or MAX(fldname), or a JOIN clause, or a host of other clauses that make sql so powerful. In your case I think you copied a sample query that (they always seem to be displayed with a 'where 1' clause) and left the 1 on it. To summarzie: SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key 100 and a.key = b.key ORDER BY a.fld1 Im no expert, but hopefully this makes it a little less complex for you. Right, Why would WHERE 1 return only 1 record? That makes no sense and, no offense, it sounds like you really don't know where you're talking about. WHERE 1 is just a useless statement, but in his case makes it easier to concatenate multiple AND ... statements. As to the original problem, I guess that there might be something wrong with your code later on that parses the result? Did you try to use echo mysqli_num_rows($result1), just after the query to see how many rows it returned? - Matijn You could be right. My interpretation of where 1 is it returns one record. And as I said I'm no expert. I guess where 1 could mean where true. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
On Sun, Jun 17, 2012 at 5:55 PM, Ethan Rosenberg eth...@earthlink.net wrote: At 04:21 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... At 03:30 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... Dear List - The same query in a PHP program will only give me results for MedRec 10003 why the where 1 clause? Do you know what that is for? = Dear Jim Thanks As I understand, to enable me to concatenate phases to construct a query. The query does work in MySQL fro the terminal. Ethan I don't think so. All it does is return one record. The where clause defines what you want returned. A '1' returns one record, the first one. #10003 I wonder why you think where 1 enables concatenation?? A query (IN SIMPLE TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with a WHERE clause to define the criteria to limit the rows, and an ORDER BY to sort the result set. More complex queries can include GROUP BY when you are including summary operators such as SUM(fldname) or MAX(fldname), or a JOIN clause, or a host of other clauses that make sql so powerful. In your case I think you copied a sample query that (they always seem to be displayed with a 'where 1' clause) and left the 1 on it. To summarzie: SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key 100 and a.key = b.key ORDER BY a.fld1 Im no expert, but hopefully this makes it a little less complex for you. This is a suggestion I received from this list - + $query = select * from Intake3 where ; Maybe I missed it, but you need to have a 1 after the where part in your select. So... $query = SELECT * FROM Intake3 WHERE 1 ; I must stress that the query works from the terminal, and fails in the PHP code. My question is why? Ethan First off, all where 1 does is make the statement always true. If you don't believe that, try this query: SELECT * FROM Intake3 WHERE 1; in mysql and contrast with: SELECT * FROM Intake3; you should see identical results. Secondly, you need to look at where you are gathering the return from the query. You show: $result = mysqli_query($cxn,$query); but you aren't showing us what you do with $result, as in where you fetch the rows and process them. I'm dubious that the query is in fact only returning one record. But you can check by echoing mysqli_num_rows($result). (I think; I never use the procedural version, opting to use the object version instead.) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
On Sun, Jun 17, 2012 at 8:22 PM, tamouse mailing lists tamouse.li...@gmail.com wrote: On Sun, Jun 17, 2012 at 5:55 PM, Ethan Rosenberg eth...@earthlink.net wrote: At 04:21 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... At 03:30 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... Dear List - The same query in a PHP program will only give me results for MedRec 10003 why the where 1 clause? Do you know what that is for? = Dear Jim Thanks As I understand, to enable me to concatenate phases to construct a query. The query does work in MySQL fro the terminal. Ethan I don't think so. All it does is return one record. The where clause defines what you want returned. A '1' returns one record, the first one. #10003 I wonder why you think where 1 enables concatenation?? A query (IN SIMPLE TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with a WHERE clause to define the criteria to limit the rows, and an ORDER BY to sort the result set. More complex queries can include GROUP BY when you are including summary operators such as SUM(fldname) or MAX(fldname), or a JOIN clause, or a host of other clauses that make sql so powerful. In your case I think you copied a sample query that (they always seem to be displayed with a 'where 1' clause) and left the 1 on it. To summarzie: SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key 100 and a.key = b.key ORDER BY a.fld1 Im no expert, but hopefully this makes it a little less complex for you. This is a suggestion I received from this list - + $query = select * from Intake3 where ; Maybe I missed it, but you need to have a 1 after the where part in your select. So... $query = SELECT * FROM Intake3 WHERE 1 ; I must stress that the query works from the terminal, and fails in the PHP code. My question is why? Ethan First off, all where 1 does is make the statement always true. If you don't believe that, try this query: SELECT * FROM Intake3 WHERE 1; in mysql and contrast with: SELECT * FROM Intake3; you should see identical results. Secondly, you need to look at where you are gathering the return from the query. You show: $result = mysqli_query($cxn,$query); but you aren't showing us what you do with $result, as in where you fetch the rows and process them. I'm dubious that the query is in fact only returning one record. But you can check by echoing mysqli_num_rows($result). (I think; I never use the procedural version, opting to use the object version instead.) Just to clarify, what Ethan has is not incorrect: the way he's constructing the where clause requires at least one expression before the $allowed_fields gets tacked on: $query .= AND ($key = '$val') ; Since if even one of those gets appended, there must be an expression before the first AND. The 1 merely evaluates to true here, creating a valid where clause. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
