Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 113
  • Last Modified:

I need an efficient way to write data from a select (multiple) field form to MySQL PHP

I want to know the best way to send data back to MySQL when you have multiple selections in a form.

Table
contacts [firstname] [lastname]
contact_tag [contact_id] [tag_id]
0
ido90
Asked:
ido90
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Julian HansenCommented:
Can you be a bit more specific?

What does your database structure look like?
What does your form data look like?

The more information you can provide the easier it will be to understand your request.
0
 
Ray PaseurCommented:
As a practical matter, software efficiency is irrelevant  when you're talking about getting external input from a client form.  Clients respond in multiple seconds.  Disks rotate in a few milliseconds.  Processors run instructions in some small number of nanoseconds.  So your focus should be on the user-experience, because it's about three to nine orders of magnitude more time-important than what happens after the data reaches your server!
0
 
Pawan KumarDatabase ExpertCommented:
The best way is to write a store procedure. IF you can give me more details I can write it for you !!
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Julian HansenCommented:
I am assuming you mean something like this
  <form action="reflect.php" method="post">
    <select multiple name="data[]">
      <option value="1">One</option>
      <option value="2">Two</option>
      <option value="3">Three</option>
      <option value="4">Four</option>
      <option value="5">Five</option>
    </select>
    <input type="submit" />
  </form>

Open in new window

Note the name of the field in the above example is data[].

To explain how POST/GET data is presented in the case of a multiple select
In a POST with a select with attribute multiple values are submited as (assume name="controlname")
controlname=value1&controlname=value2&controlname=value3...

Open in new window

When PHP is asked for $_POST['controlname'] - it will return the last value with that id - in this case value3.
To get access to all the data specify the name with [] at the end - this will result in a POST that looks like this
controlname[]=value1&controlname[]=value2&controlname[]=value3...

Open in new window

Now when you ask PHP for $_POST['controlname'] you get a variable back that is an array with all the values that had controlname[] as the key. You can now iterate through that array using a foreach or similar.

$data = isset($_POST['data']) ? $_POST['data'] : false;
if ($data && is_array($data)) {
   foreach($data as $item) {
        // append to Query here
   }
}

Open in new window

0
 
Ray PaseurCommented:
In an online forum like E-E It's often difficult to discern the level of expertise that underlies a question.  If you need general background knowledge, you might want to start here.  If you already have some familiarity with HTTP requests using PHP and MySQL, and you're looking for a table maintenance script, this article might give you some good ideas:
https://www.experts-exchange.com/articles/12335/PHP-and-MySQLi-Table-Maintenance.html
0
 
ido90Author Commented:
Based on the comments above this is what I came up with so far:
$tags = isset($_POST['tags']) ? $_POST['tags'] : false;
        global $database;
        $pre_sql = "delete from contact_tag where contact_id='$id'";
        $result_set = $database->query($pre_sql);
        if ($tags && is_array($tags)) {
            foreach($tags as $tag) {
                $id = !empty($_GET['id']) ? (int)$_GET['id'] : $newContact->id;
                $sql = "INSERT into contact_tag (contact_id, tag_id) VALUES ('$id','$tag')";
                $result_set = $database->query($sql);
            }
        }

Open in new window

Is this the best way? Also is there a way to make this code a bit more reusable?
0
 
Julian HansenCommented:
Firstly - don't use global. PHP supports it but it is not a good thing for a variety of reasons. Pass your database to functions as a parameter or use a database class - don't use global.

Secondly, are you sure you want to do the delete before you have verified that the incoming tags are valid?

In terms of the code you
1. can do a batch query
2. move the loop invariant $_GET['id'] out of the loop
  ...
  // CREATE THE QUERY PREFIX
  $sql = "INSERT iINTO contact_tag (contact_id, tag_id) VALUES ";

  // GET THE id THIS IS CONSTANT SO NO NEED FOR
  // IT TO BE IN THE LOOP
  $id = !empty($_GET['id']) ? (int)$_GET['id'] : $newContact->id;

  // ADD VALUES AS A BATCH INSERT
  foreach($tags as $tag) {
    $sql .=" ('{$id}','{$tag}'),";
  }

  // REMOVE THE TRAILING ,
  trim($query, ',');

  // RUN ONE QUERY AS A BATCH
  $result_set = $database->query(sql);

Open in new window

Not sure what you mean by making it more re-usable?
0
 
ido90Author Commented:
You mean putting a query before the delete to verify if they correct?
0
 
ido90Author Commented:
Also in terms of the delete is there a better way to check if they exist and entering or deleting the values that correspond to what the user input?
0
 
Julian HansenCommented:
better way to check if they exist and entering or deleting the values that correspond to what the user input
Can you elaborate.
What is the objective?
When they submit must all previous combinations for this contact_id be removed and replaced with the new ones?
Or are the new ones meant to add to what is there?

I am assuming from your code that it is the former - in which case you have to purge the database first before adding the new entries to ensure there is no residue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now