Joining 2 tables from one mysql db

WRStrong

I'm trying to create a document (and a form that can modify or choose the info used in the document) from 2 tables in the same db. The tables are setup like this:

Sections table

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

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))

Here's some further info on how they are to be connected:

sections table is:
section_id section_title
1 overview

2 first section
3 second section
4 forth section

articles table is:
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

What I am trying to accomplish is to create a form that will allow me to modify the article title, text and it's section (and it change the articles table, while also allowing me to modify the section title itself and it modify the sections table.

Then, I'm going to create a different form that will allow me to choose the articles to display and then it will compile the article correctly when the form is submitted.

Presently, I understand that the two tables need to be joined; however, when I try to display the combined info, I get nothing but errors. Here's the coding so far:

<!-- Include settings.php -->
<?
include 'settings.php';
?>
<!-- Connect to mysql -->
<?
$connection = mysql_connect("$mysql_host", "$mysql_user", "$mysql_password");
?>
<!-- Select the correct database -->
<?
$selected_db = mysql_select_db($mysql_db, $connection);
if (!$selected_db)
{
    die ('Can NOT select '. $mysql_db . ' database because ' . mysql_error());
}
?>
<!-- Build 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);

$num_rows = mysql_num_rows($result);

?>

The error I get is:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in line 12

I have tried everything I can think of and have come up short all the time. I am a BIG fan of the RTFM approach and I can successfully do an individual table query just fine. I'm missing something in regards to the second table, how to connect them and how to do the queries with them in order to get what I need.

Thanks for the assist!

Sincerely,

wrstrong

blckspder

Have you tried to use a Left join or inner join?

Here is some more information on joins:
http://dev.mysql.com/doc/refman/5.0/en/join.html

WRStrong

I think my join statement is wrong OR I'm missing how to handle it after it's joined. My join statement is:

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

I have read that page before, too. The more research I do, the more I believe I'm missing what to do after the query that's causing the head-scratching-thing. LOL

Thanks for the assist!

wrstrong

blckspder

well line 12 would be

$result = mysql_query($query, $connection);

The error suggest you have a problem with your query statement, try this:

$query = "SELECT section_id, article_title, article_text FROM articles LEFT JOIN sections ON articles.section_id=sections.section_id ORDER BY sections.section_id, articles.article_title";

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

See what that does.

Also is there a reason you are using html comments? Is this PHP embedded into HTML?

WRStrong

I like to use separated comments like that because my editor likes to make the php coding one color and the html another. It is easier for me to find the sections I need to find. Once I get finished code, I change everything over to all one php script.

I made the changes you suggested and now get an entirely different error.

Column 'section_id' in field list is ambiguous

My page is now:

<!-- Include settings.php -->
<?
include 'settings.php';
?>
<!-- Connect to mysql -->
<?
$connection = mysql_connect("$mysql_host", "$mysql_user", "$mysql_password");
?>
<!-- Select the correct database -->
<?
mysql_select_db($mysql_db, $connection) or die('Can NOT select '. $mysql_db . ' database because ' . mysql_error());
?>
<!-- Build query string -->
<?
$query = "SELECT section_id, article_title, article_text FROM articles LEFT JOIN sections ON articles.section_id=sections.section_id ORDER BY sections.section_id, articles.article_title"; 
$result = mysql_query($query, $connection) or die(mysql_error());
?>

Any ideas what could be going on? Could it be that my db isn't setup correctly? (That's a high possibility.)

