Solved

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

Posted on 2016-09-08
10
71 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 52

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 24

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
 
LVL 52

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 52

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 52

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

911 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