PHP : how to join this 2 relational table

programming
1

My tables are created as follows:

CREATE TABLE IF NOT EXISTS cms_section (
  section_id int(4) unsigned NOT NULL auto_increment COMMENT 'The unique ID of the section',  
  is_published tinyint(1) NOT NULL default '0',
  section_name varchar(20) NULL COMMENT 'The section name',
  section_parent_id int(4) NULL DEFAULT 0 COMMENT 'The ID of the parent section',  
  author_id int(11) NOT NULL default '0',
  PRIMARY KEY (section_id)
)TYPE=MyISAM;

CREATE TABLE IF NOT EXISTS cms_articles (
  article_id int(11) NOT NULL auto_increment,  
  section_id int(4) unsigned NOT NULL default '0' COMMENT 'Relation to section',
  author_id int(11) NOT NULL default '0',
  is_published tinyint(1) NOT NULL default '0',
  date_submitted datetime NOT NULL default '0000-00-00 00:00:00',
  date_published datetime NOT NULL default '0000-00-00 00:00:00',
  title varchar(255) NOT NULL default '',
  body mediumtext NOT NULL,
  PRIMARY KEY (article_id),
  KEY IdxArticle (author_id,date_submitted),
  FULLTEXT KEY IdxText (title,body)
)
  TYPE = MyISAM

Into the databse I have successfully insert/edit the data using this code :

<?php

require_once 'conn.php';

$article = '';
$title = '';
$section_id = ' ';
$body = '';
$author_id = '';
if (isset($_GET['a'])
    and $_GET['a'] == 'edit'
    and isset($_GET['article'])
    and $_GET['article']) {
  $sql = "SELECT title,section_id,body,author_id FROM cms_articles " .
         "WHERE article_id=" . $_GET['article'];
  $result = mysql_query($sql, $conn)
    or die('Could not retrieve article data; ' . mysql_error());

  $row = mysql_fetch_array($result);
  $title = $row['title'];
  $section_id = $row['section_id'];
  $body = $row['body'];
  $article = $_GET['article'];
  $authorid = $row['author_id'];
}
require_once 'header.php';
?>

<form method="post" action="transact-article.php">

<h2>Compose Article</h2>

<p>
  Title:<br>
  <input type="text" class="title" name="title" maxlength="255"
    value="<?php echo htmlspecialchars($title); ?>">
</p>
<p>

**Section:<br>
  <input type="text" class="section_id" name="section_id" maxlength="255"
    value="<?php echo htmlspecialchars($section_id); ?>">**

</p>
<p>
<p>

<p>
  Body:<br>
  <textarea class="body" name="body" rows="10" cols="60"><?php
    echo htmlspecialchars($body); ?></textarea>
</p>


<p>
<?php
echo '<input type="hidden" name="article" value="' .
     $article . "\">
"; 

if ($_SESSION['access_lvl'] < 2) {
  echo '<input type="hidden" name="authorid" value="' .
       $authorid . "\">
";
}

if ($article) {  
  echo '<input type="submit" class="submit" name="action" ' .
       "value=\"Save Changes\">
";
} else {
  echo '<input type="submit" class="submit" name="action" ' .
       "value=\"Submit New Article\">
";
}
?>
</p>
</form>

<?php require_once 'footer.php'; ?>

In the code above, I highlite the part that I need your advice/help. I use that code also to edit an entry. As you know, the form transaction within the code above are handled via : transact-article.php. I don’t put the transact-article.php here, but if you want me to put here, please advise.

What I want :

  1. The section choice is displayed as pull-down select form, instead of input text.
  2. The pull down selection form, coming from cms_section above. So, the idea is that, upon submitting entry the choice is displayed, and we just choose which section we want to post the entry.

Before I post this message, I have already tried 2 different attempts:

Attempt 1: Changing the SQL query.

From:

$sql = "SELECT title,section_id,body,author_id FROM cms_articles " .
         "WHERE article_id=" . $_GET['article'];
  $result = mysql_query($sql, $conn)
    or die('Could not retrieve article data; ' . mysql_error());

To:

$sql = "SELECT title,section_id,body,author_id FROM cms_articles " .
  		"LEFT OUTER JOIN cms_section"
		"ON cms_articles.section_id=cms_section.section_id".
         "WHERE article_id=" . $_GET['article'];
  $result = mysql_query($sql, $conn)
    or die('Could not retrieve article data; ' . mysql_error());

It is unsuccessful query.Error message : Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING in http://witheld/witheld/compose.php on line 16

Attempt 2 : Create select form and populated via different sql query.

Here’s the code :

Section:<br>

<select id="section_name" name="section_name" style="width:150px">

<option value="">Make A Selection</option>

<?php
$section_name = $_GET['section_id'];
$sql = "SELECT section_id, section_name FROM cms_section ORDER BY section_id";
        // "FROM cms_section ORDER BY section_id";
  $result = mysql_query($sql)
    or die("<font color=\"#FF0000\">Query Error</font>" . mysql_error());
  while ($row = mysql_fetch_array($result)) {
    
    echo '<option value="' . $row['section_id'] . '"';
    if ($row['section_id'] == $section_name) {
        echo ' selected';
    }
    echo '>' . $row['section_name'] . '</option>
';
  }
?>
</select>

The query is successfull, the select pull down option is populated but there’s no relational data successfully perform. In this regards, I can still add/edit an entry but no matter what I choose the pull-down select button, the entry is not posted in the respected section that I want to. Also the pull-down shows “Make Selection” option - instead of showing the respected section whenever I want to edit the entry

With all those background information, question:

  1. Which better attempt I should proceed?
  2. Can you please point me out which part of the code I should change?

Thanks