Using php to create and setup mysql database, tables, foreign keys, etc..

wrstrong · July 4, 2009

I am trying to create a database that:

- has sections (as in a grouping of matching types of articles)

- has article titles (which needs connected to the article itself and the section to which it belongs)

- has articles themselves (which are connected to its title and which section it belongs to)

That database needs to be able to create a final product (a document that organizes articles by the section that they belong to, then by title, and finally the article iteslf.)

I don't know if I am anywhere close as I am getting more confused the more research I do. I have virtually NO experience with foreign keys, primary keys, etc. Normally, I just read/write to a simple database to store / retrieve data.

Here is what I have so far: (The settings.php file sets up the server name, database name, etc..)

<?

// Include settings.php file
include 'settings.php';

// Connection to the database text
$connection = mysql_connect("$mysql_host", "$mysql_user", "$mysql_password");

?>
<center>
<br><br><br><b><u>Database and Table Creation Tool!</u></b><br><br><br>
<table>
<?

// Create Database
?>
<tr>
<?
if (!$connection)
  {
  die("<td><font color=red>+</font>  Could not connect to <b><i><font color=blue>" . $mysql_db . "</font></i></b>.</td><td width=25></td><td><b>Reason:</b>  <font color=blue>" . mysql_error() . "!</font></td>");
  }
if (mysql_query("CREATE DATABASE " .  $mysql_db,$connection))
  {
  echo "<td colspan=3>Database <b><i><font color=blue>" . $mysql_db . "</font></i></b> created!</td>";
  }
else
  {
  echo "<td><font color=red>+</font>  Error creating <b><i><font color=blue>" . $mysql_db . "</font></i></b> database.</td><td width=25></td><td><font color=red>+</font>  <b>Reason:</b>  <font color=blue>Database 'taxwatch' already exists!</font></td>";
  }
?>
</tr>
<?
mysql_select_db($mysql_db, $connection); 	// Select Database

// Create Table sections
?>
<tr>
<?
$create = "CREATE TABLE sections(section_id int(2), section_title text, primary key (section_id), foreign key (section_id) references report(section))";
if (!$create)
  {
  die("<td><font color=red>+</font>  Could not create table <b><i><font color=blue>SECTIONS</font></i></b>.</td><td width=25></td><td><font color=red>+</font>  <b>Reason:</b>  <font color=blue>" . mysql_error() . "!</font></td>");
  }
if (mysql_query($create,$connection))
  {
  echo "<td colspan=3><font color=red>+</font>  Table <b><i><font color=blue>SECTIONS</font></i></b> created!</td>";
  }
else
  {
  echo "<td><font color=red>+</font>  Error creating table <b><i><font color=blue>SECTIONS</font></i></b>.</td><td width=25></td><td><font color=red>+</font>  <b>Reason:</b> <font color=blue>" . mysql_error() . "!</font></td>";
  }
// Create Table titles
?>
<tr>
<?
$create = "CREATE TABLE titles(title_id int(2), title_text text, primary key (title_id), foreign key (title_id) references report(title))";
if (!$create)
  {
  die("<td><font color=red>+</font>  Could not create table <b><i><font color=blue>TITLES</font></i></b>.</td><td width=25></td><td><font color=red>+</font>  <b>Reason:</b>  <font color=blue>" . mysql_error() . "!</font></td>");
  }
if (mysql_query($create,$connection))
  {
  echo "<td colspan=3><font color=red>+</font>  Table <b><i><font color=blue>TITLES</font></i></b> created!</td>";
  }
else
  {
  echo "<td><font color=red>+</font>  Error creating table <b><i><font color=blue>TITLES</font></i></b>.</td><td width=25></td><td><font color=red>+</font>  <b>Reason:</b> <font color=blue>" . mysql_error() . "!</font></td>";
  }
?>
</tr>
<?
// Create Table articles
?>
<tr>
<?
$create = "CREATE TABLE articles(article_id int(2), article_text text, primary key (article_id), foreign key (article_id) references report(article))";
if (!$create)
  {
  die("<td><font color=red>+</font>  Could not create table <b><i><font color=blue>ARTICLES</font></i></b>.</td><td width=25></td><td><font color=red>+</font>  <b>Reason:</b>  <font color=blue>" . mysql_error() . "!</font></td>");
  }
if (mysql_query($create,$connection))
  {
  echo "<td colspan=3><font color=red>+</font>  Table <b><i><font color=blue>ARTICLES</font></i></b> created!</td>";
  }
else
  {
  echo "<td><font color=red>+</font>  Error creating table <b><i><font color=blue>ARTICLES</font></i></b>.</td><td width=25></td><td><font color=red>+</font>  <b>Reason:</b> <font color=blue>" . mysql_error() . "!</font></td>";
  }
?>
</tr>
<?
// Create Table report
?>
<tr>
<?
$create = "CREATE TABLE report(section int(2), title int(2), article int(2));";
if (!$create)
  {
  die("<td><font color=red>+</font>  Could not create table <b><i><font color=blue>REPORT</font></i></b>.</td><td width=25></td><td><font color=red>+</font>  <b>Reason:</b>  <font color=blue>" . mysql_error() . "!</font></td>");
  }
if (mysql_query($create,$connection))
  {
  echo "<td colspan=3><font color=red>+</font>  Table <b><i><font color=blue>REPORT</font></i></b> created!</td>";
  }
else
  {
  echo "<td><font color=red>+</font>  Error creating table <b><i><font color=blue>REPORT</font></i></b>.</td><td width=25></td><td><font color=red>+</font>  <b>Reason:</b> <font color=blue>" . mysql_error() . "!</font></td>";
  }
?>
</tr>
<?
// Close Database Connection
mysql_close($connection);
?>
</table>
</center>

When the creation is setup complete, I'd like to add the displaying of the structure of the database itself at the end of the output. (That, I'm stuck on, too.) LOL

Any help you can give me would be GREATLY appreciated.

Thanks in advance.

wrstrong

fenway · July 4, 2009

Yeah, I can't make any sense of that.

Post the table definitions from SHOW CREATE TABLE.

wrstrong · July 5, 2009

I embedded it into a php so that I could better understand the error-trapping process. I only have the use of php and Webmin (Ubuntu linux). Here's what I believe would be in a mysql terminal window.

CREATE DATABASE mydb;
CREATE TABLE sections
(
section_id int(2), 
section_title text, 
primary key (section_id), 
foreign key (section_id) references report(section)
);
CREATE TABLE titles
(
title_id int(2), 
title_text text, 
primary key (title_id), 
foreign key (title_id) references report(title)
);
CREATE TABLE articles
(
article_id int(2), 
article_text text, 
primary key (article_id), 
foreign key (article_id) references report(article)
);
CREATE TABLE report
(
section int(2), 
title int(2), 
article int(2)
);

Not too sure if the semicolons should be in there, either.

I'm trying to create one db that has sections (location that the article will display in), title (title of the article) and article (the article itself). I'm hoping to connect to this db and create a form where I can add / edit / delete sections, titles and articles all at once (or one at a time).

I am sorry for the crudeness; however, I'm not too sure of HOW to do it (but I know what I want to accomplish and all of my reading up on the subject has brought me to this point).

Thanks for the reply and attempt to help. I'm at a loss as to how to change it to make it do what I need it to do.

sincerely,

wrstrong

wrstrong · July 8, 2009

I have tried multiple times to get php to show the table structure using SHOW CREATE TABLE with NO success. I've even tried embedding it into a query command (mysql_query(SHOW CREATE TABLE) and still, no dice.

Of course, I've also tried putting the actual table names in and the database that it's connected with and had no luck there, either.

Here's where I am to this point:

<?
// Include 'settings.php' file (has $mysql_db, $mysql_host, $mysql_user, $mysql_password all defined)
include 'settings.php';

// Connection to the database text
$connection = mysql_connect("$mysql_host", "$mysql_user", "$mysql_password");

// Create the database
mysql_query(CREATE DATABASE $mysql_db, $connection)

// Create the table 'sections'
mysql_query(CREATE TABLE sections (section_id int, section_title text, primary key (section_id)), $connection)

// Create the table 'articles'
mysql_auery(CREATE TABLE articles (article_id int, section_id int, article_text text, article_title text, primary key (article_id), foreign key (section_id) references sections(section)), $connection)

// Close Database Connection
mysql_close($connection);
?>

Doing this gives me this table structure:

database
->articles
   ->article_id (primary key)
   ->section_id (indexed)
   ->article_text (none)
   ->article_title (none)
->sections
   ->section_id (primary key)
   ->section_title (none)
->section_id

Since all I need to do is to have the info organized first by section, then by article title and then article text, is all of this correct? If no, what do I need to do to get what I need?

