Skip to content
Advertisement

Trying to use variable in MySQL INNER JOIN

I am a php and MySQL newbie. What I have done is created an html form with a <select> dropdown. Based on the selection from the form, it changes the $_SESSION[campaignID] variable. Changing the selection in the form is supposed to then change what displays on the page. The page consists of a forum style post that allows users to fill out a textarea and then submit it into the MySQL database into a table called “posts.” On this same page I then display the contents of the “posts” table.

What I have done is in the posts MySQL table, I have added a campaignID row. Then in my campaigns table I also have a campaignID row. The campaignID in campaigns table auto increments every time a campaign is created. Then with the earlier mentioned dropdown I can select the campaign I want, and it should then show all posts with the same campaignID as the campaign I selected.

I can verify that the $_SESSION[campaignID] is changing when i select the various options. Because when I do that and then save another post, it takes the session variable campaignID and saves it properly in the posts table.

Now what I need it to do, is when I change the drop down (which then changes $_SESSION[campaignID]) I need it to display the posts that share the same campaignID as the selected campaign. I am just not able to get it to display when trying to put a variable in my INNER JOIN query. I have a feeling the info I have provided may not be enough, but not sure what else I need to include here. Help?

Code that contains the INNER JOIN query and displays the various rows of posts table

UPDATED

    <?php
$con=mysqli_connect("localhost","dorians","ds2953!b67P$","aldentec");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$campaignID = $_SESSION['campaignID'];


$result = mysqli_query($con,"SELECT posts.postDate, posts.postName, posts.postEntry FROM posts INNER JOIN campaigns ON posts.campaignID=" . $campaignID);

while($row = mysqli_fetch_array($result))
  {
  echo "<div id='campaignPostContainer'>";
  echo "<ul class='campaignPostBox'>";
  echo "<p class='postInfo'>";
  echo "Posted on:";
  echo "<li>" . $row['postDate'] . "</li>";
  echo "</p>";
  echo "<p class='postInfo'>";
  echo "Posted by:";
  echo "<li>" . $row['postName'] . "</li>";
  echo "</p>";
  echo "<li class='postEntry'>" . $row['postEntry'] . "</li>";
  echo "</ul>";
  echo "</div>";
  echo "<hr>";
  }


mysqli_close($con);
?>

Advertisement

Answer

Without going into a use-prepared-statements rant…

You’re not pulling the session variable, but assigning $campaignId to literally the string $_SESSION[campaignID].

Change your line:

 $campaignID = '$_SESSION[campaignID]';

to:

 $campaignID = $_SESSION['campaignID'];

Also, your query is going to generate a cross product unless you define something in your ON clause like:

SELECT posts.postDate, posts.postName, posts.postEntry FROM posts 
   INNER JOIN campaigns ON posts.campaignID= $campaignID
         AND posts.campaignID= campaigns.id
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement