Joining 2 tables from one mysql db

[Bjom]

I don't know webmin. Can you run queries in it? If not: Try this tool:
MySQL Administrator

no that is not an error. that simply means that your query gets executed alright, but for some reasons does not return any values.

That's why running the query outside of PHP and figuring out what the problem might be should be the next step. Are there data in the tables that match? If you do not have any records that go together you get an emtpy recordset, which is perfectly fine - not an error - but of course not satisfactory...

[Bjom]

seems a few posts were overlapping lol 🙂

sorry for the mistake with the bad opening tag.

Have a look at arrays. they are important.

And simply access it like this:

$myVal = $array['section_id'];
//or
echo $array['section_id'];

etc.

[blckspder]

lol yea I know.. I got confused there for a second! haha Thanks for your help Bjom!

[WRStrong]

I've modified the script (I am sorry for the long thread, guys. I'm just trying to make sure that we are all looking at the same thing for debugging reasons.)

<? 

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"; 

// 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))
{
echo $array['section_id']."<br>";
echo $array['section_title']."<br>";
echo $array['article_title']."<br>";
echo $array['article_text']."<br>";
echo "<hr>";
}

?>

All of the data displays and the title is appropriately connected to the actual article! 😃

What needs to be done to the query to update the records? Is it like the select?

Thanks again for all the help! It's REALLY confusing to jump in the pool with both feet (even more so when I can't swim. lol)

wrstrong

[blckspder]

you arent selecting the section title in your query, just the article title

[Bjom]

oh there is both i believe...

What do you mean by update? In the DB?

both PHP and MySQL have very good documentation. More often than not you find everythign you need there (and more)

UPDATE for example is here

😉

[WRStrong]

I fixed that..lol

I'm trying to wrap my mind around this join-thing. (and getting a headache...lol)

How would I update/delete records in this joined table?

Are they done individually to each different table? Or, is there something like the join feature used here?

Thanks again!

wrstrong

[Bjom]

It's actually quite simple.

The syntax is

UPDATE table SET field1 = newvalue1, field2 = newvalue2... [WHERE...]

so? replace table with the joint thing

UPDATE tblA JOIN tblB ON tblA.id = tblB.id SET...

[WRStrong]

Bjom;10923346 wrote:

It's actually quite simple.

The syntax is

UPDATE table SET field1 = newvalue1, field2 = newvalue2... [WHERE...]

so? replace table with the joint thing

UPDATE tblA JOIN tblB ON tblA.id = tblB.id SET...

ok. Now, I'm confused... lol

My select is...

$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";

I understand the UPDATE; however, let's say that I was updating the section_title where the section_id = 2 with let's say $replace...

I know I could do this...

$query  = "UPDATE sections"; 
$query .= " SET section_title=$replace"; 
$query .= " WHERE section_id = '2'"; 

Do I do that type of stuff with each table individually? How do I format it with the JOIN that I have in there?

wrstrong

[Bjom]

articles As a LEFT JOIN sections As s ON a.section_id = s.section_id

This is your join. It can be understood as a new table.

So in the update....replace "sections" with the whole thing above and you can access fields of both original tables

[WRStrong]

I think I understand. Do you think it would be in best interests to put that part into a string? It could then be used multiple times without thinking...lol

[Bjom]

simply try. experiment a little. it cant hurt.

A few things of interest, that you might want to explore:

sprintf
mysqli
prepared statements
strings

[WRStrong]

Hi guys!

I've gotten everything to display quickly, smoothly and cleanly!

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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font size=-1 color=black>".$array['article_text']."</font></font><br><br></td></tr>";		// The article TEXT!
   }
  $number++;
 }
echo "</table>";
echo "</center>";
?>

That displays a table that ONLY shows the section_title once, then displays the article_title and finally the article_text.

Note: I am taking things one step at a time. This script shows EVERYTHING in the db. What I'm going to work on next is a form that allows the choosing of specific articles via a checkbox. When I get to that point (or need further assistance in setting that part up,) I'll keep you posted.

Thanks again for all of your assistance! I have been having severe brain cramps when it comes to the join feature in mysql. I don't know why. It just happens. I guess the more experience I get with it, the easier it will get, right?

Thanks again!

wrstrong

[WRStrong]

When I have pulled the information from the db (just as the last post) and did a simple echo to make it viewable, I have tried to create a form with a textbox that's propogated by the db info.

The line only displays the first word of the field. How do I make the entire field be displayed in the textbox?

echo "<td><input name=".$array['section_title']." type=text value=".$array['section_title']."></td></tr>";

(For sake of brevity, I have only given you the line in question as I know the rest of the actual page works.)

Thanks for the continued assistance

I solved my own question. lol

If I put the following in the line just ABOVE the quoted script snippet above, it works perfectly.

$array['section_title'] = str_replace(" ", " ", $array['section_title']);

[Bjom]

Set the size of the textbox. It has limits on the maximum characters it shows...you should use a text area probably, because that allows multiline output.

[WRStrong]

Bjom;10923454 wrote:

Set the size of the textbox. It has limits on the maximum characters it shows...you should use a text area probably, because that allows multiline output.

True, when I am going to be doing the article text, I would use a text box; however, this part only contained less than a full sentence of words. (like 3-4 words, max.)

Thanks for the great info!

wrstrong

[WRStrong]

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(' ','&nbsp;',$array['section_id']);
    $section_title =  str_replace(' ','&nbsp;',$array['section_title']);
    $article_id = str_replace(' ','&nbsp;',$array['article_id']);
    $article_title =  str_replace(' ','&nbsp;',$array['article_title']);
    $article_text =  str_replace(' ','&nbsp;',$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:&nbsp;&nbsp;</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:&nbsp;&nbsp;</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:&nbsp;&nbsp;</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]

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(' ','&nbsp;',$array['section_id']);
	$section_title =  str_replace(' ','&nbsp;',$array['section_title']);
	$article_id = str_replace(' ','&nbsp;',$array['article_id']);
	$article_title =  str_replace(' ','&nbsp;',$array['article_title']);
	$article_text =  str_replace(' ','&nbsp;',$array['article_text']);
?>
<form method=post action=article-db-edit.php>
	<table>
		<tr>
			<td align=right>Section:&nbsp;&nbsp;</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:&nbsp;&nbsp;</td>
		<td><input name=new_article_title type=text size=100 tabindex=2 value=<? echo $article_title; ?>></td>
	</tr>
	<tr>
		<td align=right>Article:&nbsp;&nbsp;</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

[WRStrong]

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:&nbsp;&nbsp;</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:&nbsp;&nbsp;</td>
            <td>
                <input name=new_article_title type=text size=108 value=<? echo $article_title; ?>>
            </td>
        </tr>
        <tr>
            <td align=right>Article Text:&nbsp;&nbsp;</td>
            <td>
                <textarea name=new_article_text cols=80 rows=6><? echo $article_text; ?></textarea>
            </td>
        </tr>
        <tr>
            <td colspan=2>&nbsp;</td>
        </tr>
        <tr>
            <td colspan=2 align=center>
                <input name=submit type=submit value=Submit&nbsp;This&nbsp;Form><input name=reset type=reset value=Reset&nbsp;This&nbsp;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