• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

select from two tables

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
qwertq
Asked:
qwertq
1 Solution
 
peyoxCommented:
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
 
qwertqAuthor Commented:
that does nothing different then my original query.

i want sql to return:
george    apple grape
frank       orange
0
 
jeffparisCommented:
Only 50 points?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
jeffparisCommented:
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
 
jericotolentinoCommented:
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
 
jericotolentinoCommented:
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
 
jeffparisCommented:
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
 
jeffparisCommented:
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
 
jeffparisCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now