PHP - Php/mysql Update Problem

I have this query:

Code: [Select]
$sql3 = "UPDATE users SET hours = '$newHours', pay = '$newPay', virtualLocation = '$arrival', expDate = '$expDate' WHERE pilotID = '$pilotid'";

I've echoed it out and all the variables put out the correct values. The query works except for the pay section of it. It will update the hours, virtual location, and expiration date but it will  not update the pay. It inputs 1.00 when the value echoed in the query is not 1.00. I'm not sure if, because it is a decimal, it changes how the query works. Any help? Thanks.

So there is this code I have here :  http://pastebin.com/rwcN7FeJ and I need to find a way to incorporate this code that user Barand gave me Code: [Select]
$referName = mysql_real_escape_string($gateway_data['refer']);
$query = "UPDATE users a
        INNER JOIN users b ON a.refer = b.nick
        SET a.refer = '', b.balance = b.balance + 5
        WHERE b.refer = '$referName'";
mysql_query($query);
But I havent the damnedest how to do that.  If anyone could help me at all, it would be appreciated.

Code: [Select]
<?php
$querySelect = mysql_query("SELECT * FROM `G4V_Videos` WHERE `counter`");
$row = mysql_fetch_assoc($querySelect);
$counter = $row['counter'];

if (empty($counter)) {

$counter = 1;
$insert = mysql_query ("INSERT INTO counter VALUES ('counter')");
$printid = (print $data['id']);
$printlink = (print $data['link']);
}

$add = $counter+1;
$insertNew = mysql_query("UPDATE G4V_Videos SET counter=('$add') WHERE id = ('$printid') AND link = ('$printlink')") or die (mysql_error());
echo "Video Views";
echo ":";
echo" ";
echo"<br />";
echo "$counter";
?>
It shows the same amount of hits on everyvideo instead of just the one video with the id
www.game4vids.com

I have made this, although i can not seem to the status...

changeapp.php
Code: [Select]
<form method="post" name="memberadd" action="change_app_complete.php">

