Solved

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

Posted on 2016-09-08
10
86 Views
Last Modified: 2016-09-11
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
Comment
Question by:ido90
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 41790447
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41790471
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41790655
The best way is to write a store procedure. IF you can give me more details I can write it for you !!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 57

Expert Comment

by:Julian Hansen
ID: 41790762
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41790887
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
 

Author Comment

by:ido90
ID: 41793243
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
 
LVL 57

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 41793259
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
 

Author Comment

by:ido90
ID: 41793307
You mean putting a query before the delete to verify if they correct?
0
 

Author Comment

by:ido90
ID: 41793313
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
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 41793323
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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question