not updating database and prepared statements

jacko_162 · February 18, 2014

I have the following code:

<?php
	session_start();
include('Includes/auth.php');
require_once('header.php');
?>
<table width="80%" border="0" align="center" cellpadding="2" cellspacing="2">
      <tr>
        <td width="40%" valign="top">
        <div class="content-box column-left">
		<div class="content-box-header">
					<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="80%"><h3>Add New Test Results</h3></td>
    <td width="20%"></td>
  </tr>
</table>

		  </div>
				<div class="content-box-content">
				  <div class="block">
   <form enctype="multipart/form-data" action="<? $_SERVER['PHP_SELF']; ?>" method="post">
                  
<?php
  {
    $fetch=mysql_query("SELECT * FROM members WHERE member_id=$_SESSION[SESS_MEMBER_ID]");
    $fetchrow = mysql_fetch_array($fetch);
    $arr = array();
    $arr = explode(';',$fetchrow['settings']);
    $member_id = $fetchrow['member_id'];

	if (isset($_POST['submit'])) {

	$date2 = date("y-m-d"); // DATE OF TANK ADDITION
	$time2 = date("H:i:s", time() - 3600);   // TIME OF TANK ADDITION 

	$test1 = $_POST['test1'];  
	$test2 = $_POST['test2']; 
	$test3 = $_POST['test3']; 
	$test4 = $_POST['test4']; 
	$test5 = $_POST['test5']; 
	$test6 = $_POST['test6']; 
	$test7 = $_POST['test7']; 
	$test8 = $_POST['test8']; 
	$test9 = $_POST['test9']; 
	$test10 = $_POST['test10']; 
	$test11 = $_POST['test11']; 
	$test12 = $_POST['test12']; 
	$test13 = $_POST['test13'];
	$test14 = $_POST['test14'];
	$time = $_POST['time'];
	$date = $_POST['date'];
	$month = $_POST['month'];
	$day = $_POST['day'];
	$active1 = $_POST['active1'];
	$active2 = $_POST['active2'];
	$active3 = $_POST['active3'];
	$active4 = $_POST['active4'];
	$active5 = $_POST['active5'];
	$active6 = $_POST['active6'];
	$active7 = $_POST['active7'];
	$active8 = $_POST['active8'];
	$active9 = $_POST['active9'];
	$active10 = $_POST['active10'];
	$active11 = $_POST['active11'];
	$active12 = $_POST['active12'];
	$active13 = $_POST['active13'];
	$active14 = $_POST['active14'];
	$tank = $_POST['tank'];


$insert = "INSERT INTO tests (member_id, test1, test2, test3, test4, test5, test6, test7, test8, test9, test10, test11, test12, test13, test14, time, date, month, day, active1, active2, active3, active4, active5, active6, active7, active8, active9, active10, active11, active12, active13, active14, tank)

VALUES ($_SESSION[SESS_MEMBER_ID], $test1, $test2, $test3, $test4, $test5, $test6, $test7, $test8, $test9, $test10, $test11, $test12, $test13, $test14, '$time', '$date', '$month', '$day', $active1, $active2, $active3, $active4, $active5, $active6, $active7, $active8, $active9, $active10, $active11, $active12, $active13, $active14, '$tank') ";

$insert2 = "INSERT INTO `logs` SET 
typeid='1',
memberid='$_SESSION[SESS_MEMBER_ID]',
info='New Test Added',
tankname='-',
time='$time2',
date='$date2'";

if (@mysql_query($insert))
if (@mysql_query($insert2))
{
?>
                    <script type="text/javascript">
document.location.replace('results.php');
                          </script>
                          <?php
}
else {
echo('Error in you submission:' . mysql_error() . "<br />" . $sql);
}} 

?>
*Un-Tick the checkbox for values you dont want saving.
	                      <table width="100%" border="0" cellspacing="2" cellpadding="2">
                              <tr>
                                <td>
<div class="block">
                                <input type="hidden" name="time" value="<?
//get current timestamp and subtract one <strong class="highlight">hour</strong>
$dat = time() - 3600;
//output time
echo date("g:ia", $dat);?>" /> <br /> 
                  <table width="100%" border="0" align="center" cellpadding="2" cellspacing="0">
<tr>
<td colspan="2" width="50%">
<table width="100%" border="0" align="center" cellspacing="0" cellpadding="2">
  <tr>
    <td colspan="2" align="center"><div align="left" class="style1"></div></td>
    </tr>
  <tr>
    <td width="42%" align="center"><table border="0" cellspacing="0" cellpadding="2">
      <tr><td><label>Select Tank:</label><br />
      <select class="text2" name="tank">
        <option value="All" selected="selected"> Select Tank </option>
        <?  
 // Query to pull information from the "catagory" Database  
$result = mysql_query("select * from tanks WHERE member_id = '$_SESSION[SESS_MEMBER_ID]' order by id ASC");  
while ($row = mysql_fetch_object($result)){  
?>
        <option value="<?php echo $row->id; ?>"> <?php echo $row->description; ?> </option>
        <? }?>
      </select></td></tr>
    </table>      </td>
    <td valign="top" align="center" width="50%">
    						  <?php
                              $todaydate = date("Y-m-d");
							  $oldDate = date("Y-m-d", strtotime("6 months ago"));
							  ?>
    <table border="0" cellspacing="0" cellpadding="2">
      <tr><td><label>Select Date:</label><br /> <input name="date" type="text" class="date" id="datepicker" value="<?php echo $todaydate; ?>" /></td></tr>
    </table>    </td>
    </tr>
</table></td>
</tr><tr>
<div class="block">
<td valign="top" align="center" width="50%">
<table border="0" cellspacing="0" cellpadding="2">
<?php echo (in_array("test1",$arr) ? "<tr><td><Label>Salinity:</label><br><input type='checkbox' name='active1' value='1' checked='checked' /> <input class='text' name='test1' type='text' value='' /> ppt </td></tr>" : ''); ?> 
<?php echo (in_array("test2",$arr) ? "<tr><td><label>PH:</label><br><input type='checkbox' name='active2' value='1' checked='checked'  /> <input class='text' type='text' name='test2' value='' /> ppm </td></tr>" : ''); ?> 
<?php echo (in_array("test3",$arr) ? "<tr><td><label>Ammonia:</label><br><input type='checkbox' name='active3' value='1' checked='checked'  /> <input class='text' type='text' name='test3' value='' /> ppm </td></tr>" : ''); ?> 
<?php echo (in_array("test4",$arr) ? "<tr><td><label>Nitrite:</label><br><input type='checkbox' name='active4' value='1' checked='checked'  /> <input class='text' type='text' name='test4' value='' /> ppm </td></tr>" : ''); ?> 
<?php echo (in_array("test5",$arr) ? "<tr><td><label>Nitrate:</label><br><input type='checkbox' name='active5' value='1' checked='checked'  /> <input class='text' type='text' name='test5' value='' /> ppm </td></tr>" : ''); ?> 
<?php echo (in_array("test6",$arr) ? "<tr><td><label>Calcium:</label><br><input type='checkbox' name='active6' value='1' checked='checked'  /> <input class='text' type='text' name='test6' value='' /> ppm </td></tr>" : ''); ?> 
<?php echo (in_array("test7",$arr) ? "<tr><td><label>Magnesium:</label><br><input type='checkbox' name='active7' value='1' checked='checked'  /> <input class='text' type='text' name='test7' value='' /> ppm </td></tr>" : ''); ?>  
</table></td>
<td valign="top" align="center" width="50%">
<table border="0" cellspacing="0" cellpadding="2">
<?php echo (in_array("test8",$arr) ? "<tr><td><label>Alkalinity:</label><br><input type='checkbox' name='active8' value='1' checked='checked'  /> <input class='text' type='text' name='test8' value='' /> dKH </td></tr>" : ''); ?>
<?php echo (in_array("test9",$arr) ? "<tr><td><label>Phosphate:</label><br><input type='checkbox' name='active9' value='1' checked='checked'  /> <input class='text' type='text' name='test9' value='' /> ppm </td></tr>" : ''); ?>
<?php echo (in_array("test10",$arr) ? "<tr><td><label>potassium:</label><br><input type='checkbox' name='active10' value='1' checked='checked'  /> <input class='text' type='text' name='test10' value='' /> ppm </td></tr>" : ''); ?>
<?php echo (in_array("test11",$arr) ? "<tr><td><label>Iodine:</label><br><input type='checkbox' name='active11' value='1' checked='checked'  /> <input class='text' type='text' name='test11' value='' /> ppm </td></tr>" : ''); ?>
<?php echo (in_array("test12",$arr) ? "<tr><td><label>Strontium:</label><br><input type='checkbox' name='active12' value='1' checked='checked'  /> <input class='text' type='text' name='test12' value='' /> ppm </td></tr>" : ''); ?>
<?php echo (in_array("test13",$arr) ? "<tr><td><label>Silica:</label><br><input type='checkbox' name='active13' value='1' checked='checked'  /> <input class='text' type='text' name='test13' value='' /> ppm </td></tr>" : ''); ?>
<?php echo (in_array("test14",$arr) ? "<tr><td><label>Temperature:</label><br><input type='checkbox' name='active14' value='1' checked='checked'  /> <input class='text' type='text' name='test14' value='' /> 'c </td></tr>" : ''); ?>
</table>
</td>
</div>
</tr>
</table>                                   
                <div align="right"><br />
                                   <table border="0" cellspacing="0" cellpadding="8">
                                     <tr>
                                       <td><input class="btn btn-success btn-rad" type="submit" name="submit" value="Submit" /></td>
                                       <td><input class="btn btn-danger btn-rad" type="reset" name="reset" value="Reset" /></td>
                                     </tr>
                                   </table>
                               </div></div>
                   </td>
                              </tr>
                            </table> </form></div>
							   <?php
}
?>
					
				  </div>
				</div> <!-- End .content-box-content -->
                </div>
        </td>
      </tr>
