query result array in loop for another query

Question

I Queried Database Table 'users' for 'user_id'. and get an array of ids.

$sel = "SELECT user_id FROM users WHERE status='Approved'";     
$result = @mysqli_query ($dbcon, $sel);

Then i inserted values into another table income for all those user ids.

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) 
{
$ins = "INSERT INTO income (user_id, income_amount)  VALUES ('$row', '100')";       
$giv = @mysqli_query ($dbcon, $ins);
}

Notice: Array to string conversion in E:\xampp\htdocs\project\t.php on line 109

Can anyone help me resolve this issue.

`$row` is array. And `user_id` should be string. `Var_dump($row)` and choose a proper value of `user_id` from it. — u_mulder, Jul 05 '15 at 11:56
On Var_dump($row) it says array(1) { ["user_id"]=> string(1) "1" } ,array(1) { ["user_id"]=> string(1) "2" } ...... — Pyarey, Jul 05 '15 at 11:58
Just because data is in your database does not mean it is secure. You should still use prepared statements. Also don't suppress errors when debugging. — chris85, Jul 05 '15 at 11:59
@Pyarey so it should be "VALUES ('" . $row['user_id'] . "', '100')"; — JungleZombie, Jul 05 '15 at 12:00
If you insert data with a prepared statement the malicious data is stored in the DB. If you then access that data and re-insert it directly to a query you just injected yourself. — chris85, Jul 05 '15 at 12:03
You can do it all in one query... http://stackoverflow.com/questions/6354132/insert-data-into-table-with-result-from-another-select-query?answertab=active#tab-top — stdob--, Jul 05 '15 at 12:06
Thanks @JungleZombie. It helped me do what i was trying to do. Help from all you guys is valuable. — Pyarey, Jul 05 '15 at 12:10
But can you help me with Security issue you are talking about @chris85. — Pyarey, Jul 05 '15 at 12:10
Take a look at this doc http://php.net/manual/en/mysqli.quickstart.prepared-statements.php and this thread as well, http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php. Avoid inserting data directly to a query unless it is static (e.g. your `100` above). — chris85, Jul 05 '15 at 12:15

score 1 · Answer 1 · answered Jul 05 '15 at 18:48

$sel = "SELECT user_id FROM users WHERE status='Approved'";     
$result = @mysqli_query ($dbcon, $sel); 

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) 
{
$ins = "INSERT INTO income (user_id, income_amount)  VALUES ('" . $row['user_id'] . "', '100')";       
$giv = @mysqli_query ($dbcon, $ins);
}

score 0 · Answer 2 · answered Jul 05 '15 at 12:35

0

First , Check if $results is in array ..you can put some error handling checked is_array($result).

If it is fine then pass it to mysqli_fetch_array(). Do't add suppress @ error ,while developing.

answered Jul 05 '15 at 12:35

Sunil Verma

111
1
5

score 0 · Answer 3 · answered Jul 05 '15 at 19:49

i would like to suggest you a single query for that so after that you need not to use while loop to insert your data in income table:

Just try it :

INSERT INTO income (user_id,income_amount) SELECT user_id,'100' AS income_amount FROM users WHERE status = 'Approved';

You can use it like that way :

$sel = "INSERT INTO income (user_id,income_amount) SELECT user_id,'100' AS income_amount FROM users WHERE status = 'Approved'";     
$result = @mysqli_query ($dbcon, $sel);

I have created a demo scenario on local database. After that i am suggesting it to you. Wish this will help you. — Lakhan, Jul 05 '15 at 19:52

query result array in loop for another query

3 Answers3