Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h2><strong>Note:</strong> Converting from <code>mysql_</code> to <a href="http://php.net/mysqli"><code>mysqli_</code></a> may not be optimal. Consider <a href="http://php.net/pdo"><strong>PDO</strong></a> if you're prepared to convert all of your code to <a href="http://php.net/manual/en/language.oop5.php">OOP</a>.</h2> <p>It can be tempting to try to replace all instances of <code>mysql_</code> with <code>mysqli_</code> and pray it works. You'd be close but not quite on point. </p> <h2><strong>Connecting to the database:</strong></h2> <p>Fortunately, <a href="http://www.php.net/manual/en/mysqli.construct.php"><code>mysqli_connect</code></a> works closely enough to <code>mysql_query</code> that you can just swap out their function names. </p> <p>mysql_:</p> <pre><code>$con = mysql_connect($host, $username, $password); </code></pre> <p>mysqli_:</p> <pre><code>$con = mysqli_connect($host, $username, $password); </code></pre> <h2><strong>Selecting a database</strong></h2> <p>Now, with most of the other functions in the <code>mysqli_</code> library, you'll need to pass <code>mysqli_select_db</code> the database connection as its <strong>first</strong> parameter. Most of the <code>mysqli_</code> functions require the connection object first. </p> <p>For this function, you can just switch the order of the arguments you pass to the function. If you didn't pass it a connection object before, <strong>you have to add it as the first parameter now.</strong></p> <p>mysql_:</p> <pre><code>mysql_select_db($dbname, $con); </code></pre> <p>mysqli_:</p> <pre><code>mysqli_select_db($con, $dbname); </code></pre> <p>As a bonus, you can also pass the database name as the fourth parameter to <code>mysqli_connect</code> - bypassing the need to call <code>mysqli_select_db</code>.</p> <pre><code>$con = mysqli_connect($host, $username, $password, $dbname); </code></pre> <h2><strong>Sanitize user input</strong></h2> <p>Using <code>mysqli_real_escape_string</code> is very similar to <code>mysql_real_escape_string</code>. You just need to pass the connection object as the first parameter. </p> <p>mysql_:</p> <pre><code>$value1 = mysql_real_escape_string($input_string); </code></pre> <p>mysqli_:</p> <pre><code>$value1 = mysqli_real_escape_string($con, $input_string); </code></pre> <h2><strong>Very Important: Preparing and Running a Query</strong></h2> <p>One reason the <code>mysql_</code> functions were deprecated to begin with was their inability to handle prepared statements. If you simply convert your code to <code>mysqli_</code> without taking this important step, you are subject to some of the largest weaknesses of the <code>mysql_</code> functions. </p> <p>It's worth reading these articles on prepared statements and their benefits: </p> <p><a href="http://en.wikipedia.org/wiki/Prepared_statement">Wikipedia - Prepared Statements</a></p> <p><a href="http://php.net/manual/en/mysqli.quickstart.prepared-statements.php">PHP.net - MySQLi Prepared Statements</a></p> <p>Note: When using prepared statements, it's best to explicitly list each column you're attempting to query, rather than using the <code>*</code> notation to query all columns. This way you can ensure you've accounted for all of the columns in your call to <code>mysqli_stmt_bind_result</code>. </p> <p>mysql_:</p> <pre><code>$query = 'SELECT * FROM table1 WHERE table1.col1=' . $value1 . ''; $result = mysql_query($query, $con); while($row = mysql_fetch_assoc*$result) { $col1 = $row['col1']; $col2 = $row['col2']; echo $col1 . ' ' . $col2 . '&lt;br /&gt;'; } </code></pre> <p>mysqli_: </p> <pre><code>$query = 'SELECT col1,col2 FROM table1 WHERE table1.col1=?'; if ($stmt = mysqli_prepare($link, $query)) { /* pass parameters to query */ mysqli_stmt_bind_param($stmt, "s", $value1); /* run the query on the database */ mysqli_stmt_execute($stmt); /* assign variable for each column to store results in */ mysqli_stmt_bind_result($stmt, $col1, $col2); /* fetch values */ while (mysqli_stmt_fetch($stmt)) { /* on each fetch, the values for each column in the results are automatically stored in the variables we assigned using "mysqli_stmt_bind_result" */ echo $col1 . ' ' . $col2 . '&lt;br /&gt;'; } /* close statement */ mysqli_stmt_close($stmt); } </code></pre> <h2><strong>Showing errors</strong></h2> <p>Showing errors works a little differently with <code>mysqli_</code>. <a href="http://www.php.net/manual/en/mysqli.error.php"><code>mysqli_error</code></a> requires the connection object as its first parameter. But what if the connection failed? <code>mysqli_</code> introduces a small set of functions that don't require the connection object: the <a href="http://www.php.net/manual/en/mysqli.connect-error.php"><code>mysqli_connect_*</code></a> functions.</p> <p>mysql_:</p> <pre><code>if (!$con) { die('Could not connect: ' . mysql_error()); } if (!$result) { die('SQL Error: ' . mysql_error()); } </code></pre> <p>mysqli_:</p> <pre><code>/* check connection error*/ if (mysqli_connect_errno()) { die( 'Could not connect: ' . mysqli_connect_error() ); } /* check query error */ if ($stmt = mysqli_prepare($link, $query)) { // ... execute query if (mysqli_stmt_error($stmt)) { echo 'SQL Error: ' . mysqli_stmt_error($stmt); } } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow do I convert a script using mysql_ functions to use mysqli_ functions?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USNicholas Pickering
    1. USNicholas Pickering
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow do I convert a script using mysql_ functions to use mysqli_ functions?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    1. COThere's no point in converting to `mysqli_` (except for deprecation warnings) if you still do string interpolation instead of prepared statements. People should move away from string interpolation.
      singulars
      1. PO
      1. USJohn Dvorak
    2. CO@JanDvorak To move away from deprecated functions was the exact point of this article. Some of the beginning users out there like `mysql_` for its simplicity. This answer makes it easy to use `mysqli_` - which is much better.
      singulars
      1. PO
      1. USNicholas Pickering
    3. COThe need for string interpolation is the exact reason why `mysql_` was deprecated.
      singulars
      1. PO
      1. USJohn Dvorak
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload