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

Recordset query in Dreamweaver not pulling the right data.

I have two recordsets on a page: events and eventtext.  The events rs has a field called TextSelection that relates the the TextID in eventtext.  So if a record in events has a 2, then it relates to TextID 2 in the eventtext and displays the CustomText field.  I have the following query in my eventtext rs, but it still just displays the first record in the eventtext.  I know this is super simple, but why isn't it pulling the correct information in the related table? Thanks!

SELECT *
FROM tblEventText, tblEventMain
WHERE tblEventMain.TextSelection =  tblEventText.EventTextID
$colname_DetailRS1 = "-1";
if (isset($_GET['recordID'])) {
  $colname_DetailRS1 = $_GET['recordID'];
}
mysql_select_db($database_CFEvents, $CFEvents);
$query_DetailRS1 = sprintf("SELECT * FROM tblEventMain WHERE EventID = %s ORDER BY EventDate ASC", GetSQLValueString($colname_DetailRS1, "int"));
$query_limit_DetailRS1 = sprintf("%s LIMIT %d, %d", $query_DetailRS1, $startRow_DetailRS1, $maxRows_DetailRS1);
$DetailRS1 = mysql_query($query_limit_DetailRS1, $CFEvents) 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;

mysql_select_db($database_CFEvents, $CFEvents);
$query_TextSelection = "SELECT * FROM tblEventText, tblEventMain WHERE EventTextID = TextSelection";
$TextSelection = mysql_query($query_TextSelection, $CFEvents) or die(mysql_error());
$row_TextSelection = mysql_fetch_assoc($TextSelection);
$totalRows_TextSelection = mysql_num_rows($TextSelection);
?>

Open in new window

0
Kevin Smith
Asked:
Kevin Smith
  • 9
  • 9
1 Solution
 
Jason C. LevineNo oneCommented:
Hi ksmithscs,

This query won't work:

SELECT * FROM tblEventText, tblEventMain WHERE EventTextID = TextSelection

What are you trying to compare in the Where clause?
0
 
Kevin SmithAuthor Commented:
There's an EventID in the tblEventText and an EventSelection in the tblEventMain that relate.  So if the the EventSelection = 2 in tblEventMain, it needs to pull the EventDescription from tblEventText where EventID also = 2.
0
 
Jason C. LevineNo oneCommented:
It's possible that the query is not explicit enough.  Try:

SELECT * FROM tblEventText, tblEventMain WHERE tblEventText.EventTextID = tblEventMain.TextSelection
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Jason C. LevineNo oneCommented:
The other (and probably better) way to do it is with an INNER JOIN:

SELECT tblEventMain.*, tblEventText.* FROM tblEventMain INNER JOIN tblEventText ON tblEventMain.EventID = tblEventText.TextSelection;
0
 
Kevin SmithAuthor Commented:
Nope, still pulling the first record in the table.
0
 
Jason C. LevineNo oneCommented:
Can you do a SQL dump for both tables so I can take a look at the structure and data samples?
0
 
Kevin SmithAuthor Commented:
here be it (it's an adult club, so content might be adultish)
CREATE TABLE `tblEventMain` (
  `EventID` int(11) NOT NULL auto_increment,
  `EventName` varchar(225) collate latin1_german2_ci NOT NULL,
  `EventDate` date NOT NULL,
  `EventDate2` date default NULL,
  `EventDate3` date default NULL,
  `EventDescription` text collate latin1_german2_ci,
  `EventLocation` varchar(100) collate latin1_german2_ci NOT NULL,
  `EventImage` varchar(250) collate latin1_german2_ci default NULL,
  `EventImageMembers` varchar(250) collate latin1_german2_ci default NULL,
  `HotelEvent` text collate latin1_german2_ci,
  `TextSelection` enum('1','2','3','4') collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`EventID`)
) ENGINE=MyISAM AUTO_INCREMENT=120 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=120 ;

-- 
-- Dumping data for table `tblEventMain`
-- 

