CODEWITHSUNDEEP
phpsql
Tina
Tina

Reputation: 312

not updating the sql database

i wrote the following code,but its not updating the database,,its a part of a script and it cease to work..cant find a way around it .. need suggestions

<?php
$link = mysql_connect('xxxxxxxx');
if (!$link) {
die('Could not connect: ' . mysql_error());
}

mysql_select_db("xxx", $link);


$usernames='aneeshxx';
echo $usernames;

$update = "INSERT sanjana SET $name ='$usernames'";
mysql_query($update, $link);

$update1 = "INSERT INTO sanjana (name)VALUES ($usernames)";
mysql_query($update1, $link);


?>

Upvotes: 0

Views: 105

Answers (4)

Harald Brinkhof
Harald Brinkhof

Reputation: 4455

$update = "INSERT sanjana SET $name ='$usernames'";

this probably is meant as an UPDATE statement, so for an update it should be

$update = "UPDATE sanjana set name = '$usernames'";

I put name and not $name due to your second query and not seeing $name being defined anywhere. Be aware that this will change the value in the column name of every row in the sanjana table to the value of $usernames, normally a statement such as this gets limited by conditions, e.g. WHERE userid = 33

$update1 = "INSERT INTO sanjana (name) VALUES ($usernames)";

for an INSERT statement it needs to have the values quoted so

$update1 = "INSERT INTO sanjana (name) VALUES ('$usernames')";

Be wary that this way of putting variables directly into your query string makes you vulnerable to SQL injection, to combat this please use the PDO or mysqli extensions, they both protect you from injection by providing you with prepared statements ; plain old mysql_* is not recommended for use anymore.

using pdo you'd use prepared statements like this

<?php
// we got $usernames from wherever you define it

$pdo = new PDO('mysql:dbname=mydb;host=localhost','username','password');

// to insert
$statement = $pdo->prepare('INSERT INTO `sanjana` (name) VALUES (:name)');
// the following replaces :name with $usernames in a safe manner, defeating sql injection
$statement->bindParam(':name',$usernames);
$statement->execute(); // it is done


// to update
$statement = $pdo->prepare('UPDATE `sanjan` SET `name` = :name');
$statement->bindParam(':name',$usernames);
$statement->execute(); // it is done

so as you can see protecting your code from malicious input is not hard and it even makes your SQL statements a lot easier to read. Did you notice that you didn't even need to quote your values in the SQL statement anymore? Prepared statements take care of that for you! One less way to have an error in your code.

Please do read up on it, it will save you headaches. PDO even has the advantage that it's database independent, making it easier to use another database with existing code.

Upvotes: 3

paulsm4
paulsm4

Reputation: 121599

Problem 1: use the correct "insert into" (create new record) vs. "update" (modify existing record)

Problem 2: It's good practice to create your SQL string before you call mysql_query(), so you can print it out for debugging

Problem 3: It's also good practice to detect errors

EXAMPLE:

<?php
  $link = mysql_connect('xxxxxxxx')
    or die('Could not connect: ' . mysql_error());

  mysql_select_db("xxx", $link);

  $usernames='aneeshxx';
  $sql = "INSERT INTO sanjana (name) VALUES ('" . $usernames + ")";
  echo "sql: " . $sql . "...<br/>\n";
  mysql_query($sql, $link)
    or die(mysql_error());

Upvotes: 2

SIFE
SIFE

Reputation: 5695

The right update sql clause is like so:

UPDATE table
SET column = expression;

OR

UPDATE table
SET column = expression
WHERE predicates;

SQL: UPDATE Statement

Your query should be like this:

$update = "UPDATE sanjana SET $name ='$usernames'";
mysql_query($update, $link);

Of course you need to specify a row to update (id), other wise, the whole table will set column $name to $usernames.

UPDATE:

Because you are inserting a data in empty table, you should first execute $update1 query then execute $update query. UPDATE clause will make no change/insert on empty table.

Upvotes: 2

David Cheung
David Cheung

Reputation: 858

You have INSERT keyword for your update SQL, this should be changed to UPDATE:

$update = "UPDATE sanjana SET $name ='$usernames'";

Upvotes: 0

Related Questions