Please help me convert my MySQL to MySQLi

Question

Will someone please explain to me and assist to convert my old code into the new MySQLi format? I would greatly appreciate it.

This is what my current code:

<html>
    <head>
        <title>Last 10 Results</title>
    </head>
    <body>
        <table>
        <thead>
            <tr>
                <td>Id</td>
                <td>Name</td>
            </tr>
        </thead>
        <tbody>
        <?php
            $connect = mysql_connect("localhost","root", "root");
            if (!$connect) {
                die(mysql_error());
            }
            mysql_select_db("apploymentdevs");
            $results = mysql_query("SELECT * FROM demo LIMIT 10 ORDER BY Id");
            while($row = mysql_fetch_array($results)) {
            ?>
                <tr>
                    <td><?php echo $row['Id']?></td>
                    <td><?php echo $row['Name']?></td>
                </tr>

            <?php
            }
            ?>
            </tbody>
            </table>
    </body>
</html>

Again I appreciate the assistance.

This is what I have done ( I am so sorry I am such a noob )

                    <?php
                        // Database details
                        $dbhost = 'localhost';
                        $dbuser = '#';
                        $dbpass = '#';
                        $dbname = '#';
                        $db = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
                        if($db->connect_errno > 0){
                            die('Unable to connect to database [' . $db->connect_error . ']');
                        }
                        $results = mysqli_query("SELECT * FROM formdata LIMIT 10 ORDER BY id");
                        while($row = mysqli_fetch_array($results)) {
                        ?>
                            <tr>
                                <td><?php echo $row['ID']?></td>
                                <td><?php echo $row['FullName']?></td>
                                <td><?php echo $row['Mobile']?></td>
                                <td><?php echo $row['Email']?></td>
                                <td><?php echo $row['Province']?></td>
                            </tr>

                        <?php
                        }
                        ?>

@Fred-ii- I have added what I thought was right, but please don't yell at me I am learning. — Erik Thiart, Sep 16 '15 at 13:56
I want to upvote, but I cant because everyone down voted my legit question I now don't have the rep required to give back a upvote for the person who assisted me — Erik Thiart, Sep 16 '15 at 14:04
Is the while($row = mysqli_fetch_array($results)) part correct thou? — Erik Thiart, Sep 16 '15 at 14:09

NaijaProgrammer · Accepted Answer · 2015-09-16T14:34:59.420

2

Update this line:

$results = mysqli_query("SELECT * FROM formdata LIMIT 10 ORDER BY id");

to

$results = mysqli_query($db, "SELECT * FROM formdata ORDER BY id LIMIT 10");

edited Sep 16 '15 at 14:34

answered Sep 16 '15 at 13:58

NaijaProgrammer

2,892
2
24
33

1

I have a pretty good feeling who downvoted us. There's a "snake" amongst us. – Funk Forty Niner Sep 16 '15 at 14:04
Thank you for this answer, both you and Fred, Is my mysqli_fetch_array part correct? – Erik Thiart Sep 16 '15 at 14:09
Yes, that part is correct, although it can take an optional parameter. But it is ok as it is. – NaijaProgrammer Sep 16 '15 at 14:10
Dammit I get this error now: Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/test/public_html/backend.php on line 650 – Erik Thiart Sep 16 '15 at 14:19
1

Suggestions: 1. check your table names. 2. Run the code in PhpMyAdmin. 3. Put the `ORDER BY` statement before the `LIMIT` statement. – NaijaProgrammer Sep 16 '15 at 14:21
1

@ErikThiart `on line 650`? – Funk Forty Niner Sep 16 '15 at 14:23
@Fred-ii- got to be a very long one, you bet. – NaijaProgrammer Sep 16 '15 at 14:24
@NaijaProgrammer the error doesn't support the question now. – Funk Forty Niner Sep 16 '15 at 14:25
You sir are a genius !! The issue was number 3. ( Do I need to close this query or will it stop automatically? ) – Erik Thiart Sep 16 '15 at 14:30
@NaijaProgrammer aaaahhh!!! I didn't notice LIMIT before ORDER BY - that totally evaded me. That needs to be edited in your answer though. – Funk Forty Niner Sep 16 '15 at 14:33
1

