Going from php mysql_* to mysqli

Question

A lot of people have been telling me to get over mysql_* and get to either mysqli or PDO.

For a start I have choosen Mysqli as it seems very simliar.

However I've run into a problem while converting my site.

I can't seem to find the equivalent of getting my data like this: mysql_result($result, $i, 'COL 2')

The code below is what it looks like now, however I can't seem to find a way to get my data like I did with mysql_*.

I'm doing like this:

<?php 
    $sql="SELECT * FROM items"; 
    $result=mysqli_query($GLOBALS["___mysqli_ston"], $sql); 

     for ($i = $start; $i < $end; $i++) { 
        if ($i == $total_results) { 
            echo ' 
            <div class="col-sm-12 col-lg-12 col-md-12"><h4>Der er ingen produkter at vise</h4></div> 
            '; 
            break; 
        } 
        echo ' 
        <div class="col-sm-4 col-lg-4 col-md-4" style="min-height:425px;"> 
                        <div class="thumbnail"> 
                            <img src="'.mysql_result($result, $i, 'COL 25').'" alt="" style="max-height:300px;"> 
                            <div class="caption"> 
                                <h4 class="pull-right">'.mysql_result($result, $i, 'COL 20').' point</h4> 
                                <h4 style="color:rgb(220,145,27)">'.mysql_result($result, $i, 'COL 2').'</h4> 
                                <p>Vare nr.: '.mysql_result($result, $i, 'COL 14').'</p> 
                            </div> 
                            <div class="buy"> 
                                <form action="" method="POST" name="buy"> 
                                    <!--- <select name="variant" class="form-control"> 
                                        <option>small</option> 
                                    </select><br> --> 
                                    <button class="btn btn-m center-block" type="submit" style="color:white;background-color:rgb(220,145,27);">Køb</button> 
                                </form> 
                            </div> 
                        </div> 
                    </div> 
        '; 
    }; 
    ?>

Looks like a great opportunity to fix the inefficiencies in your existing code. — symcbean, Nov 25 '15 at 12:43
also take a look at [mysqli prepared statements](http://php.net/manual/de/mysqli.prepare.php) they make your code less vulnerable to SQL injections. Also take a look at [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) as it may aid you in preventing SQL injections. — BRoebie, Nov 25 '15 at 14:38
@BRoebie While it's true that you should always be aware of SQL-injections, a query like this doesn't pass any variables - and would therefor not be vulnerable to SQL injection. — Qirel, Nov 25 '15 at 14:44
@Qirel I took notice of that and I do not mean this particular example I mean in general because I saw nobody mentioned prepared statements. And also because the question title is **Going from mysql_* to mysqli**. :D — BRoebie, Nov 25 '15 at 14:51

score 2 · Accepted Answer · answered Nov 25 '15 at 12:27

2

Simply put, there is no equivalent for mysql_result, which means you have to restructure your code. You could either use a while or a foreach-loop to loop through all the rows.

<?php
$mysqli = $GLOBALS["___mysqli_ston"];
$sql = "SELECT * FROM items";

if ($result = mysqli_query($mysqli, $sql)) {
    // Query passed, let's continue
    if (mysqli_num_rows($result) > 0) {
        // We have results! Continue
        while ($row = mysqli_fetch_array($query)) {
            echo '<img src="'.$row[24].'" alt="" style="max-height:300px;"><div class="caption"> 
                <h4 class="pull-right">'.$row[20].' point</h4> 
                <h4 style="color:rgb(220,145,27)">'.$row[2].'</h4> 
                <p>Vare nr.: '.$row[4].'</p> 
                </div> ';
        }
    } else {
        echo "No results";
    }
} else {
    echo "Query failed";
}
?>

You can also use $row['columname'] instead, which makes easier when reading the code what you are actually trying to output.

If you're dead set on using mysqli_result, you could create your own function which would do approximately the same (from this PHP.net comment). (Personally I would go for the example above).

function mysqli_result($res, $row, $field=0) { 
    $res->data_seek($row); 
    $datarow = $res->fetch_array(); 
    return $datarow[$field]; 
}

I would in any case strongly suggest that you do not pass your database-connection in a global variable, instead include the database-parameters and create the connection for every script.

answered Nov 25 '15 at 12:27

Qirel

25,449
7
45
62

Thanks for your answer :-) When running the code you provided, i get the following error: Notice: Undefined variable: query in /Applications/XAMPP/xamppfiles/htdocs/b2b/pages/shop.php on line 187 Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in /Applications/XAMPP/xamppfiles/htdocs/b2b/pages/shop.php on line 187 – Emil Elkjær Nov 25 '15 at 13:02
Like 187 would be: while ($row = mysqli_fetch_array($query)) { – Emil Elkjær Nov 25 '15 at 13:02
Ahh, yeah - it's because that should be `$result` for you, like this: `mysqli_fetch_array($result)` Sorry! – Qirel Nov 25 '15 at 13:03
Seems to work that way, however before with mysql_* it only showed 6 items pr. page as it should, however now it shows all items at once. – Emil Elkjær Nov 25 '15 at 13:10
Okay, given use of the function you also wrote it works flawless! I think I'll stick with that option for now until I get more into mysqli :-) thanks for your help! – Emil Elkjær Nov 25 '15 at 13:11
I'd consider that bad practice, as you could restructure your code in a way that it'd work with use of the first example, and such I would gladly help you figure out how to solve this new issue. For that, I'd like to see the structure of your database. If you're happy with it though, stick with it. – Qirel Nov 25 '15 at 13:30

Going from php mysql_* to mysqli

1 Answers1