convert mysql code to mysqli

[Felito]

hi

i have a script that works perfectly, but i want to change to mysqli

so, the mysql is

<?php

include("includes/banco.php");
$theclass->conecta();


$consulta= "design";  

$rs = mysql_query('select profissao  from empregos where profissao like "'.$consulta .'%"');

$data = array();
if ( $rs && mysql_num_rows($rs) )
{
    while( $row = mysql_fetch_array($rs, MYSQL_ASSOC) )
    {
        $data[] = array(
            'label' => $row['profissao']  
        );
    }
}

echo json_encode($data);
flush();


?>

and the result is:

[{"label":"Design"},{"label":"Design Gráfico"},{"label":"Design"},{"label":"Design"}]

the mysqli at the moment is

<?php

include ('includes/includesMy.php');


$consulta= "design";


($sql = $db->prepare('select profissao from empregos where profissao like ?'));

$sql->bind_param('s', $consulta);
$sql->execute();
$sql->bind_result($profissao);

$data = array();

while ($sql->fetch()) {
    $data[] = array(
            'label' => $row['profissao']  
    );

}
    echo json_encode($data);
$sql -> close();
$db -> close();


?>

ant the result is

[{"label":null},{"label":null},{"label":null}]

the mysqli is not working correctly, the number of outputs are different and label are null (only matches the specific word design and not design gráfico )

any help?

[Jake Arkinstall]

The number of outputs has changed because you haven't added a % to $consulta.

The reason that you aren't getting values correctly is because nothing has defined $row in the while() loop and typically MySQLi is done a little differently. You've binded the result to $profissao, you just need to use it.

<?php 
include ('includes/includesMy.php'); 
$consulta= "design%"; 
$sql = $db->prepare('SELECT profissao FROM empregos WHERE profissao LIKE ?');
$sql->bind_param('s', $consulta);
$sql->execute();
$sql->bind_result($profissao);
$data = array();
while ($sql->fetch()) { 
    $data[] = array( 
            'label' => $profissao;
    );
}
echo json_encode($data); 
$sql->close(); 
$db->close(); 
?>

[Felito]

man you save my day, many thanks

one more question please

i have in mysql this

	mysql_query("set NAMES utf8;");

in the mysqli what is the equivalent? (problem with nulls - "gráfico" - accents)

[Jake Arkinstall]

The same

$db->query("SET NAMES 'utf8'"); 

[Felito]

yap, all works now.

thanks!

[azazul]

And why didn't you go all the way and switched it to PDO ?

[Felito]

i read that mysqli is faster than pdo. And the speed is important for the project in question.

The principal advantage of pdo, the portability in deferents DB is not a priority.

MySQLi vs. PDO Benchmarks - jonathanrobson.me

[Jake Arkinstall]

MySQLi and PDO are competing libraries. Personally I use PDO, but that's simply because I prefer the way it's used and the fact that it's built in with PHP as default.

However there are benefits to MySQLi - for one, every benchmark test I've done shows conclusively that MySQLi is faster. Which makes sense - PDO is built for access to many different possible database systems, whereas MySQLi is specialised to MySQL. Specialised libraries are generally built to be faster and easier to use than generalised libraries.

Not only that, but PDO doesn't support some advanced features that MySQL has to offer, whereas MySQLi does.

So I don't really think that its fair to suggest that MySQLi is inferior. It's just an alternative.

[aamonkey]

i read that mysqli is faster than pdo. And the speed is important for the project in question.

The principal advantage of pdo, the portability in deferents DB is not a priority.

MySQLi vs. PDO Benchmarks - jonathanrobson.me

Did you read the conclusion in the article you linked above? This is just my 2 cents, but PDO will save you hours of development time, compared to saving microseconds (or fractions of a microsecond) per page load.

[Felito]

MySQLi was about 2.5% faster for non-prepared statements and about 6.7% faster for prepared statements.

and in this article

PHP and MySQL, the future

"In my opinion, PDO is not for use on systems where mysql performance is a top goal."

i think that two libraries are good, simple they have a different focus, mysqli in mysql and pdo in the multiplataform.

And the more important reason for my choice: i never use PDO

[Jake Arkinstall]

PDO will save you hours of development time

Whilst I am a PDO user myself, I have to point out that MySQLi and PDO have negligible development time differences in a system where a change of database isn't going to happen.

[cheesedude]

Whilst I am a PDO user myself, I have to point out that MySQLi and PDO have negligible development time differences in a system where a change of database isn't going to happen.

A quick question. Given that you may need to rewrite some queries if you change to a different database system, what are the benefits to using PDO? Isn't it just more stuff to know with little benefit?

PHP: Introduction - Manual

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

Over the past decade since I've been interested in programming, I've seen stuff come and go. ATL, MFC, then comes .NET. New things come out then fall by the wayside.

I know people tend to jump on the bandwagon and hype things. mysqli is very simple to use. I don't see how PDO is going to save "hours of development time" when it is nothing more than a "data access abstraction layer".

I've read people hyping the benefits of PDO elsewhere. I cannot figure out why. Accessing a database is only a few lines of code, anyway, which can be reused.

My web host only has PDO drivers for sqlite, sqlite2, mysql installed. No Oracle, no Postgre, and not many of the others.

My impression is that PDO is more hassle than it is worth. Please correct me if I am wrong. There is something to be said for keeping stuff as simple as possible. One thing is that it is more likely to be future-proof.

[aamonkey]

Whilst I am a PDO user myself, I have to point out that MySQLi and PDO have negligible development time differences in a system where a change of database isn't going to happen.

I was referring mainly to writing code that implements either library - IMO mysqli is incredibly clunky, and binding parameters is a nightmare. To me PDO's syntax and methods are far easier to use and have saved me very significant amounts of time since switching over. As a side note, I have never used any DBMS other than mysql and have no plans to.

[azazul]

Well , i just like named parameters and features that spawn from that.

[Jake Arkinstall]

My impression is that PDO is more hassle than it is worth.

Certainly not. PDO is useful if, for example, you're building a framework or website which is designed to be used by many different developers on many different servers - using different databases. This isn't applicable to everyone but is certainly a consideration for anyone wanting to create a usable framework.

The query strings do change from database system to database system, but they can be generated from the application itself based on the database system they're using. The problem with MySQLi is that you can only use MySQL, which is an overall limitation - one that many people can cope with, but it is a limitation none-the-less.

This debate is similar in essence to the typical 'Singleton Registry vs Dependancy Injection' debate in the PHP Application Design forums. The singleton registry is easy to access - but it has limitations in that you can't channel different dependencies to different parts of an application, for example (hence the name singleton). Dependancy Injection allows for greater flexibility and you can fork out sub-applications and delegate a bit better, but Singleton supporters still claim that those benefits are unnecessary.

In the end, it is the choice of the developer. PDO is great. MySQLi is great. That's all there is to it

[cheesedude]

Certainly not. PDO is useful if, for example, you're building a framework or website which is designed to be used by many different developers on many different servers - using different databases. This isn't applicable to everyone but is certainly a consideration for anyone wanting to create a usable framework.

OK, do you know of a decent PDO tutorial? I'll practice with it a little when I find the time. I could look on the internet for some tutorials (I did a little already). But if you know of one, perhaps you could give me a link?

Thanks for the reply.

[Jake Arkinstall]

Well, the PHP manual does a rather good job at showing what you can do with it, but when I started out with PDO I found this article from PHPro more useful.

[phpghost]

This php editor enable you to convert from Mysql to Mysqli , Just click "Edit > Mysql to Mysqli"
http://sourceforge.net/projects/e-plus-plus