Sql - update table using LIKE and Where clause

General PHP Help
#1

Iam trying to update a table using where clause, but the query updates all the rows to the same result (image) in the image column:

<form action="" method="POST">                                  
<input type="text" name="fname" value="<?php echo $items['firstname']?>"/><br>
</form>

$image =addslashes(file_get_contents($_FILES['webcam']['tmp_name']));
$sql ="update employee set image='$image' where firstname LIKE '%".$_POST['fname']."%'";

When I try: 'where firstname= ‘firstname’ ’ (just for testing)an exiting user then it works fine. The problem is when I try to put the input value: 'firstname LIKE ‘%".$_POST[‘fname’]."%’";

#2

In real life, you can easily have more than one employee with the same firstname. You would not use the firstname to accomplish this.

If you are updating information for a specific employee, you would provide a means of selecting the specific employee’s id (auto-increment primary index in the employee table), then use the selected id as an exact match in the WHERE clause.

Also, for the posted form, that cannot upload a file (missing the enctype attribute and a type=‘file’ field), so, there cannot be both the $_FILES and $_POST data at the same time that you are showing being used in the code. The symptom you are getting is that of an empty $_POST[‘fname’] value, which will match everything due to the wild-card %% characters. I suspect your html markup isn’t submitting what you think it is. You would need to post your whole actual code to get help with why it isn’t working.

#5

This is my index file

<html lang="en">
<head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
    <title>Webcam</title>
</head>
<body>
 <div class="container">
        <div class="row">
            <div class="col-md-12">
                <div class="card mt-4">
                    <div class="card-header">
                        <h4>How to make Search box & filter data in HTML Table from Database in PHP MySQL </h4>
                    </div>
                    <div class="card-body">
                        <div class="row">
                            <div class="col-md-7">

                                <form action="" method="POST">
                                    <div class="input-group mb-3">
                                        <input type="text" name="search" id="search" required value="<?php if(isset($_POST['search'])){echo $_POST['search']; } ?>" class="form-control" placeholder="Search data">
                                        <button type="submit" class="btn btn-primary">Search</button>
                                    </div>
									
                                </form>

                            </div>
                        </div>
                    </div>
                </div>
            </div>

            <div class="col-md-12">
                <div class="card mt-4">
                    <div class="card-body">
                        <table class="table table-bordered">
                            <thead>
                                <tr>
                                    
                                    <th>First Name</th>
                                    <th>Last Name</th>
									<th>Bild</th>
                                    
                                </tr>
                            </thead>
                            <tbody>
                                <?php 
                                    $con = mysqli_connect("localhost","root","","cam");

                                    if(isset($_POST['search']))
                                    {
                                        $filtervalues = $_POST['search'];
                                        $query = "SELECT * FROM cam WHERE CONCAT(firstname,lastname) LIKE '%$filtervalues%' ";
                                        $query_run = mysqli_query($con, $query);

                                        if(mysqli_num_rows($query_run) > 0)
                                        {
                                            foreach($query_run as $items)
                                            {
                                                ?>
                                                <tr>
                                                    <form action="" method="POST">
												
													<input type="text" name="fname" value="<?php echo $items['firstname']?>"/><br>
													</form>
                                                    <td><?= $items['firstname']; ?></td>
                                                    <td><?= $items['lastname']; ?></td>
													
												    <td><img class="zoom" src="data:image/jpeg;base64,<?php  echo base64_encode($items['bilder']);?>" /></td>
												
                                                    
                                                </tr>
                                                <?php
                                            }
                                        }
                                        else
                                        {
                                            ?>
                                                <tr>
                                                    <td colspan="4">No Record Found</td>
                                                </tr>
                                            <?php
                                        }
                                    }
                                ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>


<div id="my_camera"></div>
<input type=button value="Take Snapshot" onClick="take_snapshot()">
 
<div id="results" >
</div>
 
<!-- Script -->
<script type="text/javascript" src="webcam.min.js"></script>


</body>
</html>



<!-- Code to handle taking the snapshot and displaying it locally -->
<script language="JavaScript">

 // Configure a few settings and attach camera
 Webcam.set({
  width: 320,
  height: 240,
  image_format: 'jpeg',
  jpeg_quality: 90
 });
 Webcam.attach( '#my_camera' );

 // preload shutter audio clip
 var shutter = new Audio();
 shutter.autoplay = true;
 shutter.src = navigator.userAgent.match(/Firefox/) ? 'shutter.ogg' : 'shutter.mp3';

function take_snapshot() {
 // play sound effect
 shutter.play();
 
 // take snapshot and get image data
 Webcam.snap( function(data_uri) {
 
  Webcam.upload( data_uri, 'saveimage.php', function(code, text,Name) {
                    document.getElementById('results').innerHTML = 
                    '' + 


 // display results in page
 //document.getElementById('results').innerHTML = 
 '<img src="'+data_uri+'"/>';
    
	
		
	
 } );
  
  
 } );
}

</script>
#6 
<div id="my_camera"></div>
<input type=button value="Take Snapshot" onClick="take_snapshot()">
 
<div id="results" >
</div>
 
<!-- Script -->
<script type="text/javascript" src="webcam.min.js"></script>


</body>
</html>



<!-- Code to handle taking the snapshot and displaying it locally -->
<script language="JavaScript">

 // Configure a few settings and attach camera
 Webcam.set({
  width: 320,
  height: 240,
  image_format: 'jpeg',
  jpeg_quality: 90
 });
 Webcam.attach( '#my_camera' );

 // preload shutter audio clip
 var shutter = new Audio();
 shutter.autoplay = true;
 shutter.src = navigator.userAgent.match(/Firefox/) ? 'shutter.ogg' : 'shutter.mp3';

function take_snapshot() {
 // play sound effect
 shutter.play();
 
 // take snapshot and get image data
 Webcam.snap( function(data_uri) {
 
  Webcam.upload( data_uri, 'saveimage.php', function(code, text,Name) {
                    document.getElementById('results').innerHTML = 
                    '' + 


 // display results in page
 //document.getElementById('results').innerHTML = 
 '<img src="'+data_uri+'"/>';
    
	
		
	
 } );
  
  
 } );
}

</script>
#7

Quit deleting your posts. Use three back-ticks ``` around your code (before the first line and after the last line), like you did in the 1st post in this thread, or just let someone with the ability to edit your post do it for you.

1 Like
#8 
<!-- CSS -->
<style>
#my_camera{
 width: 320px;
 height: 240px;
 border: 1px solid black;
}
</style>
<center>
<!-- -->

<!doctype html>
<html lang="en">
<head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
    <title>Webcam</title>
</head>
<body>
 <div class="container">
        <div class="row">
            <div class="col-md-12">
                <div class="card mt-4">
                    <div class="card-header">
                        <h4>How to make Search box & filter data in HTML Table from Database in PHP MySQL </h4>
                    </div>
                    <div class="card-body">
                        <div class="row">
                            <div class="col-md-7">

                                <form action="" method="POST">
                                    <div class="input-group mb-3">
                                        <input type="text" name="search" id="search" required value="<?php if(isset($_POST['search'])){echo $_POST['search']; } ?>" class="form-control" placeholder="Search data">
                                        <button type="submit" class="btn btn-primary">Search</button>
                                    </div>
									
                                </form>

                            </div>
                        </div>
                    </div>
                </div>
            </div>

            <div class="col-md-12">
                <div class="card mt-4">
                    <div class="card-body">
                        <table class="table table-bordered">
                            <thead>
                                <tr>
                                    
                                    <th>First Name</th>
                                    <th>Last Name</th>
									<th>Bild</th>
                                    
                                </tr>
                            </thead>
                            <tbody>
                                <?php 
                                    $con = mysqli_connect("localhost","root","","cam");

                                    if(isset($_POST['search']))
                                    {
                                        $filtervalues = $_POST['search'];
                                        $query = "SELECT * FROM cam WHERE CONCAT(firstname,lastname) LIKE '%$filtervalues%' ";
                                        $query_run = mysqli_query($con, $query);

                                        if(mysqli_num_rows($query_run) > 0)
                                        {
                                            foreach($query_run as $items)
                                            {
                                                ?>
                                                <tr>
                                                    <form action="" method="POST">
												
													<input type="text" name="fname" value="<?php echo $items['firstname']?>"/><br>
													</form>
                                                    <td><?= $items['firstname']; ?></td>
                                                    <td><?= $items['lastname']; ?></td>
													
												    <td><img class="zoom" src="data:image/jpeg;base64,<?php  echo base64_encode($items['bilder']);?>" /></td>
												
                                                    
                                                </tr>
                                                <?php
                                            }
                                        }
                                        else
                                        {
                                            ?>
                                                <tr>
                                                    <td colspan="4">No Record Found</td>
                                                </tr>
                                            <?php
                                        }
                                    }
                                ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>


<div id="my_camera"></div>
<input type=button value="Take Snapshot" onClick="take_snapshot()">
 
<div id="results" >
</div>
 
<!-- Script -->
<script type="text/javascript" src="webcam.min.js"></script>


</body>
</html>



<!-- Code to handle taking the snapshot and displaying it locally -->
<script language="JavaScript">

 // Configure a few settings and attach camera
 Webcam.set({
  width: 320,
  height: 240,
  image_format: 'jpeg',
  jpeg_quality: 90
 });
 Webcam.attach( '#my_camera' );

 // preload shutter audio clip
 var shutter = new Audio();
 shutter.autoplay = true;
 shutter.src = navigator.userAgent.match(/Firefox/) ? 'shutter.ogg' : 'shutter.mp3';

function take_snapshot() {
 // play sound effect
 shutter.play();
 
 // take snapshot and get image data
 Webcam.snap( function(data_uri) {
 
  Webcam.upload( data_uri, 'saveimage.php', function(code, text,Name) {
                    document.getElementById('results').innerHTML = 
                    '' + 


 // display results in page
 //document.getElementById('results').innerHTML = 
 '<img src="'+data_uri+'"/>';
    
	
		
	
 } );
  
  
 } );
}

</script>
#9 
<?php

//set random name for the image, used time() for uniqueness
require_once('db.php'); 




$filename =  time() . '.jpg';
$filepath = 'cam/';
if(!is_dir($filepath))
   mkdir($filepath);
if(isset($_FILES['webcam'])){	
   //move_uploaded_file($_FILES['webcam']['tmp_name'], $filepath.$filename);

   $image =addslashes(file_get_contents($_FILES['webcam']['tmp_name']));
   
   
   $sql ="update cam set bilder='$image' where firstname LIKE '%".$_POST['fname']."%'";
   
   //$sql="insert into cam(firstname,lastname,cam) VALUES('treet','hfghfg','$image')";
   
   //$sql ="update cam set cam=LOAD_FILE('C:/wamp64/www/cam/cam/'+'$filename') where firstname LIKE '%".$_POST['search']."%'";
   $result=mysqli_query($con,$sql);
   echo $filepath.$filename;
   echo $_FILES['webcam']['tmp_name'];
   
}
?>
#10

thank you for your help, the secont one is my saveimage.php file where I have my update query.

#11

I know: $_POST[‘fname’] is undefianed, but this is just one of many my attempts :frowning:

#12

The idea is to search for a user in the search box, if the user hasnt an image then take snapshot and store it in the database, thats why I need to update the table after.

Sponsor our Newsletter | Privacy Policy | Terms of Service