javascripts in php echo() with sql values

Question

I am building a web app and I have bumped into a roadblock that have been causing me trouble for a while.

The code I wrote is supposed to "INSERT/UPDATE" the chosen element value from the drop-down list to the RESPECTIVE $row['Visited']. I am confused at this point and i have tried using some javascript codes i got form google but nothing work. All i am getting is updated values only to the first row, eventhough i have selected the dropdown form from the second row. I have attached images of database

However, I am not getting the expected outcome so I must be doing something wrong so please, take a look at my code and tell me what can I do to achieve the desired result.

PHP/HTML

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "newtarget";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT *
        FROM fos_data
        WHERE Fos_Name='".$_GET["clientID"]."'
        AND Route='".$_GET["fos_route"]."'" ;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
echo "<div class='form1'>
Buyer: ".$row['Buyer_Name']."<br />
Name: ".$row['Fos_Name']."<br />
Route: ".$row['Route']."<br />
Visited: ".$row['Visited']." 

<form id='form'>
    Have You Visited this Buyer Place?
    <select id='mySelect'>
        <option value='yes'>YES</option>
        <option value='no'>NO</option>
    </select>
    <input type='submit'>
</form>
</div><br>";
}
}
else
{
    echo "0 results";
}
$conn->close();
?>

SQL

-- phpMyAdmin SQL Dump
-- version 4.9.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Mar 26, 2020 at 07:41 AM
-- Server version: 10.4.10-MariaDB
-- PHP Version: 5.6.40

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `newtarget`
--

-- --------------------------------------------------------

--
-- Table structure for table `fos_data`
--

