PHP Database Script [+REP]

[MrPinkPanther]

I have a database with 3 rows: Name, x-cord, y-cord. I need a script to handle all of these.


My external application sends the Name, x-cord and y-cord to the PHP file via POST
The PHP file saves Name, x-cord and y-cord to the database, overwriting the current x-cord and y-cord that goes with Name.
Once the x-cord and y-cord are saved into the Database they need to be written into name.txt, again overwriting anything in there.
For example:
Name = John
x-cord = 2
y-cord = 4

So John, 2 and 4 are saved in the database columns Name, x-cord and y-cord respectively. Once saved into the database x-cord and y-cord are written out into John.txt. John.txt then looks like

x-cord = 2
y-cord = 2

If anyone could do this fairly easy task then I will love you for eternity and give you +REP too.

[MrPinkPanther]

Anyone??? I really really need this.

Edited by Johno! (Forum Moderator): Please do not multiple post, thanks! :)

[IDontKnow]

Sheez, what is Georgie Porgie working on now!? :)

[Chippiewill]

I'll try and whip up something that'll send it to the DB and then I will mash together something that will send it to the file, however I am going out soon so don't expect it till tomorrow :/

Edit: I am going to do it from a modified register file from tech tut's Naresh Usersystem, you could try yourself and learn something...

Edit2: Are the details going to be from a form? If so then you may need to secure it a bit...


Only does DB for now... Havn't tested it either, should work though as I modified an existing script of mine...


<?php
$conn = mysql_connect("localhost","[Username]","[Password]");
mysql_select_db([DB_Name]) or die(mysql_error());
//fill in the above lines where there are capital letters.
$name = $_POST[name];
$xpos = $_POST[x];
$ypos = $_POST[y];

$query = mysql_query("INSERT INTO [Table Name] (name, x, y) VALUES('$name','$xpos','$ypos')");
echo('[Message to Echo upon completion]')
?>

[MrPinkPanther]

I'll try and whip up something that'll send it to the DB and then I will mash together something that will send it to the file, however I am going out soon so don't expect it till tomorrow :/
Ok thanks! I literally love you lol. If you ever need anything done then just ask.


Edit: I am going to do it from a modified register file from tech tut's Naresh Usersystem, you could try yourself and learn something..
The thing is, I rarely use PHP so there isnt really much point me learning it. Atleast not at this current time.



Edit2: Are the details going to be from a form? If so then you may need to secure it a bit...
All of that can be handled by me. I dont need a form created or anything, it will simply be sent to the PHP file via POST.

[Chippiewill]

Ok basically finished it, the syntax may be wrong so someone may want to quickly check it :P



<?php
$conn = mysql_connect("localhost","[Username]","[Password]");
mysql_select_db([DB_Name]) or die(mysql_error());
//fill in the above lines where there are capital letters.
$name = $_POST[name];
$xpos = $_POST[x];
$ypos = $_POST[y];
$query = mysql_query("INSERT INTO [Table Name] (name, x, y) VALUES('$name','$xpos','$ypos')");


$myFile = $name'.txt';
$fh = fopen($myFile, 'w') or die("can't open file");
$stringData = 'x-cord = ' $xpos \n;
fwrite($fh, $stringData);
$stringData = 'y-cord = ' $ypos \n;
fwrite($fh, $stringData);
fclose($fh);

echo('[Message to Echo upon completion]')

?>


Also by form I meant, is the end user going to specify this information, if so then it needs to be secured so that people can't use JS injections e.t.c.

[MrPinkPanther]

Whoa, thanks! Very speedy! Does that overwrite whats in the database if it already exists? Oh and replace whats in the textfile?

[Chippiewill]

Erm, for the DB you need to use IDs to overwrite it I think, not to sure. I will have a quick think about it.

Edit: I think if I get it to delete the existing row then it'll work...

Edit2: Try this....



<?php
$conn = mysql_connect("localhost","[Username]","[Password]");
mysql_select_db([DB_Name]) or die(mysql_error());
//fill in the above lines where there are capital letters.
$name = $_POST[name];
$xpos = $_POST[x];
$ypos = $_POST[y];
mysql_query("DELETE FROM [Table Name] WHERE name='$name'")
$query = mysql_query("INSERT INTO [Table Name] (name, x, y) VALUES('$name','$xpos','$ypos')");


$myFile = $name'.txt';
$fh = fopen($myFile, 'w') or die("can't open file");
$stringData = 'x-cord = ' $xpos n;
fwrite($fh, $stringData);
$stringData = 'y-cord = ' $ypos n;
fwrite($fh, $stringData);
fclose($fh);

echo('[Message to Echo upon completion]')

?>

[Robbie]

Just use UPDATE in MySQL.

[MrPinkPanther]

Thanks for all the help 00Chips, like I said, if you ever need anything then just PM me.


Just use UPDATE in MySQL.
How exactly would you use that in the context of this?

One final question while I'm here for something else I'm working on, how would I edit the last bit of code so it doesn't write over whats in the text file and just adds it to the bottom?

[Chippiewill]

You could add the current contents of the file to the top when it is being rewritten I guess... I am going in about 2 minutes so no time :/

[RichardKnox]

Not tested, hopefully should work. Updates or inserts into DB and overwrites or creates a new file for the name. Change any form variables and DB stuff, I also assumed you'd have a config file or something you could include:


<?php

if($_POST['details']) {

$name = $_POST['name'];
$x = $_POST['x_coord'];
$y = $_POST['y_coord'];

if(mysql_num_rows(mysql_query("SELECT * FROM `db_name` WHERE `name` = '$name'")) > 0) {

$upd = mysql_query("UPDATE `db_name` SET `x_cord` = '$x' AND `y_cord` = '$y' WHERE `name` = '$name'") or die(mysql_error());

}

else {

$ins = mysql_query("INSERT INTO `db_name` (`name`, `x_cord`, `y_cord`) VALUES ('$name', '$x', '$y')") or die(mysql_error());

}

$file = $name . '.txt';
$f_op = fopen($file);
$string = 'x_cord = ' . $x . '\n';
$string .= 'y_cord = ' . $y;
fwrite($f_op, $string);
fclose($f_op);

}

?>

[MrPinkPanther]

Not tested, hopefully should work. Updates or inserts into DB and overwrites or creates a new file for the name. Change any form variables and DB stuff, I also assumed you'd have a config file or something you could include:


<?php

if($_POST['details']) {

$name = $_POST['name'];
$x = $_POST['x_coord'];
$y = $_POST['y_coord'];

if(mysql_num_rows(mysql_query("SELECT * FROM `db_name` WHERE `name` = '$name'")) > 0) {

$upd = mysql_query("UPDATE `db_name` SET `x_cord` = '$x' AND `y_cord` = '$y' WHERE `name` = '$name'") or die(mysql_error());

}

else {

$ins = mysql_query("INSERT INTO `db_name` (`name`, `x_cord`, `y_cord`) VALUES ('$name', '$x', '$y')") or die(mysql_error());

}

$file = $name . '.txt';
$f_op = fopen($file);
$string = 'x_cord = ' . $x . '\n';
$string .= 'y_cord = ' . $y;
fwrite($f_op, $string);
fclose($f_op);

}

?>
Works like a dream, cheers :)