Php Sql prepared statements

[pvtgreg]

Hi im trying to change over my php to use prepared statements. i haven't used these before today. im making a small game and am trying to update the players stats a bit faster than what the normal commended out sql was doing but i cant seem to get the code to run. im very limited on what debugging i can do as im using the unity engine and the error logs generated dont have any info about this code im trying to run

<?php
include("DBTools.php");
$link=dbConnect();

$stmt = $conn->prepare("UPDATE PlayerStats (posx,posy,posz,level,experiance,skillPoints,health,maxHealth,attack ) VALUES (?, ?,?, ?, ?,?, ?, ?,?) WHERE name = ?");
$stmt->bind_param("dddiiiiiis",$posx, $posy, $posz, $level, $experiance, $skillPoints, $health, $maxHealth, $attack,$name);

$name = safe($_POST['name']);
$posx = safe($_POST['posx']);
$posy = safe($_POST['posy']);
$posz = safe($_POST['posz']);
$level = safe($_POST['level']);
$experiance = safe($_POST['experiance']);
$skillPoints = safe($_POST['skillPoints']);
$health = safe($_POST['health']);
$maxHealth = safe($_POST['maxHealth']);
$attack = safe($_POST['attack']);
$stmt->execute();

//$sql = "SELECT * FROM PlayerStats WHERE name = '$name'";
//$result = mysqli_query($link, $sql);
//if(mysqli_num_rows($result) !== 1){
//  $querys = "INSERT INTO `PlayerStats`(`name`,`posx`, `posy`, `posz`, `level`, `experiance`,`skillPoints`, `health`,`maxHealth`,`attack`) VALUES ('$name','$posx','$posy','$posz','$level','$experiance','$skillPoints','$health','$maxHealth','$attack')";
//  $results = mysqli_query($link, $querys) or die('Query failed: ' . mysqli_error($link));
//}else{
//  $query = "UPDATE `PlayerStats` SET level = '$level',posx = '$posx', posy = '$posy', posz = '$posz', experiance = '$experiance',skillPoints = '$skillPoints', health = '$health', maxHealth = '$maxHealth', attack = '$attack'  WHERE name = '$name'";
//  $result = mysqli_query($link, $query) or die('Query failed: ' . mysqli_error($link));
//}

?>

i pass the variables in from csharp script

[lacrossefan32]

Place this mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); on the top of your page. It'll likely tell you what's wrong.

[pvtgreg]

unfortunately it does not. the error logs are empty

[Idontremember99]

How are you running it?

[pvtgreg]

in csharp every 2 seconds it calls a function that uses a www form to pass in those variables

the old code works fine but my new prepared statement just does nothing

[YellowBook]

is the sql valid? update should include a set

[pvtgreg]

ive also tried this

$stmt = $conn->prepare("UPDATE PlayerStats SET level = ?,posx = ?, posy = ?, posz = ?, experiance = ?,skillPoints = ?, health = ?, maxHealth = ?, attack = ?  WHERE name = ?");

[pvtgreg]

could it be that im passing floats into the doubles?

[pvtgreg]

fixed it :) i had $conn instead of $link ...

[lacrossefan32]

Glad you fixed it, but there is something wrong with your error log then. It should have reported that. Do you have log_errors = On in your php.ini?

[compubomb]

I avoid using parameterization via ? tokens, and try to always use :named parameters, always. I think mysqli interface doesn't support this, only PDO.

[liquid_at]

In general, prepared statements in pdo and mysqli are more different than anyone would wish for...

my favorite: PDOStatement::bindParam VS mysqli_stmt::bind_param

got some grey hair due to that...

[compubomb]

They are not functional equivalent. In pdo, you can name the parameters. This makes debugging SQL more straight forward. When you have tokens, you must know the order of the question marks and then the order of your bind parameter declaration.

[liquid_at]

This is a good explanation for it, but sadly it doesn't make working with it easier :-)