DROP TABLE IF EXISTS `fos_data`;
CREATE TABLE IF NOT EXISTS `fos_data` (
  `Buyer_Name` varchar(100) DEFAULT NULL,
  `Fos_Name` varchar(100) DEFAULT NULL,
  `Fos_Pass` double DEFAULT NULL,
  `Fos_Phone` double DEFAULT NULL,
  `Route` double DEFAULT NULL,
  `Visited` varchar(100) DEFAULT NULL,
  `Report` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fos_data`
--

INSERT INTO `fos_data` (`Buyer_Name`, `Fos_Name`, `Fos_Pass`, `Fos_Phone`, `Route`, `Visited`, `Report`) VALUES
('Buyer 1', 'Fos 1', 1, 1111, 1, '', ''),
('Buyer 2', 'Fos 1', 1, 1111, 1, '', ''),
('Buyer 3', 'Fos 1', 1, 1111, 1, '', ''),
('Buyer 4', 'Fos 1', 1, 1111, 2, '', ''),
('Buyer 5', 'Fos 1', 1, 1111, 2, '', ''),
('Buyer 6', 'Fos 1', 1, 1111, 2, '', ''),
('Buyer 7', 'Fos 1', 1, 1111, 3, '', ''),
('Buyer 8', 'Fos 1', 1, 1111, 3, '', ''),
('Buyer 9', 'Fos 1', 1, 1111, 3, '', ''),
('Buyer 10', 'Fos 1', 1, 1111, 3, '', ''),
('Buyer 11', 'Fos 2', 2, 2222, 1, '', ''),
('Buyer 12', 'Fos 2', 2, 2222, 1, '', ''),
('Buyer 13', 'Fos 2', 2, 2222, 1, '', ''),
('Buyer 14', 'Fos 2', 2, 2222, 2, '', ''),
('Buyer 15', 'Fos 2', 2, 2222, 2, '', ''),
('Buyer 16', 'Fos 2', 2, 2222, 2, '', ''),
('Buyer 17', 'Fos 2', 2, 2222, 2, '', ''),
('Buyer 18', 'Fos 2', 2, 2222, 3, '', ''),
('Buyer 19', 'Fos 2', 2, 2222, 3, '', ''),
('Buyer 20', 'Fos 2', 2, 2222, 3, '', '');
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Here are the image of what I want to achieve. MAIN PAGE IMAGE I appreciate your help, Thank you!

Answer 1

A short and simple answer to your question is yes, the entire code is completely wrong, so here are few things to pay attention to...

1. First, get the basic knowledge about html, how it works and what the tags represent, then, whenever you write html code, make sure to open the <html> tag at the beginning of the document and close it at the ending </html>, the same thing applies to the <body>...</body> tags... you can't use a tag before opening the document, I mean you can but you shouldn't, this is the proper way to do it

   <html>
       <body>
       <!--Your code here-->
       </body>
   </html>
   

2. Learn to use the single quotes and double quotes properly. If you pay closer attention you will notice that your statements are a complete mess when it comes to using quotes, I guess that you were playing with the quotes until you made this thing work as it is working right now so try to write few scripts with a purpose of learning to use the quotes (save up your efforts and take notes, you may want or need to pass on the knowledge later on in life) leading us to the third point...

3. Try to build a habit of writing a clean code no matter what language you are writing in, write as clean as possible, you won't be always perfect but you will get better with each line written

   echo "<div class='form1'>
       Buyer: 13<br />
       Name: Lucas<br />
       Route: 123<br />
       Visited: lot of places
   
       <form id='form'>
           Select your favorite fruit:
           <select id='mySelect'>
               <option value='apple'>Apple</option>
               <option value='orange'>Orange</option>
               <option value='pineapple'>Pineapple</option>
               <option value='banana'>Banana</option>
           </select>
           <input type='submit'>
       </form>
       <p id='demo'></p>
       </div><br />";
   

4. When providing a code in your questions on stack overflow, try to provide as much relevant details as possible as a way to get the best answer possible. Take a moment to look at your code... You haven't posted the part where you retrieve data from your db, you haven't separated the JS from the PHP/HTML, you haven't even closed your </script> tag. More important, when asking a question try to be as clear as possible, I really had a hard time understanding what your question is and even if someone was about to answer your question, they would have needed to base half of their answers on assumptions over your code or rewrite most of it for testing purposes. By posting low quality questions, you got low chances of getting any answer at all and when you invest more time in creating a better quality question where you go into details with your problem and provide relevant information, people are more likely to get interested in helping you resolve your issue. However, in order to have enough relevant information regarding the problem, you need to try to understand what are you facing, then search the web for possible solutions and after you've gathered enough information about your problem, then ask for help, including every relevant detail that you gathered along the way

5. Regarding your question, I would recommend you not using JavaScript inside PHP since there are other ways to achieve the desired outcome as it is an event listener in your case, take a look at this

   document.getElementById('form').addEventListener('submit', function(event){
       event.preventDefault();
       var x = document.getElementById('mySelect').value;
       document.getElementById('demo').innerHTML = x;
   });
   

   Lastly, since I don't have the answer to your main question, here is a better way to ask the question and possibly get an answer or an alternative solution (feel free to copy this to a new post, modify it to your needs accordingly)

Hey there Developers, I am building a web app/ writing a script/ practicing my coding knowledge and I have bumped into a roadblock that have been causing me trouble for a while.

The code I wrote is supposed to return the chosen element from the drop-down list to an empty paragraph placed at the end of each loop return and what I expect from this code is whenever I click the submit button, for the chosen fruit from the form that I submitted to be printed on the paragraph that belongs to the same loop result. However, I am not getting the expected outcome so I must be doing something wrong so please, take a look at my code and tell me what can I do to achieve the desired result.

PHP/HTML

$result = mysqli_query($mysqli, 'SELECT * FROM info');
while($row = $result->fetch_assoc()) {
echo "<div class='form1'>
    Buyer: ".$row['Buyer_Name']."<br />
    Name: ".$row['Fos_Name']."<br />
    Route: ".$row['Route']."<br />
    Visited: ".$row['Visited']."

    <form id='form'>
        Select your favorite fruit:
        <select id='mySelect'>
            <option value='apple'>Apple</option>
            <option value='orange'>Orange</option>
            <option value='pineapple'>Pineapple</option>
            <option value='banana'>Banana</option>
        </select>
        <input type='submit'>
    </form>
    <p id='demo'></p>
    </div><br>";
}

JavaScript

document.getElementById('form').addEventListener('submit', function(event){
    event.preventDefault();
    var x = document.getElementById('mySelect').value;
    document.getElementById('demo').innerHTML = x;
});

Here are some extra images of what I want to achieve and how it actually results (your images here)...

I appreciate your help, Thank you!

So, you see, don't be afraid to play with words because you are not only learning code here, but you are also practicing your communication skills and enriching your vocabulary and the more time you invest in writing a quality question, the better chances of getting a solution for your problem you have, don't rush to ask a quick question so you can get a quick answer, rather get occupied with the problem you are facing and work around it until you find the solution or at least you have some understanding of what kind of problem you are actually facing so then you can ask for assistance.

I hope this will help you to start expressing yourself better and ask better questions in the future, have a good one, Peace!

Answer 2

Okay so here is a way to do it on the server side

connection.php

$server = 'localhost';
$username = 'root';
$password = '';
$database = 'test';
$conn = new mysqli ($server,$username,$password,$database);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

main.php

<?php
    include 'connection.php';
?>
<html>
    <body>
    <?php
        $result = mysqli_query($conn, 'SELECT * FROM info');
        while($row = $result->fetch_assoc()) {
        echo "<div class='form1'>
            Buyer: ".$row['Buyer_Name']."<br />
            Name: ".$row["Fos_Name"]."<br />
            Route: ".$row["Route"]."<br />
            Visited: ".$row["Visited"]."

            <form id='form' method='POST' action='process.php'>
                Select your favorite fruit:
                <select id='mySelect' name='selection'>
                    <option value='yes'>YES</option>
                    <option value='no'>NO</option>
                </select>
                <input type='hidden' name='buyer_id' value=".$row['Buyer_Id'].">
                <input type='submit' name='update'>
            </form>
            <p id='demo'></p>
            </div><br>";
        }
    ?>
    <body>
</html>

process.php

<?php
include 'connection.php';

if(isset($_POST['update'])){

    $buyer_id = mysqli_real_escape_string($conn, $_POST['buyer_id']);
    $selection = mysqli_real_escape_string($conn, $_POST['selection']);

    $sql = "SELECT * FROM info WHERE Buyer_Id='$buyer_id'";
    $result = mysqli_query($conn, $sql);
    $rows = mysqli_num_rows($result);

    if(mysqli_num_rows($result) > 0){
        $update = "UPDATE info SET Visited='$selection' WHERE Buyer_Id='$buyer_id'";
        $run = mysqli_query($conn, $update);
        header('Location: main.php');
    }
    else{
        echo "There was an error!";
    }
}
?>

So, as you can see, you need to tell the code what to do when you submit the form and how you do that is by defining a form action^, which's purpose is to redirect the browser to a new document in which document you tell it, if this form has really been submitted (because someone may land on the same document process.php by hand), so if the form has really been submitted then perform the desired action which in your case is updating the visited column for the selected row.

Now one of the ways to let the code know which row you want to update is by using a hidden input with a value of something as unique as a row's ID.

I am using mysqli_real_escape_string when passing on the values and assigning them to a variable as a security measure and a defensive mechanism from SQL Injections because everything that can be manipulated by the client can not be trusted and needs to pass through a filter, making sure that the client is not trying to pass some SQL statement, so put this into practice too.

Another thing that I would recommend not doing is using pure $_GET[''] variables into an SQL statement, even if you have to $_GET[''] the value let's say through the link, rather store it in a variable $clientid = $_GET['clientID']; than using the $_GET[''] into the SQL statement directly, if nothing else, it looks better, cleaner and it is easier to write.

However, this method is done server side and requires you to refresh the page in order for the browser to show the updated information (notice that the page refreshes upon submission), what this code actually does is that it sends the browser to another document process.php and then after successfully updating the database it returns you to the main page main.php so that is why you can immediately see the change, but it does the refresh anyway and this may be a bit annoying when you are trying to return a message as "User Updated" or "There was an error" and if you want to achieve this without the page refresh, you need to learn about ajax which is a method of handling server side requests on the client machine without refreshing the document.

^ Form's attribute action does not necessarily have to redirect the browser to a new document, it can also tell it to execute a code that is written in the same document upon refresh and this can be achieved by leaving the value empty action='' or using a hash symbol action='#'