Page 1 of 2

Records Not Showing Up

Posted: Thu Jan 01, 2009 4:15 pm
by amcgibbon
I am having a problem with the following page:

http://www.kidsnightonbroadway.com/kids ... _month.php

When I enter new Broadway shows they show up without any problem, however, for Off-Broadway shows despite having added several more records, I continue to get only the 2 shows presently listed. Does anyone have any suggestions as to what might be wrong in my code?

Here are my queries:

Code: Select all

<?php require_once('Connections/KidsNightOnBroadway.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $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;
}
}
?>
<!-- Broadway show query -->
<?php
mysql_select_db($database_KidsNightOnBroadway, $KidsNightOnBroadway);
$query_Broadway_Shows = "SELECT shows.showName, shows.sortName, shows.broadwayOffbroadway, events.eventDate FROM shows, events WHERE shows.show='1' AND shows.broadwayOffbroadway='Broadway' AND shows.showCode = events.showcode AND events.eventDate >= curdate() ORDER BY events.eventDate, shows.sortName";
$Broadway_Shows = mysql_query($query_Broadway_Shows, $KidsNightOnBroadway) or die(mysql_error());
$row_Broadway_Shows = mysql_fetch_assoc($Broadway_Shows);
$totalRows_Broadway_Shows = mysql_num_rows($Broadway_Shows);
?>
<!-- Off Broadway show query -->
<?php
mysql_select_db($database_KidsNightOnBroadway, $KidsNightOnBroadway);
$query_OffBroadway_Shows = "SELECT shows.showName, shows.sortName, shows.broadwayOffbroadway, events.eventDate FROM shows, events WHERE shows.show='1' AND shows.broadwayOffbroadway='Off Broadway' AND shows.showCode = events.eventCode and events.eventDate >= curdate() ORDER BY events.eventDate, shows.sortName";
$OffBroadway_Shows = mysql_query($query_OffBroadway_Shows, $KidsNightOnBroadway) or die(mysql_error());
$row_OffBroadway_Shows = mysql_fetch_assoc($OffBroadway_Shows);
$totalRows_OffBroadway_Shows = mysql_num_rows($OffBroadway_Shows);
?>
 
<?
$sortName=$_GET['sortName']
 
?>
 
<?php
mysql_select_db($database_KidsNightOnBroadway, $KidsNightOnBroadway);
$query_getShow = "SELECT * FROM shows WHERE shows.sortName='$sortName'";
$getShow = mysql_query($query_getShow, $KidsNightOnBroadway) or die(mysql_error()); 
$row_getShow = mysql_fetch_assoc($getShow); $totalRows_getShow = mysql_num_rows($getShow); 
?>
Here is how I am trying to display the results:

Code: Select all

<table width="100%" border="0" cellspacing="0" cellpadding="10">
        <tr>
          <td width="33%"><strong>Show Date</strong></td>
          <td width="67%"><strong>Show Name</strong></td>
        </tr>
        <?php do { ?> <tr>
         
            <td><?php echo $row_Broadway_Shows['eventDate'] ?></td>
            <td><em><a href="kids_display_show.php?sortName=<?php echo $row_Broadway_Shows['sortName']?>"><?php echo $row_Broadway_Shows['showName'] ?></a></em></td>
            
</tr><?php } while ($row_Broadway_Shows = mysql_fetch_assoc($Broadway_Shows)); ?>
 
      </table>
      <p><strong>Off-Broadway Shows</strong></p>
      <table width="100%" border="0" cellspacing="0" cellpadding="10">
        <tr>
          <td width="33%"><strong>Show Date</strong></td>
          <td width="67%"><strong>Show Name</strong></td>
        </tr>
        <?php do { ?>
          <tr>
            <td><?php echo $row_OffBroadway_Shows['eventDate'] ?></td>
            <td><em><a href="kids_display_show.php?sortName=<?php echo $row_OffBroadway_Shows['sortName']?>"><?php echo $row_OffBroadway_Shows['showName'] ?></a></em></td>
          </tr>
          <?php } while ($row_OffBroadway_Shows = mysql_fetch_assoc($OffBroadway_Shows)); ?>
      </table>

