-3

I recently purchased a company with a website that contains a database of different clients. It is built using PHP.

I moved the site from it's existing server (Linux) to my companies server (Windows Server 2008).

I created and imported the databases and connected everything fine.

I can still edit information on existing clients but When I tried so the function to create new entries into the databases stopped working.

I'm positive that no files have been changed and that this was a function that worked on the other server.

Here is the piece of code that enters the information 

$sql = "insert into $DB.cleints (client_id, client_phone) values ($client_id, $client_phone) on duplicate key update client_id='$client_id', client_phone='$client_phone'";

mysql_query($sql);

if (mysql_errno() != 0) {
    echo "Sorry, there was an error adding this client";
    echo "\nsql=$sql\n";
    echo mysql_error();
}
Michael
  • 33
  • 3
  • 8
  • 2
    is the "$DB.cleints" correctly spelled? – heldt Mar 15 '11 at 16:01
  • 1
    Is $DB.cleints an actual table or a typo? and I hope the fields are protected against sql injection. – Richard Adnams Mar 15 '11 at 16:02
  • It echos the errors and I'm sorry about the typo. There is no typo in the code where the issue happens – Michael Mar 15 '11 at 16:06
  • The errors are Sorry, there was an error adding this client sql=insert into databasename.clients (client_id, client_phone) values (1, '18005555555') on duplicate key update client_id=1, client_phone='18005555555' at row 1 – Michael Mar 15 '11 at 16:19
  • the actual sql error message is missing. "at row 1" is only part of it. – Marc B Mar 15 '11 at 16:32
  • Setting it to the old mysql worked mysql_query("set sql_mode = ''"); – Michael Mar 15 '11 at 16:50
  • What are the indexes on your table? I'm assuming that the query should actually be `INSERT INTO $DB.clients (client_id, client_phone) values ($client_id, $client_phone) ON DUPLICATE KEY UPDATE client_phone='$client_phone'`; BTW - ditto above comments about SQL injection. – thetaiko Mar 15 '11 at 16:55
  • setting the sql_mode="" allowed me to get past the errors but didn't actually get enter anything into the database :( – Michael Mar 15 '11 at 18:31

2 Answers2

0

I am a new to mysql and php coding. I faced the same issue and basic method of accessing mysql works perfectly. Here is my code

<?php
$server = "localhost";
$user = "cscs";
$password = "C0va!CaRe";
$database = "cscs";

$link = mysqli_connect($server, $user, $password, $database);

if(!$link){
    echo "Error connecting database, ".mysql_error();
}

?>

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP MySQL Test 1</title>
</head>

<body>

<h2 style="text-align: center;">Database Test 1 - Creating a table manually and updating through form</h2>

<form name="register" id="register" action="" method="post">

    <table width="60%" cellspacing='1' cellpadding="10" border="1">
        <tr>
            <td width="45%" valign="middle" align="left">Full Name</td>
            <td width="55%" valign="middle" align="left"><input type="text" name="full-name" id="full-name"></td>
        </tr>

        <tr>
            <td width="45%" valign="middle" align="left">Email</td>
            <td width="55%" valign="middle" align="left"><input type="text" name="email" id="email"></td>
        </tr>

        <tr>
            <td width="45%" valign="middle" align="left">Phone</td>
            <td width="55%" valign="middle" align="left"><input type="text" name="phone" id="phone"></td>
        </tr>

        <tr>
            <td width="45%" valign="middle" align="left">City</td>
            <td width="55%" valign="middle" align="left"><input type="text" name="city" id="city"></td>
        </tr>

        <tr>
            <td width="45%" valign="middle" align="left">Country</td>
            <td width="55%" valign="middle" align="left"><input type="text" name="country" id="country"></td>
        </tr>

        <tr>
            <td width="45%" valign="middle" align="left">User Name</td>
            <td width="55%" valign="middle" align="left"><input type="text" name="username" id="username"></td>
        </tr>

        <tr>
            <td width="45%" valign="middle" align="left">Password</td>
            <td width="55%" valign="middle" align="left"><input type="text" name="pass" id="pass"></td>
        </tr>

        <tr>
            <td width="45%" valign="middle" align="left">Confirm Password</td>
            <td width="55%" valign="middle" align="left"><input type="text" name="cpass" id="cpass"></td>
        </tr>

        <tr>
            <td valign="middle" align="left" colspan="2"><input type="submit" name="submit" value="Register"></td>

        </tr>

    </table> 

</form>

<?php

if(isset($_POST['submit'])){

    $fullname = $_POST['full-name'];
    $email = $_POST['email'];
    $phone = $_POST['phone'];
    $city = $_POST['city'];
    $country = $_POST['country'];
    $username = $_POST['username'];
    $pass = $_POST['pass'];

    mysqli_select_db($link,'cscs');

    $sql1 = "INSERT INTO userinfo (fullname, email, phone, city, country, username, password) VALUES ('{$fullname}', '{$email}', '{$phone}', '{$city}', '{$country}', '{$username}', '{$pass}')";

    if(mysqli_query($link, $sql1)) {

        echo "Data inserted to database";   

    }

    else {
        echo "Error updating database, ".mysql_error();
    }


}

?>

</body>
</html>
Arun Kumar S
  • 1
0

The errors are Sorry, there was an error adding this client sql=insert into databasename.clients (client_id, client_phone) values (1, '18005555555') on duplicate key update client_id=1, client_phone='18005555555' at row 1

Read the error message you posted.

"duplicate key update client_id=1, client_phone='18005555555'"

Is there already a record in the table with those two values? Is there a unique index on client_id & client_phone?

NuclearDog
  • 142
  • 3