INSERT INTO `tblEventMain` VALUES (110, 'Nice & Naughty', '2010-11-19', '2010-11-20', NULL, NULL, 'The Estate', 'cf_both_101120_nice_naughty_533.jpg', '', NULL, '3');
INSERT INTO `tblEventMain` VALUES (111, 'Roman Goddess Toga Toga Party', '2010-11-26', '2010-11-27', NULL, NULL, 'The Estate', 'cf_both_101127_toga_533.jpg', '', NULL, '');
INSERT INTO `tblEventMain` VALUES (112, 'Wild Wild West', '2010-12-03', '2010-12-04', NULL, NULL, 'The Estate', 'cf_both_101204_wild_wild_west_533.jpg', '', NULL, '');
INSERT INTO `tblEventMain` VALUES (113, 'Naughty Santa Hotel Party', '2010-12-04', NULL, NULL, 'Come tell Santa what you want!  Pre-Party Meet-N-Greet!', 'Greensboro', 'cf_pub_101204_gboro_naughty_santa_533.jpg', '', NULL, '');
INSERT INTO `tblEventMain` VALUES (114, 'Pole Dance Fantasies', '2010-12-10', '2010-12-11', NULL, NULL, 'The Estate', 'cf_both_101211_pole_dance_533.jpg', '', NULL, '');
INSERT INTO `tblEventMain` VALUES (115, 'Naughty Santa', '2011-01-14', '2011-01-15', NULL, 'Come tell Santa what you want!', 'The Estate', 'cf_both_101218_naughty_santa_533.jpg', '', NULL, '');
INSERT INTO `tblEventMain` VALUES (116, 'Ladies in Men''s Shirts...and Nothing Else!', '2011-01-07', '2011-01-08', NULL, 'Now that the holiday is over, a special present for you!', 'The Estate', 'cf_both_101225_mens_shirts_533.jpg', '', NULL, '1');
INSERT INTO `tblEventMain` VALUES (118, 'Hearts & Tarts Ball: The Estate & Hotel Takeover ', '2011-02-11', '2011-02-12', NULL, 'Learn more about the Hearts & Tarts Ball: The Estate & Hotel Takeover \r\nby <a href="m_val.php">clicking here!</a>', 'The Estate', 'cf_mem_110212_valentines_533.jpg', NULL, NULL, '3');
INSERT INTO `tblEventMain` VALUES (119, 'Hearts & Tarts Ball: The Estate & Hotel Takeover ', '2011-02-11', '2011-02-12', NULL, 'Learn more about the Hearts & Tarts Ball: The Estate & Hotel Takeover \r\nby <a href="m_val.php">clicking here!</a>', 'Couples Convention', 'cf_mem_110212_valentines_533.jpg', NULL, NULL, '4');


