Asked  7 Months ago    Answers:  5   Viewed   34 times

The classic transactions in a loop code:

$mysqli->query("START TRANSACTION");
foreach ($pdata as $key => $value) {
    $sql    = "INSERT INTO temp (`fund_id`) VALUES (" . $value . ")";
    $result = $mysqli->query($sql);
}
$mysqli->query("COMMIT");

Then we change to prepared statements:

$mysqli->autocommit(FALSE);
foreach ($pdata as $key => $value) {
    $sql  = "INSERT INTO temp (`fund_id`) VALUES (?)";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('i', $value);
    $stmt->execute();
}
$mysqli->commit();

Questions:

1) Are these two codes identical? Am I missing something in the second code with prepared statements?

2) Is $mysqli->commit() the same as $mysqli->query("COMMIT")?

3) Do I need to add $mysqli->query("START TRANSACTION"); for the prepared statements block or the transaction will automatically start when we set autocommit(FALSE)?

 Answers

25

Your loop can be optimized by pulling the prepare and bind_param statements out of the loop.

$value = null;
$mysqli->autocommit(FALSE);
$sql  = "INSERT INTO temp (`fund_id`) VALUES (?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $value);
foreach ($pdata as $value) {
    $stmt->execute();
}
$mysqli->commit();

You have turned off autocommit with your autocommit(FALSE) line and therefore don't need to use the START TRANSACTION statement.

Wednesday, March 31, 2021
 
penpen
answered 7 Months ago
80
$names = array("Mike", "Kyle", "Johnny", "Will", "Vasques");
for($td=0; $td<=9; $td++) {
  echo "<tr>";
  if ($td == 0) {
    foreach ($names as $name) {
      echo "<td>$name</td>";
    }
  }
  echo "<td></td>";
  echo "</tr>";
}
Wednesday, March 31, 2021
 
clean_coding
answered 7 Months ago
70

Either set mysqli into Exception mode

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

or always check the result of every mysqli operation and throw mysqli error manually:

$result = $stmt->execute();
if (!$result) {
    throw new Exception($mysqli->error);
}

this is the only way to know what's wrong with your execute();

I have found that the SQL syntax apparently needs quotation marks around the fields for the VALUE

Of course it is wrong. SQL syntax apparently needs quotation marks around strings only.

Saturday, May 29, 2021
 
Eugenie
answered 5 Months ago
100

You don't need variable variables for that, but just a for loop like this:

for( $i=1; $i<101; $i++ ) {
  $klass = get_post_meta( $post->ID, '_item' . $i, true );
  if( !empty($klass) ) {
     echo "<div class='$klass'></div>"; 
  }
}

This works as long as you do not need the $fooX variables later on. If you need them, you would have to use either mentioned variable variables or an array to collect all the values.

Saturday, May 29, 2021
 
Pradip
answered 5 Months ago
36

This is how your code should look (with added SQL Injection protection):

<?php
include "dbinfo.php"; //contains mysqli_connect information (the $mysqli variable)
//inputs
$name = mysqli_real_escape_string($_GET['name']);
$text = mysqli_real_escape_string($_GET['text']);

$sqlqr = "INSERT INTO `ncool`.`coolbits_table` (`name`, `text`, `date`) VALUES ('" . $name . "', '" . $text . "', CURRENT_TIMESTAMP);";

mysqli_query($mysqli,$sqlqr); //function where the magic happens.
?>

Take a look at what I've done. Firstly I've escaped the user input you're retrieving into the $name and $text variables (this is pretty much a must for security reasons) and as others have suggested you should preferably be using prepared statements.

The problem is that you weren't surrounding string values with single quotes ('), which is a requirement of the SQL syntax.

I hope this helps to answer your question.

Thursday, September 2, 2021
 
Pachvarsh
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :