Jump to content

Join query - please help

quillspirit

By quillspirit
in MySQL Help

Recommended Posts

quillspirit Newbie

quillspirit

Posted
Posted
I need to pull up all [b]distinct[/b] userid_numbers out of [i]table1[/i] (To see who is online and in that location, excluding duplicate results), and then query [i]table2[/i] for each userid_number to get profile information, and then display in a table on the website.

So basically, I need to join these two queries...

[code]$query1 = "SELECT DISTINCT usernumber FROM table1 WHERE useronline_location='$location'";[/code]

to get the list of usernumbers (in an array?)... then run the following query in a loop, so it gets the information for each usernumber...

[code]$query2 = "SELECT * FROM table2 WHERE uidnum='$usernumber'";

// Display table here

[/code]



Can somebody assist? Thanks
Link to comment
https://forums.phpfreaks.com/topic/18813-join-query-please-help/
Share on other sites
Techbot Newbie

Techbot

Posted
Posted
This is very similar to what I am trying.

However since I was trying to avoid creating a new array or table I simply added another string.

ver1) without the string  (contains duplicates)

http://87.192.229.254/test/master.php

ver2)
http://87.192.229.254/test/master2.php


I've added $old_artist

[code] <?php do { if ($Artist_old <>  $row_Artist['artist'] ) { ?>
 
      <tr>
      <td><?php echo $row_Artist['ID']; ?></td>
      <td><?php echo $row_Artist['artist'];?></td>
      <td><?php echo $Artist_old;?></td>
    </tr>
<? $Artist_old = $row_Artist['artist']; ?>
    <?php } else{}

  } 



while ($row_Artist = mysql_fetch_assoc($Artist)); ?>
[/code]

notice old artist does not = artist until AFTER the row has been printed ie as long as the are sorted (in my case alphabetically) the two strings $artist and old_artist may actually be different. 

When they are the same the loop skips them and moves to the next record.

version 3)
http://87.192.229.254/test/master3.php

This time each of the ID fields contains a link to a details page with the IDfield appended to the URL

[code]<a href="detail.php?recordID=<?php echo $row_Artist['ID']; ?>">[/code]


complete Master page code
[code]<?php require_once('Connections/bot.php'); ?>
<?php
$currentPage = $_SERVER["PHP_SELF"];
$Artist_old = 20;
$maxRows_Artist = 20;
$pageNum_Artist = 0;
if (isset($_GET['pageNum_Artist'])) {
  $pageNum_Artist = $_GET['pageNum_Artist'];
}
$startRow_Artist = $pageNum_Artist * $maxRows_Artist;

mysql_select_db($database_bot, $bot);
$query_Artist = "SELECT ID, artist, album FROM songlist ORDER BY artist ASC";
$query_limit_Artist = sprintf("%s LIMIT %d, %d", $query_Artist, $startRow_Artist, $maxRows_Artist);
$Artist = mysql_query($query_limit_Artist, $bot) or die(mysql_error());
$row_Artist = mysql_fetch_assoc($Artist);

if (isset($_GET['totalRows_Artist'])) {
  $totalRows_Artist = $_GET['totalRows_Artist'];
} else {
  $all_Artist = mysql_query($query_Artist);
  $totalRows_Artist = mysql_num_rows($all_Artist);
}
$totalPages_Artist = ceil($totalRows_Artist/$maxRows_Artist)-1;

$queryString_Artist = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_Artist") == false &&
        stristr($param, "totalRows_Artist") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_Artist = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_Artist = sprintf("&totalRows_Artist=%d%s", $totalRows_Artist, $queryString_Artist);



?>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<br>
<table border="1">
  <tr>
    <td>ID</td>
    <td>artist</td>
    <td>artist+1</td>
  </tr>
  <?php do { if ($Artist_old <>  $row_Artist['artist'] ) { ?>
 
      <tr>
      <td><a href="detail.php?recordID=<?php echo $row_Artist['ID']; ?>"><?php echo $row_Artist['ID']; ?></a></td>
      <td><?php echo $row_Artist['artist'];?></td>
      <td><?php echo $Artist_old;?></td>
    </tr><? $Artist_old = $row_Artist['artist']; ?>
    <?php } else{}

  } 



while ($row_Artist = mysql_fetch_assoc($Artist)); ?>






