'How to execute sql code based on fetch array
I have a search engine that I found on google and I would like to search in the 'barcode' database that I have and if it finds the person it executes the code that is under $statement (I added that code under $statement that wasn't include in the code that I found), I'm not interested in an echo of the information, that is why I eliminated that part and placed with a refresh, I would only be interested in executing the changes in the sql once it finds the person you are looking for, it does not have to be the code like this below, I really don't know how to do it and I'm here for help.
<?php
include 'includes/conn.php';
include 'includes/scripts.php';
if (isset($_POST['no'])) {
$sca=trim($_POST['no'],"");
$credentials="";
$new2 ="SELECT * FROM `barcode`";
$mysqli = new $conn;
$statement= $mysqli->prepare("Insert IGNORE into voters
Select * from barcode where id = id;
DELETE from barcode where id IN(SELECT id from voters)");
$res2=mysqli_query($conn, $new2);
while($row=mysqli_fetch_array($res2)){
if($row['credentials'] == $sca){
$statement->execute();
header("refresh: .5");
}
}
}
$statement->close();
mysqli_close($conn);
?>
Solution 1:[1]
It's not clear what your Insert logic is and what exactly you want to insert but let me give you how I would structure such a query (including some safeguards such as prepared statements) and hopefully you can just change the SQL statements based on what you need. I have left comments on most rows to explain
<?php
include 'includes/conn.php';
include 'includes/scripts.php';
if (isset($_POST['no'])) {
$sca=trim($_POST['no'],"");
$credentials="";
$sql = "SELECT * FROM `barcode`";
$mysqli = new $conn;
// Prepare the statement
$stmt = $mysqli->prepare($sql);
// Attempt to execute
if ($stmt->execute()) {
// Save result
$result = $stmt->get_result();
// save the result in an assoc array
$row = $result->fetch_all(MYSQL_ASSOC);
// If there is a returned entry
if (count($row) > 0) {
if ($row['credentials'] === $sca) {
// close the statement so we can re-use
$stmt->close();
// We assume id is what we need
$id = $row['id'];
// Now you have to fix your INSERT statement here. I am not sure what you need to insert but follow the general docs on how to insert https://www.php.net/manual/en/mysqli-stmt.bind-param.php
$stmt = $mysqli->prepare("INSERT IGNORE INTO `voters` (columnName) VALUES (?)");
// Here you need to decide what you are inserting and change the variable
$stmt->bind_param("s", $whatever_variable_you_insert);
// Attempt to execute
if ($stmt->execute()) {
// if successful, proceed with the deletion too... or you can put it outside this execute condition
$stmt->close();
// Prepare the delete statement
$stmt = $mysqli->prepare("DELETE FROM `barcode` WHERE id=?");
// Bind the param
$stmt->bind_para("s", $id);
if ($stmt->exceute()) {
// something else or as you wanted - refresh
header("refresh: .5");
}
}
}
}
}
}
?>
Solution 2:[2]
This is an ideal situation for a stored procedure. I assume you try to Your logic seems to be:
- find a barcode (by id and sca?)
- if a barcode is found insert into voters table
- delete barcode from the barcode table
So something like
create procedure `check_barcode` (sca int)
begin
select * into result from `code` where id = sca limit 1;
if not result is null then
insert into `voters` select * from `barcode` where id = sca;
delete from `barcode` where id = sca;
end if;
end
You did not include the table design it is a bit guesswork.
Solution 3:[3]
I get what I was looking for!
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<?php
include "includes/scripts.php";
?>
</head>
<body>
<div class="container mt-5 pt-5">
<form id="scanform" autocomplete="off" method="POST">
<div class="row">
<div class="col-sm-3">
<h1>Scan:</h1>
</div>
<div class="col-sm-6">
<div class="form-group">
<input type="text" id="no" name="no" class="form-control" required>
</div>
</div>
<div class="col-sm-3">
<button class="btn btn-info" name="sub" type="submit">Buscar</button>
</div>
</div>
</form>
</div>
</body>
</html>
<?php
include 'includes/conn.php';
if (isset($_POST['no'])) {
$sca = trim($_POST['no'],"");
$flag = 0;
$id= "";
$credentials = "";
$password = "";
$firstname = "";
$lastname = "";
$sql = "SELECT * FROM `barcode` WHERE credentials = '" . $sca . "' LIMIT 1";
$result = mysqli_query($conn , $sql);
$barcode = mysqli_fetch_assoc($result);
if ($barcode) {
$mod = "INSERT IGNORE INTO voters
Select * from barcode where id = " . $barcode['id'];
$insert_result = mysqli_query($conn , $mod);
if ($insert_result) {
$del = "DELETE from barcode where id = " . $barcode['id'];
$del_result = mysqli_query($conn , $del);
echo "<div class='alert alert-success d-flex justify-content-center mt-3'>Product has been removed from barcode!</div></div>";
} else {
echo "<div class='alert alert-danger d-flex justify-content-center mt-3'>Something went wrong while deleting from barcode!</div></div>";
}
} else {
echo "<div class='alert alert-danger d-flex justify-content-center mt-3'>Product Not Found</div></div>";
return;
}
}
mysqli_close($conn);
?>
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | theking2 |
| Solution 3 | Steven5655 |