11 Replies - 467 Views - Last Post: 08 January 2013 - 02:57 PM Rate Topic: -----

#1 childlucy00  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 45
  • Joined: 03-February 10

MySQL/PHP issue. Data not being inserted into table

Posted 08 January 2013 - 09:19 AM

Hi. Having a bit of bother with a website I'm creating for my Triathlon club. Trying to make it so a user can add data to the TrainingSchedule and RaceSchedule tables both in phpMyAdmin. I have a similar example and can not see where I'm going wrong.
This is the form page:
<?php 
$stmt = $mysqli->prepare("SELECT Week, Sport, SessionDate, Time, Coach, Location, Details FROM TrainingSchedule WHERE SessionID = ?");

$stmt->bind_param('i', $_GET['SessionID']);

$stmt->execute(); 

$stmt->bind_result($Week, $Sport, $Date, $Time, $Coach, $Location, $Details);

$stmt->fetch();

$stmt->close();

?>

<!DOCTYPE HTML>

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

<title>Content Management System</title>

<link href="css.css" rel="stylesheet" type="text/css">
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.5/jquery.min.js"></script>
<script src="../includes/datepicker.js"></script>
<link rel="stylesheet" media="screen" type="text/css" href="../includes/datepicker.css" />
</head>

<body>

<div id="container">



<header>

<h1>CMS - <a href="Member.php" title="loginPage">Back to login page</a></h1>

</header>



<div id="CMSform">
<h2>Add New Session</h2>

<section>

<form name="addSession" method="post" action="insertRecord.php">

  <p class="indent">

    <label for="Week">Week:</label>
		<select>
            <option value="1">1 == WB 14/01/13</option>
            <option value="2">2 == WB 21/01/13</option>
            <option value="3">3 == WB 28/01/13</option>   
            <option value="4">4 == WB 04/02/13</option>
        </select> 
    <input type="submit" name="Week" id="Week" value="">

  </p>

  <p class="indent">

    <label for="Sport">Sport:</label>
 		<select>
            <option value="Bike">Bike</option>
            <option value="Run">Run</option>
            <option value="StrengthandConditioning">Strength and Conditioning</option>   
            <option value="Swim">Swim</option>
        </select> 
        <input type="submit" name="Sport" id="Sport" value="">

  </p>

  <p class="indent">

    <label for ="SessionDate">Date: </label>
        <input class="inputDate" id="inputDate" value="01/01/2013" />
        <script>//http://www.eyecon.ro/datepicker/#about
            $('#inputDate').DatePicker({
            format:'m/d/Y',
            date: $('#inputDate').val(),
            current: $('#inputDate').val(),
            starts: 1,
            position: 'r',
            onBeforeShow: function(){
                $('#inputDate').DatePickerSetDate($('#inputDate').val(), true);
            },
            onchange: function(formated, dates){
                $('#inputDate').val(formated);
                    $('#inputDate').DatePickerHide();
            }
        });
        </script>
        <input type="submit" name="SessionDate" id="SessionDate" value="">
  </p>

 <p class="indent">

    <label for="Time">Time:</label>

    <input type="text" name="Time" id="Time">

  </p>

 <p class="indent">

    <label for="Coach">Coach:</label>

    <input type="text" name="Coach" id="Coach">

  </p>

  <p>

  <span class="indent">Location:</span>

    <label for="location_pf">Ponds Forge</label>

      <input type="radio" name="Location" value="PF" id="PF">



    <label for="location_ep">Endcliffe Park</label>

      <input type="radio" name="Location" value="EP" id="EP">



    <label for="location_cg">Collegiate Gym</label>

      <input type="radio" name="Location" value="CG" id="CG" checked>



    <label for="location_dvs">Don Valley Stadium</label>

      <input type="radio" name="Location" value="DVS" id="DVS">



    <label for="location_pd">Peak District</label>

      <input type="radio" name="Location" value="PD" id="PD" checked>

	
	<label for="location_h">Home</label>

      <input type="radio" name="Location" value="H" id="H" checked>
    </p>
 	
 	<p class="indent">

   

    <label for="Details">Details:</label>

    <textarea name="Details" id="Details" cols="45" rows="5"></textarea>

  </p>
  <p>

    <input type="submit" name="Insert" id="Insert" value="Insert Session">

  </p>

</form>
>


and this is the page its sent to:

<?php 

require_once('../includes/conn.inc.php');
 
$stmt = $mysqli->prepare("INSERT INTO TrainingSchedule ( Week, 

											 Sport,

											 SessionDate,

											 Time,

											 Coach,

											 Location,

											 Details)

											 VALUES (?, ?, ?, ?, ?, ?, ?)"); 

$stmt->bind_param('issssss', $_POST['Week'], 

						   $_POST['Sport'],

						   $_POST['SessionDate'],

						   $_POST['Time'],

						   $_POST['Coach'],

						   $_POST['Location'],

						   $_POST['Details']); 

$stmt->execute(); 

$stmt->close(); 
header("Location: CMS.php"); // redirect browser


exit; // make sure no other code executed 

?>


The data is not being inserted into the TrainingSchedule database table. All of the field names are correct.

Thanks for any help in advance

Is This A Good Question/Topic? 0
  • +

Replies To: MySQL/PHP issue. Data not being inserted into table

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3718
  • View blog
  • Posts: 5,989
  • Joined: 08-June 10

Re: MySQL/PHP issue. Data not being inserted into table

Posted 08 January 2013 - 09:34 AM

Hey.

The main problem is that your code is assuming that the query will always be successful, and it neither checks whether this is the case or in any way handles the error. An extremely important part of writing good code (in any language) is to account for possible errors, and make sure they are handled and/or reported.

In your case, you should at the very least check the return value of $stmt->execute(). It'll return FALSE if there is a problem executing the query, in which case you can use the mysqli_stmt::$error attribute to see what exactly caused the error. Of course, this error message should not be shown on the page. It has no business being displayed to users. It should be logged somewhere (PHP has pretty powerful error logging capabilites built in you can use) or sent to somebody responsible for investigating. You can ignore this on develompent environments, and just print the error to the page, but you need to make sure you disable that before moving the code to a live server.

I suggest you look at functions like error_log and trigger_error, as well as PHP's error logging configuration options .
Was This Post Helpful? 2
  • +
  • -

#3 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: MySQL/PHP issue. Data not being inserted into table

Posted 08 January 2013 - 09:36 AM

don't think this has to do with it, but: what ist this "i" for??
$stmt->bind_param('i', $_GET['SessionID']);


concerning your problem: do you get some errors?


EDIT:
oh, Atli was first, with a better answer then mine ;)

This post has been edited by Anthonidas: 08 January 2013 - 09:37 AM

Was This Post Helpful? 0
  • +
  • -

#4 childlucy00  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 45
  • Joined: 03-February 10

Re: MySQL/PHP issue. Data not being inserted into table

Posted 08 January 2013 - 09:44 AM

Anthonidas.... the i is a flag to say that SessionID will be an integer.

Atli...I'm not sure how to do this. Very new to Web Development. Will try though and report back.


Thanks

Atli... I've tried using
printf("Error: %s.\n", $stmt->error);
after the execute line and it's not throwing back any errors. What else can I try?
Was This Post Helpful? 0
  • +
  • -

#5 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: MySQL/PHP issue. Data not being inserted into table

Posted 08 January 2013 - 09:47 AM

you should work with the try/catch function. Something like this:

try {
    // your code here
} catch (PDOException $e) {
    print 'I got an error: ' . $e->getMessage();
}



have a look here:
http://php.net/manua...or-handling.php
Was This Post Helpful? 0
  • +
  • -

#6 childlucy00  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 45
  • Joined: 03-February 10

Re: MySQL/PHP issue. Data not being inserted into table

Posted 08 January 2013 - 09:51 AM

Which bit of code should go into that? Sorry. I'm pretty useless
Was This Post Helpful? 0
  • +
  • -

#7 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: MySQL/PHP issue. Data not being inserted into table

Posted 08 January 2013 - 09:58 AM

your query execution. in fact you can put the whole PDO code on it.

didn't you read the article i posted on the previous post? read it, it's important to understand what you can do and why you should do that!
Was This Post Helpful? 0
  • +
  • -

#8 childlucy00  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 45
  • Joined: 03-February 10

Re: MySQL/PHP issue. Data not being inserted into table

Posted 08 January 2013 - 10:00 AM

No error is coming up with anything
Was This Post Helpful? 0
  • +
  • -

#9 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2958
  • View blog
  • Posts: 10,183
  • Joined: 08-August 08

Re: MySQL/PHP issue. Data not being inserted into table

Posted 08 January 2013 - 11:33 AM

PDO is not the same as MySQLi. I think you want:
} catch (mysqli_sql_exception $e) { 


Was This Post Helpful? 0
  • +
  • -

#10 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3718
  • View blog
  • Posts: 5,989
  • Joined: 08-June 10

Re: MySQL/PHP issue. Data not being inserted into table

Posted 08 January 2013 - 12:26 PM

I seriously doubt MySQLi is set up to throw execptions here. It's not enabled by default, and unlike with PDO, I've rarely seen it turned on at all.

This is what you'd usually expect to see in MySQLi code, to handle statement execution errors:
if (!$stmt->execute()) {
    // Display or log the error here. For instance:
    trigger_error("Query failed to execute: " . $stmt->error, E_USER_ERROR);
}


This will either log the error in the error log file, or display it, depending on error reporting configuration directives I linked to before.
Was This Post Helpful? 3
  • +
  • -

#11 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2958
  • View blog
  • Posts: 10,183
  • Joined: 08-August 08

Re: MySQL/PHP issue. Data not being inserted into table

Posted 08 January 2013 - 12:29 PM

I think Atli is right. Add this to the reasons for using PDO instead.
Was This Post Helpful? 0
  • +
  • -

#12 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: MySQL/PHP issue. Data not being inserted into table

Posted 08 January 2013 - 02:57 PM

oh, i did not know that... though it is not since yesterday i use PHP and MySQL in combination :P lol
thank you for that!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1