</table>
<p>
<table border="0" width="50%" align="center">
  <tr>
    <td width="23%" align="center"><?php if ($pageNum_Artist > 0) { // Show if not first page ?>
          <a href="<?php printf("%s?pageNum_Artist=%d%s", $currentPage, 0, $queryString_Artist); ?>">First</a>
          <?php } // Show if not first page ?>
    </td>
    <td width="31%" align="center"><?php if ($pageNum_Artist > 0) { // Show if not first page ?>
          <a href="<?php printf("%s?pageNum_Artist=%d%s", $currentPage, max(0, $pageNum_Artist - 1), $queryString_Artist); ?>">Previous</a>
          <?php } // Show if not first page ?>
    </td>
    <td width="23%" align="center"><?php if ($pageNum_Artist < $totalPages_Artist) { // Show if not last page ?>
          <a href="<?php printf("%s?pageNum_Artist=%d%s", $currentPage, min($totalPages_Artist, $pageNum_Artist + 1), $queryString_Artist); ?>">Next</a>
          <?php } // Show if not last page ?>
    </td>
    <td width="23%" align="center"><?php if ($pageNum_Artist < $totalPages_Artist) { // Show if not last page ?>
          <a href="<?php printf("%s?pageNum_Artist=%d%s", $currentPage, $totalPages_Artist, $queryString_Artist); ?>">Last</a>
          <?php } // Show if not last page ?>
    </td>
  </tr>
</table>
</p>
<p>&nbsp;</p>
</body>
</html>
<?php
mysql_free_result($Artist);
?>
[/code]

complete details page code
[code]<?php require_once('Connections/bot.php'); ?><?php
$maxRows_DetailRS1 = 10;
$pageNum_DetailRS1 = 0;
if (isset($_GET['pageNum_DetailRS1'])) {
  $pageNum_DetailRS1 = $_GET['pageNum_DetailRS1'];
}
$startRow_DetailRS1 = $pageNum_DetailRS1 * $maxRows_DetailRS1;

mysql_select_db($database_bot, $bot);
$recordID = $_GET['recordID'];
$query_DetailRS1 = "SELECT * FROM songlist WHERE ID = $recordID";
$query_limit_DetailRS1 = sprintf("%s LIMIT %d, %d", $query_DetailRS1, $startRow_DetailRS1, $maxRows_DetailRS1);
$DetailRS1 = mysql_query($query_limit_DetailRS1, $bot) or die(mysql_error());
$row_DetailRS1 = mysql_fetch_assoc($DetailRS1);

