Sql query from existing page

General PHP Help
#1

I am trying to query my database and use the variables as parameters.

   <?php
      echo "<input type='hidden' id='address' name='address'>";
      echo "<input type='hidden' id='city' name='city'>";
      echo "<input type='hidden' id='state' name='state'>";
     
      $sql = "SELECT * FROM Work WHERE SCAddress = '$address' AND SCCity = '$city' AND SCState = '$state' LIMIT 2"; 
      $result = mysqli_query($conn, $sql);
      if (mysqli_num_rows($result) > 0) {
         while ($row = mysqli_fetch_assoc ($result)) {
          

            echo "<br>";
            echo $row['AssignedTo'];
            echo "<br>";
            echo $row['WorkCompleted'];
            echo "</p>";
         }
      } else {
      echo "There is no work history";
      }
   ?>

How do I get the values of the hidden inputs to be used as the AND parameters?

#2

If this is part of a search, you should use a get method form/links and propagate any existing get method parameters between page requests. If this is for displaying work history related to a specific job/order/person…, the work history records should be related through an id back to the specific job/order/person they belong to and you would select and use that id to display the related work history records.

#3

It is related to a specific address, not job number. That’s why I have the address, city, and state as the search parameters. It is for viewing any previous work done at an address. I was able to transfer the values of the address, city, and state to the hidden inputs with javascript, but the sql query doesn’t seem to see the values there.

#4

I sounds like you are overly complicating this.

The work history records should not repeat the address/city/state information in each record. Your data is not normalized.

As to your current problem, what code do you have that’s setting the $address, $city, and $state variables?

Also, you should not put external/unknown data directly into the sql query statement. You should use a prepared query.

#5

You can also get to a point where the database is so normalized that it turns into a hindrance.

For a workorder system, having city, state, and all that becomes a nuisance. Have an address table that has the address, city, and state. Link that to a customer in a many to many form, since a property can be sold. When you search you can search that table, find the work that was done to it, and whom the owner was at the time the work was completed. Cities and states don’t change, so using their text value isn’t a big deal and greatly reduces joins that are just going to add overhead.

Sponsor our Newsletter | Privacy Policy | Terms of Service