Once I get no errors, how to make all the data that's retrieved usable? Once I can get it put into variables and then viewed, I am fairly confident that I can then create the update / delete / add entry parts and then create the form that'll make the end result. (Although, I've been known to be wrong before...LOL)

Thanks for all the help.

wrstrong

blckspder

There we go!

The ambiguous means that a field is in both tables and it is not sure which field to select. So try this to see if it works:

SELECT s.section_id, a.article_title, a.article_text FROM articles As a LEFT JOIN sections As s ON a.section_id=s.section_id ORDER BY s.section_id, a.article_title"

If that works, then use mysql_fetch_array($result) in a for or while loop:
http://us3.php.net/mysql_fetch_array

WRStrong

ok, some progress.

When I modify the script as you suggest, the browser just sits there showing the wait icon instead of any error or any display whatsoever.

Here's what I have:

<!-- Include settings.php -->
<?
include 'settings.php';
?>
<!-- Connect to mysql -->
<?
$connection = mysql_connect("$mysql_host", "$mysql_user", "$mysql_password");
?>
<!-- Select the correct database -->
<?
mysql_select_db($mysql_db, $connection) or die('Can NOT select '. $mysql_db . ' database because ' . mysql_error());
?>
<!-- Build query string -->
<?
$query = "SELECT s.section_id, a.article_title, a.article_text FROM articles As a LEFT JOIN sections As s ON a.section_id=s.section_id ORDER BY s.section_id, a.article_title, a.article_text"; 
$result = mysql_query($query, $connection) or die(mysql_error());
?>

I thought that I'd have to use sections.section_id (for example) in the query string. Isn't that the case?

Once I get no errors, we're roughly 80% of the way there, right?

Thanks for the assist.

wrstrong

blckspder

How many records are you trying to return? Try changing the LEFT JOIN to INNER JOIN for the heck of it.

I must be missing something..

Are you sure you have the section_id in both tables?

blckspder

I made a mistake with the query I gave you. I don't know if it makes a difference or not but try this instead,

SELECT s.section_id, a.article_title, a.article_text FROM articles a LEFT JOIN sections s ON a.section_id=s.section_id ORDER BY s.section_id, a.article_title

Sorry its taking so long for me to figure this out for you. Im not really used to helping through forums.. If I had this right in front of me I would have solved it already... 😕

I thought I should start getting more involved on the forum because it has been such a good tool for me when Ive needed help.

WRStrong

Yes, there's a section_id in both tables. What I have is a section name and it's id number in the sections table and the associated section id number in the articles table. The articles table also has the article title and the article text.

I understand all about trying to figure things out by hand. I'm trying to learn so that I can expand my understanding and then help others as well. 🙂

I believe there's only going to be less than 150 total articles; however, they need to be edited and then chosen at will.

Thanks for the continued assist.

wrstrong

blckspder

The only other thing I can suggest to do is to copy the query statement into mysql query browser or phpmyadmin or another piece of software you might use to manage your database and to see if you get an error that way.

The query looks good to me I dont know why its not working. Maybe I am not asking the right questions.

Bjom

May I suggest that you do not use short tags but standard tags and that you do not drop in and out of php mode just to add comments? The comments are relatively redundant anyway.

Try this somewhat cleaner version - and try out the query in phpMyAdmin or MySQLAdministrator as black suggested it looks syntactically correct, but there could be other things wrong.

<php?
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);
?>

WRStrong

Here's the code as it is right now:

<?
include 'settings.php';

$connection = mysql_connect($mysql_host, $mysql_user, $mysql_password);

mysql_select_db($mysql_db, $connection) or die('Can NOT select '. $mysql_db . ' database because ' . mysql_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";

$result = mysql_query($query, $connection) or die(mysql_error());
?>

When I try to use the following coding sections, it just repeats the php code to me in the browser window (like I don't have php installed and it's just treating it like a text file.

trigger_error('Can NOT select '. $mysql_db . ' database because ' . mysql_error(), E_USER_ERROR)

and

trigger_error(mysql_error(), E_USER_ERROR)

The coding that I am using so far does not give me any errors. It doesn't output anything, either. What should I use to get some output so that I can understand the query better?

Thanks for all the help thus far.

wrstrong

Bjom

trigger_error(mysql_error(), E_USER_ERROR)

why not leave that in? and get rid of the ...or die? It will react ot error reporting levels and is thus much cleaner. What extra benefit do you get from the or die?

Simply copy and paste what I prepared? Use [php ] tags instead of

 tags?

Go to the phpAdmin or any other tool you can run queries. use my echo.... copy and paste what it gives you to such a tool. See what the query gives you.

WRStrong

When I try to use the error capturing as you suggest, I get nothing displayed in the browser just as before. Is that an error of some kind?

Here's the full script:

<php? 
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);
?>

I use webmin to gain access to mysql if not in the browser. I've tried phpmyadmin with no luck. Plus, I don't know how to access the dbs directly using any other method. 🙂

I'll make the changes to use the

 tag instead of the [code] tag from this point on.  Thanks for the notify.

Any ideas what's going wrong?

wrstrong

blckspder

your opening php tag is wrong in the code you posted. you have <php ? when it should be <?php

WRStrong

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 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!

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!