</table>
<?php
require_once('footer.php');
?>

For some reason the code doesn't update the 'test' table and people keep telling me to change it to prepared statements for protection against injection.

can anyone help me with both please.

jairathnem · February 18, 2014

Check if it is throwing an error in that insert.

$result=mysql_query($insert);
if(!$result){
    echo mysql_error();
}

This will check if the insert executed correctly or not.

tazmith · February 18, 2014

http://php.net/pdo.prepared-statements would be a good start to check out PDO and prepared statements.

but heres an example based off the script you've posted.

<?php
 
/* PDO example for a database connection */
 
$host = 'host';

$dbname = 'db_name';

$user = 'db_user';

$pass = 'db_pass';



try

{  

    $dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

}

catch(PDOException $e)

{

    echo $e->getMessage();

}
 
 
/* PDO example of select using 'bindParam' and fetchAll*/


$id = $_SESSION['SESS_MEMBER_ID'];

$stmt = $dbh->prepare('SELECT settings,member_id FROM members WHERE ' . 'member_id=:member_id ');

$stmt->bindParam(':member_id', $id, PDO::PARAM_INT);

$stmt->execute();

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row)

{

      $arr = array();

      $arr = explode(';',$row['settings']);

      $member_id = $row['member_id'];
}
 
 
/* PDO example of insert using 'bindParam' method */
 
