Jump to content

Join query - please help

quillspirit

By quillspirit
in MySQL Help

 Share

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
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
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.