Core iTOps Tube

Thursday, 26 April 2012

MySQL bulk retrieval of database thru html form

I am trying to retrieve multiple entries from MySQL database through html form with PHP script.

After spending a couple of days searching any examples, all the post/forum/tutorials are about SINGLE entry retrieval, that I can accomplish with ease of copy-paste example code. But, when dealing with MULTIPLE entries, I am stuck. The ideas seem the same, but differ in code obviously. I decided to try here to get some luck.

First I created the form in the webpage with

Code:


//retrieve.html

<html>

<head>

<title>Record Retrieve Form</title>

</head>

<body>

<form action="select.php" method="POST">

<p><b>Items to retrieve:</b><br>

<!-- <input type="text" name="testField" size="80">  -->

<textarea name="testfield" cols="40" rows="5"> </textarea> 

<p><input type="submit" name="submit" value="retrieve record"></p>

</form>

</body>

</html>


Then I create php script to retrieve the entries:


Code:


<?php

//select.php

$mysqli = new mysqli("localhost", "yifangt", "PASSWORD", "testDB");

 

if (mysqli_connect_errno()) {

    printf("Connect failed: %s\n", mysqli_connect_error());

    exit();

} else {

              foreach (explode("\n", $_POST["testfield"], 50) as $value) {       

            $sql = "SELECT * FROM testTable WHERE id=$value;";

        $res = mysqli_query($mysqli, $sql);

            if ($res) {

        while ($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC)) {

            $id  = $newArray['id'];

            $testField = $newArray['testfield'];

            echo "The ID is ".$id." and the text is ".$testField."<br/>";

                  }

                    }

               

          else {

            printf("Could not retrieve records: %s\n", mysqli_error($mysqli));

            }

 

    mysqli_free_result($res);

    mysqli_close($mysqli);

  }  } 

?>


What I want is to retrieve multiple entries by submitting the query ID with the form:


Code:


----------

1      |

3      |

5      |

-----------

[submit]


The output is here:


Code:


The ID is 1 and the text is

Could not retrieve records:  Could not retrieve records:  Could not retrieve records:


Only the first entry ID was retrieved, but not the testField, or any other queries. However, MySQL testTable is there without anything wrong as tested:


Code:


mysql> select * from testTable;

+----+------------------------------------------------------+

| id | property description                                          |

+----+------------------------------------------------------+

|  1 | some value                                          |

|  2 | second test value                                    |

|  3 | First text by insert_form.html                      |

|  4 | second test value                                    |

|  5 | insert_form.html and insert.php are working together |

| 6 | trrtoijgeijg                                        |

+----+------------------------------------------------------+

6 rows in set (0.00 sec)


It seems the problem is with the explode() function.

1) Can anybody help me out of this? I need to sort this out for learning purpose.

2) As a beginner, I am not sure if this is the right way to do the online database query for bioinformatic data, like DNA sequence search.

3) If not, what is the right technology to do this kind search? Thanks a lot!



Yifang




No comments:

Post a Comment