[RESOLVED] PHP Navigation

[jamjam]

Hi

I am building a site that will be powered by a Mysql database.
Everything has been going great until I hit this problem.

Because the site will contain TV Listings data, I have requirement for the user to select a date from a row of 7 in order to get results from the database.

To make navigation easier I want the date the user selected to be highlighted.

By adapting a tutorial I found online I was able to get this to work by adding a "navigation" div and some CSS into my stylesheet as shown below.

So currently I am able to select a date from the row of 7 and it will be highlighted just as I wanted but there is problem.

The 2 vital lines in my PHP are in the head <?php $thisPage=$_GET['date']; ?> and in the navigation div <li<?php if ($thisPage=='12-07')
echo " id=\"currentpage\""; ?>>

Currently I have had to manual insert this value '12-07' to equal the value of the
$_GET['date'];

I want the value '12-07' to be a variable obtained from the $Date variable.
Otherwise I will to constantly edit my PHP for the current "dates"

Please note that most of this PHP was created by Dreamweaver CS4 and not handwritten by myself.
I have limited knowledge of PHP hence why I am having problems solving this.

I will greatly appreciate your assistance.

PHP & HTML

<?php require_once('Connections/schedule.php'); ?>
<?php require_once('Connections/main.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;
}
}

mysql_select_db($database_schedule, $schedule);
$query_Day1 = "SELECT * FROM schedule WHERE id = -1";
$Day1 = mysql_query($query_Day1, $schedule) or die(mysql_error());
$row_Day1 = mysql_fetch_assoc($Day1);
$totalRows_Day1 = mysql_num_rows($Day1);

mysql_select_db($database_schedule, $schedule);
$query_Day2 = "SELECT * FROM schedule WHERE id = -2";
$Day2 = mysql_query($query_Day2, $schedule) or die(mysql_error());
$row_Day2 = mysql_fetch_assoc($Day2);
$totalRows_Day2 = mysql_num_rows($Day2);

mysql_select_db($database_schedule, $schedule);
$query_Day3 = "SELECT * FROM schedule WHERE id = -3";
$Day3 = mysql_query($query_Day3, $schedule) or die(mysql_error());
$row_Day3 = mysql_fetch_assoc($Day3);
$totalRows_Day3 = mysql_num_rows($Day3);

mysql_select_db($database_schedule, $schedule);
$query_Day4 = "SELECT * FROM schedule WHERE id = - 4";
$Day4 = mysql_query($query_Day4, $schedule) or die(mysql_error());
$row_Day4 = mysql_fetch_assoc($Day4);
$totalRows_Day4 = mysql_num_rows($Day4);

mysql_select_db($database_schedule, $schedule);
$query_Day5 = "SELECT * FROM schedule WHERE id = -5";
$Day5 = mysql_query($query_Day5, $schedule) or die(mysql_error());
$row_Day5 = mysql_fetch_assoc($Day5);
$totalRows_Day5 = mysql_num_rows($Day5);

mysql_select_db($database_schedule, $schedule);
$query_Day6 = "SELECT * FROM schedule WHERE id = -6";
$Day6 = mysql_query($query_Day6, $schedule) or die(mysql_error());
$row_Day6 = mysql_fetch_assoc($Day6);
$totalRows_Day6 = mysql_num_rows($Day6);

mysql_select_db($database_schedule, $schedule);
$query_Day7 = "SELECT * FROM schedule WHERE id = -7";
$Day7 = mysql_query($query_Day7, $schedule) or die(mysql_error());
$row_Day7 = mysql_fetch_assoc($Day7);
$totalRows_Day7 = mysql_num_rows($Day7);