if (isset($_GET['totalRows_DetailRS1'])) {
  $totalRows_DetailRS1 = $_GET['totalRows_DetailRS1'];
} else {
  $all_DetailRS1 = mysql_query($query_DetailRS1);
  $totalRows_DetailRS1 = mysql_num_rows($all_DetailRS1);
}
$totalPages_DetailRS1 = ceil($totalRows_DetailRS1/$maxRows_DetailRS1)-1;
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<table border="1" align="center">
 
  <tr>
    <td>ID</td>
    <td><?php echo $row_DetailRS1['ID']; ?> </td>
  </tr>
  <tr>
    <td>filename</td>
    <td><?php echo $row_DetailRS1['filename']; ?> </td>
  </tr>
  <tr>
    <td>diskID</td>
    <td><?php echo $row_DetailRS1['diskID']; ?> </td>
  </tr>
  <tr>
    <td>flags</td>
    <td><?php echo $row_DetailRS1['flags']; ?> </td>
  </tr>
  <tr>
    <td>songtype</td>
    <td><?php echo $row_DetailRS1['songtype']; ?> </td>
  </tr>
  <tr>
    <td>status</td>
    <td><?php echo $row_DetailRS1['status']; ?> </td>
  </tr>
  <tr>
    <td>weight</td>
    <td><?php echo $row_DetailRS1['weight']; ?> </td>
  </tr>
  <tr>
    <td>balance</td>
    <td><?php echo $row_DetailRS1['balance']; ?> </td>
  </tr>
  <tr>
    <td>date_added</td>
    <td><?php echo $row_DetailRS1['date_added']; ?> </td>
  </tr>
  <tr>
    <td>date_played</td>
    <td><?php echo $row_DetailRS1['date_played']; ?> </td>
  </tr>
  <tr>
    <td>date_artist_played</td>
    <td><?php echo $row_DetailRS1['date_artist_played']; ?> </td>
  </tr>
  <tr>
    <td>date_album_played</td>
    <td><?php echo $row_DetailRS1['date_album_played']; ?> </td>
  </tr>
  <tr>
    <td>date_title_played</td>
    <td><?php echo $row_DetailRS1['date_title_played']; ?> </td>
  </tr>
  <tr>
    <td>duration</td>
    <td><?php echo $row_DetailRS1['duration']; ?> </td>
  </tr>
  <tr>
    <td>artist</td>
    <td><?php echo $row_DetailRS1['artist']; ?> </td>
  </tr>
  <tr>
    <td>title</td>
    <td><?php echo $row_DetailRS1['title']; ?> </td>
  </tr>
  <tr>
    <td>album</td>
    <td><?php echo $row_DetailRS1['album']; ?> </td>
  </tr>
  <tr>
    <td>label</td>
    <td><?php echo $row_DetailRS1['label']; ?> </td>
  </tr>
  <tr>
    <td>pline</td>
    <td><?php echo $row_DetailRS1['pline']; ?> </td>
  </tr>
  <tr>
    <td>trackno</td>
    <td><?php echo $row_DetailRS1['trackno']; ?> </td>
  </tr>
  <tr>
    <td>composer</td>
    <td><?php echo $row_DetailRS1['composer']; ?> </td>
  </tr>
  <tr>
    <td>ISRC</td>
    <td><?php echo $row_DetailRS1['ISRC']; ?> </td>
  </tr>
  <tr>
    <td>catalog</td>
    <td><?php echo $row_DetailRS1['catalog']; ?> </td>
  </tr>
  <tr>
    <td>UPC</td>
    <td><?php echo $row_DetailRS1['UPC']; ?> </td>
  </tr>
  <tr>
    <td>feeagency</td>
    <td><?php echo $row_DetailRS1['feeagency']; ?> </td>
  </tr>
  <tr>
    <td>albumyear</td>
    <td><?php echo $row_DetailRS1['albumyear']; ?> </td>
  </tr>
  <tr>
    <td>genre</td>
    <td><?php echo $row_DetailRS1['genre']; ?> </td>
  </tr>
  <tr>
    <td>website</td>
    <td><?php echo $row_DetailRS1['website']; ?> </td>
  </tr>
  <tr>
    <td>buycd</td>
    <td><?php echo $row_DetailRS1['buycd']; ?> </td>
  </tr>
  <tr>
    <td>info</td>
    <td><?php echo $row_DetailRS1['info']; ?> </td>
  </tr>
  <tr>
    <td>lyrics</td>
    <td><?php echo $row_DetailRS1['lyrics']; ?> </td>
  </tr>
  <tr>
    <td>picture</td>
    <td><?php echo $row_DetailRS1['picture']; ?> </td>
  </tr>
  <tr>
    <td>count_played</td>
    <td><?php echo $row_DetailRS1['count_played']; ?> </td>
  </tr>
  <tr>
    <td>count_requested</td>
    <td><?php echo $row_DetailRS1['count_requested']; ?> </td>
  </tr>
  <tr>
    <td>last_requested</td>
    <td><?php echo $row_DetailRS1['last_requested']; ?> </td>
  </tr>
  <tr>
    <td>count_performances</td>
    <td><?php echo $row_DetailRS1['count_performances']; ?> </td>
  </tr>
  <tr>
    <td>xfade</td>
    <td><?php echo $row_DetailRS1['xfade']; ?> </td>
  </tr>
  <tr>
    <td>bpm</td>
    <td><?php echo $row_DetailRS1['bpm']; ?> </td>
  </tr>
  <tr>
    <td>mood</td>
    <td><?php echo $row_DetailRS1['mood']; ?> </td>
  </tr>
  <tr>
    <td>rating</td>
    <td><?php echo $row_DetailRS1['rating']; ?> </td>
  </tr>
  <tr>
    <td>overlay</td>
    <td><?php echo $row_DetailRS1['overlay']; ?> </td>
  </tr>
  <tr>
    <td>playlimit_count</td>
    <td><?php echo $row_DetailRS1['playlimit_count']; ?> </td>
  </tr>
  <tr>
    <td>playlimit_action</td>
    <td><?php echo $row_DetailRS1['playlimit_action']; ?> </td>
  </tr>
  <tr>
    <td>songrights</td>
    <td><?php echo $row_DetailRS1['songrights']; ?> </td>
  </tr>
 
 
</table>

</body>
</html><?php
mysql_free_result($DetailRS1);
?>
[/code]
Link to comment
https://forums.phpfreaks.com/topic/18813-join-query-please-help/#findComment-81191
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.