Solved

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

Posted on 2016-09-08
10
67 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 51

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 108

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 18

Expert Comment

by:Pawan Kumar Khowal
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
 
LVL 51

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 108

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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 51

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 51

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now