I'm trying to create a script that updates data from a table in one database to a table in another database. I'm self-made and my abilities are very limited. I'm sure there's a better way of doing this but it might be out of my reach. Anyway, this is how I'm trying to do it.
The first document selects the data from the table I want to get the information from. It contains a link that allows me to select the row I want to copy. This link basically creates a form with the information I want to copy into the second database. The idea is that when I press submit, it sends the information to the second database and it updates the fields.
The problem I'm having is that, even though it says it has executed the query successfully, it simply doesn't update the second database.
Can anyone see why this is not working?
These are the scripts I'm using:
TEST-DISPLAY.php
<?php
$host="localhost"; // Host name
$username="username"; // Mysql username
$password="password"; // Mysql password
$db_name="db1"; // Database name
$tbl_name="table1"; // Table name
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from mysql </strong> </td>
</tr>
<tr>
<td align="center"><strong>Uid</strong></td>
<td align="center"><strong>User Name</strong></td>
<td align="center"><strong>Password</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Update</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td><? echo $rows['uid']; ?></td>
<td><? echo $rows['user']; ?></td>
<td><? echo $rows['password']; ?></td>
<td><? echo $rows['email']; ?></td>
// link to update.php and send value of id
<td align="center"><a href="http://soundsurgery.co.uk/main/TEST5/TEST-EDIT.php?uid=<? echo $rows['uid']; ?>">update</a></td>
</tr>
<?php
}
?>
</table>
</td>
</tr>
</table>
<?php
mysql_close();
?>
TEST-EDIT.php
<?php
$host="localhost"; // Host name
$username="username"; // Mysql username
$password="password"; // Mysql password
$db_name="db1"; // Database name
$tbl_name="table1"; // Table name
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
// get value of id that sent from address bar
$uid=$_GET['uid'];
// Retrieve data from database
$sql="SELECT * FROM $tbl_name WHERE uid='$uid'";
$result=mysql_query($sql);
$rows=mysql_fetch_array($result);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="TEST-EXEC.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td> </td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"><strong>Uid</strong></td>
<td align="center"><strong>User Name</strong></td>
<td align="center"><strong>Password</strong></td>
<td align="center"><strong>Email</strong></td>
</tr>
<tr>
<td> </td>
<td align="center">
<input name="uid" type="text" id="uid" value="<? echo $rows['uid']; ?>">
</td>
<td align="center">
<input name="user" type="text" id="user" value="<? echo $rows['user']; ?>">
</td>
<td align="center">
<input name="password" type="text" id="password" value="<? echo $rows['password']; ?>" size="15">
</td>
<td>
<input name="email" type="text" id="email" value="<? echo $rows['email']; ?>" size="15">
</td>
</tr>
<tr>
<td> </td>
<td>
<input name="uid" type="hidden" id="uid" value="<? echo $rows['uid']; ?>">
</td>
<td align="center">
<input type="submit" name="Submit" value="Submit">
</td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>
<?php
// close connection
mysql_close();
?>
TEST-EXEC.php
<?php
$host="localhost"; // Host name
$username="username"; // Mysql username
$password="password"; // Mysql password
$db_name="db2"; // Database name
$tbl_name="table2"; // Table name
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
// update data in mysql database
$sql="UPDATE $tbl_name SET uname='$user', password='$password', email='$email' WHERE uid='$uid'";
$result=mysql_query($sql);
// if successfully updated.
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='TEST-DISPLAY2.php'>View result</a>";
}
else {
echo "ERROR";
}
?><!doctype html>
<html>
<head>
<meta charset="UTF-8">
<title>Untitled Document</title>
</head>
<body>
</body>
</html>
TEST-DISPLAY2.php is the same as TEST-DISPLAY.php with the difference that it displays results for the second table.
Eugenie, in TEST-EDIT.PHP you created a form in the script TEST-EXEC.PHP by the name of "form1" which uses the POST method. Inside of form1 you are seeding the variables username, uid, password and email. When you submit form1 it TEST-EXEC.PHP needs to capture the variables that you seeded. You do this by:
$username = $_POST['username'];
$uid = $_POST['uid'];
$password = $_POST['password'];
$email = $_POST['email'];
$_POST is a predefined variable that gets the values from a form sent with method="post". Put these at the top of TEST-EXEC.PHP. You can also use $user = mysql_real_escape_string($_POST['user']); like another person mentioned so the values in variable user are escaped.
I am going to echo others and tell you that you have some serious security issues happening with this code because you are not paying attention to your strings. If you do not research the security dangers people are mentioning then if you use this code in production you are opening up the entire database and webserver to malicious code. And it's just not worth it.
Here's something you need, it is a function that validates email addresses to make sure they do not contain bad things.
function validEmail($email)
{
$isValid = true;
$atIndex = strrpos($email, "@");
if (is_bool($atIndex) && !$atIndex)
{
$isValid = false;
}
else
{
$domain = substr($email, $atIndex+1);
$local = substr($email, 0, $atIndex);
$localLen = strlen($local);
$domainLen = strlen($domain);
if ($localLen < 1 || $localLen > 64)
{
// local part length exceeded
$isValid = false;
}
else if ($domainLen < 1 || $domainLen > 255)
{
// domain part length exceeded
$isValid = false;
}
else if ($local[0] == '.' || $local[$localLen-1] == '.')
{
// local part starts or ends with '.'
$isValid = false;
}
else if (preg_match('/\\.\\./', $local))
{
// local part has two consecutive dots
$isValid = false;
}
else if (!preg_match('/^[A-Za-z0-9\\-\\.]+$/', $domain))
{
// character not valid in domain part
$isValid = false;
}
else if (preg_match('/\\.\\./', $domain))
{
// domain part has two consecutive dots
$isValid = false;
}
else if
(!preg_match('/^(\\\\.|[A-Za-z0-9!#%&`_=\\/$\'*+?^{}|~.-])+$/',
str_replace("\\\\","",$local)))
{
// character not valid in local part unless
// local part is quoted
if (!preg_match('/^"(\\\\"|[^"])+"$/',
str_replace("\\\\","",$local)))
{
$isValid = false;
}
}
// if ($isValid && !(checkdnsrr($domain,"MX") || ?checkdnsrr($domain,"A")))
if ($isValid && !(checkdnsrr($domain,"MX") || checkdnsrr($domain,"A")))
{
// domain not found in DNS
$isValid = false;
}
}
return $isValid;
}
You're never setting the variables $user, $password, $email, or $uid in TEST-EXEC.php. You need:
$uid = mysql_real_escape_string($_GET['uid']);
$user = mysql_real_escape_string($_POST['user']);
$password = mysql_real_escape_string($_POST['password']);
$email = mysql_real_escape_string($_POST['email']);
Notice that you use $_GET for parameters from the URL, $_POST for inputs from the form (unless the form uses method="get").