Solved

Recordset query in Dreamweaver not pulling the right data.

Posted on 2011-02-13
18
212 Views
Last Modified: 2012-05-11
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
Comment
Question by:Kevin Smith
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
18 Comments
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34885115
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
 

Author Comment

by:Kevin Smith
ID: 34885121
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34885137
It's possible that the query is not explicit enough.  Try:

SELECT * FROM tblEventText, tblEventMain WHERE tblEventText.EventTextID = tblEventMain.TextSelection
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34885148
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
 

Author Comment

by:Kevin Smith
ID: 34885169
Nope, still pulling the first record in the table.
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34885305
Can you do a SQL dump for both tables so I can take a look at the structure and data samples?
0
 

Author Comment

by:Kevin Smith
ID: 34885379
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34885395
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
 

Author Comment

by:Kevin Smith
ID: 34885419
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34885428
Show the complete page code as it stand now.
0
 

Author Comment

by:Kevin Smith
ID: 34885429

<?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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34885441
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
 

Author Comment

by:Kevin Smith
ID: 34885483
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34885500
SELECT tblEventMain.*, tblEventText.*
FROM tblEventMain INNER JOIN tblEventText ON tblEventMain.TextSelection = tblEventText.EventTextID WHERE tblEventText.EventTextID = $row_DetailRS1['TextSelection'];
0
 

Author Comment

by:Kevin Smith
ID: 34885552
sql syntax error near TextSelection....
0
 

Author Comment

by:Kevin Smith
ID: 34885553
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
 
LVL 70

Accepted Solution

by:
Jason C. Levine earned 500 total points
ID: 34885560
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
 

Author Comment

by:Kevin Smith
ID: 35128859
I'll revisit this tonight...
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

729 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