How to fetch assoc array while using mysqli prepare

Question

To make sure my database is secure I'm using prepare statements. Here is my code:

//connecting to MySql database
$con=mysqli_connect("host","user","pass","dbname");
// checking database connection
if (mysqli_connect_errno($con)){
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$stmt = mysqli_prepare($con,"SELECT * FROM `table` WHERE emb=? LIMIT 1");
mysqli_stmt_bind_param($stmt, 's', $emb);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);

Now I want to know how can I use ASSOC fetch array

$embInfo = mysqli_fetch_array($stmt, MYSQLI_ASSOC);

I want this so that I can just put something like below to get values

$embInfo['name']

and

$embInfo['email']

I guess this would help http://stackoverflow.com/questions/15846583/mysqli-fetch-array-prepared-statement-and-like-statement And better move to PDO — Hansjörg Hofer, Sep 08 '13 at 21:06
if the mysqlnd is not available then you need to define all fields and use bind_result or [use something like this to dynamically define the fields.](http://stackoverflow.com/questions/18501513/prepared-statment-method-confused/18502088#18502088) — Prix, Sep 08 '13 at 21:09
What if I use `mysqli-stmt.get-result`? Will this help? Someone suggested this in stackoverflow chat. — mwaseema, Sep 08 '13 at 21:15

score -1 · Answer 1 · answered Mar 02 '14 at 23:02

try this:

//connecting to MySql database
$con=mysqli_connect("host","user","pass","dbname");
// checking database connection
if (mysqli_connect_errno($con)){
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$stmt = mysqli_prepare($con,"SELECT * FROM `table` WHERE emb=? LIMIT 1");
mysqli_stmt_bind_param($stmt, 's', $emb);
mysqli_stmt_execute($stmt);


while($embInfo = mysqli_fetch_array($stmt, MYSQLI_ASSOC)){
  echo 'My name is '.$embInfo['name'].'and my email is '.$embInfo['email'].'<br/>';

}

mysqli_stmt_close($stmt);

score -3 · Answer 2 · answered Sep 08 '13 at 21:17

-3

May i suggest an alternative

{
  $server = '';
  $user = '';
  $pass = '';
  $db = '';

  // connect to the database
  $mysqli = new mysqli($server, $user, $pass, $db);

  // show errors (remove this line if on a live site)
  mysqli_report(MYSQLI_REPORT_ERROR);

  $club=$_POST'club'];
  $sql = "SELECT * FROM players WHERE club = '$club'";
  $result=mysqli_query($mysqli,$sql);
  while($row = mysqli_fetch_array($result))
  {
    echo $row['player'];
  }
  }

answered Sep 08 '13 at 21:17

Sushruth Siv

45
1
8

But I think this is not secure. SQL injections can destroy database through this code. This is the reason why I am trying to use prepared statements. – mwaseema Sep 08 '13 at 21:20
$club = mysql_real_escape_string($_POST['club']); try using this – Sushruth Siv Sep 08 '13 at 22:01
@sushruth-sv real escape string is outdated and you are suggesting mysql which is deprecated. I cannot understand what are you trying to say. You should know prepare that method is most secure these days. – mwaseema Sep 08 '13 at 22:26
both more secure & faster due to the fact, the MySQL Engine is able to reuse it's old execution plan for consecutive queries – Tomer W Oct 16 '14 at 07:18

How to fetch assoc array while using mysqli prepare

2 Answers2