Multiple inserts with query

Question

I'm trying to make a cronjob, that deletes everything from a table then refills it with random values from another table, my problem is it is only inserting 1 row, when I want it to insert 24 different ones that it grabs from the other table. any help will be very much appreciated. btw I know mysql is deprecated.

$result = mysql_query("SELECT * FROM market ORDER BY RAND() LIMIT 24"); while($row = mysql_fetch_array( $result )) { mysql_query("TRUNCATE TABLE market2"); mysql_query("INSERT INTO `market2` (`id`, `pokemon`, `price`, `type`) VALUES ('".$row['id']."', '".$row['pokemon']."', '".$row['type']."', '".$row['price']."') "); }

Is the fixed version for anyone else that has this question.

$result = mysql_query("SELECT * FROM market ORDER BY RAND() LIMIT 24"); mysql_query("TRUNCATE TABLE market2"); while($row = mysql_fetch_array( $result )) { mysql_query("INSERT INTO `market2` (`id`, `pokemon`, `price`, `type`) VALUES ('".$row['id']."', '".$row['pokemon']."', '".$row['type']."', '".$row['price']."') "); }

You need to loop through $row and insert each time. It would be much better with a prepared statement though - one query sent to MySQL, and loop through just the variables instead. — ElendilTheTall
– ElendilTheTall, Commented Jan 22, 2014 at 21:14

mamdouh alramadan · Accepted Answer · 2014-01-22 21:19:32Z

regardless on whether I agree to your methodology, your problem is that you are truncating with each loop. To fix please use the following:

$result = mysql_query("SELECT * FROM market ORDER BY RAND() LIMIT 24"); mysql_query("TRUNCATE TABLE market2"); while($row = mysql_fetch_array( $result )) { mysql_query("INSERT INTO `market2` (`id`, `pokemon`, `price`, `type`) VALUES ('".$row['id']."', '".$row['pokemon']."', '".$row['type']."', '".$row['price']."') "); }

Collectives™ on Stack Overflow

Multiple inserts with query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related