$coldate_schedule = date('d-m', strtotime('yesterday'));
if (isset($_GET['date'])) {
  $coldate_schedule = $_GET['date'];
}
mysql_select_db($database_main, $main);
$query_schedule = sprintf("SELECT * FROM main WHERE channel = 'One' AND date = %s AND time IN (19, 20, 21, 22, 23, 00, 01, 02, 03, 04, 05)", GetSQLValueString($coldate_schedule, "int"));
$schedule = mysql_query($query_schedule, $main) or die(mysql_error());
$row_schedule = mysql_fetch_assoc($schedule);
$totalRows_schedule = mysql_num_rows($schedule);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-GB" lang="en-GB"><head profile="http://dublincore.org/documents/dcq-html/">
<link rel="stylesheet" type="text/css" href="http://localhost//files/stylesheet.css" />
<?php $thisPage=$_GET['date']; ?>
</a></li>
</ul>
</a><br /> <div id="navigation">
  <ul>
    <li<?php if ($thisPage=='12-07') 
      echo " id=\"currentpage\""; ?>>
      <a href="<?php echo $row_Day1['date']; ?>	"><?php echo $row_Day1['day']; ?></a></li>
    <li<?php if ($thisPage=="11-07") 
      echo " id=\"currentpage\""; ?>>
      <a href="<?php echo $row_Day2['date']; ?>"><?php echo $row_Day2['day']; ?></a></li>
    <li<?php if ($thisPage=="10-07") 
      echo " id=\"currentpage\""; ?>>
      <a href="<?php echo $row_Day3['date']; ?>"><?php echo $row_Day3['day']; ?></a></li>
    <li<?php if ($thisPage=="09-07") 
      echo " id=\"currentpage\""; ?>>
      <a href="<?php echo $row_Day4['date']; ?>"><?php echo $row_Day4['day']; ?></a></li>
    <li<?php if ($thisPage=="08-07") 
      echo " id=\"currentpage\""; ?>>
      <a href="<?php echo $row_Day5['date']; ?>"><?php echo $row_Day5['day']; ?></a></li>
    <li<?php if ($thisPage=="07-07") 
      echo " id=\"currentpage\""; ?>>
      <a href="<?php echo $row_Day6['date']; ?>"><?php echo $row_Day6['day']; ?></a></li>
    <li<?php if ($thisPage=="06-07") 
      echo " id=\"currentpage\""; ?>>
      <a href="<?php echo $row_Day7['date']; ?>"><?php echo $row_Day7['day']; ?></a></li>
  </ul>
</body></html>
<?php
mysql_free_result($Day1);

mysql_free_result($Day2);

mysql_free_result($Day3);

mysql_free_result($Day4);

mysql_free_result($Day5);

mysql_free_result($Day6);

mysql_free_result($Day7);

mysql_free_result($schedule);
?>

CSS

#navigation ul {
	list-style: none;
	margin: 0;
	padding: 0;
	}

#navigation li {
	background: #ccc;
	border-left: 1px solid #999;
	float: left;
	margin: 0;
	padding: 0;
	}

#navigation a {
	color: #666;
	font-weight: bold;
	padding: 5px 10px;
	text-decoration: none;
	}

#navigation a:hover {
	color: #333;
	}

#navigation #currentpage a {
	background: #fff;
	color: #333;
	}

[bradgrafelman]

Ouch... that code is in need of some serious optimization/reworking! :o

First off... why do you have hard-coded (and negative?) id values in your SQL queries? This 'id' column sounds like a good candidate for an AUTO_INCREMENT field, meaning that each new entry receives an incremented id from the last row in the table.

Doing that would lead into my next point. There's no reason to have seven separate queries in seven separate variables just to obtain seven consecutive rows from the same table. Instead, you should use a single query and use a "LIMIT" clause to retrieve 7 rows (or less, in case there aren't 7 rows in the table) all in one query.

Doing that would lead into my next point. As you're looping through the result sets, you can output the necessary HTML code for each date rather than repeating the same HTML code in your script 7 times over. This would also allow you to get rid of the hard-coded dates in the if() statements, because then you could do something like:

echo "<li" . ($row['date'] == $thisPage ? ' id="currentpage"' : '') . "> etc.";

[jamjam]

Hi

Thanks for your quick response bradgrafelman.

There are several reason as to why I have set up the script this way.
A combination of limited knowledge of PHP and also some application requirements.

For example my complete "Schedule" table looks like this

