Edit code to update multiple records

[jeffshead]

I have a PHP script that updates the 'userpoints_count' record.

I want to change it so it also updates the 'userpoints_totalearned' record as well. The 'userpoints_totalearned' record is in the same database, table and row as 'userpoints_count' but may or may not contain the same amount of points. I need the function to do the same thing (increase points) to both records.

Here is original code:

if($aArcadeSttng['activitypoints'] > 0){

//grab user points
$sql = "SELECT `[COLOR="SeaGreen"]userpoints_count[/COLOR]` FROM `se_semods_userpoints` WHERE `userpoints_user_id` = '$iUserId'";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
$userpoints = $row['[COLOR="seagreen"]userpoints_count[/COLOR]'];


//add points
$finalpoints = $userpoints + $aArcadeSttng['activitypoints'];
$sql = "UPDATE `se_semods_userpoints` SET `[COLOR="seagreen"]userpoints_count[/COLOR]` = '$finalpoints' WHERE `userpoints_user_id` = '$iUserId'";
$result = mysql_query($sql) or die(mysql_error());
}

I've tried several different things but I can't get it to work. I'm sure this is very elementary for an experienced coder. Any help will be greatly appreciated 🙂

[samuelcook]

UPDATE table SET key="value", key="value", key="value" ... WHERE

[jeffshead]

samuelcook;10987988 wrote:

UPDATE table SET key="value", key="value", key="value" ... WHERE

Thank you!

In my case, would it be:

$sql = "UPDATE se_semods_userpoints SET key="userpoints_count", key="userpoints_totalearned" = '$finalpoints' WHERE userpoints_user_id = '$iUserId'";

That does not take into consideration that the values of 'userpoints_count' and 'userpoints_totalearned' are probably different. Does it?

[samuelcook]

No it wouldn't make a difference, you would just use the respective values for each:

UPDATE se_semods_userpoints SET userpoints_count='$variable_for_count', userpoints_totalearned='$finalpoints' WHERE userpoints_user_id= '$iUserId'

[jeffshead]

samuelcook;10987990 wrote:

No it wouldn't make a difference, you would just use the respective values for each:

UPDATE se_semods_userpoints SET userpoints_count='$variable_for_count', userpoints_totalearned='$finalpoints' WHERE userpoints_user_id= '$iUserId'

OK. Now I understand that part. So I will need to create new variables for 'userpoints_totalearned' ?

Sorry to be such a dummy, but how do I change the other portion of the code to get the data from both records? The code below only gets data for 'userpoints_count'.

//grab user points
	$sql = "SELECT `userpoints_count` FROM `se_semods_userpoints` WHERE `userpoints_user_id` = '$iUserId'";
	$result = mysql_query($sql);
	while($row = mysql_fetch_array($result))
	$userpoints = $row['userpoints_count'];

And are the funny looking quotes (``) needed?

[samuelcook]

ahh! its understandable;

This will need to be two separate calls to the db;

<?php
$conn = conn(); // db setup
$sql = "YOUR_ORIGINAL_SELECT_SQL LIMIT 1"; // select sql from above
$res = $conn->query($sql) or die( mysqli_error($conn) ); // execute sql
$res = $res->fetch_object(); // fetch object from returning row
$res = $res->num_rows > 0 ? $res->userpoints_count : false; // create clause to contiune
if( $res ) { // execute clause
	$sql = "YOUR_ORIGINAL_UPDATE_SQL"; // update sql
	$conn->query($sql) or die( mysqli_error($conn) ); // execute sql
	$affected = $conn->affected_rows; // used if you want to know num rows affected
	mysqli_close($conn); // close db connection
	return $affected; // used if you want to return num rows affected
}else { // fail clause
	mysqli_close($conn); // close db connection
	return false; // return not true :)
}?>

It will probably be a bit harder to understand, as i do everything using mysqli;

but you will want to make two(2) query statements, i.. to get something and, ii.. to update that something;

the reason that i used the fetch_object() call is because there is no need (in my view) to run a while loop on something that we know we only have one row of, and additionally are only trying to get one value out of that row.

so once we get that particular "something to update", then we can begin running our update sql. to be programmatic-lly(?) correct, we need to make sure that our SELECT statement came back with something or we will run into errors on the UPDATE;

IF the "something to update" came back with <i>something</i> then we can create our WHERE clause;

YOU do NOT need to use `` symbols in THIS instance; there are particular instance where you MUST.

Example: If i created a column name desc (short for description) I would NOT be able to UPDATE, SELECT this column because desc is short for descending;

SELECT desc FROM useful_information;
SELECT desc FROM useful_information;

The first would fail, and the second would work;

I hope you find this mildly useful...