?
Solved

select from two tables

Posted on 2005-04-06
9
Medium Priority
?
196 Views
Last Modified: 2008-02-01
table 1:
MARKID     KEYWORD
3               apple
3               grape
4               orange

table 2:
MARKID     NAME
3               george
4               frank

ok, so i have two tables, table 2 has the record in it, and table 1 has the keywords. they are linked by MARKID...  notice how one record can have more then one keyword. is there a way, in one query, to return the results AND the keywords....

SELECT * FROM table1 t1, table2 t2 WHERE t1.MARKID = t2.MARKID

i want to see

george     apple grape
frank        orrange  

or do i have to do it with two querys? one where i select all the records, and the go thru and select all keywords for eachr record?
0
Comment
Question by:qwertq
9 Comments
 
LVL 6

Expert Comment

by:peyox
ID: 13722003
select t2.NAME, t1.KEYWORD
from table1 t1 left join table2 t2 on t1.MARKID=t2.MARKID
order by t2.NAME

you will get:
george    apple
george    grape
frank       orange

then in PHP when displaying results you will concatenate keywords for the same name.
0
 

Author Comment

by:qwertq
ID: 13722463
that does nothing different then my original query.

i want sql to return:
george    apple grape
frank       orange
0
 
LVL 1

Expert Comment

by:jeffparis
ID: 13722653
Only 50 points?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:jeffparis
ID: 13722908
Try this: GROUP BY



select t2.NAME, t1.KEYWORD from table1 t1 left join table2 t2 on t1.MARKID=t2.MARKID GROUP BY t2.NAME;
0
 
LVL 5

Expert Comment

by:jericotolentino
ID: 13723260
Hi qwertq,

I don't think you can concatenate that in MySQL, like what you want. Why do you want to display it in MySQL anyway? Just wondering...

You should do it using server side as suggested by expert peyox.

So using his code, you'd place this on your page:

mydatabase.php
-----------------------
<?

//Some code here...

$sql =  select t2.NAME, t1.KEYWORD from table1 t1 left join table2 t2 on t1.MARKID=t2.MARKID
order by t2.NAME
$result = mysql_query($sql);

while ($row=mysql_fetch_array($result)) {
$name = $row['name'];
$keyword = $row['keyword'];
$displayresults .= "<tr><td width=\"75\">" . $name . "</td><td width=\"75\">" . $keyword . "</td></tr>";
}

?>

<!-- HTML headers -->

<body>
<table width="150">
<? echo $displayresults; ?>
</table>
</body>

-----------------

Good luck!
0
 
LVL 5

Expert Comment

by:jericotolentino
ID: 13723319
Sorry, I forgot something. LOL!

Concatenate the string if the names are the same. Don't just close the table row immediately...

Then just end with </tr> when you're done to move to the next row.

;-)
0
 
LVL 1

Expert Comment

by:jeffparis
ID: 13723531
jericotolentino, the mysql/php does not show in a php file, only in an html file. Think before answering. SSI is far more dangerous. Your code also does not include stripslashes, to remove addslashes() or slashes added by gpc_magic_quotes, found in php.ini.

Here is what you need, without compromising your system:






#### Name the below file database.php



<?php

// Always keep this data separate from the rest.

$dbhost = 'localhost'; //Hostname - This is usually correct.
$dbname =''; //database name
$dbusername = ''; //database user
$dbpass = ''; //database password

$conn  = mysql_connect($dbhost, $dbusername, $dbpass)
            or die(mysql_error( ));

mysql_select_db($dbname,$conn)
            or die(mysql_error( ));



?>






#########Name the below file whatever.php

<?php

include("./_database.php");

echo '<table width="400" border="0" cellspacing="0" cellpadding="0">';

$i = 0;
$get_items = 'select t2.NAME, t1.KEYWORD from table1 t1 left join table2 t2 on t1.MARKID=t2.MARKID GROUP BY t2.NAME';
$get_table_data = mysql_query($get_items) or die("MySQL Error #".mysql_errno().": ".mysql_error()."\nQuery:");

      if (mysql_num_rows($get_table_data)<1) {
      echo "<tr><td width="100%">No data.</td></tr>;

} else {
            while ($row = mysql_fetch_array($get_table_data)) {
            $name = stripslashes($row['name']);
            $keyword       = stripslashes($row['keyword']);
            $i++;

                                echo '<tr><td width="50%">' . $name . '</td><td width="50%">' . $keyword . .</td></tr>';

            }            
      }

echo '</table>';

?>




Email dude ** freeonlineblogs.com if this fails, I will fix it.

TIP for jericotolentino: You won't have to use backslashes with double quotes if oyu place the double quotes inside of single quotes as I have done.

0
 
LVL 1

Expert Comment

by:jeffparis
ID: 13723553
I am sorry, I posted that second file wrong, here it is:



<?php

include("./_database.php");

echo '<table width="400" border="0" cellspacing="0" cellpadding="0">';

$i = 0;
$get_items = 'select t2.NAME, t1.KEYWORD from table1 AS t1 left join table2 AS t2 on t1.MARKID=t2.MARKID GROUP BY t2.NAME';
$get_table_data = mysql_query($get_items) or die("MySQL Error #".mysql_errno().": ".mysql_error()."\nQuery:");

     if (mysql_num_rows($get_table_data)<1) {
     echo "<tr><td width="100%">No data.</td></tr>;

} else {
          while ($row = mysql_fetch_array($get_table_data)) {
          $name = stripslashes($row['name']);
          $keyword      = stripslashes($row['keyword']);
          $i++;

                                echo '<tr><td width="50%">' . $name . '</td><td width="50%">' . $keyword . .</td></tr>';

          }          
     }

echo '</table>';

?>
0
 
LVL 1

Accepted Solution

by:
jeffparis earned 150 total points
ID: 13723566
oops.... nope...this is it...promise:



<?php

include("./database.php");

echo '<table width="400" border="0" cellspacing="0" cellpadding="0">';

$i = 0;
$get_items = 'select t2.NAME, t1.KEYWORD from table1 AS t1 left join table2 AS t2 on t1.MARKID=t2.MARKID GROUP BY t2.NAME';
$get_table_data = mysql_query($get_items) or die("MySQL Error #".mysql_errno().": ".mysql_error()."\nQuery:");

     if (mysql_num_rows($get_table_data)<1) {
     echo "<tr><td width="100%">No data.</td></tr>;

} else {
          while ($row = mysql_fetch_array($get_table_data)) {
          $name          = stripslashes($row['name']);
          $keyword      = stripslashes($row['keyword']);
          $i++;

                                echo '<tr><td width="50%">' . $name . '</td><td width="50%">' . $keyword . '</td></tr>';

          }          
     }

echo '</table>';

?>
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month17 days, 13 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question