Just mark the answer as the accepted answer, that should do. – NaijaProgrammer Sep 16 '15 at 14:34
@Fred-ii- thanks. Answer updated accordingly. – NaijaProgrammer Sep 16 '15 at 14:35
@NaijaProgrammer do I need to close the connection afterwards? If so how would one do that? There is other scripts writing to the DB on other pages. – Erik Thiart Sep 16 '15 at 14:58
1

@ErikThiart, if the connection was not opened as a persistent connection, it will be automatically closed. However, if you need to explicitly close it, you can do so by calling: `mysqli_close($db)`. – NaijaProgrammer Sep 16 '15 at 15:00
Excuse this noob question to follow... Where must I add mysqli_close($db); ie Between this part? – Erik Thiart Sep 16 '15 at 15:06
A good place is after the `while` loop, just before the closing `?>`. – NaijaProgrammer Sep 16 '15 at 15:11
Warning: mysqli_close(): Couldn't fetch mysqli – Erik Thiart Sep 16 '15 at 15:12
where did you add the code? – NaijaProgrammer Sep 16 '15 at 15:17
I added it before the first `?>` I also tried to add it before the second `?>` – Erik Thiart Sep 16 '15 at 15:20
I moved it till after the `}` in the last `` block seemed to work! Thanks so much – Erik Thiart Sep 16 '15 at 15:24

Funk Forty Niner · Answer 2 · 2015-09-16T14:56:39.037

1

The problem here, is that you haven't passed the connection to your query

$results = mysqli_query($db, "SELECT 
                        ^^^^

Read the documentation:

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

Unlike mysql_, it must be passed as a parameter.

Then make sure your query isn't failing.

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

Edit:

Footnote:

As caught by NaijaProgrammer in a comment to the OP, LIMIT goes after ORDER BY and was a complete oversight on my part.

"Suggestions: 1. check your table names. 2. Run the code in PhpMyAdmin. 3. Put the ORDER BY statement before the LIMIT statement. – NaijaProgrammer"

Reference for SELECT:

https://dev.mysql.com/doc/refman/5.0/en/select.html

Example from the manual:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

I would like to point out though that, you're using a lowercase here for the "id" ORDER BY id and that shouldn't be an issue for the ORDER BY.

However, there is a difference between $row['ID'] and $row['id']. If your database is configured to be case sensitive, then you will need to write the ID in lowercase.

edited Sep 16 '15 at 14:56

answered Sep 16 '15 at 13:57

Funk Forty Niner

74,450
15
68
141

1

when someone says "I'm such a noob", many times, they don't fully grasp the official PHP manual, which is the reason they probably came to SO in the first place. Then also, they usually would prefer the code fully written out for them. I'm speaking from experience. – NaijaProgrammer Sep 16 '15 at 14:01
@NaijaProgrammer It's understandable. I too was a newbie once, and I didn't know what to Google for. We're talking many years ago of course ;-) – Funk Forty Niner Sep 16 '15 at 14:02
@NaijaProgrammer I noticed your answer got a downvote :( that was NOT me, I can assure you of that. I even got one too. – Funk Forty Niner Sep 16 '15 at 14:03
@Fred-ii- not to worry, getting a downvote shows that someone at least read my post, I'm cool with that. – NaijaProgrammer Sep 16 '15 at 14:04
@NaijaProgrammer I like your style ;-) – Funk Forty Niner Sep 16 '15 at 14:07
@Fred-ii-, I suppose it's time for me to say "I like your style ;-)" – NaijaProgrammer Sep 16 '15 at 14:46
@NaijaProgrammer thanks ;-) glad to see the OP accepted your answer, *cheers*. I'm just doing a few more edits in regards to clarification and a few other things I spotted in the OP's question/code. – Funk Forty Niner Sep 16 '15 at 14:59

Please help me convert my MySQL to MySQLi

2 Answers2