Problem with storing info & associated ID

blepblep · April 23, 2012

Hi guys, hoping for some help here.

I've a database created with a website. I recently got help here off Muddy Funster who was a MASSIVE help to me on this thread - http://www.phpfreaks.com/forums/index.php?topic=357580.new;topicseen#new

I've another slight problem with what I am trying to do. At the moment the user can search for an ID and it brings up the relevant information.

What I am doing now is that the user has entered the ID, their taken to the results where they can be viewed. What I have added is a form at the bottom of the results page, which has to be filled in and saved. I thought this would be relatively easy. I have the form storing in the database, but there is one problem, its being added as another document! So instead of clicking save and the information being added to the ID that is already associated with it, it is incrementing the ID in the database and being stored separately to the ID that was searched.

Could anyone give any help as to why this might be happening? I have on the button 'Save' click, it goes to another php file and inserts the data to the relevant fields, I'm not sure why it is incrementing the ID separately instead of just saving to the ID that it is associated with :confused:

I don't no if I'm making it clear enough but what I wanted to do was save the form they filled out with the ID that was searched if you get me?

Thanks for any help

Jessica · April 23, 2012

You'll need to do an UPDATE statement instead of an INSERT is the best I can guess without code.

blepblep · April 23, 2012

You'll need to do an UPDATE statement instead of an INSERT is the best I can guess without code.

I don't really no what code to show but here goes, any more needed just ask -

Button code on the results page:


<form action = "saveMom.php" method = "post">

<tr>
     <td>
           <input type="button" name="reset_form" value="Clear Fields" onclick="this.form.reset();">
   <input type="submit" name="insert2" value="Save MOM" />
     </td>
</tr>

Inserting the data:

if(isset($_POST['insert2']))
{
$insert_query = "INSERT INTO tc_tool.forms
        ...........
        VALUES (
	    '$_POST[booking_number]',
                     ..........

}

Should I have the insert data to the database on the same page as the results or would that make a difference?!

Jessica · April 23, 2012

You'll need to do an UPDATE statement instead of an INSERT

blepblep · April 24, 2012

You'll need to do an UPDATE statement instead of an INSERT

Thanks for the reply, I've tried it using an UPDATE query:


$insert_query = "UPDATE tc_tool.forms WHERE
			(booking_number,
                                  ................
                                 )
                                 VALUES
			 (
			 '$_POST[booking_number]',

But I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (booking_number, quality_ranking_review, input_doc_rank, ' at line 1

I'm not really sure why I'm getting that error

blepblep · April 24, 2012

Tried it these ways too -

if(isset($_POST['insert2']))
{
$insert_query = "UPDATE tc_tool.forms

			SET booking_number = 'booking_number',
			quality_ranking_review = 'quality_ranking_review',
			input_doc_rank = 'input_doc_rank',
			correct_doc = 'correct_doc',
			internally_reviewed = 'internally_reviewed',
			reqs_covered = 'reqs_covered',
			correct_storage_library = 'correct_storage_library',
			reports_described = 'reports_described',
			approved_change_requests_included = 'approved_change_requests_included',
			issues_addressed = 'issues_addressed',
			doc_available = 'doc_available',
			statement_problem_chapter = 'statement_problem_chapter',
			major_comments = 'major_comments',
			result = 'result',
			num_of_major_comments = 'num_of_major_comments',
			minor_comments = 'minor_comments',
			next_review_forum = 'next_review_forum',
			date = 'date',
			time = 'time',
			venue = 'venue',
			time2 = 'time2',
			location = 'location',
			severity = 'severity',
			resp = 'resp',
			status = 'status',
			comment = 'comment',
			remark = 'remark',
			miscellaneous = 'miscellaneous'

			WHERE

			(booking_number,
			quality_ranking_review, 
			input_doc_rank, 
			correct_doc,
			internally_reviewed, 
			reqs_covered, 
			correct_storage_library, 
			reports_described, 
			approved_change_requests_included, 
			issues_addressed,
			doc_available, 
			statement_problem_chapter,
			major_comments, 
			result, 
			num_of_major_comments, 
			minor_comments, 
			next_review_forum,
			date, 
			time, 
			venue, 
			time2,
			location,
			severity,
			resp,
			status,
			comment,
			remark,
			miscellaneous)

			 VALUES
			 (
			 '$_POST[booking_number]',
			 '$_POST[quality_ranking_review]', 
			 '$_POST[input_doc_rank]', 
			 '$_POST[correct_doc]', 
			 '$_POST[internally_reviewed]', 
			 '$_POST[reqs_covered]', 
			 '$_POST[correct_storage_library]',
			 '$_POST[reports_described]', 
			 '$_POST[approved_change_requests_included]', 
			 '$_POST[issues_addressed]', 
			 '$_POST[doc_available]',
			 '$_POST[statement_problem_chapter]',
			 '$_POST[major_comments]',
			 '$_POST[result]',
				 '$_POST[num_of_major_comments]', 
				 '$_POST[minor_comments]', 
				 '$_POST[next_review_forum]', 
			 '$_POST[date]',
			 '$_POST[time]',
			 '$_POST[venue]',
			 '$_POST[time2]',
			 '$_POST[location]',
			 '$_POST[severity]',
			 '$_POST[resp]',
			 '$_POST[status]',
			 '$_POST[comment]',
			 '$_POST[remark]',
			 '$_POST[miscellaneous]')";

Get this error -

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES ( '', '', '', '', '', '', ''' at line 63

This way -

if(isset($_POST['insert2']))
{
$insert_query = "UPDATE tc_tool.forms

			SET booking_number = 'booking_number',
			quality_ranking_review = 'quality_ranking_review',
			input_doc_rank = 'input_doc_rank',
			correct_doc = 'correct_doc',
			internally_reviewed = 'internally_reviewed',
			reqs_covered = 'reqs_covered',
			correct_storage_library = 'correct_storage_library',
			reports_described = 'reports_described',
			approved_change_requests_included = 'approved_change_requests_included',
			issues_addressed = 'issues_addressed',
			doc_available = 'doc_available',
			statement_problem_chapter = 'statement_problem_chapter',
			major_comments = 'major_comments',
			result = 'result',
			num_of_major_comments = 'num_of_major_comments',
			minor_comments = 'minor_comments',
			next_review_forum = 'next_review_forum',
			date = 'date',
			time = 'time',
			venue = 'venue',
			time2 = 'time2',
			location = 'location',
			severity = 'severity',
			resp = 'resp',
			status = 'status',
			comment = 'comment',
			remark = 'remark',
			miscellaneous = 'miscellaneous'

			WHERE

			(booking_number,
			quality_ranking_review, 
			input_doc_rank, 
			correct_doc,
			internally_reviewed, 
			reqs_covered, 
			correct_storage_library, 
			reports_described, 
			approved_change_requests_included, 
			issues_addressed,
			doc_available, 
			statement_problem_chapter,
			major_comments, 
			result, 
			num_of_major_comments, 
			minor_comments, 
			next_review_forum,
			date, 
			time, 
			venue, 
			time2,
			location,
			severity,
			resp,
			status,
			comment,
			remark,
			miscellaneous)";

Results in this error -

Operand should contain 1 column(s)

I want to store this information in the database too so I'm not sure if a UPDATE statement is right, as this is new information being passed into the database but I want the ID of this new information to be the same as the ID that was searched if that is any clearer?

I don't understand why it's incrementing the ID in the database each time the button is clicked

Muddy_Funster · April 24, 2012

you're over complicating it (the S in SQL is for Simple ) :

<?php
if(isset($_POST['insert2']))
{
$insert_query = "UPDATE tc_tool.forms

			SET booking_number = '{$_POST['booking_number']}',,
			quality_ranking_review =  '{$_POST[quality_ranking_review]}',,
			...
			remark =  '{$_POST['remark']}',,
			miscellaneous =  '{$_POST['miscellaneous']}'

			WHERE

			(id = $id)";
?>

I assume you are storing the relevent id in some variable or other, for this example I have used $id, you should change this to your relevent id varaible.

Remember to use curly braces {} arround array keys when puting them in strings and to use single quotes around array key names (inside the square brackets []).

On another note, you really shouldn't be sending raw form data to usour database, you should always check and sanatise it before hand.

blepblep · April 24, 2012

Thanks for the help Muddy!!

I get this error when I run it:

Query failed:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' quality_ranking_review = '',, input_doc_rank = '',, correc' at line 3

As for id, it is being stored in the database as review_id. I have it sanitized by doing this -

function sanitize_data($data)
    {
        $data = array_map('trim',$data);
        $data = array_map('strip_tags',$data);
        $data = array_map('htmlspecialchars',$data);
        $data = array_map('mysql_real_escape_string',$data);
        return $data;
    }
    $post = sanitize_data($_POST);

if(isset($_POST['insert2']))

Muddy_Funster · April 24, 2012

I coppied too many commas at the end of the lines, make sure there is only 1 at each line.

You will need to have a php variable that contains the id of the record you are trying to update, before you can perform the update, you should be pulling it from the previous pages lookup either as a session variable or as a post varialbe from a hidden field.

I was talking about sanitising all the inputs that you are using in the UPDATE in this code. Unless you are modifying the contents of the $_POST array directly you are updating the database with raw, unchecked user input.

blepblep · April 24, 2012

I don't really understand what you mean by I need to have a php variable that contains the id of the record I'm trying to add? Or the sanitizing all the inputs :facewall:

This is how it looks at the moment:

<?php
///------------------------
///	Connecting to database
///------------------------

include 'connect_db.php';

///-----------------------------
///	Inserting data into database
///-----------------------------

function sanitize_data($data)
    {
        $data = array_map('trim',$data);
        $data = array_map('strip_tags',$data);
        $data = array_map('htmlspecialchars',$data);
        $data = array_map('mysql_real_escape_string',$data);
        return $data;
    }
    $post = sanitize_data($_POST);

if(isset($_POST['save']))
{
$insert_query = "UPDATE tc_tool.forms

			SET	booking_number = 				'{$_POST['booking_number']}',
			quality_ranking_review = 			        '{$_POST['quality_ranking_review']}',
			input_doc_rank = 					'{$_POST['input_doc_rank']}',
			correct_doc = 						'{$_POST['correct_doc']}',
			internally_reviewed = 				        '{$_POST['internally_reviewed']}', 
			reqs_covered = 						'{$_POST['reqs_covered']}',
			correct_storage_library = 			        '{$_POST['correct_storage_library']}',
			reports_described = 				        '{$_POST['reports_described']}',
			approved_change_requests_included =            '{$_POST['approved_change_requests_included']}',
			issues_addressed = 					'{$_POST['issues_addressed']}',
			doc_available = 					        '{$_POST['doc_available']}',
			statement_problem_chapter = 		        '{$_POST['statement_problem_chapter']}',
			major_comments = 					'{$_POST['major_comments']}',
			result = 							'{$_POST['result']}',
			num_of_major_comments =  			        '{$_POST['num_of_major_comments']}',
			minor_comments = 					'{$_POST['minor_comments']}',
			next_review_forum = 				        '{$_POST['minor_comments']}',
			date = 						        '{$_POST['date']}',
			time =							'{$_POST['time']}',
			venue =							'{$_POST['venue']}',
			time2 =							'{$_POST['time2']}',
			location =							'{$_POST['location']}',
			severity =							'{$_POST['severity']}',
			resp =							'{$_POST['resp']}',
			status =							'{$_POST['status']}',
			comment =							'{$_POST['comment']}',
			remark =							'{$_POST['remark']}',
			miscellaneous =						'{$_POST['miscellaneous']}'

			 WHERE

			 (review_id = $id)";

	//-----------------------------
	// Prints error if there is one
	//-----------------------------

	if (!mysql_query($insert_query, $connection))
	{
		echo "Query failed: $query<br />" . mysql_error();
	}

	mysql_close($connection);
}
?>

My id row in my database is called review_id but that doesnt make any difference to whats being done.

Muddy_Funster · April 24, 2012

Okay, your sanisize is redundant the way you have the code just now. you are taking the post array and running your sanatise_data() function directly against it, that's not going to work as it's not the array that needs sanitized but rather the values in it. As such you need to loop through the $_POST array and sanitize each value individualy. Also, you are saying that the variable $post is = to the sanitized $_POST, and then using the raw $_POST in the update, rather than the $post sanitized version.

change

    $post = sanitize_data($_POST);

to

 $post = array();
foreach($_POST as $key =>$value){
$post[$key] = sanitize_data($value);
}

Then change your query to use the $post array rahter than the $_POST array.

As for the id variable, how do you plan to tell the query which id field in the database its supposed to be updating?

blepblep · April 24, 2012

Thanks Muddy, I've changed my function sanitize_data($data) to this:

function sanitize_data($data)
{    
    $post = array();
foreach ($_POST as $key => $value)
{
	$post[$key] = sanitize_data($value);
}
}

if(isset($post['save']))
{
$insert_query = "UPDATE tc_tool.forms

			SET	booking_number =  '{$post['booking_number']}',
                                .............. 
                                WHERE
			 (id = $_id)";

I don't get any errors now when I click save, and it doesn't create a new ID, but still doesn't save to the ID it's supposed to be updating.

I thought I could tell the query which ID field in the database it's meant to update by doing something like this?

WHERE
(id = $post[review_id])";

// or

WHERE 
(id = $post['review_id'))";

But neither give me an error, ahhh!! :suicide:

Muddy_Funster · April 24, 2012

do we know that there are values getting populated into the $post array? try a var_dump($post) just before the query

batwimp · April 24, 2012

Your sanitize_data function is no longer doing anything but calling itself. I think he meant for you to keep the function the way it was, but put the post values into an array and call sanitize_data from outside the function. Something like this:

function sanitize_data($data) // create the function sanitize_data
    {
        $data = array_map('trim',$data);
        $data = array_map('strip_tags',$data);
        $data = array_map('htmlspecialchars',$data);
        $data = array_map('mysql_real_escape_string',$data);
        return $data;
    }

$post = array(); 
foreach($_POST as $key =>$value){
$post[$key] = sanitize_data($value);   // call sanitize_data using each value from the $post array
}

Am I right, Muddy?

blepblep · April 24, 2012

var_dump($post);

if(isset($post['save']))

Results in:

NULL So no, nothing is getting passed in either!

Adam · April 24, 2012

you're over complicating it (the S in SQL is for Simple )

It actually stands for "Structured"

blepblep · April 24, 2012

This is what happens when I do the sanitize like this -

function sanitize_data($data) // create the function sanitize_data
    {
        $data = array_map('trim',$data);
        $data = array_map('strip_tags',$data);
        $data = array_map('htmlspecialchars',$data);
        $data = array_map('mysql_real_escape_string',$data);
        return $data;
    }

$post = array(); 
foreach($_POST as $key =>$value){
$post[$key] = sanitize_data($value);   // call sanitize_data using each value from the $post array
}

I get this -

Thanks for the help so far guys

batwimp · April 24, 2012

Oops, right. You're passing in scalar data instead of arrays. My bad. Inside your function you may want to do something like:

function sanitize_data($data) // create the function sanitize_data
    {
        $data = mysql_real_escape_string(trim($data));
        return $data;
    }

Adam · April 24, 2012

array_map takes an array, as the error suggests, and you're trying to pass each item in the $_POST array separately as a string. One solution would be to pass the $_POST array itself, which I'm not keen on because it assumes all data should be sanitised the same. Sanitisation should be done input by input to ensure you're sanitising the data correctly. For example if it's numeric data and you're just escaping quotes, that doesn't necessarily prevent SQL injection.

The other option would be to pass each item separately as you're doing, but dropping array_map() and directly invoking trim, strip_tags, etc. The problem here, and this applies to the other solution, is that you're not sanitising the data correctly. You should aim to keep the data as close as possible to what the user entered when inserting into the database, and apply your escaping functions (htmlspecialchars namely) when outputting.

blepblep · April 24, 2012

@Batwimp - Thanks, I've changed it to how you've said.

@Adam -I don't really understand how to do that. Do you mean I should have checkfields on each of the text box's that I want the user to fill in to verify that they have been filled in or what? Sorry about this, I've just been learning PHP about 2-3 months

batwimp · April 24, 2012

You don't necessarily want a catch-all function to sanitize your inputs unless you put checks in to see what data type each input is (string, integer, etc.) because each different data type requires a different kind of sanitation. So strings should use trim and mysql_real_escape_string, integers should be cast to integers -- (int)$value -- or use some other type of method such as intval(). Most programmers know what kind of data they expect for a given variable, and sanitize it accordingly. Ultimately if you want to pass off your sanitizing to a function, you need some kind of checks inside that function to verify what type of data it is and apply sanitation accordingly.

Search for some tutorials on google and youtube for php data sanitizing and you should get some good info.

blepblep · April 24, 2012

Thanks batwimp, I'll have a look at doing that once I get some more things sorted with my site. Appreciate the help!

Any idea why my query isnt working? It's not giving me out any errors but it's not working either. Muddy was saying something earlier about it:

As for the id variable, how do you plan to tell the query which id field in the database its supposed to be updating?

I'm not sure how to tell the query which field ID in the database it is supposed to update.

blepblep · April 25, 2012

Bump

Muddy_Funster · April 25, 2012

Ok Blep, let's see the up to date code and a description of what's going on. Is $_POST['review_id'] actually the ID from the database that defines the record? and is it actualy in the form that is submitted?

blepblep · April 25, 2012

Woo Muddy! Yes, review_id is the field in the database that the ID defines each record:

My code up to date is below:

<?php

include 'connect_db.php';

function sanitize_data($data) // create the function sanitize_data
    {
        $data = mysql_real_escape_string(trim($data));
        return $data;
    }

$post = array(); 
foreach($_POST as $key =>$value){
$post[$key] = sanitize_data($value);   // call sanitize_data using each value from the $post array
}

//var_dump($post);

if(isset($post['save']))
{

$insert_query = "UPDATE tc_tool.forms

			SET	quality_ranking_review = 		'{$post['quality_ranking_review']}',
			input_doc_rank = 					'{$post['input_doc_rank']}',
			correct_doc = 						'{$post['correct_doc']}',
			internally_reviewed = 				'{$post['internally_reviewed']}', 
			reqs_covered = 						'{$post['reqs_covered']}',
			correct_storage_library = 			'{$post['correct_storage_library']}',
			reports_described = 				'{$post['reports_described']}',
			approved_change_requests_included = '{$post['approved_change_requests_included']}',
			issues_addressed = 					'{$post['issues_addressed']}',
			doc_available = 					'{$post['doc_available']}',
			statement_problem_chapter = 		'{$post['statement_problem_chapter']}',
			major_comments = 					'{$post['major_comments']}',
			result = 							'{$post['result']}',
			num_of_major_comments =  			'{$post['num_of_major_comments']}',
			minor_comments = 					'{$post['minor_comments']}',
			next_review_forum = 				'{$post['minor_comments']}',
			date = 								'{$post['date']}',
			time =								'{$post['time']}',
			venue =								'{$post['venue']}',
			time2 =								'{$post['time2']}',
			location =							'{$post['location']}',
			severity =							'{$post['severity']}',
			resp =								'{$post['resp']}',
			status =							'{$post['status']}',
			comment =							'{$post['comment']}',
			remark =							'{$post['remark']}',
			miscellaneous =						'{$post['miscellaneous']}'

			 WHERE

			 (review_id = '{$post['review_id']}')";

	if (!mysql_query($insert_query, $connection))
	{
		echo "Query failed: $query<br />" . mysql_error();
	}

function died($error) 
{
// Error code here
	echo "Error, not saving successfully. ";
	echo "The errors are below.<br /><br />";
	echo $error."<br /><br />";
	echo "Please retry.<br /><br />";
	var_dump($_POST);
	die();
}

	mysql_close($connection);
}
?>

It's weird because it's not printing out any errors, but also not storing in the database atall now.. I'm nearly sure it has something to do with this line:

WHERE
 (review_id = '{$post['review_id']}')";

I don't no if you remember from the other thread you helped me in but I have nothing in my code defining review_id, although I have $id, done like below:

if (isset($post['searchReviewForum']) && $post['searchbyforumtype'] != '')
{
$id = $post['searchbyforumtype'];
$sql = <<<SQL

Sign In

Problem with storing info & associated ID

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information