Pfthb

I am mysqli upgrading from mysql. I know how to use basic connect and query statements. But I can't get the result statement to work. All the solutions on the net I have found blind me with science. I would like to have "mysql_result" working in mysqli.

$alltb = "SELECT * FROM mytable";
$alltbd=mysqli_query($conned, $alltb);
$num_all=mysqli_num_rows($alltbd); 
mysqli_close($conned);
$i = 0;
while($i < $num_all) 
 $ttt=mysql_result($alltbd,$i,"ttt"); 
 $sss=mysql_result($alltbd,$i,"sss");
 print $ttt . " and " . $sss;
$i++;

Any help greatly appreciated. I don't want to use things like fetch, for each or array. I would like to keep it simple.

Garry Jones
Sweden

You can do mysqli_fetch_all for example:

$query = "SELECT * FROM mytable";

$sqlResult = mysqli_query($conned, $query);

$allRecords = mysqli_fetch_all($sqlResult, MYSQLI_ASSOC); 

mysqli_close($conned);

foreach($allRecords as $record) 
 $ttt = $record['ttt']; 
 $sss = $record['sss'];
 print $ttt . " and " . $sss;

MySQL and MySQLi are PHP database extensions implemented by using PHP extension framework. PHP database extensions are used to write PHP code for accessing the database. They expose database API to provide interfaces to use database functions.

MySQL extension is deprecated and will not be available in future PHP versions. It is recommended to use the MySQLi extension with PHP 5.5 and above.

MySQL:

• MySQL extension added in PHP version 2.0. and deprecated as of PHP
  5.5.0.



• MySQL provides the procedural interface.




• MySQL extension does not support stored procedure.




• MySQL extension lags in security and other special features,
  
  comparatively.



• Transactions are handled by SQL queries only.


• Extension directory: ext/mysql.

MySQLi

• MySQLi extension added in PHP 5.5 and will work on MySQL 4.1.3 or
  above.


• MySQLi supports prepared statements.


• MySQLi provides both procedural and object-oriented interface.


• MySQLi supports store procedure.


• MySQLi extension is with enhanced security and improved debugging.


• MySQLi supports transactions through API.


• Extension directory: ext/mysqli.

For MySQLi installation, click here : http://php.net/manual/en/mysqli.installation.php

Note:

Though MySQL extension is deprecated, for backward compatibility it will be available. But do not use if you are starting something new and recommend to migrate the older from mysql to mysqi extension.

Other MySQLi Advantages

• MySQLi function mysqli_query() allows to enforce error prone queries
  and prevents bugs like SQL injection.


• Using MySQLi data fetch, we can get buffered or unbuffered based on
  server resource size.


• MySQLi API allows executing multiple queries with single expression
  using multi_query() function.

You can connect your application to database by two ways :

1. Connection with the object oriented way


2. Connection with the procedural way

Example (MySQLi Procedural)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) 
 die("Connection failed: " . mysqli_connect_error());


$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) 
 // output data of each row
 while($row = mysqli_fetch_assoc($result)) 
 echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
 
 else 
 echo "0 results";


mysqli_close($conn);
?>

Example (MySQLi Object-oriented)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) 
 die("Connection failed: " . $conn->connect_error);
 

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) 
 echo "<table><tr><th>ID</th><th>Name</th></tr>";
 // output data of each row
 while($row = $result->fetch_assoc()) 
 echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]." ".$row["lastname"]."</td></tr>";
 
 echo "</table>";
 else 
 echo "0 results";

$conn->close();
?>

Select Data With PDO (+ Prepared Statements)

<?php
echo "<table style='border: solid 1px black;'>";
echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>";

class TableRows extends RecursiveIteratorIterator 
 function __construct($it) 
 parent::__construct($it, self::LEAVES_ONLY); 
 

 function current() 
 return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
 

 function beginChildren() 
 echo "<tr>"; 
 

 function endChildren() 
 echo "</tr>" . "n";
 
 

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try 
 $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests"); 
 $stmt->execute();

 // set the resulting array to associative
 $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
 foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) 
 echo $v;
 

catch(PDOException $e) 
 echo "Error: " . $e->getMessage();

$conn = null;
echo "</table>";
?>