Possible to make this code valid/speed up?

Question

I have an enrollment form which takes consumer information, stores it in session, passes from page to page then stores in a database when finished. Originally the table simply listed fields for up to 16 persons but after reading into relational databases, found this was foolish.

I have since created a table named "members" and "managers". Each enrollment will take the information input, store the manager ID in the respective table and place a reference field in each member row containing the manager ID.

While I allow up to 16 members to be enrolled at once, this can range from 1-16.

My best guess is to use a FOR-loop to run though multiple INSERT statements in the event more than 1 member is enrolled.

In the example below, I am using the variable $num to represent the individual member's information and $total to represent the number of all members being enrolled. The code here does not function but am looking for:

a) ways to correct

b) understand if there are more 'efficient' ways of doing this type of INSERT

sample code:

<?php 
    $conn = mysql_connect("localhost", "username", "pw");
    mysql_select_db("db",$conn);

    for ($num=1; $num<=$total; $num++) {

    $sql = "INSERT INTO table VALUES ('', '$clean_f'.$num.'fname', '$clean_f.$num.mi', '$clean_f.$num.lname', '$clean_f.$num.fednum', '$clean_f.$num.dob', '$clean_f.$num.ssn', '$clean_f.$num.address', '$clean_f.$num.city', '$clean_f.$num.state', '$clean_f.$num.zip', '$clean_f.$num.phone', '$clean_f.$num.email')";

    $result = mysql_query($sql, $conn) or die(mysql_error());
    }

    mysql_close($conn);
    header("Location: completed.php");
?>

score 2 · Accepted Answer · answered Aug 24 '10 at 01:28

2

If all of your statements are structurally the same, but with different parameter values, consider using the PDO extension, which supports prepared statements. The benefits of prepared statements can be read here (http://www.php.net/manual/en/pdo.prepared-statements.php), but in general, the same statement will only need to be compiled once, but can be executed as many times as you want with different parameters, which can make your script more "efficient".

Using PDO, your code could look something like:

$db = new PDO('mysql:host=localhost;dbname=db', 'username', 'pw');

$statement = $db->prepare('INSERT INTO tablename (field1, field2, field3, ...) VALUES (?,?,?,?');

for ($num=1; $num<=$total; $num++) {
    $statement->execute(array('val1', 'val2', 'val3', '...'));
}

answered Aug 24 '10 at 01:28

rr.

6,114
8
37
43

@rr - I will have to look into this. This is exactly what I need and yes, the INSERT statements are identical. Thank you for sharing. I will let you know my results! – JM4 Aug 24 '10 at 01:34
This is a better practice than my suggestion. Use this one if you can. – Drew Aug 24 '10 at 02:03
@rr - thank you very much for your help. Note: i am actually slightly torn between utilizing mysqli or PDO (came up in your suggestion actually) but will make a decision soon. Thanks again and great response! – JM4 Aug 24 '10 at 04:46
Sure, the two are very similar. The main difference is that PDO provides a layer of abstraction for data access (so switching between database drivers is easy) and is arguably more "standardized", but it really doesn't matter for a small application. – rr. Aug 24 '10 at 14:55
@rr - i have researched online but am unable to find a solid answer. In the midst of modifying existing scripts, do you still need to use mysql_real_escape_string when using PDO and prepared statements like that above? – JM4 Aug 24 '10 at 21:55
No, PDO will take care of escaping the parameters when using prepared statements. I do believe however that if you do not use the placeholders and instead include input directly into the initial query statement, it will not be escaped automatically, but double check the PHP manual to make sure. – rr. Aug 25 '10 at 03:19
1

Also, see http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons for discussion on mysqli vs PDO – rr. Aug 25 '10 at 03:21

score 1 · Answer 2 · answered Aug 24 '10 at 00:17

1

Generally, putting a query in a loop is bad thing. There is usually a better way. In this case, you should use the multi-insert syntax. Your INSERT isn't working because you didn't specify the fields. I'm assuming the lack of a space between the table name and VALUES is a typo, along with the bad quoting.

INSERT INTO table_name (field1, fname, lname, fednum, ...) 
VALUES ('val1', 'Pete', 'Moss', 1234), 
('val2', 'T.', 'Cupp', 54321), 
('val3', 'Youdid', 'Watt', 787123);

answered Aug 24 '10 at 00:17

Brent Baisley

12,441
2
21
39

your code assumes I know how many insert statements to make. I do not know val2,3,4,5,6,7,8+ will exist. I only know that 1 member is required, there CAN be up to 16 at a time but it is up to the user. You can insert into any table without naming the fields by the way (although yes I agree is is poor practice) – JM4 Aug 24 '10 at 00:31
Then use your for loop to **build a string** of all the stuff after `VALUES (` and append another `)` on the end to cap it. Then you still only need one query and you don't need to know how many there are. – Drew Aug 24 '10 at 00:54
@Andrew - I suppose that is my direct question. Also - VALUES with only () implies a single insert. The idea is to have the possibility of 14+ rows being created with new information, not all in one row. – JM4 Aug 24 '10 at 01:22
The example I gave above would create 3 rows. Not sure what you mean by "all in one row". Not specifying fields is extremely poor practice, since the data must then be in a specific order. – Brent Baisley Aug 24 '10 at 10:20

score 0 · Answer 3 · answered Aug 24 '10 at 02:02

The solution, if I read you right, is to start with the fixed query string:

$queryString = "INSERT INTO table (field1, field2, ...) VALUES ";

then run a loop to build the malleable part. Putting your values into arrays makes things easier:

$queryInsert = '';
$total = count($value1Array);
while ($i < $total) {
    $queryInsert .= "('$value1Array[$i]','$value2Array[$i]','$value3Array[$i],...), ";
    ++$i;
}

then append to the first query piece:

$queryString = $queryString.$queryInsert;

and trim off the trailing , and you're good to go.

Possible to make this code valid/speed up?

sample code:

3 Answers3