SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    convert mysql code to mysqli

    hi

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

    so, the mysql is
    PHP Code:
    <?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($rsMYSQL_ASSOC) )
        {
            
    $data[] = array(
                
    'label' => $row['profissao']  
            );
        }
    }

    echo 
    json_encode($data);
    flush();


    ?>
    and the result is:
    Code:
    [{"label":"Design"},{"label":"Design Gráfico"},{"label":"Design"},{"label":"Design"}]
    the mysqli at the moment is

    PHP Code:
    <?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
    Code:
    [{"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?

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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(); 
    ?>
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    man you save my day, many thanks

    one more question please

    i have in mysql this

    Code:
    	mysql_query("set NAMES utf8;");
    in the mysqli what is the equivalent? (problem with nulls - "gráfico" - accents)

  4. #4
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The same

    PHP Code:
    $db->query("SET NAMES 'utf8'"); 
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yap, all works now.

    thanks!

  6. #6
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And why didn't you go all the way and switched it to PDO ?

  7. #7
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  9. #9
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Felito View Post
    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.
    aaron-fisher.com - PHP articles and more

  10. #10
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by aamonkey
    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.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  12. #12
    SitePoint Wizard
    Join Date
    Oct 2005
    Posts
    1,832
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Jake Arkinstall View Post
    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.

  13. #13
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jake Arkinstall View Post
    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.
    aaron-fisher.com - PHP articles and more

  14. #14
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well , i just like named parameters and features that spawn from that.

  15. #15
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cheesedude View Post
    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
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  16. #16
    SitePoint Wizard
    Join Date
    Oct 2005
    Posts
    1,832
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Jake Arkinstall View Post
    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.

  17. #17
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  18. #18
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post

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



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •