Link to home
Start Free TrialLog in
Avatar of dloj
dlojFlag for United States of America

asked on

Getting wrong data from mysql query in Dreamweaver

The database I built for the website in question, http://www.socalda.org contains meeting infomation, General meeting info, location, focus, and contact.  There are different recordsets for different pages.  

 
I have pages for meetings on days of the week, time of day, and counties and cities.  

 
I just entered a new meeting to my database and it seems that the contact info is not coming out correctly.  

 
I thought it was pulling the wrong contact info but I am now satisfied that what is actually happening is the layout on my pages, for the results,  is bad.  

 
When I test this SQL query both in PHPMyAdmin and Dreamweaver it works fine.  When I pull the page up It only shows 9 records.  

I have upped the repeat region to 11, lowered it to 4 and 3 with no different results.  It always returns 9 rows.  

I have tried to place mysql_num_rows but have not had any number return.  


 
I have upped the repeat to 20 in the Server Behaviors tab and it is still the same.  

 
Here is the code for this page:

 
<?php require_once('Connections/connda.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_wed_meet = 20;
$pageNum_wed_meet = 0;
if (isset($_GET['pageNum_wed_meet'])) {
  $pageNum_wed_meet = $_GET['pageNum_wed_meet'];
}
$startRow_wed_meet = $pageNum_wed_meet * $maxRows_wed_meet;

mysql_select_db($database_connda, $connda);
$query_wed_meet = "SELECT mstr_meeting.NAME, mstr_meeting.ROOM_NAME, TIME_FORMAT(mstr_meeting.start_time,'%l:%i %p') AS TIME, mstr_meeting.DURATION, mstr_meeting.DAY_OF_WEEK, mstr_meeting.MEETING_NOTES, mstr_location.BUILDING_NAME, mstr_location.STREET_ADDRESS, mstr_location.CITY, mstr_location.ZIP_CODE, mstr_location.SPEC_DIRECTIONS, mstr_location.MAP_URL, mstr_contact.CNCT_FNAME, mstr_contact.CNCT_PHONE, group_concat(mstr_feature.DESCR) AS DESCR 

 
FROM (((((mstr_meeting INNER JOIN mstr_location ON mstr_meeting.LID = mstr_location.LID) 

LEFT JOIN mstr_meeting_contact ON mstr_meeting.MID = mstr_meeting_contact.MID) 

LEFT JOIN mstr_contact ON mstr_meeting_contact.CID = mstr_contact.CID) 

LEFT JOIN mstr_meeting_feature ON mstr_meeting.MID = mstr_meeting_feature.MID) 

LEFT JOIN mstr_feature ON mstr_meeting_feature.FID = mstr_feature.FID) 

LEFT JOIN mstr_days ON mstr_meeting.DAY_OF_WEEK = mstr_days.DAY 

 
WHERE mstr_meeting.DAY_OF_WEEK = 'WE' 

 
GROUP BY mstr_meeting.NAME, mstr_meeting.ROOM_NAME, mstr_meeting.start_time, mstr_meeting.DURATION, mstr_meeting.DAY_OF_WEEK, mstr_meeting.MEETING_NOTES, mstr_location.BUILDING_NAME, mstr_location.STREET_ADDRESS, mstr_location.CITY, mstr_location.ZIP_CODE, mstr_location.SPEC_DIRECTIONS, mstr_location.MAP_URL, mstr_contact.CNCT_FNAME, mstr_contact.CNCT_PHONE ORDER BY mstr_days.NO, mstr_meeting.start_time, mstr_meeting.NAME ";


$query_limit_wed_meet = sprintf("%s LIMIT %d, %d", $query_wed_meet, $startRow_wed_meet, $maxRows_wed_meet);
$wed_meet = mysql_query($query_limit_wed_meet, $connda) or die(mysql_error());
$row_wed_meet = mysql_fetch_assoc($wed_meet);

if (isset($_GET['totalRows_wed_meet'])) {
  $totalRows_wed_meet = $_GET['totalRows_wed_meet'];
} else {
  $all_wed_meet = mysql_query($query_wed_meet);
  $totalRows_wed_meet = mysql_num_rows($all_wed_meet);
}
$totalPages_wed_meet = ceil($totalRows_wed_meet/$maxRows_wed_meet)-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=utf-8" />
<title>Wednesday DA  Meetings</title>
<link href="styles/daone.css" rel="stylesheet" type="text/css" />
<script src="SpryAssets/SpryMenuBar.js" type="text/javascript"></script>
<link href="SpryAssets/SpryMenuBarHorizontal.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
/* Give the menu bar a width and set the margins to "auto"
* so that the browser does the centering.
*/

ul.MenuBarHorizontal {
width: 50.2em;
margin: auto;
}
/* Center the text within all menu item links.
*/

ul.MenuBarHorizontal a {
text-align: center;
background-color:#FC9;
}

/* Set the the alignment back to left for any
* menu item links that are in a sub menu.
*/

ul.MenuBarHorizontal ul a {
text-align: left;
}
-->
a:link {
text-decoration: none;
}
a:visited {
text-decoration: none;
}
a:hover {
text-decoration: none;
}
a:active {
text-decoration: none;
}
</style>

</head>

<body >


<div id="container">
  <div id="banner"><img src="images/newheadercb2.png" alt="socalda" width="1101" height="180"  /></div>
  <div id="main_image">
  <?php include("includes/menu.php"); ?>
  
  </div><br /><br /><br />
  <div id="meeta_col"><h2 align="center">Meeting Lists</h2>
  <?php include("includes/menu2.php"); ?>
    </div>
  <div id="meetb_col"><h2 align="center">Wednesday Meetings</h2>
     <table  class="myTable">
      <tr>
        <th>NAME</th>
        <th>ROOM</th>
        <th>TIME</th>
        <th>LENGTH</th>
        <th>DAY</th>
        <th>NOTES</th>
        <th>BUILDING</th>
        <th>ADDRESS</th>
        <th>CITY</th>
        <th>ZIP</th>
        <th>DIRECTIONS</th>
        <th>MAP</th>
        <th>CONTACT</th>
        <th>PHONE</th>
        <th>DESCR</th>
      </tr>
      <?php do { ?>
        <tr>
          <td><?php echo $row_wed_meet['NAME']; ?></td>
          <td><?php echo $row_wed_meet['ROOM_NAME']; ?></td>
          <td><?php echo $row_wed_meet['TIME']; ?></td>
          <td><?php echo $row_wed_meet['DURATION']; ?></td>
          <td><?php echo $row_wed_meet['DAY_OF_WEEK']; ?></td>
          <td><?php echo $row_wed_meet['MEETING_NOTES']; ?></td>
          <td><?php echo $row_wed_meet['BUILDING_NAME']; ?></td>
          <td><?php echo $row_wed_meet['STREET_ADDRESS']; ?></td>
          <td><?php echo $row_wed_meet['CITY']; ?></td>
          <td><?php echo $row_wed_meet['ZIP_CODE']; ?></td>
          <td><?php echo $row_wed_meet['SPEC_DIRECTIONS']; ?></td>
          <td><?php echo $row_wed_meet['MAP_URL']; ?></td>
          <td><?php echo $row_wed_meet['CNCT_FNAME']; ?></td>
          <td><?php echo $row_wed_meet['CNCT_PHONE']; ?></td>
          <td><?php echo $row_wed_meet['DESCR']; ?></td>
        </tr>
        <?php } while ($row_wed_meet = mysql_fetch_assoc($wed_meet)); ?>
    </table>
<br /><br />
  <hr />
  </div>
</div>


</body>

</html>
<?php
mysql_free_result($wed_meet);
?>

Open in new window


I actually should have 10 rows for meetings on Wednesday but still only get nine.  

 
The funny part is the record that shows up in Dreamweaver test and PHPMyAdmin below the newly inserted record has there contact showing up in the newly inserted meeting info but that meeting does not show up at all.  It keeps it to nine records.  

 
So the rows in the browser are overlapping for some reason?  

 
I have deleted the new entries and reinserted them to no avail.  It still only shows 9 rows.  I actually had the rows set to all records and have played with that but it didn't do anything.  

 
If anyone has any pointers to where I can go get info on this issue I would appreciate it.  

Thanks.
Avatar of Robert Granlund
Robert Granlund
Flag of United States of America image

Do all of the records have complete info? Is there something about the one record that is different from the others? Did you try taking out the limit all together?
Avatar of Jason C. Levine
Also, sometimes using GROUP BY instead of HAVING will produce different results.
Avatar of dloj

ASKER

Thanks for the reply.

Originally I had 'all records' checked in my repeat setting.  When I saw the error I started troubleshooting changing it to 20 then 11 even went to 4 and 3 since rows 3 and 4 are the ones having problems.

Someone suggested I might have a off-by-one coding error.  No matter what I changed it to I always came up with nine records.  

The records do have complete information except:

The newly added record comes out as row 3, when I run this in PHPMyAdmin or the test button on the recordset itself in Dreamweaver.  

What happens in any browser though is record 4 is dropped except for the contact in row 4 that shows up in row 3 and the actual contact for row 3 does not show up.  

So simply put  the old row 3 is eliminated by the new row 3 records  except for the contact.  The contact person from the old row 3 stays and the new contact person doesn't show up.

I hope that makes sense.  thanks again.
Avatar of dloj

ASKER

Thanks Jason1178,

It is strange because the same query works fine in PHPMyAdmin and the by using the test button in Dreamweavers recordset dialog box.  

I get 10 rows in both of the above mentioned apps.  No errors.  It is only when I run that page in a browser that the error comes up, any browser.
Well, it's hard to troubleshoot this without the data involved.  It would be really helpful if you could post a SQL dump of the tables involved in the query above and let us rebuild and test for ourselves.

My next guess would be to examine the output of the SQL closely.  Are there any rows being duplicated that would possibly collapse under the GROUP BY?
When you say the same query works fine in PHPMyAdmin, are you echoing the actual query string from the PHP script or are you assembling the query string manually? I would echo the query string before executing it, then copy and paste the query string from your page to PHPMyAdmin to confirm the results.
Avatar of dloj

ASKER

Thanks for the input.

Yes I am copying and pasting the same query string.   And I get correct results in both PHPMyAdmin and the test button in the DW recordset dialog box.    

It seems i am having a display issue.  

I need to also check all the other pages that are pulling data.  I know I am having issues when I pull for certain days and certian times, am, pm, and evening.  

I will work on a SQL dump.
Does Line 74 have anything to do with it?
$totalPages_wed_meet = ceil($totalRows_wed_meet/$maxRows_wed_meet)-1;

Open in new window


Also, are you starting your count from 0 (Zero) and not at 1?
Avatar of dloj

ASKER

Thanks for the question rgranlund.

Line 74 was input by Dreamweaver, I am new to PHP Mysql coding.

I am not trying to paginate anything.  All output from queries is one page.  

Site is http://socalda.org/meet.php
ASKER CERTIFIED SOLUTION
Avatar of dloj
dloj
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.