<label>Name:</label>
<select name="member">
<?php
$con = mysql_connect("host","user","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("database",$con);
$sqlquery="SELECT * FROM `application` Order By Name";
$result=mysql_query($sqlquery,$con);
while($row=mysql_fetch_array($result))
{
echo "<option value='".$row['ID']."'>".$row['Name']." (".$row['Status'].")</option>";
}
?>
</select>
<br>

<label>Status:</label>
<select name="Status">
<option value="PENDING">PENDING</option>
<option value="ACCEPTED">ACCEPTED</option>
<option value="DENIED">DENIED</option>
<br>
<input type="submit" value="submit" />
</form>
change_app_complete.php
Code: [Select]
<?php
$status=$_POST['Status'];

$con = mysql_connect("host","user","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("database",$con);

$sql="UPDATE application SET Status = '$status' WHERE Name= '$member'";
if(mysql_query($sql,$con))
{
echo 'Status Changed.<br /><a href="../applications.php">Return To Members List</a>';
}
else
{
die('Could not submit: ' . mysql_error());
}
mysql_close($con);

?>
What is wrong?

Code: [Select]
if(isset($_POST['npassword']) and ($_POST['cpassword'])){
$npassword = $_POST['npassword'];
$cpassword = $_POST['cpassword'];
$username = $_SESSION['SESS_login'];
if($npassword==$cpassword){
$password = md5($npassword);
$lol = mysql_query("UPDATE `members` SET  `passwd` = '$password' WHERE  `login`= '$username';");
echo"Password Changed!";
}
}

It echos password changed, but its not updating the password. If I run it in phpmyadmin it works fine... So i'm really stuck.

So i have this following code that doesn't work

this is what i have so far but i cant get it to update the data

update page

<?php
$host="localhost"; // Host name
$username="**"; // Mysql username
$password="*****"; // Mysql password
$db_name="manews"; // Database name
$tbl_name="Shows"; // 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
$id=$_GET['id'];


// Retrieve data from database
$sql="SELECT * FROM $tbl_name WHERE id='$id'";
$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="update_ac.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>&nbsp;</td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
</tr>
<tr>
<td align="center">&nbsp;</td>
<td align="center"><strong>Day</strong></td>
<td align="center"><strong>Month</strong></td>
<td align="center"><strong>Year</strong></td>
<td align="center"><strong>Song</strong></td>
<td align="center"><strong>Data</strong></td>
</tr>
<tr>
<td>&nbsp;</td>
<td align="center"><input name="Day" type="text" id="Day" value="<? echo $rows['Day']; ?>"></td>
<td align="center"><input name="Month" type="text" id="Month" value="<? echo $rows['Month']; ?>"></td>
<td align="center"><input name="Year" type="text" id="Year" value="<? echo $rows['Year']; ?>" size="15"></td>
<td align="center"><input name="Song" type="text" id="Song" value="<? echo $rows['Song']; ?>"></td>
<td align="center"><input name="Data" type="text" id="Data" value="<? echo $rows['Data']; ?>" size="15"></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>"></td>
<td align="center"><input type="submit" name="Submit" value="Submit"></td>
<td>&nbsp;</td>
</tr>
</table>
</td>
</form>
</tr>
</table>

<?

// close connection
mysql_close();

?>

update script

<?php
$host="localhost"; // Host name
$username="**"; // Mysql username
$password="**"; // Mysql password
$db_name="manews"; // Database name
$tbl_name="Shows"; // 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 Day='$Day', Month='$Month', Year='$Year', Year='$Song', Year='$Data' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated.
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}

else {
echo "ERROR";
}

?>

Is there something seriously wrong with this code that im missing ?

I have written it at least 10-12 different ways and it will NOT update my db..

Code: [Select]
<?php 
mysql_connect('localhost','root','');
mysql_select_db('myDB');

$dates = $_POST['date']; 
$store = $_POST['store'];  // these actually display the correct information

mysql_query = ("UPDATE Agency Stores - Table 1 SET date = '".$date."' WHERE F3 = '".$store."' ");

?>


there are several other columns in the table.  I have tried to put them in as the value they already have and it still didnt work...

any ideas ?

I am trying to update the content of my website which is all stored in a MySQL database.

Currently on the page I am displaying all the data stored in the database using MySQL SELECT and a while loop. Using JavaScript when the text is clicked the element changes into a text box allowing the text to be edited.

What I want to do is be able to change the data in the text box and then store it into the database updating the current data.

Code: [Select]
<form method="post" action="editindex.php">
<?php
$sql = "SELECT * FROM home ORDER BY id ASC";
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)){
echo "<span id=\"itm1\" onclick=\"exchange(this);\">" . $row['title'] . "</span><input ondblclick=\"exchange(this);\" id=\"itm1b\" class=\"replace\" type=\"text\ name=\"title\">";?>
<input type="submit" value="Save" name="submit">
<input type="hidden" name="id" value="<?php  $row['id'] ?>"
</form>
<?php 
if (isset ($_POST['submit'])) {
$update = "UPDATE home SET title=".$_POST['title']." WHERE id=".$_POST['id']."";
mysql_query($update);
}
?>

I have more text being output from the while loop below but I just want to get it working on the title first.

Any ideas of why the update is not working?
 

So, I've just recently gotten into the more advanced web scripting after writing static HTML pages for years and I must say it's pretty exciting.  I've finally got my home LAMP up and running and I'm really excited about learning PHP and getting into dynamic web content.  One of the areas that I'm really interested in is writing applications for my work and that's one of the first things I've been getting into first.  I have some PHP code below that is giving me fits though, I'm afraid.

MySQL server version: 5.1.49-1ubuntu8.1 (Ubuntu)

My goal:
I have an HTML page that has a text input, a drop down box and a submit button.  The user inputs a username into the text field, and chooses an option from the drop down box and hits the submit button.  I would like for a table holding member information to be updated to reflect the new status based on the user input from the forms.  My table, named 'members', has the following fields:


id (int) (primary)
name (varchar)
username (varchar)
department (varchar)
type (varchar)
status (int)


The status is a 0 or a 1.  Effectively a True/False field.

Here's the code in question:


<?php
// Set variables to get the username and the away/here status
// also trim the username variable to ensure there are no inadvertent extra whitespace characters at the 
// beginning or end
$username=$_POST["username"];
$username=trim($username);
$status=$_POST["statuschange"];

// Check to ensure the username had a value input
// This should be ran before the connection to the database is made to lessen impact on the web server
// This is a temporary method, the production version should have a JavaScript function to check form input before
// being passed on to the web server
if (!$username)
{
echo "No username was submitted, please try again.  Redirecting to main page.";
echo "<meta http-equiv='refresh' content='3;url=/ns_index.php' />";
exit;
}

// Set variables to connect to the mysql server and to access the database
// root user is for development purposes only.  The production version should have a user setup
// specifically for accessing the database with restricted privileges
$ns_dev_conn=mysql_pconnect("localhost","mysql","mysqlpwd") or die("Could not connect: ".mysql_error());
$dbvar=mysql_select_db("dbname",$ns_dev_conn) or die("Could not select database: ".mysql_error());

// Set variables for the SQL statement and results
$statusaway="0";
$statushere="1";

// Create the SQL statements based on the status being either away or here
// This method of generating the SQL statement was used based on recommendation by: http://www.php.net/
if ($status=="away")
{
$status_query=sprintf("UPDATE `members` SET `status`='%s' WHERE `username`='%s'",mysql_real_escape_string($statusaway),mysql_real_escape_string($username));
}
else
{
$status_query=sprintf("UPDATE `members` SET `status`='%s' WHERE `username`='%s'",mysql_real_escape_string($statushere),mysql_real_escape_string($username));
}

// Set the variable to determine the result of the query
$result=mysql_query($status_query,$ns_dev_conn) or die("Query failed: ".mysql_error());

// Determine if any rows were affected
$num_rows=mysql_affected_rows($result, $ns_dev_conn) or die("Hrrmm. . .<b>".$username."</b>'s status could not be changed to <b>".$status."</b>.  Please go back and try again.  MySQL Error: ".mysql_error());

// This is a development only block of code
// Echo all the variables to ensure the proper values are being passed
// If these variables aren't being echoed one of the die() functions was passed.  
// Compare the error message received to the other die() functions to determine what is invalid
// To get the variable values comment out the bad expression
echo "Username: ".$username;
echo "<br />Status (char): ".$status;
echo "<br />Status away (int): ".$statusaway;
echo "<br />Status here (int): ".$statushere;
echo "<br />Server Connection Link ID: ".$ns_dev_conn;
echo "<br />Database Selection ID: ".$dbvar;
echo "<br />SQL Statement: ".$status_query;
echo "<br />Result: ".$result;
echo "<br />Number of Affected rows: ".mysql_affected_rows($result);

/* This should be uncommented for production version
// If $num_rows didn't die than the database was updated
// Update the user that the query was successful and then redirect them back to the main page
echo $username."'s status was updated to ".$status.".  You will be redirected to the main page now.";
echo "<meta http-equiv="refresh" content="4;url='/ns_index.php'" />";
*/
?>


So, I've tried a bunch of different things, including echoing back all the variables to ensure the connection and SQL statements are correct, in addition I've taken the echoed SQL statement and copied it into phpmyadmin and the statement works with no errors!  I've also looked at the mysql_info() return values and the row is being matched, it's just not being changed or affected.  Right now when I activate this script it's returning the error associated with die() on mysql_affected_rows().  This is telling me that everything is working, except for the table actually being updated.  Also, just for development and testing purposes I've been using the root MySQL user so I'm pretty sure it's not a privilege issue.

If anybody could provide some insight into this problem I'd be most appreciative.

Thanks.

Hi,   Im having a problem and I can't seem to figure it out or find anything on the net.   If I use the following code the script successfully updates every row in the table:

mysqli_query($con,"UPDATE Ads SET Ads_LocalArea='Stroud'");

However if I try updating the table using the WHERE clause in any of the combinations below nothing happens.

mysqli_query($con,"UPDATE Ads SET Ads_LocalArea='Stroud' WHERE Ads_ID=$DBROWID");

----------------------------------------------------------------------   My Script:

mysqli_query($con,"INSERT INTO Ads (Ads_ID, Ads_AID, Ads_Title)
VALUES ('', '$Polished_AdRef', '$Polished_AdTitle')");

$DBROWID = mysqli_insert_id($con);

mysqli_query($con,"UPDATE Ads SET Ads_AID='Stroud' WHERE Ads_ID=$DBROWID");

// TRIED THESE TOO
// mysqli_query($con,"UPDATE Ads SET Ads_AID='Stroud' WHERE Ads_ID='$DBROWID'");
// mysqli_query($con,"UPDATE Ads SET Ads_AID='Stroud' WHERE Ads_ID='5'");

Does any one know where I am going wrong?

Hello there, here is my page I'm having problem with..
Code: [Select]
<?php
session_start();
require_once 'database.php';
include("header.php");
$username="***";
$password = "****"; 
$hostname = "localhost"; 
$database = "***";      
$id = $_GET['id'];
// Connect to MySQL...
$conn = mysql_connect($hostname, $username, $password)      or die("Connecting to MySQL failed");  
mysql_select_db($database, $conn)     or die("Selecting MySQL database failed"); 

// Run our query, see if session username exists in session field...
$sql="select username,gang,gangid from user where username='{$_SESSION['user']}' limit 1";
$result=mysql_query($sql,$conn);

// Parse our results into $data (as an associative array)...
$data=mysql_fetch_assoc($result);
$gangsql="select id,to,gang,gangid from ganginv where id='$id' limit 1";
$gangresult=mysql_query($gangsql,$conn);

// Parse our results into $data (as an associative array)...
$data2=mysql_fetch_assoc($gangresult);   // This is line 24
if ( $data['username'] == $data2['to'] ){
$gang = $data2['gang'];
$gangid = $data2['gangid'];
$insertelse="UPDATE user SET gang='$gang', gangid='$gangid' WHERE username='{$_SESSION['user']}'";
$insertresultelse=mysql_query($insertelse); 
}else{
echo "This invite was not for you.";
}
?>

What this does is it performs to queries to two different tables. Then it matches the usernames ( to make sure it's the same) and then update the table "user" to the column "gang" and "gangid".  Here is the error it gives me:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home2/******/ganginv.php on line 24
This invite was not for you.


Line 24 is marked. I appreciate all the help/info you have to offer!

Hi,

I'm only just getting started with php and mysql but I like to go off the deep end.

something is wrong with my script.

it seems to work up untill it comes to the UPDATE part. It wont actually update the table and the mysql_affected_rows bit returns -1 I've searched for an explanation and can't find one.

Can someone tell me what I'm doing wrong

Thanks.

//connect to mysql
$con = mysql_connect("xxx","yyy","zzz");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
//use the right database
mysql_select_db("foo_bar");

//select all cancelled tickets
$status_query = "SELECT * FROM jos_vm_orders where order_status = 'X'";
$status_result = mysql_query($status_query) or die(mysql_error());

while ($newArray = mysql_fetch_array($status_result, MYSQL_ASSOC)) {
                $order_id = $newArray['order_id'];


$quantity_query = "SELECT * FROM jos_vm_order_item where order_id = $order_id";
$quantity_result = mysql_query($quantity_query) or die(mysql_error());

        while ($newArray2 = mysql_fetch_array($quantity_result, MYSQL_ASSOC)) {
                $product_quantity = $newArray2[product_quantity];
                $order_item_sku = $newArray2[order_item_sku];

echo ("$order_id cancelled $product_quantity tickets sku was $order_item_sku \n");

mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + $product_quantity
WHERE product_sku = $order_item_sku AND STR_TO_DATE($order_item_sku, '%Y-%d-%m')
$quantity_query = "SELECT * FROM jos_vm_order_item where order_id = $order_id";
$quantity_result = mysql_query($quantity_query) or die(mysql_error());

        while ($newArray2 = mysql_fetch_array($quantity_result, MYSQL_ASSOC)) {
                $product_quantity = $newArray2[product_quantity];
                $order_item_sku = $newArray2[order_item_sku];

echo ("$order_id cancelled $product_quantity tickets sku was $order_item_sku \n");

mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + $product_quantity)
WHERE product_sku = $order_item_sku AND STR_TO_DATE($order_item_sku, '%Y-%d-%m')
>= CURDATE()");
echo ("$product_quantity tickets released into $order_item_sku \n");
echo ("Affected rows ");
echo (mysql_affected_rows());
echo ("\n");

}
}
mysql_free_result($status_result);
mysql_free_result($quantity_result);
mysql_close($con);
?>

Hi guys, I am trying to UPDATE some records on a mySQL database but can't seem to find out why it is not working. This is my code.


Code: [Select]
<?php


$latitude = $_POST['lat_location'];
$longitude = $_POST['long_location'];
$unique_ID = $_POST['unique_ID'];

include('connect2.php');



$query = mysql_query("SELECT * FROM user_location WHERE unit = '$unique_ID'");

$numrows = mysql_num_rows($query);


if ($numrows == 1) {



$query2="UPDATE user_location SET lat = '$latitude', long = '$longitude' WHERE unit = '$unique_ID'";
mysql_query($query2);



$test = "matches";



} else {

mysql_query("INSERT INTO user_location VALUES ('','$unique_ID','$latitude','$longitude')");

$test = "not match";

}

echo $test . "<br />";
echo $numrows;

?>
The script receives the data via the POST method and assigns it to variables. Then I query the database for one of those variables and check how many results are found. If 0 results are found then a new record is created on the database, but if there is 1 record found then the record that is found has to be UPDATED. When the result is 0 the scripts creates the new record fine, but if the result is 1 it doesn't update. I just can't figure out why. Any help will be greatly appreciated.

Thanks in advanced,

Good Day    Please could someone be so gracious as to help me with the following problem.   I am wanting to update a MYSQL database from a Firebird database.    I have tested connecting to both databases individually which works fine, it is just when I put the code together it does not work.   Below is the code

//Firstly I connect to the MYSQL database to see what needs to updated
      
 mysql_connect("localhost", "database", "password") or die(mysql_error());
 mysql_select_db("database") or die(mysql_error());

$check = "SELECT * FROM table WHERE blah != ''";

$result = mysql_query($check)
or die ("Error in query: $check. " . mysql_error());
if (mysql_num_rows($result) > 0)
{
while($row = mysql_fetch_object($result))

 
{
$blah = $row->blah;

// Then I connect to the Firebird database to get the info

$dsndb = 'host';
$userdb = 'user';
$passdb = 'password'; 
try {  
    $dbh = new PDO($dsndb, $userdb, $passdb);
               
   $sth = ("SELECT * from table WHERE blah = '$blah'");


  foreach ($dbh->query($sth) as $row)
   
   {

//Then I update the MYSQL database from the Firebird database info

$sql = "UPDATE table SET field1 = 'field1', field2 = 'field2', field3 = 'field3' WHERE blah = '$blah'";

$update=mysql_query($sql) or die(mysql_error());

     }

}

  } 
}

Your assistance will be greatly appreciated.