$member_id = $_SESSION['SESS_MEMBER_ID'];
$date = date("y-m-d");

$time = date("H:i:s", time() - 3600);
 
$stmt = $dbh->prepare("INSERT INTO tests (member_id, test1, time, date, month, day, active1)

            VALUES (:member_id, :test1, :time, :date, :month, :day, :active1)");

$stmt->bindParam(':member_id', $_SESSION['SESS_MEMBER_ID'], PDO::PARAM_INT);

$stmt->bindParam(':test1', $_POST['test1'], PDO::PARAM_STR); //by default it will be param_str.

$stmt->bindParam(':time', $time);

$stmt->bindParam(':date', $date);

$stmt->bindParam(':month', $_POST['month']);

$stmt->bindParam(':day', $_POST['date']);

$stmt->bindParam(':active1', $_POST['active1']);

$stmt->execute();

If your going to stick with mysql_ when your using $_POST, get into the habit of storing it using mysql_real_escape_string if you're worried about 'sql injection.' Otherwise your completely vulnerable.

example:

$test1 = $_POST['test1']; 
 
//becomes
 
$test1 = mysql_real_escape_string($_POST['test1']);

hope that helps, good luck.

jacko_162 · February 18, 2014

ok i managed to get it to INSERT the data, forgot to wrap my VALUES with '' around them.

Next question is the data im inserting into the database is numbers with possible decimals. is there a way I can enter NULL ONLY if text field is left empty?

default is null in table setup.

jacko_162 · February 18, 2014

this is my current code;

	$test1 = mysql_real_escape_string($_POST['test1'] === '') ? NULL : $_POST['test1'];
	$test2 = mysql_real_escape_string($_POST['test2'] === '') ? NULL : $_POST['test2'];
	$test3 = mysql_real_escape_string($_POST['test3'] === '') ? NULL : $_POST['test3'];
	$test4 = mysql_real_escape_string($_POST['test4'] === '') ? NULL : $_POST['test4'];
	$test5 = mysql_real_escape_string($_POST['test5'] === '') ? NULL : $_POST['test5'];
	$test6 = mysql_real_escape_string($_POST['test6'] === '') ? NULL : $_POST['test6'];
	$test7 = mysql_real_escape_string($_POST['test7'] === '') ? NULL : $_POST['test7'];
	$test8 = mysql_real_escape_string($_POST['test8'] === '') ? NULL : $_POST['test8'];
	$test9 = mysql_real_escape_string($_POST['test9'] === '') ? NULL : $_POST['test9'];
	$test10 = mysql_real_escape_string($_POST['test10'] === '') ? NULL : $_POST['test10'];
	$test11 = mysql_real_escape_string($_POST['test11'] === '') ? NULL : $_POST['test11'];
	$test12 = mysql_real_escape_string($_POST['test12'] === '') ? NULL : $_POST['test12'];
	$test13 = mysql_real_escape_string($_POST['test13'] === '') ? NULL : $_POST['test13'];
	$test14 = mysql_real_escape_string($_POST['test14'] === '') ? NULL : $_POST['test14'];
	$time = $_POST['time'];
	$date = $_POST['date'];
	$month = $_POST['month'];
	$day = $_POST['day'];
	$active1 = mysql_real_escape_string($_POST['active1']);
	$active2 = mysql_real_escape_string($_POST['active2']);
	$active3 = mysql_real_escape_string($_POST['active3']);
	$active4 = mysql_real_escape_string($_POST['active4']);
	$active5 = mysql_real_escape_string($_POST['active5']);
	$active6 = mysql_real_escape_string($_POST['active6']);
	$active7 = mysql_real_escape_string($_POST['active7']);
	$active8 = mysql_real_escape_string($_POST['active8']);
	$active9 = mysql_real_escape_string($_POST['active9']);
	$active10 = mysql_real_escape_string($_POST['active10']);
	$active11 = mysql_real_escape_string($_POST['active11']);
	$active12 = mysql_real_escape_string($_POST['active12']);
	$active13 = mysql_real_escape_string($_POST['active13']);
	$active14 = mysql_real_escape_string($_POST['active14']);
	$tank = mysql_real_escape_string($_POST['tank']);


$insert = "INSERT INTO tests (member_id, test1, test2, test3, test4, test5, test6, test7, test8, test9, test10, test11, test12, test13, test14, time, date, month, day, active1, active2, active3, active4, active5, active6, active7, active8, active9, active10, active11, active12, active13, active14, tank_id)

VALUES ($_SESSION[SESS_MEMBER_ID], '$test1', '$test2', '$test3', '$test4', '$test5', '$test6', '$test7', '$test8', '$test9', '$test10', '$test11', '$test12', '$test13', '$test14', '$time', '$date', '$month', '$day', '$active1', '$active2', '$active3', '$active4', '$active5', '$active6', '$active7', '$active8', '$active9', '$active10', '$active11', '$active12', '$active13', '$active14', '$tank') ";

i can only assume its passing 'NULL' instead of just NULL, how can i get it to remove the '' around the null value?

jairathnem · February 18, 2014

Implement that at the database and set default to NULL.
alter code : ALTER TABLE tests ALTER test3 SET DEFAULT NULL

if no value is sent for test3 it will add NULL, else it adds the passed value.

Sign In

not updating database and prepared statements

Recommended Posts

jacko_162

Link to comment

Share on other sites

jairathnem

Link to comment

Share on other sites

tazmith

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

jairathnem

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information