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



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php