Re: [PHP-DB] Prepared Statements
On Fri, Nov 11, 2011 at 10:41 AM, Ron Piggott
ron.pigg...@actsministries.org wrote:
?php
$dsh = 'mysql:host=localhost;dbname='.$database;
$dbh = new PDO($dsh, $username, $password);
#query for the authorization code
$query = SELECT `authorization_code` FROM
`directory_listing_update_authorizations` WHERE NOW() BETWEEN `start_date`
AND `end_date` AND `authorization_code` = :authorization_code AND
`directory_entries_reference` = :directory_entries_reference LIMIT 1;;
$stmt = $dbh-prepare($query);
$stmt-bindValue(':directory_entries_reference',
$directory_entries_reference, PDO::PARAM_STR);
$stmt-bindValue(':authorization_code', $authorization_code, PDO::PARAM_STR);
$stmt-execute() or die(print_r($stmt-errorInfo(), true));
while ($row = $stmt-fetch()) {
Not entirely clear here why you need a while statement if your query
above is limit 1.
if ( $row['authorization_code'] == $authorization_code ) {
This is redundant with the query statement above; given that, this
will always pass.
#update directory_entries.last_review with today's date
$query = UPDATE `directory_entries` SET `last_review` = NOW() WHERE
`reference` = :directory_entries_reference LIMIT 1;;
$stmt = $dbh-prepare($query);
This concerns me -- the outer loop is using the previously prepared
and exectued statement in the $stmt variable, then you're resetting it
here. This probably works because your initial query was limit 1, but
it might not in another context. Regardless, it makes the outer while
loop test invalid.
$stmt-bindValue(':directory_entries_reference',
$directory_entries_reference, PDO::PARAM_STR);
$stmt-execute() or die(print_r($stmt-errorInfo(), true));
} else {
#failure, direct user to request new authorization code or login
manually
}
}
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Need help with updating existing mysql records with something else
2011/11/11 Amit Tandon att...@gmail.com:
U can update the record with following
===
$result = mysql_query(UPDATE tax set mwool40_
totqty = $res, $connection) or die(error
updating database);
Won't this update every row in the table?
I think you'd want:
$result = mysql_query(UPDATE tax SET mwool40_totqty = $res
WHERE id=.$res['id'],$connection) or die(blah blah);
Where id is the identifier of the current record you're looking at
(however you've done that).
if (mysql_affected_rows($connection) != 1 (/* no of rows that should be
updated */)
{
die(Update problem) ;
}
The only issue is the where condition that you would put in the query
UPDATE tax set mwool40_totqty = $res where ...
^This. Don't leave this out.
regds
amit
The difference between fiction and reality? Fiction has to make sense.
2011/11/11 Guru™ nagendra802...@gmail.com
Hi All,
I want to update an existing mysql record with another value after
calculating it. Below is the code.
?php
$connection = mysql_connect(localhost, root, ) or die(Error
connecting to database);
mysql_select_db(maha, $connection);
$result = mysql_query(SELECT * FROM tax, $connection) or die(error
querying database);
$i = 0;
while($result_ar = mysql_fetch_assoc($result)){
?
tr ?php if($i%2 == 1){ echo class='body2'; }else{echo
class='body1';}?
td
?php
$res=$result_ar['mwool40_totqty']-10;
echo $res;
?/td
/tr
?php
$i+=1;
}
?
I want to append the $mwool40_totqty value with the calculated one $res.
Let say initially the value of $mwool40_totqty is 50. and after calculation
its value became 40, then the code should edit/alter/update the value in
mysql table with integer 40. Please help me with this.
--
*Best,
*
*Guru™*
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] question about best practice for coding sql statements
I'm curious how others feel about this. When I'm creating an SQL string, either for a non-prepared or prepared execution, I build it in a variable and then pass the variable to the query or prepare function. This lets me easily add something to view the SQL statement, and also if there's an error, emit the SQL statement. Thus: $sql = SELECT * FROM TABLE WHERE id=$id; if ($debug) error_log($sql. from .__FILE__.' at '.__LINE__' in '__FUNCTION__.PHP_EOL); // just an example $rslt = $db-query($sql) or die(Could not get result from $sql: .$db-errno.: .$db-error...PHP_EOL); and so on... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
