PHP - Can You Update A Mysql Value Within The Query?

I made a small editing system for my news page, and I need to update three columns within my table "announcements" in the database. I tried a method of updating all of them with one MySQL query instead of using three as it just isn't neat. I've searched several methods via google and I've tried all of them, but just can't seem to get it to work. Is this MySQL query correct?


mysql_query("UPDATE announcements SET title = {$title} WHERE id = '$id', content = {$content} WHERE id = '$id', lastmodified = ". date('M-d-Y') ." WHERE id = '$id'");

Hello, I am trying to pick up php again and just exercising my skills. So I have it so that it fills my form with the values of what I want to edit, and when I click the edit button, it doesn't edit any of the information. When I echo out $result, I get a MYSQL query string that has the same values as the table, so its not getting the new values that are edited.


<?php

@mysql_connect('localhost', 'root', '') or die("Could not connect to Mysql Server. " . mysql_error());

@mysql_select_db('tutorials') or die("Could not connect to Database. " . mysql_error());

if(isset($_GET['edit']))

{

$id = $_GET['edit'];

$query = "SELECT `username`, `password` FROM `users` WHERE `id` = '$id'";

$result = mysql_query($query);

$row = mysql_fetch_array($result);

$name = $row['username'];

$password = $row['password'];

}

if(isset($_POST['edit']))

{

$id = $_GET['edit'];

$query = "UPDATE `users` SET `username` = '$name', `password` = '$password' WHERE `id` = '$id'";

$result = mysql_query($query);

echo $query;

if(!$result)

{

echo mysql_error();

}else{

echo 'updated post';

}

}
?>
<form method="POST" action="" >

<input type="text" name="name" value="<?php echo $name; ?>" /> First name <br />

<input type="text" name="password" value="<?php echo $password; ?>" /> Last name <br />

<input type="submit" name="edit" value="edit" />
</form>


I believe it has something to do with the values of $name and $password in the form conflicting with the first if isset and the second if isset.

Thanks for any help possible   

Hi all,

I have the following MySQL insert query:
Code: [Select]
$insert= mysql_query   ("INSERT INTO tablename (column1,`".$EXPfields."`) VALUES ('$something','".$EXPvalues."')");
where $EXPfields is an array of table-field-names and $EXPvalues is an array of table-field-values.

Now I want to write an equivalent query, but using UPDATE instead of INSERT INTO, but I don't want to write out all the field names/values separately, but again want to use $EXPfields and $EXPvalues.

