Solved

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

Posted on 2016-09-08
10
75 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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 54

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 109

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 54

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 109

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 54

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 54

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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.

770 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