How to Insert returned values in another table

Question

I am selecting 5 rows at random from a table.

$query = "SELECT stdid, name FROM students order by rand(UNIX_TIMESTAMP()) limit 5"
$myquery = mysqli_query($db_connect, $query);
while($students = mysqli_fetch_assoc($myquery)){
    $stdid =$students['stdid']; $name = $students['name']; $dept = $students['dept'];
    echo "<br><br>".$stdid."<br>".$name."<br>".$dept;
//NOT SURE IF I ADD INSERT HERE     
}

I want to INSERT (5 rows) the displayed 'stdid' into another table.

Do i need to add the INSERT in the WHILE loop ? Is there another way to go about this ?

Many thanks.

Why insert? That potentially doubles the table storage size and is not normalized. Add a flag on the table (eg was_shown), and update it after the echo. — Kevin Seifert, Mar 19 '15 at 15:07

score 0 · Answer 1 · edited May 23 '17 at 11:44

0

It will work to put the insert inside the while loop. With only five entries, this won't be too inefficient. If you want to insert all of the values at once, you should check out this question on how to do it. Inserting multiple rows in a single SQL query?

edited May 23 '17 at 11:44

Community

1
1

answered Mar 19 '15 at 14:43

pocketg99

124
1
12

bloodyKnuckles · Accepted Answer · 2015-03-19T15:03:48.293

Using PHP MySQLi prepared statements to prepare the insert query, once, outside the loop, then reuse that prep'd insert object to dump values into the desired table inside the loop:

$query1 = "SELECT stdid, name FROM students order by rand(UNIX_TIMESTAMP()) limit 5";
$myquery1 = mysqli_query($db_connect, $query1);

// prepare insert stdid
$query2 = "INSERT INTO someothertable (stdid) VALUES (?)";
$myquery2 = mysqli_prepare($db_connect, $query2);
mysqli_stmt_bind_param($myquery2, 'i', $stdid);

while($students = mysqli_fetch_assoc($myquery1)){
    $stdid =$students['stdid']; $name = $students['name']; $dept = $students['dept'];
    echo "<br><br>".$stdid."<br>".$name."<br>".$dept;

    // insert stdid
    mysqli_execute($myquery2);
}

score 0 · Answer 3 · answered Mar 19 '15 at 14:58

You can just insert the statement as mentioned above. Here is some code to help you make a prepared statement which will add your values that are not predefined.

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->query("CREATE TABLE myCity LIKE City");

/* Prepare an insert statement */
$query = "INSERT INTO myCity (Name, CountryCode, District) VALUES (?,?,?)";
$stmt = $mysqli->prepare($query);

$stmt->bind_param("sss", $val1, $val2, $val3);

$val1 = 'Stuttgart';
$val2 = 'DEU';
$val3 = 'Baden-Wuerttemberg';

/* Execute the statement */
$stmt->execute();

$val1 = 'Bordeaux';
$val2 = 'FRA';
$val3 = 'Aquitaine';

/* Execute the statement */
$stmt->execute();

/* close statement */
$stmt->close();

Taken from the manual. Hope it helps!

How to Insert returned values in another table

3 Answers3