Many-to-Many

I created three tables:


(table - 'joke')

    id              joketext
----------------------------------
     1              Joke One
     2              Joke Two
     3              Joke Three


(table - 'author')

    id              name
----------------------------------
     1              Pete
     2              John
     3              Kevin


(table - 'authorjoke')

    authorid       jokeid
----------------------------------


Internal site search will pull all the jokes according to keywords, for “joke” - pulls all three “Joke One”, “Joke Two”, “Joke Three”.


<?php
// The basic SELECT statement
$select = 'SELECT DISTINCT id, joketext';
$from   = ' FROM joke';
$where  = ' WHERE 1=1';

$searchtext = $_POST['searchtext'];
if ($searchtext != '') { // Some search text was specified
  $where .= " AND joketext LIKE '%$searchtext%'";
}
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<label><b>Assign:</b>
<select name="aid" size="1">
<option selected value="">Any Author</option>
<?php
  $authors = @mysql_query('SELECT id, name FROM author');
  if (!$authors) {
    exit('<p>Unable to obtain author list from the database.</p>');
  }
  while ($author = mysql_fetch_array($authors)) {
    $aid = $author['id'];
    $aname = htmlspecialchars($author['name']);
    if ($aid == $authid) {
      echo "<option selected='selected' value='$aid'>$aname</option>\
";
    } else {
      echo "<option value='$aid'>$aname</option>\
";
    }
  }
?>
</select>
</label>
<br />
<br />
<table>
<tr><th>Check</th><th>Joke Text</th></tr>
<?php
$jokes = @mysql_query($select . $from . $where);
if (!$jokes) {
  echo '</table>';
  exit('<p>Error retrieving jokes from database!<br />'.
      'Error: ' . mysql_error() . '</p>');
}

while ($joke = mysql_fetch_array($jokes)) {
  echo "<tr valign='top'>\
";
  $id = $joke['id'];
  $joketext = htmlspecialchars($joke['joketext']);
  echo "<td><input type='checkbox' name='jokes[]' value='$id' /><input type=\\"hidden\\" name=\\"id\\" value=\\"$id\\" /></td>\
";
  echo "<td>$joketext</td>\
";
  echo "</tr>\
";
}
?>
</table>
<br />
<input type="hidden" name="id" value="<?php echo $id; ?>" />
<input type="submit" value="SUBMIT" />
</form>

I want to assign them or some of them to only one author - NOT individually, but at once, by choosing an “author” from Drop-Down menu and checking the checkboxes of jokes.

It suppose to be involve (form “action”) for inserting the choosen relationship into “authorjoke” table, but couldn’t figure it out where is the problem.


<?php
if (isset($_POST['name'])):
  // The Author-Joke Relationship have been updated.
  // Delete all existing entries for this joke from the authorjoke table.

  $ok = mysql_query("DELETE FROM authorjoke
                     WHERE authorid='$id'");
  if (!$ok) {
    exit('<p>Error removing Joke from Author-Joke Relationship Table:' .
        mysql_error() . '</p>');
  }

  if (isset($_POST['joks'])) {
    $joks = $_POST['joks'];
  } else {
    $joks = array();
  }

  foreach ($joks as $jokID) {
    $sql = "INSERT IGNORE INTO authorjoke
            SET authorid='$id', jokeid='$jokID'";
    $ok = @mysql_query($sql);
    if (!$ok) {
      echo "<p>Error inserting joke into authorjoke $jokID: " .
          mysql_error() . '</p>';
    }
  }
 endif;
?>

This would only work if he did an if (!is_array($jokes))

Thank you for your kindness to spend your time to help me. Yes it works, it fills the table the way it’s suppose to be. The only problem I got, it’s this message:

Warning: Invalid argument supplied for foreach() in /home/mysite/public_html/whatever/joker.php on line 63

This line below:


    foreach ($jokes as $jokeId) {

In this section of the code, below:


<?php
// here's the post part
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $jokes = $_POST['jokes'];
    $aid = (int)$_POST['aid'];
    
    foreach ($jokes as $jokeId) {
        $jokeId = (int)$jokeId;
        
        // delete old author/joke references
        mysql_query("DELETE FROM authorjoke WHERE jokeid = $jokeId");
        
        // insert new author/joke references
        mysql_query("INSERT INTO authorjoke (authorid, jokeid)
            VALUES ($aid, $jokeId)");
    }
}
?>

metrosky for future reference if your getting an error like:

Warning: Invalid argument supplied for foreach() in /home/mysite/public_html/whatever/joker.php on line 63

Then just before the lines of a script when you read whatever into the array (ie when reading results of a query into an array add the following like:

$name_of_the_array=array();

With that just before the data is read into the array, if there is data to be read into the array then that will happen as normal, but if there isn’t any data, that will initialize the variable as an empty array, so that the foreach loop sees an array like it expects to receive albeit an empty one.

i didn’t really test this, and i think there are better ways to do it, but i found some errors in your code and i think the following will accomplish what your looking for

http://poundpipe.com/v/baaap/updating-author-joke-references

I figure it out. I have to add “if statement”.