How to select data to another table and update it to other-CodePudding

I'm trying to get data userid and votes from table polls and then update users table, but can't figure out how I can do it.

When I implode ids, i got userid's like

293,934

But I dont know who I can get all userid's and their votes from table and update users table....

$pollid = 0   $_GET["pollid"];
$poll_res = mysql_query("SELECT userid FROM polls WHERE pollid = $pollid");

$idss = array();
        
   while($poll_row = mysql_fetch_array($poll_res)) {
        
      $idss[] = 0   $poll_row['userid'];
   }
           
  $implode = implode(',', $idss);
  $get_votes = mysql_query("SELECT votes FROM polls WHERE pollid = $pollid AND userid In($implode)");

 // How I can update multilple user (rows) who are voted (found user votes and id from POLLS table) ?
 // I just add $votes and $userid here so you know what I want to do....

 mysql_query("UPDATE users SET total_votes = votes   $votes WHERE id = $userid");

CodePudding user response：

Mysql is deprected several version before the actual 8.1, if you have some old php version, you should urgently update it, use then newer mysqli or pdo

To the sql injection problem everything is explained in How can I prevent SQL injection in PHP? and also the homepage gives you some hints https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

Lets assume you have following database

CREATE TABLE polls(userid int,pollid INT, votes int);
INSERT INTO polls VALUES(1,1,1),(2,1,1),(3,1,0),(1,2,0),(2,2,0),(3,2,1);

CREATE tABLe users(id int, total_votes int);
INSERT INTO users VALUES (1,0),(2,0),(3,0);

then you would run only one query

<?php
$poll = 1;
/* create a prepared statement */
$stmt = $mysqli->prepare("UPDATE users
JOIN ( SELECT userid ,votes FROM polls WHERE pollid = ?) t1
ON t1.userid = users.id  SET users.total_votes = users.total_votes   t1.votes");

/* bind parameters for markers */
$stmt->bind_param("s", $poll);

/* execute query */
$stmt->execute();

/* fetch value */
printf("%d Row inserted.\n", $stmt->affected_rows);

This returns.

2 Row inserted.

There are only 2 because user id 3 doesn't change the total_votes as it adds only 0 to the current value