CODEWITHSUNDEEP
phpmysql
user3060463
user3060463

Reputation: 51

UPDATED:[MySQL won't update in Where condition PHP]

I just updated this question. I can't seem to update my database whenever I am putting variable $ecode on my WHERE condition. But when I echo this variable it always echoes its right value.

<?php
    require 'sqlicon.php';
    $q=$_GET['q'];
    $ecode= $_GET['ecode'];
    echo"".$ecode;
    $result=$db->query("UPDATE offset_form SET Approved='".$q."' WHERE Employee_Code='".$ecode."'");
?>

this is the content of sqlicon.php:

<?php
    $db=new mysqli('localhost','root','',dbuser'); //localhost,username,password, dbname
?>

This is where I am getting the date for $q and $ecode: Sorry if it haven't been in mysqli yet.

testingjava.php:

<html>
<title> Offset Requests </title>
<head><link rel="stylesheet" type="text/css" href="up.css"/></head>
<script>
    function Approval() {
        var name;
        name=document.getElementById('ename').textContent;
        if(document.form1.approval[0].checked   true) { 
            alert(name);
            window.location.href = "sqli.php?q=Yes"  + "&ecode=" + name;
        }
    }
</script>
<body>
    <form id="form1" name="form1" method="post" action="testingjava.php"> 
    <?php
        $conn = mysql_connect("localhost","root","");
        if(!$conn)
            echo ("Could not connect");

        mysql_select_db("dbuser",$conn);
        $query=mysql_query("Select * from offset_form where Approved=''");
        while($fetch=mysql_fetch_array($query)) {
            $ecode=$fetch['Employee_Code'];
            //$_SESSION['ecode']=$ecode;
            $ename=$fetch['Employee_Name'];
            $epos=$fetch['Employee_Position'];
            $edpt=$fetch['Employee_Department'];
            $dleave=$fetch['Date_Leave'];
            $dreturn=$fetch['Date_Return'];
            $reason=$fetch['Offset_Reason'];

            echo "".$ecode ."".$ename." ".$epos." ".$edpt." ".$dleave." ".$dreturn." ".$reason;
            echo "<input type='radio' name='approval' onChange='Approval()'>Yes";
            echo "<input type='radio' name='approval'>No";
            echo "<input type='text' name='remarks' size='30'>";
            echo"<hr id='br'></hr>";
            echo"<input type='submit' value='Submit' name='send' onClick='Approval()'>";
        }
    ?>
    </form>
</body>
</html>

I am only testing to manipulate my database when I triggered a radio button.

Upvotes: 0

Views: 208

Answers (5)

Vijay Verma
Vijay Verma

Reputation: 3698

Please debug the value of $q and try to run this code:

session_start();
    $q=$_GET['q'];
    $ecode=$_GET['ecode'];
    $conn = mysql_connect("localhost","root","");
    if(!$conn)
        echo ("Could not connect");

    mysql_select_db("asiantech",$conn);
    echo"".$ecode;
    echo"<br>".$q;
    $sql="update offset_form set Approved ='".mysql_real_escape_string($q)."' where Employee_Code='".$ecode."'";
    //$sql = "INSERT INTO offset_form (Approved) VALUES ('".$ecode."')";
    mysql_query($sql,$conn);

Upvotes: 0

Pebbl
Pebbl

Reputation: 35995

are you should your query is what you want?

One thing that is confusing is the fact that you have this commented out:

"INSERT INTO offset_form (Approved) VALUES ('".$ecode."')"

And then you have this as your update:

"UPDATE offset_form SET Approved = '$q' WHERE Employee_Code = '".$ecode."'"

The values you are using don't tally together. Surely you should have:

"UPDATE offset_form SET Approved = '$q' where Approved = '".$ecode."'"

This is because you are inserting $ecode into the column Approved, but then searching for $ecode in another column called Employee_Code. Perhaps you need to modify your insert statement instead? Either that or $ecode could be just representing two different values at different times?

quotes

The only way switching quotes will make a difference is if your embedded values contain quotes themselves. In which case using the correct escape function will sort the problem. So you are free to use either:

"UPDATE offset_form SET Approved = '$q' where Approved='$ecode'"

or:

"UPDATE offset_form SET Approved = '".$q."' where Approved = '".$ecode."'"

or:

'UPDATE offset_form SET Approved = "'.$q.'" where Approved = "'.$ecode.'"'

but not:

'UPDATE offset_form SET Approved = "$q" where Approved = "$ecode"'

either of the first three should not make a difference.

further things to do

backticks

As a rule I always write my queries escaping table and column names using backticks, just to make sure I'm not accidentally using a reserved word:

"UPDATE `offset_form` SET `Approved`='$q' WHERE `Employee_Code`='".$ecode."'"
double check your dataset

Make certain that the same query you are trying to run in PHP, works inside your dbms. This involves echoing the query out in PHP and then executing it via PHPMyAdmin, Navicat, or whatever you use to access your database outside of coding. For example, a query with hard-coded values, if this doesn't work you have a logic problem in your query or database design that has nothing to do with PHP:

"UPDATE offset_form SET Approved='13' WHERE Employee_Code='12'"
check your white space

Sometimes queries that seem they should be working are having problems because your column values contain accidental invisible white space. If so, they would only be selectable using something like:

"UPDATE offset_form SET Approved='$q' WHERE Employee_Code LIKE '%".$ecode."%'"
check user privileges

Make certain your MySQL user has the ability to perform the type of query you are attempting, this means allowing SELECT, INSERT and UPDATE queries.

disclaimer

As others have already stated, you should upgrade to non deprecated database access methods. If not, you should at least be using mysql_real_escape_string to better protect against malicious intent.

Upvotes: 0

uvais
uvais

Reputation: 416

if password is set to your dbms the provide the third param passwrod

$conn = mysql_connect("localhost","root","<passwrod>");

or you can leave it blank if passwrod is not set. and try this

$sql="update offset_form set Approved =$q where Employee_Code=$ecode";

or

$sql="update offset_form set Approved ='".$q."' where Employee_Code='".$ecode."'";

note: double quotes will parse the php variable , most probably there is problem in the manner of quotes you are using.

Upvotes: 0

Mark Gilchrist
Mark Gilchrist

Reputation: 2032

1) you should be using mysql_real_escape_string($_GET[]) or someone with inject a mysql command into you system like DROP TABLE which will be the end of your database.

2)secondly I would move over to using PHP PDO it is more secure and it is faster (by a long way).

3) change your scond to last line from

mysql_query($sql,$conn);

to

mysql_query($sql,$conn) or die(mysql_error()." _____is the string correct? ".$sql);

then is should echo out any errors, if you post the echoed error we can probably fix it

having looked at it I am guessing the problem is you have missed the .. around the $q, so the $sql contains the string "$q" rather than the string assigned to the variable $q

try this

$sql="update offset_form set Approved ='".$q."' where Employee_Code='".$ecode."'");

Upvotes: 3

404 Not Found
404 Not Found

Reputation: 1223

try this way..

     $sql=("update offset_form set Approved ='".$q."' where Employee_Code='".$ecode."'");

always try to echo your query and see what's going wrong with your query..

Upvotes: 0

Related Questions