Records Not Showing Up

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

amcgibbon
Forum Newbie
Posts: 10
Joined: Thu Jan 01, 2009 4:08 pm

Records Not Showing Up

Post 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>
Last edited by amcgibbon on Thu Jan 01, 2009 6:40 pm, edited 1 time in total.
User avatar
it2051229
Forum Contributor
Posts: 312
Joined: Tue Dec 25, 2007 8:34 pm

Re: Records Not Showing Up

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Records Not Showing Up

Post 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.
amcgibbon
Forum Newbie
Posts: 10
Joined: Thu Jan 01, 2009 4:08 pm

Re: Records Not Showing Up

Post 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.
amcgibbon
Forum Newbie
Posts: 10
Joined: Thu Jan 01, 2009 4:08 pm

Re: Display Month and Group Events by Month

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Records Not Showing Up

Post 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?
amcgibbon
Forum Newbie
Posts: 10
Joined: Thu Jan 01, 2009 4:08 pm

Re: Records Not Showing Up

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Records Not Showing Up

Post 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.
Last edited by califdon on Sat Jan 03, 2009 1:12 pm, edited 2 times in total.
Reason: Corrected a missing opening ( on line 3 and == should have been != on line 4 of the second code snippet.
amcgibbon
Forum Newbie
Posts: 10
Joined: Thu Jan 01, 2009 4:08 pm

Re: Records Not Showing Up

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Records Not Showing Up

Post 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>";
}
amcgibbon
Forum Newbie
Posts: 10
Joined: Thu Jan 01, 2009 4:08 pm

Re: Records Not Showing Up

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Records Not Showing Up

Post by califdon »

What problem? :wink: You didn't say what's wrong. Are you getting an error message? Is anything being displayed?
amcgibbon
Forum Newbie
Posts: 10
Joined: Thu Jan 01, 2009 4:08 pm

Re: Records Not Showing Up

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Records Not Showing Up

Post 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.
amcgibbon
Forum Newbie
Posts: 10
Joined: Thu Jan 01, 2009 4:08 pm

Re: Records Not Showing Up

Post 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:
Post Reply