CODEWITHSUNDEEP
phpmysqlsql
dovydasm
dovydasm

Reputation: 63

Combine a variable with SELECT results into an INSERT statement

I have 3 tables:

Users - uID (INT AUTO_INCREMENT), name (VARCHAR)

Movies - mID (IN AUTO_INCREMENT), title (VARCHAR)

Watched - uID (INT), mID (INT)

I'm writing a php function that constructs a query which adds records of movies watched by a particular person. Here's what I've come up with so far:

function set_watched($name, $movies){
    $sql = "SET @userid = (SELECT uID FROM users WHERE name = '$name' LIMIT 1); ";
    $sql .= "INSERT INTO watched (uID, mID) VALUES ('";

    foreach ($movies as $index => $movie){

    }

}

My question: Is there a way to combine the @userid variable with the results of a SELECT mID FROM MOVIES WHERE title = $movie OR title = $movie [generated with foreach]?

I don't want to generate separate SELECT statements for every movie title. Perhaps I don't even have to use the @userid variable at all?

Upvotes: 1

Views: 1171

Answers (2)

Karsten
Karsten

Reputation: 1886

Try something like this:

$sql = "INSERT INTO watched (uID, mID)
  SELECT User.uID, Movies.mID
  FROM (SELECT uID FROM Users WHERE Users.name = '$name' LIMIT 1) AS User, Movies      
  WHERE ";

foreach ($movies as $index => $movie){
  $sql .= "Movies.title = '$movie' OR ";
}

$sql = substr($sql, 0, -4) . ";";

Upvotes: 2

nickb
nickb

Reputation: 59709

I prefer using arrays and imploding them for this sort of an application. Also, I wouldn't try and force these two things into one query. I would either:

  1. Modify the function parameters to accept uID as its input, instead of name
  2. Change the logic to two queries.

Besides, PHP's mysql_query function doesn't support multiple queries, so if you're using the standard mysql functions, you can't execute two queries with one call to mysql_query.

Running with case #2, you can use something like this (untested, of course):

$sql = 'SELECT uID FROM users WHERE name = "' . $name. '" LIMIT 1';
$result = mysql_query( $sql);
$row = mysql_fetch_row( $result);
mysql_free_result( $result);

$values_array = array();
foreach ($movies as $index => $movie)
{
     $values_array[] = '( "' . $row['uID'] . '", "' . $movie . '")';
}

$sql = 'INSERT INTO watched (uID, mID) VALUES ' . implode( ', ', $values_array);
$result = mysql_query( $sql);

Upvotes: 1

Related Questions