+6 Mon 19th ?date=19-07
+5 Sun 18th ?date=18-07
+4 Sat 17th ?date=17-07
+3 Fri 16th ?date=16-07
+2 Thu 15th ?date=15-07
+1 Tomorrow ?date=14-07
0 Today ?date=13-07
-1 Yesterday ?date=12-07
-2 Sun 11th ?date=11-07
-3 Sat 10th ?date=10-07
-4 Fri 9th ?date=09-07
-5 Thu 8th ?date=08-07
-6 Wed 7th ?date=07-07
-7 Tue 6th ?date=06-07

As you have noticed the last 7 rows are negative. This why SQL is also negative.
The negative id represents days past in the "TV Listings" concept.

There is another page which uses the other 7 positive id's .

As you suggest I could adapt the entire script but before I embark on this major surgery I wondered if there easy way to remove the hard coded value in the if statement and replacing it with a $Date variable?

If not that I will go head to change the script entirely although this may take sometime.

Thanks again.

[bradgrafelman]

Sounds like you designed your table structure around the fact that id=0 is today. Why do that, though? Personally, I would probably just create a DATE column type to store the date of the row and make that the PRIMARY key (since that is the primary way you identify a unique set of data in this table, right?).

Thus, if you wanted to get the next seven days' schedule, you'd do a query like:

SELECT col1, col2 FROM schedules WHERE dateCol >= CURDATE() LIMIT 7

Similarly, for the previous seven days, you'd just do a less-than comparison (possibly using an ORDER BY clause to flip the order if desired).

jamjam wrote:

I wondered if there easy way to remove the hard coded value in the if statement and replacing it with a $Date variable?

What $Date variable are you talking about?

[jamjam]

What $Date variable are you talking about?

So I have 3 columns in my table

1. id
2. Day
3. Date

By $Date variable I am referring to 3rd column.

Current I have to hard-code the value "Date" into the if statement.

I wanted to somehow replace this with "$Date variable".

I am properly using wrong terminology hence the confusion.

[bradgrafelman]

I'm really starting to wonder if you shouldn't consider simplifying/optimizing your DB design. For example, the Day and Date columns seem superfluous, because if you know the date is 2010-07-13 then you already know that it's a Tuesday; there's no reason to store both in the database.

Furthermore, is the 'id' column referenced (e.g. like a foreign key) in other tables? Even if it isn't, do you have multiple schedules per day? If not, then it sounds like the date itself could be a unique key (as I mentioned previously).

All of this is assuming, of course, that you aren't actually just trying to create a range of dates (which you definitely don't need a database for :p).

[jamjam]

Its important to to have both the Day and Date columns as they are used for different purposes.

The Day column is used for display, so that users can click on it.
The Date column is added as a link to acts as the query string.

Using the Date column for display will be ugly to a user as something like 12-07 is not user friendly as Mon 12th.
Although i have just realized I could use the Day column as the query string thus eliminating the date column all together.

This table does not use foreign key at all and has no multiple schedule per day.

I think you right, A complete redesign is necessary.

[bradgrafelman]

jamjam wrote:

Its important to to have both the Day and Date columns as they are used for different purposes.

The Day column is used for display, so that users can click on it.
The Date column is added as a link to acts as the query string.

Your entire argument seems to be based on the fact that you can't take a date (a properly formatted date, mind you, e.g. 2010-07-13) and get "Tuesday" out of it. This is definitely false - both MySQL and PHP have built-in functions that can do that.

Speaking of the date issue, you really should be using a DATE column in the YYYY-MM-DD format. Again, you can easily manipulate it using built-in functions to display it however you'd like, but dates like "12-07" don't really mean anything. Is that December 7th? July 12th? The month of December in 2007? Something else?

[jamjam]

OK

I have taken all that you have said onboard and will rework everything.

Will post back soon, properly to ask more questions.

Cheers.

[jamjam]

Hi

I have made some progress. I took into PHP's built in date functions as you suggested.
I can't quite believe it but I have managed to do away with the datatbase all together.

I am now able to generate the links which contain the query string and also echo out the dates ideally formated using PHP alone.

However I still have not resolved my original problem.

Which was to highlight the navigation when clicking these links.

I have created a separate thread to get more assistance on this matter.

Thanks for your help.