CREATE TABLE `tblEventText` (
  `EventTextID` int(11) NOT NULL auto_increment,
  `EventTextTitle` varchar(150) collate latin1_german2_ci NOT NULL,
  `EventTextContent` text collate latin1_german2_ci,
  PRIMARY KEY  (`EventTextID`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `tblEventText`
-- 

INSERT INTO `tblEventText` VALUES (1, 'Estate', 'Make new friends and enjoy connecting with old ones in any of several friendly areas. Enjoy hot dancing at the Main Bar with music provided by our own DJ. Enjoy intimate conversation and cocktails in the Jacuzzi Bar. Shoot a game of pool in the Billiards Room. Sit back with friends and enjoy the XXX shows in the Theater Room. The party continues all night with an entire Hospitality Floor featuring the Nexxus Playroom and both group and private rooms available just a few steps away! <br><br>\r\n\r\nDress to Impress & Sexy Attire always welcome! Baggy pants, sweat pants, gym attire, un-tucked T-shirts and hats are NOT permitted. We are an upscale club, please help us keep it that way by dressing accordingly. We reserve the right to refuse anyone from entering the club based on overall appearance and demeanor.<br><br>\r\n\r\n\r\nEnjoy our FREE catered Dinner Buffet and our Late Night Breakfast Buffet both Friday & Saturday nights. Great food at no additional cost! Light munchies provided after dinner too! The Estate does not serve nor sell alcohol but you are welcome to bring your own! Save some money and still have the best party around.<br><br>\r\n\r\nDownstairs at The Estate you''ll enjoy a playful, social atmosphere! Upstairs at The Estate (and in the downstairs Theater Room) you''ll have an opportunity to explore fantasies in a comfortable and private atmosphere. The Estate Jacuzzis, Jacuzzi Bar, Heated Pool (in season only), Theater Room and entire second floor (before 11 PM) are clothing optional and open for all Estate Parties. After 11 PM, the second floor is designated as No Street Clothing... lingerie, towels we''ll provide and nothing at all are welcome! Lockers are available on both the first and second floors.<br><br>\r\n\r\n\r\nRooms are available at a nearby hotel for those interested and free shuttle service is available all Saturday evening. Neither a hotel room nor reservation is required to attend the party. If you want a room at the nearby hotel, please contact our reservation service for help!\r\n');
INSERT INTO `tblEventText` VALUES (2, 'Hotel Event', 'As always... 100% no pressure fun!! BYOB at the hotel so you can save $$$$. Enjoy this great hotel with inside hotel rooms …and a great evening or weekend out with friends new and old! <br><br>\r\n\r\nDress to Impress ~ Sexy Attire always welcome! Baggy pants, sweat pants, gym attire, un-tucked T-shirts and hats are NOT permitted. We are an upscale club, please help us keep it that way by dressing accordingly. We reserve the right to refuse anyone from entering the club based on overall appearance and demeanor.<br><br>\r\n\r\nClub Members'' registration will open Friday afternoon at 3 PM at the hotel Night Club and will close at midnight. If you plan to arrive after midnight, please let us know so advance arrangements can be made. Email us if you need such arrangements. <br><br>\r\n\r\nHotel rooms are available for those interested and advance reservations are recommended. Neither a hotel room nor reservation is required to attend the party. If you want a hotel room, please contact our reservation service. ');
INSERT INTO `tblEventText` VALUES (3, 'Valentines Weekend', '<body style="text-align: center"><p><a href="m_val.php">CLICK HERE FOR COMPLETE MEMBER INFORMATION!</a></p>\r\n<p>Valentine''s Weekend in Charlotte<BR>\r\n  Full Hotel Takeover   Saturday<BR>\r\n  Friday &amp; Saturday at The Estate <BR>\r\n  Two Complete Parties for the   Price of One<BR>\r\n  Complete Privacy All Weekend <BR>\r\n  <BR>\r\n  Treat Your Valentine to a Very Special   Weekend<BR>\r\n  Saturday Hearts &amp; Tarts Ball ~ Decorated Ballroom<BR>\r\n  Full Friday   &amp; Saturday Evening Parties at The Estate<BR>\r\n  Special Hot Dinner Buffets at The Estate Both Night<BR>\r\n  Special Hot Late Night Breakfast Buffets at The Estate Both Nights<BR>\r\n  Deluxe After Midnight Snack at The Hotel Saturday <BR>\r\n  <BR>\r\n  Hotel Room Nor Reservation Required To Attend Party<BR>\r\n  Call Exotic Travel for Hotel Room Reservation<BR>\r\n  866-822-2228 or   704-926-1503<BR>\r\n  Don''t Disappoint Your Valentine ~ Make Your Plans Now\r\n</p>');
INSERT INTO `tblEventText` VALUES (4, 'Custom 2 (no pool party) -example', 'This is an example.');

Open in new window

0
 
Jason C. LevineNo oneCommented:
So you're trying to get tblEventMain.TextSelection to hook up with tblEventText.EventTextID?

In that case, this query works:

SELECT tblEventMain.*, tblEventText.*
FROM tblEventMain INNER JOIN tblEventText ON tblEventMain.TextSelection = tblEventText.EventTextID;

Screenshot attached as proof.

It works, dude.
If you only get one record to return on the web page, are you sure you have a repeat region defined?

0
 
Kevin SmithAuthor Commented:
it's still just pulling the first record.  look at this:

http://vekins.com/hosted/cf/evnt_detail.php?recordID=118

notice the number 3 which links to the EventID 3 which is a diff text than what's being shown.  the text below the "3 number" is the record the above query is pulling.  still pulling first record.
0
 
Jason C. LevineNo oneCommented:
Show the complete page code as it stand now.
0
 
Kevin SmithAuthor Commented:

<?php
session_start();
?><?php require_once('../../Connections/CFEvents.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$maxRows_DetailRS1 = 10;
$pageNum_DetailRS1 = 0;
if (isset($_GET['pageNum_DetailRS1'])) {
  $pageNum_DetailRS1 = $_GET['pageNum_DetailRS1'];
}
$startRow_DetailRS1 = $pageNum_DetailRS1 * $maxRows_DetailRS1;

$colname_DetailRS1 = "-1";
if (isset($_GET['recordID'])) {
  $colname_DetailRS1 = $_GET['recordID'];
}
mysql_select_db($database_CFEvents, $CFEvents);
$query_DetailRS1 = sprintf("SELECT * FROM tblEventMain WHERE EventID = %s ORDER BY EventDate ASC", GetSQLValueString($colname_DetailRS1, "int"));
$query_limit_DetailRS1 = sprintf("%s LIMIT %d, %d", $query_DetailRS1, $startRow_DetailRS1, $maxRows_DetailRS1);
$DetailRS1 = mysql_query($query_limit_DetailRS1, $CFEvents) 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;

mysql_select_db($database_CFEvents, $CFEvents);
$query_TextSelection = "SELECT tblEventMain.*, tblEventText.* FROM tblEventMain INNER JOIN tblEventText ON tblEventMain.TextSelection = tblEventText.EventTextID;";
$TextSelection = mysql_query($query_TextSelection, $CFEvents) or die(mysql_error());
$row_TextSelection = mysql_fetch_assoc($TextSelection);
$totalRows_TextSelection = mysql_num_rows($TextSelection);
?>
<!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=utf-8" />
<title>Carolina Friends</title>
<style type="text/css">
<!--
body {
	margin-left: 0px;
	margin-top: 0px;
	margin-right: 0px;
	margin-bottom: 0px;
}
.style1 {font-weight: bold}
.style2 {font-weight: bold}
.style4 {font-size: 14px}
.style7 {font-weight: bold}
-->
</style>

<script src="js/dw_event.js" type="text/javascript"></script>
<script src="js/dw_rotator.js" type="text/javascript"></script>


</head>

<body>
<div align="center">
  
   
      <?php include("header.php"); ?>
  
</div>

<table width="800" border="0" align="center" cellpadding="0" cellspacing="0">
  
  <tr>
    <td style="border-left:solid 1px #000000; border-right: solid 1px #000000"><table width="800" border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
     
      
        <td width="14">&nbsp;</td>
        <td width="100%" valign="top"><div align="left">
          <table width="533" border="0" align="center">
            <tr valign="top">
              <td colspan="2" align="center"><h1><?php echo $row_DetailRS1['EventName']; ?></h1>
                <strong><?php echo $row_DetailRS1['EventDescription']; ?></strong></td>
              </tr>
            <tr valign="top">
              <td colspan="2">&nbsp;</td>
            </tr>
            <tr valign="top">
              <td colspan="2" align="center"><img src="event_images/<?php echo $row_DetailRS1['EventImage']; ?>" /><br /></td>
              </tr>
            <tr valign="top">
              <td colspan="2">&nbsp;</td>
              </tr>
            <tr valign="top">
              <td width="276"><div align="left">When is the event?</div></td>
              <td width="458"><strong><?php echo date('l, m/j/Y',strtotime($row_DetailRS1['EventDate'])); ?><br />                  
                                    
<?php if(!empty($row_DetailRS1['EventDate2'])) { 
 echo date('l, m/j/Y',strtotime($row_DetailRS1['EventDate2']));
} else {
 echo '';
}
?><br />


<?php if(!empty($row_DetailRS1['EventDate3'])) {
 echo date('l, m/j/Y',strtotime($row_DetailRS1['EventDate3']));
} else {
 echo '';
}
                  ?>
                  </strong></td>
            </tr>
            <tr valign="top">
              <td><div align="left">Where will this event take place?</div></td>
              <td><strong><?php echo $row_DetailRS1['EventLocation']; ?> </strong><a href="locations.php">(learn more)</a></td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>&nbsp;</td>
            </tr>
            <tr>
              <td colspan="2"><p><?php echo $row_TextSelection['EventTextContent']; ?> content</p>
                <p><?php echo $row_DetailRS1['TextSelection']; ?> number
                
</p>
                <p><?php echo $row_TextSelection['EventTextTitle']; ?></p>
                <p><?php echo $row_TextSelection['EventTextContent']; ?></p>
                </p></td>
              </tr>
          </table>
          <p>&nbsp;</p>
          </div>
          <p>&nbsp;</p></td>
        <td width="10">&nbsp;</td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
    </table></td>
  </tr>
  
</table>
<div align="center">
  
   
      <?php include("footer.php"); ?>
  
</div>
</body>
</html>
<?php
mysql_free_result($DetailRS1);

mysql_free_result($TextSelection);
?>

Open in new window

0
 
Jason C. LevineNo oneCommented:
1) There's no repeat region defined above for $TextSelection.  That's why only one record returns.

2) Are you trying to limit the results of $TextSelection to only that which matches in $DetailRS1?  Because if you are, then you need to use a WHERE clause in $TextSelection to limit that further.

0
 
Kevin SmithAuthor Commented:
Yes, trying to limit results.  If the textselection field is 3, i only want it to show the description for the eventID 3 in the other table.
0
 
Jason C. LevineNo oneCommented:
SELECT tblEventMain.*, tblEventText.*
FROM tblEventMain INNER JOIN tblEventText ON tblEventMain.TextSelection = tblEventText.EventTextID WHERE tblEventText.EventTextID = $row_DetailRS1['TextSelection'];
0
 
Kevin SmithAuthor Commented:
sql syntax error near TextSelection....
0
 
Kevin SmithAuthor Commented:
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /homepages/36/d219225426/htdocs/hosted/cf/evnt_detail.php on line 62
0
 
Jason C. LevineNo oneCommented:
Is this how you have it?

$query_TextSelection = "SELECT tblEventMain.*, tblEventText.*
FROM tblEventMain INNER JOIN tblEventText ON tblEventMain.TextSelection = tblEventText.EventTextID WHERE tblEventText.EventTextID = $row_DetailRS1['TextSelection']";
0
 
Kevin SmithAuthor Commented:
I'll revisit this tonight...
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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