Lots of Questions

PHP
1

Edit:One more issue

First off, I really appreciate the help so far and any future help. This is my first time really working with php/mysql and it’s going great so far. I have a some questions so I thought it’d be easier just to make one big thread.

Here’s the code:

<?php 

		include("connect.php"); 

		$query    = "SELECT * FROM specials ORDER BY specialsprice"; 
		$result = mysql_query($query); 
		$num    = mysql_num_rows ($result); 
		mysql_close(); 

		$i = 0; 

		if ($num > 0) 
		{ 
			echo '<table class="table1">';
			 echo '<thead>'; 
			     echo '<tr>'; 
			     echo "<td><a href='food.php'<b>Food:</b></a></td>"; 
			     echo "<td><a href='drink.php'><b>Drink:</b></a></td>"; 
			     echo "<td><a href='location.php'><b>Location:</b></a></td>"; 
			     echo "<td><a href='time.php'><b>Time:</b></a></td>"; 
			     echo "</tr>";
				 echo '</thead>';

			    while ($i < $num) 
			    { 
			        $food    = mysql_result($result,$i,"food"); 
			        $drink     = mysql_result($result,$i,"drink"); 
			        $location    = mysql_result($result,$i,"location"); 
			        $time    = mysql_result($result,$i,"time"); 
			        $id            = mysql_result($result,$i,"id"); 

			        echo '<tr>'; 
			        echo "<td>$food</td>"; 
			        echo "<td>$drink</td>"; 
			        echo "<td>$location</td>"; 
			        echo "<td>$time</td>";  
			        echo "</tr>"; 

			        ++$i; 
			    } 

			    echo '</table>';
		} 
		else 
		echo "The database is empty"; 

		?>

So, I’d displaying a table that has 4 columns : food, drink, location, and time. You can click on the column head to sort the data based on that column.

  1. I currently just have 4 separate .php pages with the same code that just differ in the SELECT for the ORDER BY. If you click on “Drink,” then the drink.php page is loaded with the data sorted based on the drink column. Is this the best way to accomplish this?

  2. The “Time” column. I would really like for that data to be kind of a countdown until a specified time. So id=“2” has the time of 5:00PM Today. It is currently 3:00PM Today. I want the data that is shown to be 2 hours left in the “Time” column. And when you ORDER BY for the “Time” column, it will go from 5 minutes left to 5 hours left.

These might be more related to the Mysql database, not sure

  1. The table currently shows all of the information in the database table. I’d like to make this data be relevant based on the day.
    -You assign a date to each id when added to the table. If the date is “today”, then the id is displayed in the table. The date is not shown.
    -If the date has passed, then the id is removed from the database table (just wasting space).
    -If the date is in the future, then the data is displayed when that date is “today” or when the user chooses to view the data from that date.
    -Most of the id’s will actually be reoccurring. Is there a way to have the data be displayed for every Monday when Monday is “today”? Instead of having to input this data for every Monday until the end of time.
    -I would like for the user to be able to see into the future for a few days. The option to view the data for “today,” “tomorrow,” and maybe “the day after tomorrow.”

I’m thinking that I can either have to input this data daily and remove the ones that are not relevant daily or figure out how to make the ^^above stuff work. The latter sounds much better.

4.There is another way of sorting the information. For the “Food” column we are drawing information from the “Food” field. I would like to assign a food type for each id that is created. So let’s say “hot” and “cold”. There are buttons for the user to click that say “hot” or “cold”. When the user clicks on “hot,” the only data that populates the table is “hot” food. This means Drinks, Location, and Time are still included. How can I treat this in the database? Should I make another field that is “foodtype” and then input “hot” or “cold” when adding an id? And when creating the form you fill out to add id’s to the table, should/how can I use checkboxes or a dropdown for foodtype?

Honestly, any help is appreciated. Thanks!

2

First off, I would suggest that rather than using mysql_result to access the columns (which is mainly used to retrieve a single cell), you should use the mysql_fetch_* functions to loop through the result set.

i.e:

while ($row = mysql_fetch_assoc($result))
{
   $food = $row['food'];
   $drink = $row['drink'];
   etc...
}

As for the first question, basically what c2uk said. If the code on every page is almost identical, then the best way to do it would be to pass the variable in the url that indicates what page to view, and alter the select query based on that.

Something like the following would suffice:

switch ($_GET['page'])
{
   case 'drink':
      $order = 'drink';
      break;
   case 'food':
      $order = 'food';
      break;
   default:
      $order = 'specialsprice';
      break;
}

$query = 'SELECT * FROM specials ORDER BY ' . $order;
3

Honestly?

Try again, just ask one question, and do a bit of work yourself - isolate the code that encapsulates that one single problem.

Divide and conquer, eh?

(I’ve lost count of the times the above has lead to me finding the answer to the complicated question I wanted to ask)

4

First off, sorry for not addressing your php issues, but your html could be better:


            echo '<table class="table1">';
             echo '<thead>'; 
                 echo '<tr>'; 
                 echo "<th><a href='food.php'>Food:</a></th>"; 
                 echo "<th><a href='drink.php'>Drink:</a></th>"; 
                 echo "<th><a href='location.php'>Location:</a></th>"; 
                 echo "<th><a href='time.php'>Time:</a></th>"; 
                 echo "</tr>";
                 echo '</thead>';
  • The food line had a missing > just after food.php’
  • th is the proper way to declare table cells in the table header. then you wouldn’t need <b> to “emphasise”. Also, <b> only serves a visual purpose, it doesn’t actually emphasise anything, that’d be <strong>.
  1. I currently just have 4 separate .php pages with the same code that just differ in the SELECT for the ORDER BY. If you click on “Drink,” then the drink.php page is loaded with the data sorted based on the drink column. Is this the best way to accomplish this?

My php skills are limited, but I would pass on variables via URL and then use a switch to add the respective order by to the mysql query

5

That’s exactly what I’ve been doing all day. It never hurts to ask.