Thanks so much for your help thus far.

Sincerely,

wrstrong

wrstrong · July 11, 2009

I've figured out how to make the db. Here it is below for anyone who needs the info.

I'm breaking it down into the individual mysql_query commands so that the tables will work properly.

Open connection to mysql.

$connection = mysql_connect(mysql_host, mysql_user, mysql_password);
if (!connection) {die(mysql_error());}

Creating the db.

CREATE DATABASE mydb

Creating the sections table.

CREATE TABLE sections (section_id int, section_title text, primary key (section_id))

Creating the articles table.

CREATE TABLE articles (article_id int, section_id int, article_text text, article_title text, primary key (article_id), foreign key (section_id) references sections(section))

Close the db.

mysql_close($connection);

Now that I've create the db, I entered the beginning data to the db manually. I am trying to run a mysql_query to read the db and make sure that it connects the two tables properly. Here's what I have.

The query string.

$query = 'SELECT section_id, article_title, article_text FROM articles JOIN sections ON articles.section_id=sections.section_id ORDER BY sections.section_id, articles.article_title';
$result = mysql_query($query, $connection);

A loop to display the material needed.

$i = 0;
$len = mysql_num_rows($result, $connection);
echo $len;
while ( $i < $len )
{
   echo $result['section_id'] . '-' . $result['section_title'] . '-' . $result['article_title'] . '-' . $result['article_text'] . '<br>';
   $i++;
}

I get a, 'Warning: Wrong parameter count for mysql_num_rows()' in the line, '$len = mysql_num_rows($result, $connection);' It should work; but, I can't figure that out. Any suggestions?

Thanks for all of your help so far.

Sincerely,

wrstrong

fenway · July 15, 2009

It only takes one parameter....

wrstrong · July 21, 2009

I have been going the possible error over & over and I must be missing somehting. I know that the error is somewhere in the loop or the actual query; however, I just can't get it. I can handle normal mysql queries; but, this is something more. Where is my error & what do I need to fix?

Thanks again for the help.

Sincerely,

wrstrong

fenway · July 24, 2009

mysql_num_rows($result, $connection)

Should only take one parameter... according to the refman.

wrstrong · July 25, 2009

I HAVE to be missing something. I've been doing the RTFM approach on this for a while now. At php.net, they have the following:

<?php
$link = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("database", $link);

$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";

?>

This is almost exactly what I have been trying.

The entire page is as follows:

<?  include 'settings.php';  $connection = mysql_connect("$mysql_host", "$mysql_user", "$mysql_password");  mysql_select_db($mysql_db, $connection);  $query = 'SELECT section_id, article_title, article_text FROM articles JOIN sections ON articles.section_id=sections.section_id ORDER BY sections.section_id, articles.article_title';  $result = mysql_query($query, $connection);  $num_rows = mysql_num_rows($result);  echo $num_rows;  ?>

I'm getting an error when clearly the online php site says that I shouldn't be.

I'm sorry if I am not understanding your suggestions. I have been trying and also digging further into what should work. Somehow, I must be missing something.

Thanks for the help thus far.

wrstrong

wrstrong · July 28, 2009

I think I might be missing a step. I have been checking into mysql's join feature. Would that connect the two tables like I need to? The info on mysql.com is rather confusing. The page I'm looking at is:

http://dev.mysql.com/doc/refman/5.0/en/join.html

Can you please help enlighten me? Is this what I'm looking for? If so, how to add and where to add in order to make it work like I need?

Sorry about my continued confusion about this situation. I guess I must have a mental block where this quantum leap comes from. :facewall:

Thanks for any continued help with this situation.

Sincerely,

wrstrong

wrstrong · July 28, 2009

:facewall: Sorry about all this confusion. What I guess I am having difficulties with is the issue that the sections table is not the same length as the articles table. Could that be the issue?

My sections table looks sorta like this:

section_id	section_title
1	overview
2	first section
3	second section
4	forth section

and so on...

My articles table is like this:

section_id	article_title	article_text
1	first article	This is the first article - it's in the first section
1	second article	This is the second article - it's in the first section
2	third article	This is the third article - it's in the second section
2	forth article	This is the forth article - it's in the second section
3	fifth article	This is the fifth article - it's in the third section

and so on...

What I'm trying to do is to setup the db so that I can work with the info like this::

