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

Question

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");

first mysql_query is deprecated and you should have gotten an error second you are **vulnerable** to **sql injection** see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — nbk, Sep 20 '22 at 17:53
Please read the link provided in the commnt, don't concatenate variables — nbk, Sep 20 '22 at 18:13
**Warning:** `mysql_*` extension is deprecated as of PHP 5.5.0 (2013), and has been removed as of PHP 7.0.0 (2015). Instead, either the [mysqli](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. — Dharman, Sep 20 '22 at 21:04

score 1 · Accepted Answer · answered Sep 20 '22 at 18:18

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

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

1 Answers1