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 | 10000 | 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