homepage Welcome to WebmasterWorld Guest from 50.19.169.37
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
mysql to mysqli, prepared statement
helenp




msg:4639835
 10:44 am on Jan 24, 2014 (gmt 0)

Hi,
Puf, exhausted.
Have just added sessions to my pay and booking pages, and then not to touch them more, I started to change mysql to mysqli.
I choosed mysqli and not pdo as I have I think hundred of pages maybe that needs to be updated, so I wanted the less changes in code as possible.

However found a problem when I was going to escape using mysqli_real_escape_string.
I used before for sql inyection but also to escape and insert ' in irish names etc.
It did not work, so I read I have to do prepared statements....
I been trying for hours,
but cant manage:

I have this so far:
<?
$mysqli = @new mysqli('localhost', 'xxxxx', 'xxx', 'table');
$mysqli->set_charset("utf8");

if ($mysqli->connect_error)
{
die('There is a temporary problem with this page at this moment: ' . $mysqli->connect_error);
}
include 'conexionmysqli.php';
$emailtrue = 'test';
$orderdatetime= '2014-01-21';
$statement = 'notpaid';
$query = $mysqli->prepare ("insert into orders (emailtrue, orderdatetime, statement) values (?, ?, ?)");
/* Bind parameters
s - String, b - Boolean, i - Integer etc, d - DOUBLE and FLOAT*/
$query->bind_param("sss", $emailtrue, $orderdatetime, $unpaid);
$query->execute();
$query->close();

?>


This is really a pain when you have many parameters to insert in bind parameter.
Above does not work.
Also another two questions:
Can I use s for everything even when numbers are inserted?
Other things that is inserted such as now() for exampel, should that be included in the bind_param?
Thanks in advance.

 

helenp




msg:4639880
 1:07 pm on Jan 24, 2014 (gmt 0)

There is an error with the parameters, I changed it into:
$emailtrue = 'test';
$orderdatetime= '2014-01-21';
$unpaid = 'notpaid';
$concepto = 'que';
but it does still not insert anything

helenp




msg:4639883
 1:39 pm on Jan 24, 2014 (gmt 0)

Got it, best way or not, no idea,
didnīt know had to use stmp

$stmt = $mysqli->prepare ("insert into orders (emailtrue, orderdatetime, statement, concepto) values (?, ?, ?,?)");
/* Bind parameters
s - String, b - Boolean, i - Integer etc, d - DOUBLE and FLOAT*/
$stmt->bind_param("ssss", $emailtrue, $orderdatetime, $unpaid, $concepto);
$stmt->execute();

helenp




msg:4639906
 2:46 pm on Jan 24, 2014 (gmt 0)

Btw, why is this forum slower and slower every day?

penders




msg:4640315
 6:08 pm on Jan 26, 2014 (gmt 0)

didnīt know had to use stmp


Your first "non-working" example and your last "working" example appear to be the same, except you have a 4th column in your "working" example?

The only other difference, that you appear to hint at, is that you've used a different variable name - but you can call the variable anything you like (it contains a mysqli_stmt object regardless).

$myVar = $mysqli->prepare(....); 
$myVar->bind_param(....);
$myVar->execute();

swa66




msg:4640342
 9:32 pm on Jan 26, 2014 (gmt 0)

FWIW: prepare(), bind_param() and execute() can fail: you should check the return values.

It helps in debugging to print out the $mysqli->error in your logs (don't expose that to end-users they've no use for it and it will help attackers.)

helenp




msg:4640344
 9:53 pm on Jan 26, 2014 (gmt 0)

Thanks all,
I didnt like the mysqli prepared statements, so I changed to do PDO wich I seem a lot cleaner and easier to work with.
This is my first code that works:
//prepared insert
$emailtrue = 'tres';
$name='t';
$concepto='r';
$telhome='p';
$telmobile='k';
$holidaycost='l';
$sentencia = $dbh->prepare("INSERT INTO ordered (emailtrue, orderdatetime, statement, name, concepto, telhome, telmobile, holidaycost) VALUES (:emailtrue, NOW(), 'unpaid', :name, :concepto, :telhome, :telmobile, :holidaycost)");
$sentencia->bindParam(':emailtrue', $emailtrue);
$sentencia->bindParam(':name', $name);
$sentencia->bindParam(':concepto', $concepto);
$sentencia->bindParam(':telhome', $telhome);
$sentencia->bindParam(':telmobile', $telmobile);
$sentencia->bindParam(':holidaycost', $holidaycost);
// insertar una fila
$sentencia->execute();
print $dbh->lastInsertId();
// create prepared statement and update
$id = $dbh->lastInsertId();
echo $id;
$sql = $dbh->prepare ("UPDATE ordered SET id='pay$id' WHERE order_id = :id");
$sql->bindParam(':id', $id);
$sql->execute();

[edited by: helenp at 9:57 pm (utc) on Jan 26, 2014]

helenp




msg:4640346
 9:56 pm on Jan 26, 2014 (gmt 0)

And this is a prepared statement for selecting, very clean, and short code:

$id='pay455';
$query = $dbh->query("
SELECT id, order_id FROM ordered WHERE id = :id");
// fetch row
// execute statement, binding values to the parameters
$query->execute(array(
'id' => $id
));
foreach ($query as $row) {
echo html_escape('Post ' . $row['id'] . ' from ' . $row['order_id']) . '<br/>';
}

penders




msg:4640359
 11:54 pm on Jan 26, 2014 (gmt 0)

Yes, PDO would seem to be a better extension to work with, given the choice. Mysqli's bind_param() is a bit of a bind (pun intended) to say the least - particularly when it comes to dynamic queries and a variable number of parameters.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved