Solved

Recordset query in Dreamweaver not pulling the right data.

Posted on 2011-02-13
18
206 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
  • 9
  • 9
18 Comments
 
LVL 70

Expert Comment

by:Jason C. Levine
Comment Utility
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
Comment Utility
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
Comment Utility
It's possible that the query is not explicit enough.  Try:

SELECT * FROM tblEventText, tblEventMain WHERE tblEventText.EventTextID = tblEventMain.TextSelection
0
 
LVL 70

Expert Comment

by:Jason C. Levine
Comment Utility
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
Comment Utility
Nope, still pulling the first record in the table.
0
 
LVL 70

Expert Comment

by:Jason C. Levine
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 70

Expert Comment

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

Author Comment

by:Kevin Smith
Comment Utility

<?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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
sql syntax error near TextSelection....
0
 

Author Comment

by:Kevin Smith
Comment Utility
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
Comment Utility
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
Comment Utility
I'll revisit this tonight...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now