PHP mySQL query question from PHP newb...

I

IronChefMorimoto

Limp Gawd
Joined
Apr 14, 2002
Messages
273
I'm having trouble accessing the row that I'm updating via the SQL update statement below. I'm parsing the results with mysql_fetch_row($submitQuery) and trying to access the 'master_count' field via $submitQueryRow['master_count'] -- it comes up empty.

How do I access the row data that I just updated AFTER the query?

Code: 
<?php
$submitConfigID = $_POST["rank_config_id"];
$submitRawData = $_POST["rank_data"];
$submitData = explode(",", $submitRawData);
$submitDataLen = count($submitData);

$submitCon = mysql_connect("dumbasscentral.org","xyz","xyz");
if (!$submitCon)
{
	die('Could not connect: ' . mysql_error());
}

mysql_select_db("xyz", $submitCon);

$submitQuery = mysql_query("UPDATE cxn_RankConfig SET master_count = master_count + 1 WHERE id = '$submitConfigID'");
?>

Thanks,
IronChefMorimoto
 
First, look up mysql_escape_string() and use it. Don't ever just grab whatever you get from any GET/POST and plop it into a query. Validation is your friend.

Second, once you update the record you then have to ask the db for it. You'll have to execute a select statement using the primay key field (I would guess the id field) and then you have it available to you. You can perform a msyql_affected_rows to find out how many records your update altered.
 
like deuce868 said, you need a second query to retrieve the row after it. additionally, mysql_fetch_row returns an indexed array (as opposed to an associative one) which you can't do $submitQueryRow['master_count'] with, for that you need to use mysql_fetch_assoc

if your query is just 'SELECT master_count FROM ..... WHERE ...' though you can use mysql_fetch_row and then $submitQueryRow[0] would do the same thing
 
deuce868 said:
First, look up mysql_escape_string() and use it. Don't ever just grab whatever you get from any GET/POST and plop it into a query. Validation is your friend.

Second, once you update the record you then have to ask the db for it. You'll have to execute a select statement using the primay key field (I would guess the id field) and then you have it available to you. You can perform a msyql_affected_rows to find out how many records your update altered.
Click to expand...

I think you mean: http://us2.php.net/manual/en/function.mysql-real-escape-string.php mysql_real_escape_string() as that one is old and depreciated.
 
Thanks to everyone who chimed in -- I'm a Flash developer -- well -- shit -- I don't really know what I'm "best" at, but right now, I'm integrating a Flash project or three w/ PHP and MySQL, and the security precautions you noted will be acted upon.

For a "newb" -- is there a good site/tutorial to reference when building "safe" apps like this?

Thanks!

IronChefMorimoto
 
Also, if you need a guaranteed accurate value of master_count, you have to use InnoDB tables and put BEGIN/END TRANSACTION calls before your update and after your select, respectively.

And I would change that database password immediately, now that everyone knows it ;).
 
Xeth said:
Also, if you need a guaranteed accurate value of master_count, you have to use InnoDB tables and put BEGIN/END TRANSACTION calls before your update and after your select, respectively.

And I would change that database password immediately, now that everyone knows it ;).
Click to expand...

I am a damned idiot...now I have a lot of work to do...

IronChefMorimoto
 
M'k -- when I add mysql_real_escape_string() calls to the end of my mySQL queries, I get errors.

Is this the proper syntax?

Code: 
$submitQuery = mysql_query("UPDATE cxn_RankConfig SET master_count = master_count + 1 WHERE id = '$submitConfigID'",
	mysql_real_escape_string($submitConfigID));

Assuming that $submitConfigID is a variable from a $_POST var?

IronChefMorimoto
 
Try this
Code: 
$submitQuery = mysql_query("UPDATE cxn_RankConfig SET master_count = master_count + 1 WHERE id = '" . mysql_real_escape_string( $submitConfigID ) . "'";
 
OK -- this seems to be working now...

Code: 
<?php
$submitConfigID = $_POST["rank_config_id"];
$submitRawData = $_POST["rank_data"];
$submitData = explode(",", $submitRawData);
$submitDataLen = count($submitData);

$submitCon = mysql_connect("dumbasscentral.org","xyz","xyz");
if (!$submitCon)
{
	die('Could not connect: ' . mysql_error());
}

mysql_select_db("coxnet_prod", $submitCon);

$submitQuery = mysql_query("UPDATE cxn_RankConfig SET master_count = master_count + 1 WHERE id = '" . mysql_real_escape_string($submitConfigID) . "'");

$submitQuery = mysql_query("SELECT master_count FROM cxn_RankConfig WHERE id ='" . mysql_real_escape_string($submitConfigID) . "'");
$submitQueryRow = mysql_fetch_array($submitQuery);

$submitVars = "";
$submitVars .= "&result=ranked";
$submitVars .= "&results_count=" . $submitQueryRow['master_count'];

foreach ($submitData as $arrayKey => $arrayValue)
{
	$submitScore = $submitDataLen - $arrayKey;
	$submitQuery = mysql_query("UPDATE cxn_RankData SET score = score + '$submitScore', count = count + 1 WHERE rank_config_id = '$submitConfigID' AND id = '$arrayValue'");
}

$submitQuery = mysql_query("SELECT title, count, (score/count) AS average_score FROM cxn_RankData WHERE rank_config_id = '$submitConfigID' ORDER BY average_score DESC");

$submitVars .= "&results=";

$submitQueryCount = 1;
while($submitQueryRow = mysql_fetch_array($submitQuery)) {
	if ($submitQueryCount == mysql_num_rows($submitQuery))
	{
		$submitVars .= $submitQueryRow['title'];
	}
	else
	{
		$submitVars .= $submitQueryRow['title'] . ",";
		$submitQueryCount++;
	}
}

echo $submitVars;
?>

Thanks, Xeth, for your help. Thanks to others who chimed in too.

IronChefMorimoto
 
You must log in or register to reply here.
Back
Top