How to execute sql code based on fetch array

Question

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);
 
?>

The goal is that when I enter the name(credentials) of a person in the search bar, it looks for it in the 'barcode' database and if it finds it, execute the INSERT IGNORE code and make the modifications in the database, the INSERT IGNORE I tried it directly in phpmyadmin and it works perfect, the only thing is that I don't know how to implement it in the code — Steven5655, Apr 28 '22 at 20:09
Not true @YourCommonSense. If I want to track searches it very well makes sense to insert these. — theking2, May 09 '22 at 14:43

Djongov · Accepted Answer · 2022-05-09T14:44:42.017

0

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");
                            }
                        }
                    }
                }
        }
    }
?>

edited May 09 '22 at 14:44

answered May 09 '22 at 14:09

Djongov

195
2
13

3

Hello Dimitar. Please do not post answers that just fix some formatting issues but do not answer the question asked. Your code doesn't work and doesn't help. There are too many issues in the code and **you keep them all** in your answer. – Your Common Sense May 09 '22 at 14:18
My code is far from only formatting. What are you referring to as "doesn't work" ? – Djongov May 09 '22 at 14:21
1

How many errors do you see in this query? `Insert IGNORE into voters Select * from barcode where id = ?; DELETE from barcode where ? IN(SELECT ? from voters)"` – Your Common Sense May 09 '22 at 14:22
yes, you are right. I am editing my answer. Hopefully good now – Djongov May 09 '22 at 14:35
2

You need to stop manually checking for errors. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) and [Should I manually check for errors when calling “mysqli_stmt_prepare”?](https://stackoverflow.com/q/62216426/1839439) – Dharman May 09 '22 at 14:39
`$result->free_result();` is wrong and will cause errors. Please remove all 3 of these lines as they don't make any sense at the end of the script. – Dharman May 09 '22 at 14:43
Noted. I am only ever using this in dev by sending it to a log file or email but not displaying it. I should have commented there that it's only for displaying pruposes. – Djongov May 09 '22 at 14:43
1

PHP can display, log or email errors for you. Without any code. Without all these `if` statements. Please read the links above. – Your Common Sense May 09 '22 at 14:47
Thanks Your Common Sense. Definitely reducing the IFs after reading this. Didn't see how it can mail errors but all the rest is there! – Djongov May 09 '22 at 16:06
With a site-wide error handler you can catch all errors and then email could be an option too. The point is, one shouldn't write any code to report or silence errors - all those if's. Instead, one should make sure that the error would be *thrown* when the problem occurred. While reporting/default handling should be done elsewhere. – Your Common Sense May 10 '22 at 05:47

score 0 · Answer 2 · answered May 09 '22 at 15:06

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.

score -2 · Answer 3 · answered May 10 '22 at 05:25

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);
?>

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, May 10 '22 at 18:36

How to execute sql code based on fetch array

3 Answers3