Re: Records Not Showing Up

Posted: Thu Jan 01, 2009 5:11 pm
by it2051229

Code: Select all

 
$query_OffBroadway_Shows = "SELECT shows.showName, shows.sortName, shows.broadwayOffbroadway, events.eventDate FROM shows, events WHERE shows.show='1' AND shows.broadwayOffbroadway='Off Broadway' AND shows.showCode = events.eventCode and events.eventDate >= curdate() ORDER BY events.eventDate, shows.sortName";
$OffBroadway_Shows = mysql_query($query_OffBroadway_Shows, $KidsNightOnBroadway) or die(mysql_error());
 
1. Do a test, insert a new data if you have an interface for that, THEN manually check the newly inserted data IF the columns are properly/APPROPRIATELY filled.
2. Something is logically wrong with your query.

Re: Records Not Showing Up

Posted: Thu Jan 01, 2009 7:03 pm
by califdon
Since the 2 older records are showing up, and your queries for the two categories are the same except for the value of the BroadwayOffBroadway field, one of the following must be true:
  • The new records didn't get added to the table, or
  • The new records contain data that results in their not being selected (such as if there is no matching record in the events table with the same eventCode)
Since your queries are inner joins of 2 tables, there must be a matching record in both tables in order to be selected. That's most likely where your problem is.

Re: Records Not Showing Up

Posted: Thu Jan 01, 2009 9:48 pm
by amcgibbon
That was the answer. I was looking to match an incorrect field. It should have been events.showCode. Thank you so much for the help.

Re: Display Month and Group Events by Month

Posted: Thu Jan 01, 2009 10:08 pm
by amcgibbon
Taking this one step further, one of the things I would like to do is to display a headline for the months that have a show with a discount in it and then display the event dates for that month. I'm feeling a little lost as to how to structure this. If there is something already online that you can point me to, I would really appreciate it. I'm not exactly sure what I'm looking for here.

Thanks,
Andy

Re: Records Not Showing Up

Posted: Thu Jan 01, 2009 11:24 pm
by califdon
Glad your first problem was solved, Andy. I'm not clear on what you have in mind now, though. Do you want this in-line with all the other displayed data, or are you talking about a separate display? Maybe it would help if you do a crude mock-up of what you want it to look like on the web page. Also how is a show with a discount identified?

Re: Records Not Showing Up

Posted: Fri Jan 02, 2009 7:16 am
by amcgibbon
I'm talking about inline in the current code, breaking down events by month. You can see this page here:

http://www.kidsnightonbroadway.com/kids ... _month.php

The way it is displaying the dates all run together. I would like the data to display like this:

February

Dates for February

March

Dates for March

etc.

But this needs to display based upon only those months with an actual date in the event.eventDate field.

Thank you again for your generous help.

Andy

Re: Records Not Showing Up

Posted: Fri Jan 02, 2009 3:38 pm
by califdon
Oh, that's easy! :) I thought you were describing something more complicated. I have a site that does just that (http://theatreorgans.com/norcal/newevents.asp). As you can see, the month headers only occur when there is data for that month. This particular one is on a Microsoft server, so it's written in ASP rather than PHP, but the principle is exactly the same:

