Form input to update mysql Database

[tiranto]

Hi,

Initialy i use the below code to load the inputed data into the Databse with a auto-incrament primary key resulting with a ID number for the registration.

--------------
$sql = "insert into `$tbl_name` (`cna`, `phone`, `mobile`, `f_name`, `s_name`, .........................
------------

I have worked out a way of loading, and now updating the data useing the below code
-----------------
$sql = "replace into `$tbl_name` (`cna`, `phone`, `mobile`, `f_name`, `s_name`, .........................
----------------
but the problem is that the querie will delete the original key, load a new key with the updated information on it.

which now causes conflictions where the original ID when recorded no longer exisits.

Is there anouther way to go about this ? below is the full list of code from the php script.

Thanks
Tiranto
----------------------
<?php
$host="#Removed#"; // Host name
$username="#Removed#"; // Mysql username
$password="#Removed#"; // Mysql password
$db_name="foc"; // Database name
$tbl_name="foc_data"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// username and password sent from form
$cna = mysql_real_escape_string(strip_tags($_POST['cna']));
$phone = mysql_real_escape_string(strip_tags($_POST['phone']));
$mobile = mysql_real_escape_string(strip_tags($_POST['mobile']));
$f_name = mysql_real_escape_string(strip_tags($_POST['f_name']));
$s_name = mysql_real_escape_string(strip_tags($_POST['s_name']));

$sql = "replace into `$tbl_name` (`cna`, `phone`, `mobile`, `f_name`, `s_name`)
values ('$cna', '$phone', '$mobile', '$f_name', '$s_name')";
//$result=mysql_query($sql)or die("test");

$result=mysql_query($sql)or header("location:../error messages/fail.php");


// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){

//Register $cna and redirect to file "success.php"
session_register("cna");


}
else {
$focid = mysql_real_escape_string(strip_tags($_GET["focid"]));
$sql = 'SELECT * FROM ' . $tbl_name . ' WHERE cna="' . $cna . '"';
$result = mysql_query($sql);
$output_focid = mysql_result($result,0,"focid");

include("form_body.php");
include("form_fotter.php");

}
?>
----------------------
Note im learing as I go, so you probably will see code that might be a older way or a longer way of doing things, but its how i manage to get it to work from researching the internet help guides to build this my self.

[Matthew1980]

Hi there tiranto,

Doesn't look too bad, the only thing as I can suggest at this point is, if you are wanting to update a record set using an ID number, just use the mysql UPDATE `table_name` SET instruction:-

$sql = "UPDATE `table_name` SET `column1` = 'new value' `column2` = 'another value' WHERE `ID` = 'YOUR_ID' LIMIT 1";

You need the reference ID number so that when the update occurs, you only update the sets selected values according to the ID.

That's the way I would go with that, just added the LIMIT 1 instruction so that you only update 1 record set on the strength that the ID number matches, else you need the "id number not found clause".

Cheers,
MRb

[tiranto]

Ahh so going by the original source code, it would look somthing like

$sql = "UPDATE `$tbl_name` SET `cna` = '$cna' `phone` = '$phone' `mobile` = '$mobile' `f_name` = '$f_name' `s_name` = '$s_name' WHERE `focid` = 'your_focid' LIMIT 1";

Obviously to the existing code ill need to get it to pull the data from the Database ie focid and refference the your_focid.

as I have the "focid" as the index and primary key as an auto incrament on the database table.

[Matthew1980]

Hi there tiranto,

Error in my original post:-

$sql = "UPDATE `table_name` SET `column1` = 'new value' `column2` = 'another value' WHERE `ID` = 'YOUR_ID' LIMIT 1";

Should have been:-

$sql = "UPDATE `table_name` SET `column1` = 'new value', `column2` = 'another value' WHERE `ID` = 'YOUR_ID' LIMIT 1";

You need a comma there to separate the columns & values to go into them ;)

So yours will read like this:-

$sql = "UPDATE `".$tbl_name."` SET `cna` = '".$cna."', `phone` = '".$phone."', `mobile` = '".$mobile."', `f_name` = '".$f_name."', `s_name` = '".$s_name."' WHERE `focid` = 'your_focid' LIMIT 1";

I have concatenated the vars into the string too, this makes for better reading IMHO but no doubt people will disagree with that ;)

Sorry about that :)

Have fun anyway!

Cheers,
MRb

[tiranto]

Hi the script works, and pass's values to the echo page but doesn’t load into the DB..

I have added the below to make the ID collection load.
//collect existing ID number from the DB
$focid = mysql_real_escape_string(strip_tags($_GET["focid"]));
$sql = 'SELECT * FROM ' . $tbl_name . ' WHERE cna="' . $cna . '"';

//collect the same ID from the form post to marry the two ID's
$your_focid = mysql_real_escape_string(strip_tags($_POST['your_focid']));

------------------------

If i use
(WHERE `$focid` = '$your_focid' LIMIT 1"; )
It resulting saying the Information exists in my database

but if I use
(WHERE `focid` = '$your_focid' LIMIT 1"; )
It echo's with out editing the Database
--------------------------
I think this is because my column "cna" and column and one of the other fields column "serial" both are (Primary, Unique, Index, and Full text) illuminated on my PHPmyadmin database view screen.

Would my thinking be correct do you think, as the script is set to check for duplications and in the load and error page as below.

$result=mysql_query($sql)or header("location:../error messages/fail.php");

if($count==1){
session_register("cna");
}
else {
<<<Success Echo>>>
}

[Matthew1980]

Hi there tiranto,

Instead of using mysql_real_escape_string($_GET["focid"]) you could just use is_numeric($_GET["focid"]), that is of course if the var is ONLY numeric, though you would need an error handler in place to redirect back to the form, if the var wasn't an int. Just a thought there, but you see the logic.

mysql_real_escape_string() only makes data 'safe' by escaping certain chars so that it doesn't affect the operation of the query, so really you only need it if there is a sting being used in the query.

The only thing I will suggest is that when developing the sql stuff, add the error handler onto the functions so that you get any errors flagged up from the sql side of things shown to screen, but ONLY use this when developing ;-p

mysql_query(A_QUERY) or die(mysql_error());

that should who anything up coming from sql as an error.

As for your theory, give it a try, can't hurt, I would personally approach it slightly differently.

Don't forget that when using double quotes the code will work without the concatenations, I just find the code easier to read with them in.

and pass's values to the echo page but doesn’t load into the DB..

Could you elaborate a bit please? Not too sure what you are referring to :)

Cheers,
MRb

[tiranto]

Ah ok, Well what I’ve built so far is a form which a user can input data to register a request for replacement of parts.

that form dumps the data into the database first, the database allocates the "focid" a auto incremented, unique and primary key number for the record.

It then loads an included php page “form_body” page which echos the inputted data back to the user to which they can print the page.

From this printable echo'ed page Iv added code where the user can go and edit the data and update the form which is currently working but deletes the original unique primary key number "focid" and creates a new number.

The downside of this current setup is “data integrity” where the capture data changes after the fact and if the original record number is noted down and change the references will be lose as the Id has changed.

So At this stage, when editing a document, it loads the data fine, when trying to apply the update it results that two additional key input fields IE "Serial number" and a 3rd party id number "cna" is already in existence.

In the early days when it was not able to edit the data, and still today they forms check for existing data, which is what is happening now when trying to use the sql code “update” in replacement for “replace into”.

In addition to this Iv also created an admin page to log in and load data assigned to a user type for approval, which this update code will be used also for to imbed the approval comments into the existing record.

[tiranto]

One other thing, I added the or die(mysql_error()); to the end of my SQL which results
------------------------------------
Unknown column '' in 'where clause'
-----------------------------------

In the Where statement (WHERE `focid` = 'your_focid' )

the first value after the where I have set "focid" which is the name of the column headding, and the second "$posted_focid" which is the data posted into the form. is that correct in my thinking how it should be setup?

[tiranto]

Ok, Its all fixed, I worked out where the kink was, the input form had the name value of your_foc, so the php code looking for your_focid failed to post to the DB but still echo'ed, Fixed and all working.

Learning a lot building this from having to research on how to do it first to make it work :D

Thanks again ever so much for all your help with this. Much appreciated.

[Matthew1980]

Hi there tiranto,

Well, I'm pleased as you are all sorted now - yes naming conventions can catch even an experienced coder out from time to time ;-p The trick is to use this on the receiving script:-

echo "<pre>";
print_r($_POST);
echo "</pre>";

Then from this you can see what names you have and assign them from that - I use this all the time when doing form's great way of 'spell checking' $_POST vars.

Yep, looks like your on a decent learning curve, but once you are there the sense of 'I built that' really kicks in and you will be looking for the next project/improvement.

No problem, have fun with the rest of the project - don't forget - we were all beginners at some stage.

Cheers,
MRb