-replace the section_id with the section_title in the articles table (for outright viewing)-allow the modifying of the article title, text and section so that I can rename the section title, article title and article text (and it update the appropriate tables.)

Am I going about everything the right way???

(Sorry if I am repeating things. I'm trying to better understand what I am to do and how to go about doing it.)

Thanks for being patient with me. I realize that it's hard to second-guess what someone else is trying to do (and debug their mistakes.) This connecting multiple tables and trying to get the output / updating is a little confusing.

Again, thanks for all the help so far. If I can get over this, I believe I can take things from there.

Sincerely,

wrstrong

wrstrong · July 29, 2009

I've made some progress in the right direction!

Here's what I have so far:

<?   include 'settings.php';   $connection = mysql_connect($mysql_host,  $mysql_user,  $mysql_password);   mysql_select_db($mysql_db,  $connection) or trigger_error('Can NOT select '. $mysql_db . ' database because ' . mysql_error(), E_USER_ERROR);   $query  = "SELECT s.section_id, a.article_title, a.article_text";  $query .= " FROM articles As a LEFT JOIN sections As s";  $query .= " ON a.section_id = s.section_id";  $query .= " ORDER BY s.section_id, a.article_title, a.article_text";   
// added the following line for debugging purposes and the be 100% sure the query turns out ok:  echo $query;  $result = mysql_query($query, $connection) or trigger_error(mysql_error(), E_USER_ERROR);  
while($array = mysql_fetch_array($result)) { print_r($array);print "<hr>";}  ?>

(I was given the 'trigger_error(mysql_error(), E_USER_ERROR)' section by someone else and I now trap the errors easier.)

I DO get output with NO errors!

How do I separate that array so that I can get the 'section_id', 'section_title', 'article_title', and 'article_text'?

When I get that separated, can I use a simple update instead of a select in the query to make any changes necessary?

Thanks ever so much for the assist thus far!

wrstrong

wrstrong · July 29, 2009

<? 
include 'settings.php'; 
$connection = mysql_connect($mysql_host,  $mysql_user,  $mysql_password); 
mysql_select_db($mysql_db,  $connection) or trigger_error('Can NOT select '. $mysql_db . ' database because ' . mysql_error(), E_USER_ERROR); 
$query  = "SELECT s.section_id, s.section_title, a.article_title, a.article_text"; $query .= " FROM articles As a LEFT JOIN sections As s"; $query .= " ON a.section_id = s.section_id"; $query .= " ORDER BY s.section_id, a.article_title, a.article_text"; 
$result = mysql_query($query, $connection) or trigger_error(mysql_error(), E_USER_ERROR);
while($array = mysql_fetch_array($result)){echo $array['section_id']."<br>";echo $array['section_title']."<br>";echo $array['article_title']."<br>";echo $array['article_text']."<br>";echo "<hr>";}
?>

How do I delete and update the records? Is it UPDATE like the SELECT in the query?

Thanks for the help thus far!

wrstrong

wrstrong · July 30, 2009

I have made some serious progress with this project!

Here's where I am so far:

<?
$number = 0;
$maxnumber=11; 
include 'settings.php';                    // Include the settings file!
$connection = mysql_connect($mysql_host,  $mysql_user,  $mysql_password);            // Connect to the MySQL server!
mysql_select_db($mysql_db,  $connection) or trigger_error('Can NOT select '. $mysql_db . ' database because ' . mysql_error(), E_USER_ERROR); // Select the db! 
echo "<center>";
echo "<table width=60%>";
while ($number < $maxnumber)
{
  $query  = "SELECT s.section_id, s.section_title, a.article_id, a.article_title, a.article_text";         // MySQL query body!
  $query .= " FROM articles As a LEFT JOIN sections As s"; 
  $query .= " ON a.section_id = s.section_id";
  $query .= " WHERE s.section_id = $number"; 
  $query .= " ORDER BY s.section_id, a.article_title, a.article_text"; 
  $result = mysql_query($query, $connection) or trigger_error(mysql_error(), E_USER_ERROR);         // Actual MySQL query!
  $show_section_title="yes";
  while($array = mysql_fetch_array($result))
   {
    if ($show_section_title=="yes")                   // ONLY show the section title ONCE!
     {
      echo "<tr><td><font size=+1 color=black><b><u>".$array['section_title']."</u></b></font></font><br></td></tr>";
      $show_section_title="no";
     }
    echo "<tr><td><font size=-1 color=black><u>".$array['article_title']."</u></font></font></td></tr>";       // The article TITLE!
    echo "<tr><td>     <font size=-1 color=black>".$array['article_text']."</font></font><br><br></td></tr>";  // The article TEXT!
   }
  $number++;
}
echo "</table>";
echo "</center>";
?>

Now, all I have to do is to make a form that utilizes checkboxes to choose which articles to show. I'll keep you posted! Thanks for all of your help thus far!

wrstrong

wrstrong · August 14, 2009

I do have parts of the project working; however, I'm stumped again.

Here's the issue::

I am making a page (for editing and adding) that is merely links to the pages that will actually do the modification and/or addition that I need.

Here's what I need to do:

The article-edit page that I have so far is this...

<?

$number = $_GET['number'];

include 'settings.php';                                                                                                                                                             // Include the settings file!
$connection = mysql_connect($mysql_host,  $mysql_user,  $mysql_password);                                                                                                             // Connect to the MySQL server!
mysql_select_db($mysql_db,  $connection) or trigger_error('Can NOT select '. $mysql_db . ' database because ' . mysql_error(), E_USER_ERROR);                                        // Select the db!

$query  = "SELECT s.section_id, s.section_title, a.article_id, a.article_title, a.article_text";
$query .= " FROM articles As a LEFT JOIN sections As s";
$query .= " ON a.section_id = s.section_id";
$query .= " WHERE a.article_id = $number";
$query .= " ORDER BY s.section_id, a.article_title, a.article_text";

$result = mysql_query($query, $connection) or trigger_error(mysql_error(), E_USER_ERROR);

while($array = mysql_fetch_array($result))
{
     $section_id = str_replace(' ',' ',$array['section_id']);
     $section_title =  str_replace(' ',' ',$array['section_title']);
     $article_id = str_replace(' ',' ',$array['article_id']);
     $article_title =  str_replace(' ',' ',$array['article_title']);
     $article_text =  str_replace(' ',' ',$array['article_text']);
?>
<form method=post action=article-db-edit.php>
   <input type=hidden name=new_article_id value=<? echo $article_id; ?>>
     <table>
         <tr>
             <td align=right>Section Title:  </td>
             <td>
             <input name=new_section_title type=text size=100 tabindex=1 value=<? echo $section_title; ?>>
             </td>
         </tr>
         <tr>
             <td align=right>Article Title:  </td>
             <td><input name=new_article_title type=text size=100 tabindex=2 value=<? echo $article_title; ?>></td>
         </tr>
         <tr>
             <td align=right>Article Text:  </td>
             <td><textarea name=new_article_text cols=85 rows=5 tabindex=2><? echo $article_text; ?></textarea></td>
         </tr>
         <tr>
             <td colspan=2 align=center><input name=Submit type=submit value=submit><input name=Reset1 type=reset value=reset></td>
         </tr>
     </table>
</form>
<?
}
?>

When you pass $number (the article_id number in the articles table,) it displays the proper values that are associated to both tables.

Now, what I need to do is to modify that so that the section_id is connected with the appropriate section_title so that if the section itself is modified via this form, the appropriate section_id AND section_title are modified. So far, I've been unsuccessful.

Something that at least functions similarly to what I'd need would be to have one dropdown (section_title) that would modify a second dropdown (section_id) within this form.

Here's a javascript/html example that would do the modifying the actual dropdown menus; however, how to I integrate it into my script to do what I need?

<html>
<head>
<link rel=stylesheet href="1dynamicMenu.css" type="text/css">
<script language="JAVASCRIPT" src="1js.js">
<!--// 
//--></script>
<script language="javascript"
src="BackForwardNew.js">
<!--// 
//--></script>

<meta name="description" content="Using a select to write a select (Double menus)">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="GENERATOR" content="Microsoft FrontPage 12.0">
<style type="html/txt">
<!--h2 { color: rgb(128,0,0) }
h1 { text-align: center; color: rgb(103,104,74) }
-->
</style>
<!--start of function-->
<script language="javascript">
<!--// 
function ldMenu(mySubject) {
var Indx=mySubject;
with (document.form2.select2) 
{
document.form2.select2.options.length=0;
if (Indx==0)
{
options[0]=new Option("Pages appear here","");
}
if (Indx==1)
{
options[0]=new Option("Choose a JavaScript Page","");
options[1]=new Option("Alerts","alerts.htm");
options[2]=new Option("Back and Forward Buttons","BackForward.htm");
options[3]=new Option("Contents","index.html");
}

if (Indx==2)   
{  
options[0]=new Option("Choose an HTML Page","");
options[1]=new Option("HTML Contents","../HTMLGuide/index.html");
options[2]=new Option("Meta Tags","../HTMLGuide/metaTags.htm");
options[3]=new Option("Hyperlinks","../HTMLGuide/ImageHyperlink.htm");
options[4]=new Option("iframes","../HTMLGuide/iframes3.htm");
}

if (Indx==3)
{
options[0]=new Option("Choose a Style Sheets Page","");
options[1]=new Option("Style Sheets Contents","../StyleSheets/index.html");
options[2]=new Option("Fonts","../StyleSheets/fonts.htm");

}

form2.select2.options[0].selected=true;
}

}
//-->
</script>
<!--end of function-->
<script language="JavaScript"> 
<!-- Hide from old browsers 
//Hide from Java Script 
function goToPage() 
{ 
PageIndex2=document.form2.select2.selectedIndex 
if (document.form2.select2.options[PageIndex2].value != "") 
{ 
location.href = document.form2.select2.options[PageIndex2].value;
} 
} 
//--> 

</script>

<meta name="KEYWORDS"
content="javascript,menus,form,select,onChange,onChange,new Option,options,location.href,HTML,navigator.appName ,history.go(0),">
<link rel="stylesheet" href="javascript.css" type="text/css">
<title>Using a menu to write a menu</title>
</head>

<body vlink="green" alink="blue" link="red" background="backg2.gif">

<form name="form1">
   <p><select name="select1" onChange="ldMenu(this.selectedIndex);" size="1">

     <option value selected>Select a subject </option>
     <option value>JavaScript </option>
     <option value>HTML </option>
     <option value>Style Sheets </option>
   </select> </p>
</form>

<form name="form2" method="post" action enctype="text/plain">
   <p><select name="select2" onChange="goToPage()" size="1">
<option selected value="Select a page after selecting a subject">Select a page 
after selecting a subject</option>
   </select> </p>
</form>

</body>
</html>

Any ideas on how to do this?

Thanks for all the help thus far!

wrstrong

wrstrong · August 20, 2009

I have gotten some progress; although, I've hit a few road blocks... lol

I've tried to simply use the, 'left join' to actually join the tables; however, I've noticed that sometimes it works, and sometimes it doesn't. When it doesn't work properly, the error that is displayed makes no sense.

I have had to use some pretty fancy footwork to get to this point. (Please forgive the crudeness within the coding.)

I'm working on a form that will be used to edit the article, it's title and change the section it's in (as needed.) I have everything working EXCEPT getting the section that's connected with the article to be in the drop down box as SELECTED instead of merely an option.

Here's where I am at right now:::

When you use the actual page, it would be page.php?number=... Where number is the article number you want to change.

<?

$number = $_GET['number'];

include 'settings.php';                                                                // Include settings.php file
$connection = mysql_connect("$mysql_host", "$mysql_user", "$mysql_password");        // Connection to the database text
mysql_select_db($mysql_db, $connection);                                            // make $mysql_db the current db
$res = mysql_query('SELECT section_id, section_title FROM sections', $connection);    // Get section_id and section_title from SECTIONS table
while($row = mysql_fetch_assoc($res))                                                  // Setup and assign the actual title to an array, 'section[$row['section_id']]
{
$section[$row['section_id']] = $row['section_title'];
}

$query  = "SELECT s.section_id, s.section_title, a.article_id, a.article_title, a.article_text";
$query .= " FROM articles As a LEFT JOIN sections As s";
$query .= " ON a.section_id = s.section_id";
$query .= " WHERE a.article_id = $number";
$query .= " ORDER BY s.section_id, a.article_title, a.article_text";

$result = mysql_query($query, $connection) or trigger_error(mysql_error(), E_USER_ERROR);

while($array = mysql_fetch_array($result))
{
    $section_id = str_replace(' ',' ',$array['section_id']);
    $section_title =  str_replace(' ',' ',$array['section_title']);
    $article_id = str_replace(' ',' ',$array['article_id']);
    $article_title =  str_replace(' ',' ',$array['article_title']);
    $article_text =  str_replace(' ',' ',$array['article_text']);
?>
<form method=post action=article-db-edit.php>
    <table>
        <tr>
            <td align=right>Section:  </td>
            <td>
                <select name=new_section_title>
                    <?
                        $res = mysql_query('SELECT section_id, section_title FROM sections', $connection);    // Get section_id and section_title from SECTIONS table
                        while($row = mysql_fetch_assoc($res))                                                  // Setup and assign the actual title to an array, 'section[$row['section_id']]
                        {
                             $section[$row['section_id']] = $row['section_title'];

                             if ($section_id = $section[$row['section_id']])
                                 {
                                     ?>
                                         <option selected><? echo $section[$row['section_id']]; ?></option>
                                     <?
                                 }
                             else
                                 {
                                     ?>
                                         <option><? echo $section[$row['section_id']]; ?></option>
                                     <?
                                 }
                        }
                    ?>
                </select>
            </td>
        </tr>
        <tr>
            <td align=right>Title:  </td>
            <td><input name=new_article_title type=text size=100 tabindex=2 value=<? echo $article_title; ?>></td>
        </tr>
        <tr>
            <td align=right>Article:  </td>
            <td><textarea name=new_article_text cols=85 rows=5 tabindex=2><? echo $article_text; ?></textarea></td>
        </tr>
        <tr>
            <td colspan=2 align=center><input name=Submit1 type=submit value=submit><input name=Reset1 type=reset value=reset></td>
        </tr>
    </table>
</form>
<?
}
?>

ANY help you can give me would be FANTASTIC! Once I get it to pass the appropriate values in the form, I can then go on to edit the appropriate lines in the db tables.

Also, if you can think of an easier way to do the form so that the, 'join' can be fully utilized, I'd love to see how it's done.

Thanks for ALL of the support you've given me thus far. I've been learning a lot.

Sincerely,

wrstrong

fenway · August 21, 2009

That's an entire script.... what's the isolated mysql issue?

wrstrong · August 21, 2009

When the form is to display the sections that are available, one that's to be associated with the article should be selected. What's actually going on is the LAST section that's available from the sections table is listed (as in selected.) All of the available other sections are in there; however, it's not selecting the proper one to match the article itself.

Not to mention that I have to make 2 different queries in order to make this happen. How do I correct these issues?

fenway · August 22, 2009

It's important to determine if the query itself produces the correct resultset, and the php logic is flawed, or vice versa.

wrstrong · August 22, 2009

Agreed; however, what do I need to change? That's a good question. I have the loop down correct; otherwise, I wouldn't see ALL of the sections in the form's drop-down box. The part that's not working correctly is the logic that says....

When you display the form itself, the SELECTED option in the drop-down menu SHOULD be the one that shows up from the loading of the form.

Instead, it's just displaying all of the choices from the sections table, but NOT making any of them SELECTED.

Where is the error? I can't see any; however, obviously, it is happening.

Thanks for all the assistance thus far.

Sincerely,

wrstrong

wrstrong · August 24, 2009

I've modified the full page somewhat to try and cure the issue; however, I still get the strange error.

Here's the full script for that page:

(Please note that the article number is just being forced as $number=4. When finished, I'll take that line out and un-comment the $_GET['number'] line above it.)

<!-- Get section_id and section_title data from the SECTIONS table (for use in the form)  -->

<?

// $number = $_GET['number'];

$number = 4;

include 'settings.php';                                                                // Include settings.php file
$connection = mysql_connect("$mysql_host", "$mysql_user", "$mysql_password");        // Connection to the database text
mysql_select_db($mysql_db, $connection);                                            // make $mysql_db the current db

$res = mysql_query('SELECT section_id, section_title FROM sections', $connection);    // Get section_id and section_title from SECTIONS table

while($row = mysql_fetch_assoc($res))                                                  // Loop through all section_id's
{
    $section_id[$n] = $row['section_id'];                                              // Assign the section_id to $section_id[$n}
    $section_title[$n] = $row['section_title'];                                     // Assign the section_title to $section_title[$n}
}
?>

<!-- Get the highest section number (for use in the form)  -->

<?

$query ="SELECT max(section_id) FROM sections";
$result = mysql_query($query, $connection) or trigger_error(mysql_error(),E_USER_ERROR);
$row = mysql_fetch_row($result);

$max_section_number = $row[0];

?>

<!-- Get section_id, article_id, article_title, article_text from ARTICLES table (for use in form) -->

<?

$query  = "SELECT s.section_id, s.section_title, a.article_id, a.article_title, a.article_text";
$query .= " FROM articles As a LEFT JOIN sections As s";
$query .= " ON a.section_id = s.section_id";
$query .= " WHERE a.article_id = ".$number;
$query .= " ORDER BY s.section_id, a.article_title, a.article_text, a.article_id";

$result = mysql_query($query, $connection) or trigger_error(mysql_error(), E_USER_ERROR);

while($array = mysql_fetch_array($result))
{

$article_section_id = $array['section_id'];
$article_section_title = $array['section_title'];

$article_title = $array['article_title'];
$article_text = $array['article_text'];

// echo "<font size=-2 color=red>Section ID:  <font color=black size=-2><i>".$article_section_id."</i></font></font><br>";
// echo "<font size=-1 color=red>Section Title:  <font color=black><u>".$article_section_title."</u></font></font><br>";
// echo "<font size=-2 color=red>Article ID:  <font color=black size=-2><i>".$number."</i></font></font><br>";
// echo "<font size=-1 color=red>Article Title:  <font color=black><b><u>".$article_title."</u></b></font></font><br>";
// echo "<font size=-1 color=red>Article Text:  <font color=black>".$article_text."</font></font><br>";
// echo "<hr>";
}

?>

<!-- The form itself  -->

<form method=post action=>
    <table>
        <tr>
            <td align=right>Section Title:  </td>
            <td>
                <select name=new_section_id>
                    <?
                      $res = mysql_query('SELECT section_id, section_title FROM sections', $connection);
                       while($row = mysql_fetch_assoc($res))
                        {
                         if($row['section_id'] == $section_id)
                            {
                                ?><option selected><? echo $row['section_title']; ?></option><?
                            }
                         else
                            {
                                ?><option><? echo $row['section_title']; ?></option><?
                            }
                           }
                        ?>
                </select>
            </td>
        </tr>
        <tr>
            <td align=right>Article Title:  </td>
            <td>
                <input name=new_article_title type=text size=108 value=<? echo $article_title; ?>>
            </td>
        </tr>
        <tr>
            <td align=right>Article Text:  </td>
            <td>
                <textarea name=new_article_text cols=80 rows=6><? echo $article_text; ?></textarea>
            </td>
        </tr>
        <tr>
            <td colspan=2> </td>
        </tr>
        <tr>
            <td colspan=2 align=center>
                <input name=submit type=submit value=Submit This Form><input name=reset type=reset value=Reset This Form>
            </td>
        </tr>
    </table>
</form>

The section that's to display the drop-down menus are the issue.

What's SUPPOSED to happen is IF the section_id from the sections table MATCHES the section_id from the articles table, it's to mark that option as SELECTED. If not, it's just another choice in the drop-down menu.

I've tried to do this:

<select name=new_section_id>
                    <?
                      $res = mysql_query('SELECT section_id, section_title FROM sections', $connection);
                       while($row = mysql_fetch_assoc($res))
                        {
                         if($row['section_id'] = $section_id)
                            {
                                ?><option selected><? echo $row['section_title']; ?></option><?
                            }
                         else
                            {
                                ?><option><? echo $row['section_title']; ?></option><?
                            }
                           }
                        ?>
                </select>

and ONLY the last section is chosen and when I view source, all them are marked as selected.

When I try this:

<select name=new_section_id>
                    <?
                      $res = mysql_query('SELECT section_id, section_title FROM sections', $connection);
                       while($row = mysql_fetch_assoc($res))
                        {
                         if($row['section_id'] == $section_id)
                            {
                                ?><option selected><? echo $row['section_title']; ?></option><?
                            }
                         else
                            {
                                ?><option><? echo $row['section_title']; ?></option><?
                            }
                           }
                        ?>
                </select>

none of them are marked as selected and the first section is displayed in the drop-down menu.

Either way, that's not right; however, I can't find where the error is. What I need to have happen is that the section_title that matches the id in the article that's being edited is to be marked selected and the other sections are not marked as selected, but is there in the drop-down menu in case I need to change the section that the article in question is being edited.

Can you please double-check the script for me and help me fix this issue? This is the next largest hurdle that I have to get over in order to complete this project.

Thank you for all your help so far!

Sincerely,

wrstrong

fenway · August 28, 2009

Check the section_id that you're passing in, and all of the section_ids from your query.

Sign In

Using php to create and setup mysql database, tables, foreign keys, etc..

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information