You already have a query that is ordered by the performance date, right? That's 1/3 of the solution! And you're already looping through the rows in the result and displaying them, right? That's another 1/3 of the solution! You're almost there! All you need to do is add a column in your query to return just the Month name and Year of the date (you could do it without this, but I think this is the easiest way), then as you loop through each row, you check to see if the month is the same or different from the previous record (which you'll need to store in a variable within the loop) and if it's different, print it out as a header. That's it! You can embellish this with how it's displayed, etc., but the basic logic is very simple.

So for example, in each your queries you need to do something like this:

Code: Select all

$query_Broadway_Shows = "SELECT shows.showName, shows.sortName, shows.broadwayOffbroadway, events.eventDate[color=#009900], Date('F Y',events.eventDate) As monthyear
[/color]
FROM shows, events WHERE shows.show='1' AND shows.broadwayOffbroadway='Broadway' 
AND shows.showCode = events.showcode AND events.eventDate >= curdate() 
ORDER BY events.eventDate, shows.sortName";
The 'F Y' specified in the PHP Date() function formats the date as the complete spelled-out month followed by a space and the 4-digit year. I'm assuming that your dates are stored in the table as PHP date data types.

Then in your display logic, you do something like the following (I won't attempt to reconstruct your table formatting here):

Code: Select all

$currmonthyear="";
while ($row_Broadway_Shows = mysql_fetch_assoc($Broadway_Shows)) {
    extract ($row);  // (assigns the $row[] array members to $variable names)
    if($currmonthyear!=$monthyear) {  // needs a header
        echo "<div class='hdr'>$monthyear]</div>";
        $currmonthyear=$monthyear;  // establishes the new current month-year value
    }
    echo "<div class='showdt'>$eventDate</div><div class='shownm'>$showName</div>";
}
Take a look at that and see if you figure out the logic, then adapt it to your needs.

Re: Records Not Showing Up

Posted: Sat Jan 03, 2009 10:43 am
by amcgibbon
Thanks for your reply.

I am looking at the mySQL documentation here:

http://dev.mysql.com/doc/refman/5.1/en/ ... ction_date

But I can't seem to find anything that describes the use of the date function you show in the code:

Code: Select all

Date('F Y',events.eventDate)
You mention in your post above that this is a php function. But since it is in the select statement, how is this a php function? As you can see, I'm a little confused. And yes, my event date is in a date data type.

Am I missing something on this page? I get it conceptually, I'm just trying to understand it better technically.

Andy

Re: Records Not Showing Up

Posted: Sat Jan 03, 2009 2:08 pm
by califdon
amcgibbon wrote:Thanks for your reply.

I am looking at the mySQL documentation here:

http://dev.mysql.com/doc/refman/5.1/en/ ... ction_date

But I can't seem to find anything that describes the use of the date function you show in the code:

Code: Select all

Date('F Y',events.eventDate)
You mention in your post above that this is a php function. But since it is in the select statement, how is this a php function? As you can see, I'm a little confused. And yes, my event date is in a date data type.

Am I missing something on this page? I get it conceptually, I'm just trying to understand it better technically.

Andy
My apologies, you're absolutely right, I stuffed a PHP function into an SQL statement--bad on me! The proper SQL equivalent would be to use MONTHNAME() and YEAR() SQL functions. See http://dev.mysql.com/doc/refman/5.1/en/ ... _monthname. Maybe I was tired when I wrote that, I know I hadn't been drinking! :oops:

Code: Select all

$query_Broadway_Shows = "SELECT shows.showName, shows.sortName, shows.broadwayOffbroadway[color=#009900], events.eventDate, MONTHNAME(events.eventDate) As mymonth, YEAR(events.eventDate) As myyear[/color]
FROM shows, events WHERE shows.show='1' AND shows.broadwayOffbroadway='Broadway'
AND shows.showCode = events.showcode AND events.eventDate >= curdate()
ORDER BY events.eventDate, shows.sortName";

Code: Select all

$currmonthyear="";
while ($row_Broadway_Shows = mysql_fetch_assoc($Broadway_Shows)) {
    extract ($row);  // (assigns the $row[] array members to $variable names)
    $monthyear=$mymonth . " " . $myyear;  // <---
    if($currmonthyear != $monthyear) {  // needs a header
        echo "<div class='hdr'>$monthyear]</div>";
        $currmonthyear=$monthyear;  // establishes the new current month-year value
    }
    echo "<div class='showdt'>$eventDate</div><div class='shownm'>$showName</div>";
}

Re: Records Not Showing Up

Posted: Tue Jan 06, 2009 12:46 pm
by amcgibbon
Thanks for assisting me on this. I have hit a bit of a wall getting this to work and I'm not sure where my problem is: Here is the code for the query:

Code: Select all

<!-- Broadway show query -->
<?php
mysql_select_db($database_KidsNightOnBroadway, $KidsNightOnBroadway);
$query_Broadway_Shows = "SELECT shows.showName, shows.sortName, shows.broadwayOffbroadway, events.eventDate, MONTHNAME(events.eventDate) AS myMonth, YEAR(events.eventDate) AS myYear FROM shows, events WHERE shows.show='1' AND shows.broadwayOffbroadway='Broadway' AND shows.showCode = events.showcode AND events.eventDate >= curdate() ORDER BY events.eventDate, shows.sortName";
$Broadway_Shows = mysql_query($query_Broadway_Shows, $KidsNightOnBroadway) or die(mysql_error());
$row_Broadway_Shows = mysql_fetch_assoc($Broadway_Shows);
$totalRows_Broadway_Shows = mysql_num_rows($Broadway_Shows);
?>
and here is the code to display it:

Code: Select all

    <?php $currmonthyear="";
while ($row_Broadway_Shows = mysql_fetch_assoc($Broadway_Shows)) {
extract ($row);  // (assigns the $row[] array members to $variable names)
$monthyear=$myMonth . " " . $myYear;  // <---
if($currmonthyear != $monthyear) {  // needs a header
      echo "<div class='hdr'>$monthyear]</div>";
       $currmonthyear=$monthyear;  // establishes the new current month-year value
  }
 echo "<table width="100%" border="0" cellspacing="0" cellpadding="10">
        <tr>
          <td width="33%"><strong>Show Date</strong></td>
          <td width="67%"><strong>Show Name</strong></td>
        </tr>";
do {  <tr>
         
            <td><?php echo $row_Broadway_Shows['eventDate'] ?></td>
            <td><em><a href="kids_display_show.php?sortName=<?php echo $row_Broadway_Shows['sortName']?>"><?php echo $row_Broadway_Shows['showName'] ?></a></em></td>
            
</tr><?php } while ($row_Broadway_Shows = mysql_fetch_assoc($Broadway_Shows)); ?>
 
<?php  </table>
      
      }</p> ?>
Any pointers on where the problem may lay?

Thank you again so very much.

Andy

Re: Records Not Showing Up

Posted: Tue Jan 06, 2009 1:44 pm
by califdon
What problem? :wink: You didn't say what's wrong. Are you getting an error message? Is anything being displayed?

Re: Records Not Showing Up

Posted: Tue Jan 06, 2009 2:58 pm
by amcgibbon
My apologies, how silly of me. You can see this at:

http://www.kidsnightonbroadway.com/kids ... month2.php

As you can see, the page is not showing up at all. I guess I am getting confused as to how the display code gets laid out and how it fits in with the code I have already written.

Re: Records Not Showing Up

Posted: Tue Jan 06, 2009 5:32 pm
by califdon
When you get a totally blank page like that, it means that the PHP parser has encountered an irreconcilable error, such as a syntax error and therefore halts, without sending anything to the browser. There are a couple of things you can do right away, but probably I'll need to go through your entire script, or at least everything that you might have changed in any way since it was at least running. The problem is, I am rather tied up for a few days and may not have time to do that. In the meantime, do this: at the VERY BEGINNING of the script, within the <?php block, insert the following 2 lines, which turn on the PHP error reporting and might produce an error message that would be helpful (remove these lines once the error has been corrected, since hackers sometimes gain useful info about your script if you leave the error reporting on and another error occurs):

Code: Select all

 
ini_set(’display_errors’, ‘On’);
ini_set(’error_reporting’, E_ALL);
I will try to get back to your script in detail. If adding the error reporting results in errors when you run it, please post the exact error messages here.

Re: Records Not Showing Up

Posted: Sun Jan 11, 2009 5:36 pm
by amcgibbon
I tried to insert the code you suggested into the code and it didn't affect it at all, it was still a blank page. :banghead: