variable issue in mysqli string

Question

this is the code

if($_POST['submit']) {

$rid = $_GET['rid'];
$award = $_GET['award'];
$award_id = $_GET['award_id'];
$member_id = $_GET['member_id'];

$result = $mysqli->query("UPDATE ".$dbprefix."clanawards_requests SET maa_status = '3' WHERE maa_request_id = '$rid'");
$result = $mysqli->query("UPDATE ".$dbprefix."clanawards_members SET '".$award."' = '1' WHERE maa_member_id = '$member_id'");

the problem is in this line

$result = $mysqli->query("UPDATE ".$dbprefix."clanawards_members SET '".$award."' = '1' WHERE maa_member_id = '$member_id'");

to be precise SET '".$award."' = '1'

'".$award."' is a column name and a variable and everything else works but this part of the code is not updating.

what i need to know is how to get the variable to work in this mysqli string.

how about if you say $sql="create your update string here" ... then echo it to see how awful it looks — Drew, Aug 29 '15 at 23:44
and what would that column name be exactly; `column-1`? `column 1`? `column 'xxx'`? `roman gods temple column`? — Funk Forty Niner, Aug 29 '15 at 23:45
Ugly variable nesting, no `$_GET[]` filtering, setting integers as strings, mixing quote types. Oh boy. — DeDee, Aug 29 '15 at 23:45

Funk Forty Niner · Accepted Answer · 2015-08-30T00:00:03.253

"column name would be Testing Award"

You have to wrap that column name in ticks

SET `$award` = '1'

and not in quotes since it contains a space.

MySQL should have thrown you an syntax error: (consult Error checking methods below).

http://php.net/manual/en/mysqli.error.php

Either that, or rename your column to contain an underscore.

I.e.: Testing_Award.

Do not use a hyphen, as MySQL would interpret Testing-Award as Testing minus Award, resulting in a syntax error, unless using backticks.

You should also use a prepared statement:

https://en.wikipedia.org/wiki/Prepared_statement

You're wide open to SQL injection.

Error checking methods:

Example:

$res = $mysqli->query($result);

if ($mysqli->error) {
    try {    
        throw new Exception("MySQL error $mysqli->error <br> Query:<br> $query", $mysqli->errno);    
    } catch(Exception $e ) {
        echo "Error No: ".$e->getCode(). " - ". $e->getMessage() . "<br >";
        echo nl2br($e->getTraceAsString());
    }
}

or:

$result = mysqli_query($mysqli, $result);

if (!$result)
{
    throw new Exception(mysqli_error($mysqli));
}

else{ echo "Success."; }

Ideally, using mysqli_affected_rows() will give you a better result, if the query was truly successful.

http://php.net/manual/en/mysqli.affected-rows.php

i would use prepared statements but my coding skill isn't that high — Nuker Viper, Aug 29 '15 at 23:52
@NukerViper It's not that hard really. Here are a few links you can look into. PDO http://php.net/manual/en/pdo.prepared-statements.php and MySQLi http://php.net/manual/en/mysqli.quickstart.prepared-statements.php - couple of days and you'll be up and running in no time ;-) — Funk Forty Niner, Aug 29 '15 at 23:53
Drive-by downvote, have a great day at church today. Thanks for the lovely Sunday surprise. Oh, and if you care to enlighten us with your almighty given power, we'll be more than happy to listen. Sounds like someone with "no common sense" to me. — Funk Forty Niner, Aug 30 '15 at 13:01

variable issue in mysqli string

1 Answers1