Passing Form Data to SELECT

[OldGuysCodeToo]

Maybe there's a much easier way to solve this problem, and if there is, please tell me.

 

I didn't know a lot about PHP and MySQL,  but recently managed to put together a database for my business, using an old script I found.  Everything's going well.  So far.

 

Each of my database tables stores my daily travel activity, and is named after that current day (ex: 04_15_09, 04_16_09, etc).  What I'm trying to do now is build a form where I can simply enter any past date, and see all my activity for that date.  This is where the problem comes in.

 

Here's my form, so far:

 

<form action="archived_data.php" method="get">
       <table width="300" border="1" cellpadding="4" cellspacing="0">
    <tr>
<td>Month:</td>
<td><input type="text" size="5" name="month"></td>
    </tr>
    <tr>
<td>Day:</td>
<td><input type="text" size="5" name="day"></td>
    </tr>
    <tr>
<td>Year:</td>
<td><input type="text" size="5" name="year"></td>
    </tr>
    <tr>
<td colspan="2"><input type="submit" class="submit_button" value="Get Data"></td>
    </tr>
        </table>
<form>

 

Once that form gets processed, I need to somehow pass the resulting data to the "SELECT" line, which will identify the appropriate table.  How can I do this?  Below is an example.

$result=mysql_query("SELECT id,pass,pickup,stop_1,stop_2,stop_3,dropoff,amt_coll FROM [b]04_16_09[/b] ORDER BY id;");

[revraz]

If you store the date properly, as a DATE field in the DB, then it will be much easier for you to query it.

[Bizty]

I suppose you dont have a new table for each data-input but instead multiple rows. So the query would look like this:

 

$result=mysql_query("SELECT id,pass,pickup,stop_1,stop_2,stop_3,dropoff,amt_coll FROM yourTable WHERE date='$date' ORDER BY id;");

 

Would probably look something like this:

function WriteTravel($month, $day, $year)
{
$month = $_GET['month'];
$day = $_GET['day'];
$year = $_GET['year'];

if(empty($month) or empty($day) or empty($year))
{
	print "Error: month, day or year wasn't entered.";
	die;
}

$lookup_date = $month ."_". $day ."_". $year; 
$result = mysql_query("SELECT * FROM yourTable WHERE date='$lookup_date'");		
}

[OldGuysCodeToo]

Bitzy, I didn't understand your reply.  You mentioned two different queries in your reply, so I'm not sure which one to use.  I did try each one, along with different variations, but kept getting errors.

 

As far as having a new table for each data input, you are correct. I don't, but I do  have a new table for each day.  Below is a sample dump:

CREATE TABLE IF NOT EXISTS `04_18_09` (
  `pass` int(1) DEFAULT NULL,
  `pickup` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `stop_1` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `stop_2` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `stop_3` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `dropoff` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `amt_coll` varchar(10) COLLATE latin1_general_ci DEFAULT NULL,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=144 ;

 

I suppose you dont have a new table for each data-input but instead multiple rows. So the query would look like this:

 

$result=mysql_query("SELECT id,pass,pickup,stop_1,stop_2,stop_3,dropoff,amt_coll FROM yourTable WHERE date='$date' ORDER BY id;");

 

Would probably look something like this:

function WriteTravel($month, $day, $year)
{
$month = $_GET['month'];
$day = $_GET['day'];
$year = $_GET['year'];

if(empty($month) or empty($day) or empty($year))
{
	print "Error: month, day or year wasn't entered.";
	die;
}

$lookup_date = $month ."_". $day ."_". $year; 
$result = mysql_query("SELECT * FROM yourTable WHERE date='$lookup_date'");		
}

[Bizty]

Is it important that you have a new table for each day?

 

Else you could just have a database like this:

 

//The table name is just what I could think of, add your own.
CREATE TABLE IF NOT EXISTS `Trips` (
  `pass` int(1) DEFAULT NULL,
  `pickup` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `stop_1` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `stop_2` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `stop_3` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `dropoff` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `amt_coll` varchar(10) COLLATE latin1_general_ci DEFAULT NULL,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=144 ;

 

Then each time you have a new entry:

mysql_query("INSERT INTO Trips (pass, pickup, stop_1, stop_2, stop_3, dropoff, amt_coll) VALUES ('$pass', '$pickup', '$stop_1', '$stop_2', '$stop_3', '$dropoff', '$amt_coll')"); 

 

So instead of making a new table, you put the data into a new row. Now you can acces it like I posted above. If you need to make a new table each time (for whatever reason) I guess you could do it like this:

 

//The function requires that your html-form has month, day, and year filled.
function WriteTravel($month, $day, $year)
{
   $month = $_GET['month']; //storing the month in the variable $month
   $day = $_GET['day']; //storing the day in the variable $day
   $year = $_GET['year']; //storing the year in the variable $year
   
   //if any of the above variables is empty, we return an error. (so we dont get a lookup like 06__09 because the day was left out)
   if(empty($month) or empty($day) or empty($year))
   {
      print "Error: month, day or year wasn't entered.";
      die;
   }
   
   //Gather up the month day and year to create the table name, stored in $lookup_date
   $lookup_date = $month ."_". $day ."_". $year;
    
   //SELECT ALL (*) FROM $lookup_date for example: SELECT * FROM 05_05_2009
   $result = mysql_query("SELECT * FROM $lookup_date");  

   //Create an array of the table data for the given date.
   $row = mysql_fetch_array($result);    
}

 

Now instead of selecting a row from yourTable it selects the table based on what date you input. Now you can print out the data in the table with $row['pass'], $row['stop_1'], $row['amt_coll'] etc.

 

A little tired so hope I didnt make any mistakes, if you cant get this to work I'll try and have a better look and try it out on my own server - hope it helps

 

If there's any specific parts you dont understand let me know and I'll try and explain them better.