inserting multiple rows into a table want to understand the process of what is happening or not happening

Question

hello I'm trying to insert values from an array using PHP into multiple rows in a mysql table.

this is basically what i'm doing:

$x=0;

while($x < 40){ <----the 40 is the size of the array

$nameinsert=$name[$x];

$sql = "INSERT INTO mytable VALUES ('','$nameinsert')";

$x++;
}
return mysql_query($sql);

obviously solutions would be great, but my main question is why doesn't this work? why does it only insert the last value from the array?

from what i can tell it should:

get the value from the array insert into the the table increase value $x then get the next value from the array then insert that into the table

so why doesn't it?

PHP Processes from the top down. your while statement must execute 40 times before any code outside of the WHILE block will be triggered. Therefore by the time you touch `$sql` it's been propagated with the last iteration from your while statement. if you want to insert it multiple times, put it in the while function and remove the return. Also, **please don't use** the `mysql_` library and consider switching to `mysqli` or `pdo`. Also consider using `count(array)` instead of hard coding values. — Ohgodwhy, Mar 22 '14 at 18:50
`"INSERT INTO mytable VALUES ('','$nameinsert')"`: **never do this**! See [this question](http://stackoverflow.com/q/60174/372643). — Bruno, Mar 22 '14 at 18:55
Thank you, i do use count(array) i just use the number for the example, but now i understand it - i didn't realise the mysql_query($sql) was need ed for each execution, but just at the end when i had finished. but it works fine now. and yes i will look into mysqli although i don't know exactly why yet but i'm sure i'll find out. — user2975156, Mar 22 '14 at 19:02
Why not have a single multiple insert like `INSERT INTO mytable (cola, colb) VALUES('','$nameinsert'), ('','$nameinsert')` — Class, Mar 22 '14 at 19:02
@user2975156, the reason why you need to look into `mysqli` is that it allows for parametrised queries. Yours isn't, you're injecting the parameter straight into the query string: this is a security risk. — Bruno, Mar 22 '14 at 19:05

score 1 · Accepted Answer · edited Mar 22 '14 at 19:35

1

You should write where the code insert the values in which column try like this:

INSERT INTO mytable('name of column') VALUES('value here');

Also i don't know why did you let an empty value !

So the question is why does it insert only the last value?

because you are putting the execution of query out of while loop try like this

$stmt = $dbConnection->prepare("INSERT INTO mytable VALUES ('', ?)");
if ($stmt) {
    while($x < 40){ <----the 40 is the size of the array
        $nameinsert = $name[$x];
        $stmt->bind_param('s', $nameinsert);
        $stmt->execute();

        $x++;
   }

   $stmt->close();
}

Now what happen?

it goes for the first value it will add it and execute the query by mysql_query() after that it increase the value and it will execute the seconde value....etc

edited Mar 22 '14 at 19:35

Bruno

119,590
31
270
376

answered Mar 22 '14 at 18:53

Walid Naceri

102
9

thanks, i see my mistake now, i was really getting frustrated not knowing why it wasn't working but it doe now, works like a charm, thank you so much. – user2975156 Mar 22 '14 at 19:03
It's a shame there's still a big security hole in this answer (like any one that uses `mysql_query` and puts the parameters in the query string directly). – Bruno Mar 22 '14 at 19:07
1

Well i know their is an SQL INJECTION :) to privent this i will fix the code thank you, of course i answred about the question he didn't ask about security – Walid Naceri Mar 22 '14 at 19:08
Too bad `mysql_real_escape_string` [isn't the way to fix this](http://stackoverflow.com/q/5741187/372643). – Bruno Mar 22 '14 at 19:10
1

hello bruno, i don't see where the security issue is, although i do understand mysqli is more secure, all the variables going into the table are created by me the user doesn't and can't put anything into the table, but i am starting to read up about mysqli thanks. walid - how do i accept an answer, do you mean vote for it? – user2975156 Mar 22 '14 at 19:11
Please edit the code for the right answer :) so we can learn from you and thank you – Walid Naceri Mar 22 '14 at 19:11
@user2975156, you should read [this](http://stackoverflow.com/q/60174/372643). Essentially, the problem is that `$nameinsert` could contain SQL statements (e.g. `"'); DELETE FROM mytable; --"`), which would mean you're actually running `"INSERT INTO mytable VALUES ('',''); DELETE FROM mytable;"`. `mysql_real_escape_string` is often used as a workaround, but [it's still flawed in general](http://stackoverflow.com/q/5741187/372643) unfortunately. Just use `mysqli` or PDO. (Of course use `mysqli` *with* parametrised queries, not just the same string again.) – Bruno Mar 22 '14 at 19:19
@WalidNaceri, probably best if you edit your own answer. – Bruno Mar 22 '14 at 19:21
@Bruno well this is my knowledge I'm also learning, if you can edit it for a best one it will be better so we can learn – Walid Naceri Mar 22 '14 at 19:25
@WalidNaceri, I haven't tried this code (so there might be typos), but this should be the idea. (Another general SQL recommendation: name the columns you insert into, e.g. `INSERT INTO (column1, column2) VALUES (...)`, so that you don't get surprises if for some reason, later in the evolution of the application, the order of the columns change.) – Bruno Mar 22 '14 at 19:36
well thank you @Bruno really and i tried it its working also :) it is really nice if you can post for us a video or a tutorials to see how tha hackers can bybass the mysql_real_escape_string(); – Walid Naceri Mar 22 '14 at 20:02
Go through the answers I've linked to, in particular http://stackoverflow.com/a/12118602/372643 – Bruno Mar 22 '14 at 20:20

inserting multiple rows into a table want to understand the process of what is happening or not happening

1 Answers1