[PHP-DB] Re: [PHP] PHP & Database Problems -- Code Snippets
It is a good habbit to write the core code just for inserting/retrieving data from database. It will reduce much of your code complexity. When you see your code is working, you can continue to embed it with your visual front-end or with your filter. On Thu, May 3, 2012 at 4:43 AM, Ethan Rosenberg wrote: > Dear list - > > Sorry for the attachment. Here are code snippets --- > > GET THE DATA FROM INTAKE3: > >function handle_data() >{ > global $cxn; > $query = "select * from Intake3 where 1"; > > > > if(isset($_Request['Sex'])&& trim($_POST['Sex']) != '' ) > { >if ($_REQUEST['Sex'] === "0") >{ > $sex = 'Male'; >} >else >{ > $sex = 'Female'; >} > } > >} > >$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); >} > >$num = mysqli_num_rows($result1); >if(($num = mysqli_num_rows($result1)) == 0) >{ > ?> >No > Records Retrieved #1 > exit(); >} > > DISPLAY THE INPUT3 DATA: > > >>> THIS SEEMS TO BE THE ROUTINE THAT IS FAILING <<< > >Search Results > > frame="box"> > >Site >Medical Record >First Name >Last Name >Phone >Height >Sex >History > > > > while ($row1 = mysqli_fetch_array($result1, MYSQLI_BOTH)) > { >print_r($_POST); > global $MDRcheck; > $n1++; > echo "n1 ";echo $n1; >{ > if (($n1 > 2) && ($MDRcheck == $row1[1])) > { >echo ">2== "; >echo $MDRcheck; >echo " $row1[0] \n"; >echo " $row1[1] \n"; >echo " $row1[2] \n"; >echo " $row1[3] \n"; >echo " $row1[4] \n"; >echo " $row1[5] \n"; >echo " $row1[6] \n"; >echo " $row1[7] \n"; >echo "\n"; > } > elseif (($n1 > 2) && ($MDRcheck != $row1[1])) > { >echo ">2!= "; > >echo $MDRcheck; > > >continue; > } > elseif ($n1 == 2) > { > >define( "MDR" , $row1[1]); >echo "row1 ";echo $row1[1]; >echo "\n"; > >$_GLOBALS['mdr']= $row1[1]; >$_POST['MedRec'] = $row1[1]; >$MDRold = $_GLOBALS['mdr']; >echo " $row1[0] \n"; >echo " $row1[1] \n"; >echo " $row1[2] \n"; >echo " $row1[3] \n"; >echo " $row1[4] \n"; >echo " $row1[5] \n"; >echo " $row1[6] \n"; >echo " $row1[7] \n"; >echo "\n"; > } > >} > } > > ?> > > SELECT AND DISPLAY DATA FROM VISIT3 DATABASE > > $query2 = "select * from Visit3 where 1 AND (Site = 'AA') AND (MedRec > = $_GLOBALS[mdr])"; >$result2 = mysqli_query($cxn, $query2); >$num = mysqli_num_rows($result2); > > >global $finished; >$finished = 0; > > >while($row2 = mysqli_fetch_array($result2, MYSQLI_BOTH)) >{ > global $finished; > echo "\n"; > echo " $row2[0] \n"; > echo " $row2[1] \n"; > echo " $row2[2] \n"; > echo " $row2[3] \n"; > echo " $row2[4] \n"; > echo " $row2[5] \n"; > echo " $row2[6] \n"; > echo "\n"; > >} > > echo ""; > > ENTER MORE DATA: > >function More_Data() >{ > $decision = 5; > ?> > >Do you Wish to Enter More Data? > >Yes type="radio" name="decision" value="0" />No > > > > > } //>end function More_Data > > > >switch ( @$_POST[next_step] ) >{ > > case "step10": > { >if (!isset($_POST['decision'])) >{ > $_POST['decision'] = 5; >} > >if ($_POST['decision'] == 0) >{ > exit(); >} >if ($_POST['decision'] == 1) >{ > ; > echo ""; >echo " />"; >echo ""; >echo "Medical Record: value=\" $_GLOBALS[mdr]\" />"; >echo " Weight: name=\"Weight\" /> "; >echo "Notes: "; >
[PHP-DB] Re: [PHP] PHP & Database Problems -- Code Snippets
On Wed, May 2, 2012 at 11:43 PM, Ethan Rosenberg wrote: > Dear list - > > Sorry for the attachment. Here are code snippets --- Ethan, I don't want to sound rude, but it appears to me you don't have any understanding of what you're doing. It might help if you understand what the code is doing... Let me explain. > > GET THE DATA FROM INTAKE3: > > function handle_data() > { > global $cxn; > $query = "select * from Intake3 where 1"; > > > > if(isset($_Request['Sex'])&& trim($_POST['Sex']) != '' ) $_Request does not exists, you're looking for $_REQUEST. And why are you mixing $_REQUEST and $_POST here? > { > if ($_REQUEST['Sex'] === "0") > { > $sex = 'Male'; > } > else > { > $sex = 'Female'; > } > } > > } What is the point of the handle_data function above? It doesn't do anything. > $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); > } First, this will allow SQL injections, because you insert the values directly from the browser. Second, you should move the last line ($result1=...), outside of the foreach loop, now you're executing the query multiple times. Third, you should check if $result1 === FALSE, in case the query fails > > $num = mysqli_num_rows($result1); > if(($num = mysqli_num_rows($result1)) == 0) Doing the same thing twice? > { > ?> > No Records > Retrieved #1 > exit(); > } > > DISPLAY THE INPUT3 DATA: > THIS SEEMS TO BE THE ROUTINE THAT IS FAILING <<< > > Search Results > > frame="box"> > > Site > Medical Record > First Name > Last Name > Phone > Height > Sex > History > > > > while ($row1 = mysqli_fetch_array($result1, MYSQLI_BOTH)) > { > print_r($_POST); Doesn't really make sense to print $_POST here.. > global $MDRcheck; > $n1++; > echo "n1 ";echo $n1; > { > if (($n1 > 2) && ($MDRcheck == $row1[1])) > { > echo ">2== "; > echo $MDRcheck; > echo " $row1[0] \n"; > echo " $row1[1] \n"; > echo " $row1[2] \n"; > echo " $row1[3] \n"; > echo " $row1[4] \n"; > echo " $row1[5] \n"; > echo " $row1[6] \n"; > echo " $row1[7] \n"; > echo "\n"; > } > elseif (($n1 > 2) && ($MDRcheck != $row1[1])) > { > echo ">2!= "; > > echo $MDRcheck; > > > continue; continue doesn't do anything here. > } > elseif ($n1 == 2) > { > > define( "MDR" , $row1[1]); > echo "row1 ";echo $row1[1]; > echo "\n"; > > $_GLOBALS['mdr']= $row1[1]; > $_POST['MedRec'] = $row1[1]; You're not supposed to set variables in $_POST... > $MDRold = $_GLOBALS['mdr']; It appears you want the old value of mdr, if so, then you should do this before you set it again 2 lines above.. > echo " $row1[0] \n"; > echo " $row1[1] \n"; > echo " $row1[2] \n"; > echo " $row1[3] \n"; > echo " $row1[4] \n"; > echo " $row1[5] \n"; > echo " $row1[6] \n"; > echo " $row1[7] \n"; > echo "\n"; > } > > } > } > > ?> You say this routine is probably the one that is failing.. but what is going wrong? And how the heck are we supposed to know what this function should do? > > SELECT AND DISPLAY DATA FROM VISIT3 DATABASE > > $query2 = "select * from Visit3 where 1 AND (Site = 'AA') AND (MedRec = > $_GLOBALS[mdr])"; You're using mdr as a constant here, this will generate a warning, but sadly enough it works. > $result2 = mysqli_query($cxn, $query2); You should check if $result2 === FALSE, in case the query fails. > $num = mysqli_num_rows($result2); You're counting the rows here, but you don't do anything with the result? > << Snip the rest of this crappy code >> > > I hope this helps. > > Ethan > > I think I made my point. I guess if I continued on the rest of the code t
[PHP-DB] PHP & Database Problems -- Code Snippets
Dear list - Sorry for the attachment. Here are code snippets --- GET THE DATA FROM INTAKE3: function handle_data() { global $cxn; $query = "select * from Intake3 where 1"; if(isset($_Request['Sex'])&& trim($_POST['Sex']) != '' ) { if ($_REQUEST['Sex'] === "0") { $sex = 'Male'; } else { $sex = 'Female'; } } } $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); } $num = mysqli_num_rows($result1); if(($num = mysqli_num_rows($result1)) == 0) { ?> No Records Retrieved #1 >> THIS SEEMS TO BE THE ROUTINE THAT IS FAILING <<< Search Results cellspacing="55" rules="all" frame="box"> Site Medical Record First Name Last Name Phone Height Sex History n1 ";echo $n1; { if (($n1 > 2) && ($MDRcheck == $row1[1])) { echo ">2== "; echo $MDRcheck; echo " $row1[0] \n"; echo " $row1[1] \n"; echo " $row1[2] \n"; echo " $row1[3] \n"; echo " $row1[4] \n"; echo " $row1[5] \n"; echo " $row1[6] \n"; echo " $row1[7] \n"; echo "\n"; } elseif (($n1 > 2) && ($MDRcheck != $row1[1])) { echo ">2!= "; echo $MDRcheck; continue; } elseif ($n1 == 2) { define( "MDR" , $row1[1]); echo "row1 ";echo $row1[1]; echo "\n"; $_GLOBALS['mdr']= $row1[1]; $_POST['MedRec'] = $row1[1]; $MDRold = $_GLOBALS['mdr']; echo " $row1[0] \n"; echo " $row1[1] \n"; echo " $row1[2] \n"; echo " $row1[3] \n"; echo " $row1[4] \n"; echo " $row1[5] \n"; echo " $row1[6] \n"; echo " $row1[7] \n"; echo "\n"; } } } ?> SELECT AND DISPLAY DATA FROM VISIT3 DATABASE $query2 = "select * from Visit3 where 1 AND (Site = 'AA') AND (MedRec = $_GLOBALS[mdr])"; $result2 = mysqli_query($cxn, $query2); $num = mysqli_num_rows($result2); global $finished; $finished = 0; while($row2 = mysqli_fetch_array($result2, MYSQLI_BOTH)) { global $finished; echo "\n"; echo " $row2[0] \n"; echo " $row2[1] \n"; echo " $row2[2] \n"; echo " $row2[3] \n"; echo " $row2[4] \n"; echo " $row2[5] \n"; echo " $row2[6] \n"; echo "\n"; } echo ""; ENTER MORE DATA: function More_Data() { $decision = 5; ?> Do you Wish to Enter More Data? Yes No end function More_Data switch ( @$_POST[next_step] ) { case "step10": { if (!isset($_POST['decision'])) { $_POST['decision'] = 5; } if ($_POST['decision'] == 0) { exit(); } if ($_POST['decision'] == 1) { ; echo ""; echo "value=\"step4\" />"; echo ""; echo "Medical Record: name=\"MedRec\" value=\" $_GLOBALS[mdr]\" />"; echo " Weight: name=\"Weight\" /> "; echo "Notes: "; echo "rows=\"10\" wrap=\"hard\" name=\"Notes\" > "; echo ""; echo " "; echo ""; echo ""; } } /* End Step10 */ case "step4": { if (!isset($_POST['datain'])) { $_POST['datain'] = 5; } if( $_POST['datain'] == "already") { date_default_timezone_set('America/New_York'); $Date = date('Ymd'); $Date2 = date('d M Y'); $sql1 = "select MedRec, Height from Intake3 where (MedRec = $MDRold)"; $result4 = mysqli_query($cxn, $sql1); $row4 = mysqli_fetch_array($result4, MYSQLI_BOTH); $Height = $row4[1]; $Weight = $_POST['Weigh
[PHP-DB] Re: [PHP] PHP & Database Problems
Dear Ethan, It would be useful to see what code you are using. The syntax is: UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value The data that you are required to enter is for Intake3. I am assuming that the only change is the History and/or Phone that you need to update since the Site, MedRec, Fname, Lname, Height and Sex should remain the same. Therefore I imagine the code should be something like mysql_query("UPDATE Intake3 SET History='Has been convinced by his friends that he is obese. Normal BMI = 23', Phone='325 563-4178' WHERE MedRec='1' LIMIT 1"); Having said that I notice that you have two primary keys in Intake3. Not aware that a table can carry two. Terry On 2 May 2012 19:53, Ethan Rosenberg wrote: > have a database > > 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| | > ++-+--**+-+-+---+ > 8 rows in set (0.00 sec) > > 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|| > ++--+-**-+-+-+**+ > > and a program to enter and extract data. > > I can easily extract data from the database. However, if I try to enter > data, it goes into the incorrect record. Following are some screenshots. > The program is attached. [pardon the comical names. This is a test, and > any resemblance to true names is not intentional] > > Let us say that I wish to deal with Medical Record 1: > > > This it data from Intake3: > Site Medical Record First Name Last Name Phone Height Sex History > AA 1 David Dummy 845 365-1456 66 Male c/o obesity. Various treatments > w/o success > > This is data from Visit3: > Index Site Medical Record Notes Weight BMI Date > 2322 AA 1 Second Visit. 170 27.4 2010-01-20 > 2326 AA 1 Third visit. Small progress, but pt is very happy. 165 26.6 > 2010-02-01 > > > I then request to enter additional data: > > Site Medical Record First Name Last Name Phone Height Sex History > AA 10003 Stupid Fool 325 563-4178 65 Male Has been convinced by his > friends that he is obese. Normal BMI = 23. > Index Site Medical Record Notes Weight BMI Date > > Notice that it is entered into record 10003 > > The data is "First Try" > > Index Site Medical Record Notes Weight BMI Date > 2590 AA 10003 First Try 189 31.4 02 May 2012 > > Help and advice, please. > > Thanks. > > Ethan > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- *Terry Ally* Twitter.com/terryally Facebook.com/terryally ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ To print or not to print this email is the environmentally-searching question! Which has the highest ecological cost? A sheet of paper or constantly switching on your computer and connecting to the Internet to read your email?
RE: [PHP-DB] PHP & Database Problems
Ethan, Some coding you are using would be helpful (as far as i am aware attachments are not support on the mailing list's) Gav -Original Message- From: Ethan Rosenberg [mailto:eth...@earthlink.net] Sent: 02 May 2012 19:54 To: php-db-lists.php.net; php-gene...@lists.php.net Subject: [PHP-DB] PHP & Database Problems have a database 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| | ++-+--+-+-+---+ 8 rows in set (0.00 sec) 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|| ++--+--+-+-++ and a program to enter and extract data. I can easily extract data from the database. However, if I try to enter data, it goes into the incorrect record. Following are some screenshots. The program is attached. [pardon the comical names. This is a test, and any resemblance to true names is not intentional] Let us say that I wish to deal with Medical Record 1: This it data from Intake3: Site Medical Record First Name Last Name Phone Height Sex History AA 1 David Dummy 845 365-1456 66 Male c/o obesity. Various treatments w/o success This is data from Visit3: Index Site Medical Record Notes Weight BMI Date 2322 AA 1 Second Visit. 170 27.4 2010-01-20 2326 AA 1 Third visit. Small progress, but pt is very happy. 165 26.6 2010-02-01 I then request to enter additional data: Site Medical Record First Name Last Name Phone Height Sex History AA 10003 Stupid Fool 325 563-4178 65 Male Has been convinced by his friends that he is obese. Normal BMI = 23. Index Site Medical Record Notes Weight BMI Date Notice that it is entered into record 10003 The data is "First Try" Index Site Medical Record Notes Weight BMI Date 2590 AA 10003 First Try 189 31.4 02 May 2012 Help and advice, please. Thanks. Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] PHP & Database Problems
have a database 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| | ++-+--+-+-+---+ 8 rows in set (0.00 sec) 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|| ++--+--+-+-++ and a program to enter and extract data. I can easily extract data from the database. However, if I try to enter data, it goes into the incorrect record. Following are some screenshots. The program is attached. [pardon the comical names. This is a test, and any resemblance to true names is not intentional] Let us say that I wish to deal with Medical Record 1: This it data from Intake3: Site Medical Record First Name Last Name Phone Height Sex History AA 1 David Dummy 845 365-1456 66 Male c/o obesity. Various treatments w/o success This is data from Visit3: Index Site Medical Record Notes Weight BMI Date 2322 AA 1 Second Visit. 170 27.4 2010-01-20 2326 AA 1 Third visit. Small progress, but pt is very happy. 165 26.6 2010-02-01 I then request to enter additional data: Site Medical Record First Name Last Name Phone Height Sex History AA 10003 Stupid Fool 325 563-4178 65 Male Has been convinced by his friends that he is obese. Normal BMI = 23. Index Site Medical Record Notes Weight BMI Date Notice that it is entered into record 10003 The data is "First Try" Index Site Medical Record Notes Weight BMI Date 2590 AA 10003 First Try 189 31.4 02 May 2012 Help and advice, please. Thanks. Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php