So something like this:
Code: [Select]
$update = mysql_query ("UPDATE tablename
                     SET (column1,`".$EXPfields."`) = ('$something','".$EXPvalues."')
                     WHERE ....
                  ");

Is this possible? If so, what is the proper syntax? Thanks!

I've messed around with this for three hours now, and it's driving me batty.  I'm running the following Query:

mysql_select_db("myDB", $con);
$query = "UPDATE mytable SET Name = '$Name', Address = '$Address', City = '$City', State = '$State', Zip = '$Zip', Phone = '$Phone', Website = '$Website', Type = '$Type' WHERE id = '$ID'";

The problem is that it's not updating my database.  I've tried everything.  I've forced the page to Echo out the variables so that I know they're right.  I've run (successful) Delete queries to make sure I'm connecting to the right table.  I've forced it to spit out the variables at the end of the Update.   Everything I've tried works beautifully... except the Update itself.  It doesn't error out or anything- it acts like it's Updating, but the data never changes.  I can add to the database and delete from it, but it refuses to let me Update anything. 

I've looked all over the net, and tried every variation I can find.  Nothing works.   I also tried this:

$query = "UPDATE `testbed` SET `Name`=[$Name],`Address`=[$Address],`City`=[$City],`State`=[$State],`Zip`=[$Zip],`Type`=[$Type],`Phone`=[&Phone],`website`=[$Website] WHERE `ID`=[$ID]";
 
And

$result = mysql_query("UPDATE mytable SET Name = '$Name', Address = '$Address', City = '$City', State = '$State', Zip = '$Zip', Phone = '$Phone', Website = '$Website', Type = '$Type' WHERE id = '$ID'");

None of them do anything at all.

Hello all,   Based on the suggestion of you wonderful folks here, I went away for a few days (to learn about PDO and Prepared Statements) in order to replace the MySQLi commands in my code. That's gone pretty well thus far...with me having learnt and successfully replaced most of my "bad" code with elegant, SQL-Injection-proof code (or so I hope).   The one-and-only problem I'm having (for now at least) is that I'm having trouble understanding how to execute an UPDATE query within the resultset of a SELECT query (using PDO and prepared statements, of course).   Let me explain (my scenario), and since a picture speaks a thousand words I've also inlcuded a screenshot to show you guys my setup:   In my table I have two columns (which are essentially flags i.e. Y/N), one for "items alreay purchased" and the other for "items to be purchased later". The first flag, if/when set ON (Y) will highlight row(s) in red...and the second flag will highlight row(s) in blue (when set ON).   I initially had four buttons, two each for setting the flags/columns to "Y", and another two to reverse the columns/flags to "N". That was when I had my delete functionality as a separate operation on a separate tab/list item, and that was fine.   Now that I've realized I can include both operations (update and delete) on just the one tab, I've also figured it would be better to pare down those four buttons (into just two), and set them up as a toggle feature i.e. if the value is currently "Y" then the button will set it to "N", and vice versa.   So, looking at my attached picture, if a person selects (using the checkboxes) the first four rows and clicks the first button (labeled "Toggle selected items as Purchased/Not Purchased") then the following must happen:   1. The purchased_flag for rows # 2 and 4 must be switched OFF (set to N)...so they will no longer be highlighted in red. 2. The purchased_flag for row # 3 must be switched ON (set to Y)...so that row will now be highlighted in red. 3. Nothing must be done to rows # 1 and 5 since: a) row 5 was not selected/checked to begin with, and b) row # 1 has its purchase_later_flag set ON (to Y), so it must be skipped over.   Looking at my code below, I'm guessing (and here's where I need the help) that there's something wrong in the code within the section that says "/*** loop through the results/collection of checked items ***/". I've probably made it more complex than it should be, and that's due to the fact that I have no idea what I'm doing (or rather, how I should be doing it), and this has driven me insane for the last 2 days...which prompted me to "throw in the towel" and seek the help of you very helpful and intellegent folks. BTW, I am a newbie at this, so if I could be provided the exact code, that would be most wonderful, and much highly appreciated.   Thanks to you folks, I'm feeling real good (with a great sense of achievement) after having come here and got the great advice to learn PDO and prepared statements.   Just this one nasty little hurdle is stopping me from getting to "end-of-job" on my very first WebApp. BTW, sorry about the long post...this is the best/only way I could clearly explaing my situation.   Cheers guys!

case "update-delete":
	if(isset($_POST['highlight-purchased'])) 
		{
			// ****** Setup customized query to obtain only items that are checked ******
			$sql = "SELECT * FROM shoplist WHERE";
					for($i=0; $i < count($_POST['checkboxes']); $i++)
						{
							$sql=$sql . " idnumber=" . $_POST['checkboxes'][$i] . " or";
						}
					$sql= rtrim($sql, "or");
											
			$statement = $conn->prepare($sql);
			$statement->execute();
																						
			// *** fetch results for all checked items (1st query) *** //
			$result = $statement->fetchAll();

			$statement->closeCursor();
											
			// Setup query that will change the purchased flag to "N", if it's currently set to "Y"
			$sqlSetToN = "UPDATE shoplist SET purchased = 'N' WHERE purchased = 'Y'";
											
			// Setup query that will change the purchased flag to "Y", if it's currently set to "N", "", or NULL
			$sqlSetToY = "UPDATE shoplist SET purchased = 'Y' WHERE purchased = 'N' OR purchased = '' OR purchased IS NULL";

			$statementSetToN = $conn->prepare($sqlSetToN);
			$statementSetToY = $conn->prepare($sqlSetToY);
											
			/*** loop through the results/collection of checked items ***/
			foreach($result as $row)
				{
					if ($row["purchased"] != "Y")
						{
							// *** fetch one row at a time pertaining to the 2nd query *** //
							$resultSetToY = $statementSetToY->fetch();
															
							foreach($resultSetToY as $row)
								{															
									$statementSetToY->execute();																
								}
						}
					else
						{
							// *** fetch one row at a time pertaining to the 2nd query *** //
							$resultSetToN = $statementSetToN->fetch();
															
							foreach($resultSetToN as $row)
								{															
									$statementSetToN->execute();																
								}
						}
				}
				break;
		}

 CRUD Queston.png   20.68KB   0 downloads    

I created this code to upload a member's main picture on his member page on website.  I'll only include the query part of the code since that's what is relevant to my problem.  The idea is basically to upload a new picture onto the database if no picture already exists for that member and display the picture on the page.  If a picture already exists, then the script replaces the old picture with the new one upon upload.    But for whatever reason
I don't understand,  when I try to replace the old pic, it gets inserted in a new row on the database instead of replacing the old row, and the new pic gets displayed on the web page alongside the old. Code: [Select]
$query = "SELECT username FROM images WHERE member_id = '".$_SESSION['id']."' AND image_cartegory = 'main'";
        $result = @mysql_query($query);
        $num = @mysql_num_rows($result);
       
        if ($num> 0)   {

    //Update the image

    $update = mysql_query("UPDATE images SET image = '" . $image['name'] . "' WHERE member_id = '".$_SESSION['id']."' AND image_cartegory = 'main'"); 

   
          $_SESSION['error'] = "File updated successfully.";  //really should be session success message.

                    header("Location: member.php");


    exit;
}

else

{
// NOTE: This is where a lot of people make mistakes.
// We are *not* putting the image into the database; we are putting a reference to the file's location on the server

    $sql = "insert into images (member_id, image_cartegory, image_date, image) values ('{$_SESSION['id']}', 'main', NOW(), '" . $image['name'] . "')";

    $result = mysql_query($sql) or die ("Could not insert data into DB: " . mysql_error());

    $_SESSION['error'] = "File uploaded succussfully.";  //really should be session success message.

                    header("Location: member.php");
                                }


So can anyone tell me what the problem is?  Could the fact that my insert script actually uploads the image onto a folder on my server and only stores the path name in the database have anything to contribute to the mixup?  Appreciate your responses in advance.

Can anyone post a generic update function to update mysql table.
The manual approach:

update $tablename
set $column1='a', $column2='b'
where $id=$value;

Here is my code:
// Start MySQL Query for Records
$query = "SELECT codes_update_no_join_1b" .
"SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2" .
"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";
$results = mysql_query($query) or die(mysql_error());
// End MySQL Query for Records


This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this???

Code: [Select]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1

If you also have any feedback on my code, please do tell me. I wish to improve my coding base.

Basically when you fill out the register form, it will check for data, then execute the insert query. But for some reason, the query will NOT insert into the database. In the following code below, I left out the field ID. Doesn't work with it anyways, and I'm not sure it makes a difference.

Code:

  Code: [Select]
mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
Full code:

Code: [Select]
<?php
include_once("includes/config.php");
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><? $title; ?></title>
<meta http-equiv="Content-Language" content="English" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<body>
 
<div id="wrap">
 
<div id="header">
<h1><? $title; ?></h1>
<h2><? $description; ?></h2>
</div>
 
<? include_once("includes/navigation.php"); ?>
 
<div id="content">
<div id="right">
<h2>Create</h2>
<div id="artlicles">
<?php
 
if(!$_SESSION['user'])
{
 
        $username = mysql_real_escape_string($_POST['username']);
        $password = mysql_real_escape_string($_POST['password']);
        $name = mysql_real_escape_string($_POST['name']);
        $server_type = mysql_real_escape_string($_POST['type']);
        $description = mysql_real_escape_string($_POST['description']);
 
        if(!$username || !$password || !$server_type || !$description || !$name)
        {

        echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br/>All forms are required to be filled out.<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>";
        echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'>
       
        <option value='Any'>Any</option>
        <option value='PvP'>PvP</option>
        <option value='Creative'>Creative</option>
        <option value='Survival'>Survival</option>
        <option value='Roleplay'>RolePlay</option>
       
        </select></td></tr>
       
        <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40' name='description'></textarea></td></tr>";
        echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>";
        }
        elseif(strlen($password) < 8)
        {
        echo "Password needs to be higher than 8 characters!";
        }
        elseif(strlen($username) > 13)
        {
                echo "Username can't be greater than 13 characters!";
        }
        else
        {
                $check1 = mysql_query("SELECT username,name FROM servers WHERE username = '$username' OR name = '$name' LIMIT 1");
               
                if(mysql_num_rows($check1) < 0)
                {
                        echo "Sorry, there is already an account with this username and/or server name!";
                }
                else
                {
                        $ip = $_SERVER['REMOTE_ADDR'];

                        mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
                        echo "Server has been succesfully created!";
                }
        }
       
}
else
{
        echo "You are currently logged in!";
}
 
?>
</div>
</div>
 
<div style="clear: both;"> </div>
</div>
 
<div id="footer">
<a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop
</div>
</div>
 
</body>
</html>

After a ton of trial and error and searching and searching for the answer, I have finally resorted to posting to ask for help.  I am very new to php so please bear with me.   I have been building an employee training database at work.  Basically to make sure that for example we have the current drivers liscense on file for those employees who drive and have gone through the required company training.  I initially built an access database with a frontend that does everything needed. However, the company is very decentralized and we have several users who would regularly need access to the information when they are offsite.  The database does work offsite however it is extremely slow.  After purchasing a buffalo linkstation, I discovered it has the capability for a mysql server.     I have been able to migrate the data over to the mysql server and build a simple read only php form but cant get the changes you make to update back into the database.   I found a method of fetching the data using json that is based on a drop down selection, upon change, it goes and gets the correct data.

<script type="text/javascript">
      function insertResults(json){
		$("#Separated").val(json["Separated"]);
		$("#id").val(json["id"]);
        $("#Employee_ID").val(json["Employee_ID"]);
        $("#Last_Name").val(json["Last_Name"]);
        $("#First_Name").val(json["First_Name"]);
        $("#Date_Of_Birth").val(json["Date_Of_Birth"]);
        $("#Original_Hire_Date").val(json["Original_Hire_Date"]);
        $("#Current_Hire_Date").val(json["Current_Hire_Date"]);

However, lets say you correct a mis-spelling in their name, I cant seem to write back to the db.  I have a submit button that uses this code

<a href="InputFormSubmit.php?id=<?php echo $rows['id']; ?>"><input type="submit" name="submit2" value="Submit"></a>

But I guess I cant seem to #1 completely grasp how to pass on the selected information using POST (correct?) to the next form "InputFormSubmit" and #2 correctly build the query for it?  Code for it below:

<?php

 		include_once('db.php');
		$Employee_ID  = $_POST['Employee_ID'];
		$Last_Name = $_POST['Last_Name'];
		$First_Name = $_POST['First_Name'];
		$id=$_POST['id'];
		echo "$id";

		echo "$Employee_ID";
	// update data in mysql database


	$con=mysql_connect("localhost","usernamegoeshere","passwordgoeshere","databasenamegoeshere");
	// Check connection

	mysql_query("UPDATE tbl_Employee_Master SET Last_Name='$Last_Name', First_Name='$First_Name' WHERE id='$id'");

	mysql_close($con);
?>

The proper EmployeeID is passed from the previous form into this one, but the query dies I guess.   What I would like to do is pass the updated info into InputFormSubmit and then run the query and redirect back to the search page. Potentially passing back the employee ID and setting the drop down to that value so you would see the employees file you just updated again.   Super long post, thanks for any direction anyone can give me, Im sure my code looks awful from trying a thousand different things.   Thanks again.

Currently I show a list of users and their information. You can than click that specific user and it shows you their information to edit. My problem is it's not updating the query with the new input information.  Here is the page that displays the registration information:

<?php
$con=mysqli_connect('localhost', 'root', '');

/* check connection */

if (mysqli_connect_errno($con)) {
	trigger_error('Database connection failed: '  . mysqli_connect_error(), E_USER_ERROR);
}

 $query = "SELECT * FROM `bencobricks` . `users`";
 $result = mysqli_query($con, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($con), E_USER_ERROR); 

 
 ?>

<table width="1000" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="1000" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from mysql </strong> </td>
</tr>

<tr>
<td align="center"><strong>Username</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Membership</strong></td>
<td align="center"><strong>First Name</strong></td>
<td align="center"><strong>Last Name</strong></td>
<td align="center"><strong>Gender</strong></td>
<td align="center"><strong>Birthdate</strong></td>
<td align="center"><strong>Sets</strong></td>
<td align="center"><strong>Checkbox</strong></td>
<td align="center"><strong>Admin Flag</strong></td>
</tr>

<?php
while($rows=mysqli_fetch_array($result)){
	
	?>

<tr>
<td><?php echo $rows['username']; ?></td>
<td><?php echo $rows['email']; ?></td>
<td><?php echo $rows['membership']; ?></td>
<td><?php echo $rows['firstName']; ?></td>
<td><?php echo $rows['lastName']; ?></td>
<td><?php echo $rows['gender']; ?></td>
<td><?php echo $rows['dateOfBirth']; ?></td>
<td><?php echo $rows['sets']; ?></td>
<td><?php echo $rows['checkbox']; ?></td>
<td><?php echo $rows['adminFlag']; ?></td>

<td align="center"><a href="update.php?userID=<?php echo $rows['userID']; ?>">update</a></td>
</tr>

<?php
}
?>

</table>
</td>
</tr>
</table>

<?php
mysqli_close($con);
?>

Page that displays specific user for updating:
 

<?php
$con=mysqli_connect('localhost', 'root', '');

/* check connection */

if (mysqli_connect_errno($con)) {
	trigger_error('Database connection failed: '  . mysqli_connect_error(), E_USER_ERROR);
}

$id=$_GET['userID'];

$query = "SELECT * FROM `bencobricks` . `users` WHERE `userID` = '$id'";
$result = mysqli_query($con, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($con), E_USER_ERROR);
$rows=mysqli_fetch_array($result);

?>

<table width="1000" 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"><strong>Username</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Membership</strong></td>
<td align="center"><strong>First Name</strong></td>
<td align="center"><strong>Last Name</strong></td>
<td align="center"><strong>Gender</strong></td>
<td align="center"><strong>Birthdate</strong></td>
<td align="center"><strong>Sets</strong></td>
<td align="center"><strong>Checkbox</strong></td>
<td align="center"><strong>Admin Flag</strong></td>
</tr>
<tr>

<td align="center">
<input name="username" type="text" id="username" value="<?php echo $rows['username'] ;
echo (isset($_POST['username']) ? $_POST['username'] : ''); ?>"> 
</td>
<td align="center">
<input name="email" type="text" id="email" value="<?php echo $rows['email']; ?>" size="15">
</td>
<td>
<input name="membership" type="text" id="membership" value="<?php echo $rows['membership']; ?>" size="15">
</td>
<td>
<input name="firstName" type="text" id="firstName" value="<?php echo $rows['firstName']; ?>" size="15">
</td>
<td>
<input name="lastName" type="text" id="lastName" value="<?php echo $rows['lastName']; ?>" size="15">
</td>
<td>
<input name="gender" type="text" id="gender" value="<?php echo $rows['gender']; ?>" size="15">
</td>
<td>
<input name="dateOfBirth" type="text" id="dateOfBirth" value="<?php echo $rows['dateOfBirth']; ?>" size="15">
</td>
<td>
<input name="sets" type="text" id="sets" value="<?php echo $rows['sets']; ?>" size="15">
</td>
<td>
<input name="checkbox" type="text" id="checkbox" value="<?php echo $rows['checkbox']; ?>" size="15">
</td>
<td>
<input name="adminFlag" type="text" id="adminFlag" value="<?php echo $rows['adminFlag']; ?>" size="15">
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td>
<input name="id" type="hidden" id="id" value="<?php echo $rows['userID']; ?>">
</td>
<td align="center">
<input type="submit" name="Submit" value="Submit">
</td>
<td>&nbsp;</td>
</tr>
</table>
</td>
</form>
</tr>
</table>

<?php
// close connection 
mysqli_close($con);

?>

Finally the update query: 
 

<?php
function test_input($data){
	$data = trim($data);
	$data = stripslashes($data);
	$data = htmlspecialchars($data);
	return $data;
}

$con=mysqli_connect('localhost', 'root', '');

/* check connection */

if (mysqli_connect_errno($con)) {
	trigger_error('Database connection failed: '  . mysqli_connect_error(), E_USER_ERROR);
}

// update data in mysql database 
if (empty($_POST["username"]))
{/* can never be empty due to design */}
else
{$username= test_input($_POST['username']);}
$id = 'id';
$query = "UPDATE `bencobricks`. `users` SET `username` = '$username', `password` = 'NULL', `email` = 'email', `membership` = 'membership', `firstName` = 'firstName', `lastName` = 'lastName',
`gender` = 'gender', `dateOfBirth` = 'dateOfBirth', `date` = 'NULL', `sets` = 'sets', `checkbox` = 'checkbox', `adminFlag` = 'adminFlag' WHERE `userID` = '$id'";




$result = mysqli_query($con, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($con), E_USER_ERROR);

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

else {
echo "ERROR";
}

?>

Attached Files  1.PNG   19.06KB   0 downloads  2.PNG   47.07KB   0 downloads

Hi all,

I'm trying to make a query that allows the user to edit the name of a entry in SQL. I believe I have the corect Syntax but I'm not sure if I have the code set up correctly. I am using 2 queries when I think it is possible with one. The first query is used to Pint out results for a certain year and the second to update the names.

Code: [Select]
$desiredYear = $_POST['year'];
$nameEdit = $_POST['edit'];
$origName = $_POST['orig'];

echo ' The year you have chosen is '.$desiredYear;

$describeQuery = "SELECT ID, Name, (SELECT SUM(SalesVolume) as SalesVolume FROM MonthlySales WHERE ProductCode=Products.ID AND Year = '$desiredYear') AS num_sales FROM Products";
$editQuery = "UPDATE Products SET Name = '$nameEdit'  WHERE Name ='$origName'";


$results = sqlsrv_query($conn, $describeQuery);

echo '<table border="1" BORDERCOLOR=Black>';
echo '<tr><th bgcolor = "LightBlue">Name</th><th bgcolor = "LightBlue" >ID</th> <th bgcolor = "LightBlue" >Sales</th></tr>';


while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
  {   
    echo '<tr>';
echo '<td >' .$row['Name'].'</td>';
echo '<td>' .$row['ID'].'</td>';
echo '<td>' .$row['num_sales'].'</td>';

echo '</tr>';
}

echo '</table>';
sqlsrv_close($conn);


 
Can somebody help me with this?

Thank you

Hi Guys,

I'm hoping someone can find what I clearly cant see, I have a page meant to update database records, the page will load and populate with the existing data and allow 5 out of the 7 fields to be edited, however when editing any of those 5, the remaining 2 blank out and can't be filled in again. Im at a bit of loss and have been staring at it for a bit too long i think, hoping a fresh set of eyes can help.

Here's the page (the fields I'm having issues with are inv1/inv2:

<?php

$page_title = 'Edit a Cart';
include ('style.html');

// Check for a valid ID, through GET or POST.
if ( (isset($_GET['id'])) ) { // Accessed through active_search.php
$id = $_GET['id'];
} elseif ( (isset($_POST['id'])) ) { // Form has been submitted.
$id = $_POST['id'];
} else { // No valid ID, kill the script.
echo '<div id="title">Page Error</div>
<p class="error">This page has been accessed in error.</p><p><br /><br /></p>'; 
exit();
}

require_once ('mysql_connect.php'); // Connect to the db.

// Check if the form has been submitted.
if (isset($_POST['submitted'])) {

$errors = array(); // Initialize error array.

// Check for a PO.
{
$po = trim($_POST['po']);
}

// Check for a Master Serial.
{
$sr = trim($_POST['serial']);
}

// Check for a Charger Serial.
{
$ch = trim($_POST['charger']);
}

// Check for a Battery Serial.
{
$bt1 = trim($_POST['battery1']);
}

// Check for a Battery Serial.
{
$bt2 = trim($_POST['battery2']);
}

// Check for a Inverter Serial.
{
$in1 = trim($_POST['inv1']);
}

// Check for a 2nd Inverter Serial.
{
$in2 = trim($_POST['inv2']);
}

// Check for a Radio Serial.
{
$abc = trim($_POST['abc']);
}

if (empty($errors)) { // If everything's OK.

//  Test for unique master serial.
$query = "SELECT serial FROM serials WHERE serial='$id'";
$result = mysql_query($query);
if (mysql_num_rows($result) == 1) {

// Make the query.
$query = "UPDATE serials SET po='$po', serial='$sr', charger='$ch', batt1='$bt1', batt2='$bt2', inv1='$in1', inv2='$in2', abc='$abc' WHERE serial='$id'";
$result = @mysql_query ($query); // Run the query.
if (mysql_affected_rows() == 1) { // If it ran OK.

// Print a message.
echo '<div id="title">Success!</div>
<p>The node has been edited.<a href="javascript:window.close();">Close</a></p><p><br /><br /></p>';

} else { // If it did not run OK.
echo '<div id="title">System Error</div>
<p class="error">The node could not be edited due to a system error. We apologize for any inconvenience.</p>'; // Public message.
echo '<p>' . mysql_error() . '<br /><br />Query: ' . $query . '</p>'; // Debugging message. 
exit();
}

} else { // Already registered.
echo '<div id="title">Error!</div>
<p class="error">The Serial has already been registered.</p>';
}

} else { // Report the errors.

echo '<div id="title">Error!</div>
<p class="error">The following error(s) occurred:<br />';
foreach ($errors as $msg) { // Print each error.
echo " - $msg<br />\n";
}
echo '</p><p>Please try again.</p><p><br /></p>';

} // End of if (empty($errors)) IF.

} // End of submit conditional.

// Always show the form.

// Retrieve the user's information.
$query = "SELECT * FROM serials WHERE serial='$id'";
$result = @mysql_query ($query); // Run the query.

if (mysql_num_rows($result) == 1) { // Valid ID, show the form.

// Get the user's information.
$row = mysql_fetch_array ($result);

// Create the form.
echo '<div id="title">Edit a Cart</div>
<form action="serial_edit.php" method="post">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>P.O #:</td><td><input type="text" name="po" size="15" maxlength="15" value="' . $row['po'] . '" /></td>
</tr>
<tr>
<td>Cart Serial:</td><td><input type="text" name="serial" size="15" maxlength="30" value="' . $row['serial'] . '" /></td>
</tr>
<tr>
<td>Charger:</td><td><input type="text" name="charger" size="20" maxlength="40" value="' . $row['charger'] . '"  /> </td>
</tr>
<tr>
<td>Battery 1:</td><td><input type="text" name="battery1" size="20" maxlength="40" value="' . $row['batt1'] . '"  /> </td>
</tr>
<tr>
<td>Battery 2:</td><td><input type="text" name="battery2" size="20" maxlength="40" value="' . $row['batt2'] . '"  /> </td>
</tr>
<tr>
<td>Inverter 1:</td><td><input type="text" name="inverter1" size="20" maxlength="40" value="' . $row['inv1'] . '"  /> </td>
</tr>
<tr>
<td>Inverter 2:</td><td><input type="text" name="inverter2" size="20" maxlength="40" value="' . $row['inv2'] . '"  /> </td>
</tr>
<tr>
<td>ABC:</td><td><input type="text" name="abc" size="20" maxlength="40" value="' . $row['abc'] . '"  /> </td>
</tr>
<tr>
<td><input type="submit" name="submit" value="Submit" /><input type="hidden" name="submitted" value="TRUE" /></td>
<td><input type="hidden" name="id" value="' . $id . '" /></td></tr>
</form>';

} else { // Not a valid ID.
echo '<div id="title">Page Error</div>
<p class="error">This page has been accessed in error.</p><p><br /><br /></p>';
}

mysql_close(); // Close the database connection.

?>

Hi, i am currently designing a new website and am testing a few different compnents of it. One part gathers two variables of the url using the GET method and selects the relevant data from the database. It then performs an UPDATE of a field called balance before INSERTING a new record. Everything works and gets inserted except for the UPDATE.

Here is the code

Code: [Select]
<?php
include ("connect.php");

$id = "";
$offerid = "";

$id = $_GET['user_id'];
$offerid = $_GET['offer_id'];

$sql_user = mysql_query("SELECT * FROM user_info WHERE id = $id");

$sql_offer = mysql_query("SELECT * FROM offers WHERE offerid = $offerid");

$balance += $payout;

mysql_query("UPDATE user_info SET balance = $balance WHERE id = $id");

mysql_query ("INSERT INTO offer_credited (id, offerid, date_credited, payout_to_user) VALUES ('$id', '$offerid', CURDATE(), '$payout')") or die(mysql_error());

echo "Your Offer has been recorded and Updated"
?>
The balance field is a float that i want to increase by the amount of the payout variable which is from the offers table. However, the balance never gets updated and remains at 0.

Any ideas what it could be, i am thinking it is something to do with,

Code: [Select]
$balance += $payout;
But i can't think of what else to do.

Hopefully this isn't too confusing.

Thanks for the help