[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
Re: [PHP-DB] Need help with updating existing mysql records with something else
2011/11/11 Amit Tandon :
> 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™
>
>> Hi All,
>>
>> I want to update an existing mysql record with another value after
>> calculating it. Below is the code.
>>
>> > $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)){
>> ?>
>> > "class='body1'";}?>>
>>
>> > $res=$result_ar['mwool40_totqty']-10;
>> echo $res;
>> ?>
>>
>> > $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
Re: [PHP-DB] Prepared Statements
On Fri, Nov 11, 2011 at 10:41 AM, Ron Piggott
wrote:
>
> $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
