How to Query on multiple tables using data from HTML form submit?

Question

prerit -1 Newbie Poster

16 Years Ago

I have a HTML form Having two fields From: and To: both are text fields(to genereate a report).I want to use these data to query on two tables based on the date specified(DD-MM-YYYY) i.e from and to and using these to select values from the two tables visitor_in and visitor_out each having same no. of fields except visitor_in has Time_In and other visitor_out has Time_Out.
Here's my code:

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("entry", $con);  //entry is the database


$from=$_POST['from']; //values from
$to=$_POST['to'];     //text fields of HTML form      

// Construct our join query
$result = mysql_query("SELECT visitor_in.Name,visitor_in.Contact_Number,visitor_in.Date,visitor_in.Time_In,visitor_out.Time_Out FROM visitor_in,visitor_out WHERE visitor_in.Date BETWEEN '$from' AND '$to' ") or die(mysql_error());
 
//Printing as a HTML table
echo "<table border='1'>
<tr>
<th>Name</th>
<th>Contact Number</th>
<th>Date</th>
<th>Time In</th>
<th>Time Out</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['visitor_in.Name'] . "</td>";
  echo "<td>" . $row['visitor_in.Contact_Number'] . "</td>";
  echo "<td>" . $row['visitor_in.Date'] . "</td>";
  echo "<td>" . $row['visitor_in.Time_In'] . "</td>";
  echo "<td>" . $row['visitor_out.Time_Out'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

After I input form field with date(DD-MM-YYYY) no result is yielded only the table headings Name contact number date time in time out appear but not the rows to be selected. Im using WAMP server.

mysql php

4 Contributors
10 Replies
196 Views
3 Days Discussion Span
Latest Post 16 Years Ago Latest Post by Stefano Mtangoo

Dani AI

Generated 6 Months Ago

Three likely causes are visible in the thread: your form values never arrive in PHP (the “Undefined index” notices), the date column is stored as text in DD-MM-YYYY so a lexicographic BETWEEN will fail, and the query has no proper join or usable column aliases so PHP can’t read the returned fields. ’s advice to echo the inputs and ’s to show the DB error are good debugging steps; ’s hint to guard against undefined indexes is also important.

Quick, practical checklist to get results

Confirm the HTML form uses method="post" and the input name attributes exactly match from and to. Add a quick debug to see what was submitted (for example var_dump($_POST)), and wrap reads in isset() so you don’t get notices.
Parse the DD‑MM‑YYYY user input into a canonical date before querying. Using PHP’s DateTime prevents format surprises and produces YYYY‑MM‑DD for SQL.
Use an explicit JOIN with a real key (visitor_id). If you have no id, join on stable columns (e.g., Name + Date) but be aware of duplicate-name pitfalls.
Alias selected columns so PHP array keys are simple (avoid trying to read $row['visitor_in.Name']).

Example (safe flow using modern PDO — replaces the old mysql_* approach):

// validate & convert form dates
$d1 = DateTime::createFromFormat('d-m-Y', trim($_POST['from'] ?? ''));
$d2 = DateTime::createFromFormat('d-m-Y', trim($_POST['to'] ?? ''));
$from = $d1 ? $d1->format('Y-m-d') : null;
$to   = $d2 ? $d2->format('Y-m-d') : null;

// use prepared statement and an explicit join
$sql = "
  SELECT i.Name AS name, i.Contact_Number AS contact, i.Time_In AS time_in,
         o.Time_Out AS time_out
  FROM visitor_in i
  LEFT JOIN visitor_out o ON i.visitor_id = o.visitor_id
  WHERE STR_TO_DATE(i.Date, '%d-%m-%Y') BETWEEN :from AND :to
";

If you can change the schema (recommended): back up, run an UPDATE to convert stored DD‑MM‑YYYY strings to DATE with STR_TO_DATE, then ALTER TABLE to change the column to DATE. That makes future BETWEEN work naturally and simplifies queries. Finally, always escape output (htmlspecialchars) and use prepared statements to avoid injection and debugging headaches.

All 10 Replies

Stefano Mtangoo 455 Senior Poster

16 Years Ago

Have you displayed dates to see if Form dates format matches the Database stored dates?

Stefano Mtangoo 455 Senior Poster

16 Years Ago

try echo the query and pase what it displays
also you can try this

$result = mysql_query("SELECT visitor_in.Name,visitor_in.Contact_Number,visitor_in.Date,visitor_in.Time_In,visitor_out.Time_Out FROM visitor_in,visitor_out WHERE visitor_in.Date BETWEEN '".$from."'AND '".$to."' ") or die(mysql_error());"

Member #334542

16 Years Ago

you can solve the undefined error by using the below code

http://en.kioskea.net/faq/3140-php-notice-undefined-index

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

phpstan 0 Newbie Poster · Answer 1 · 2010-08-16T07:51:58+00:00

Rather than using "die()" if your mysql_query fails, try printing the error message.
http://www.php.net/manual/en/function.mysql-error.php will show you how.

prerit -1 Newbie Poster · Answer 2 · 2010-08-16T13:25:56+00:00

Have you displayed dates to see if Form dates format matches the Database stored dates?

Yes I always Make sure to input date from the user In the format (DD-MM-YYYY) i always write it next to the input field.

I have the Date field in both the table as TEXTfield for compatibility with the input field instead of Date type could that be a problem?

prerit -1 Newbie Poster · Answer 3 · 2010-08-16T15:44:25+00:00

try echo the query and pase what it displays
also you can try this

$result = mysql_query("SELECT visitor_in.Name,visitor_in.Contact_Number,visitor_in.Date,visitor_in.Time_In,visitor_out.Time_Out FROM visitor_in,visitor_out WHERE visitor_in.Date BETWEEN '".$from."'AND '".$to."' ") or die(mysql_error());"

I tirwd what you wrote but gave me following error:

Notice: Undefined index: from in C:\wamp\www\vm\report.php on line 11

Notice: Undefined index: to in C:\wamp\www\vm\report.php on line 12

prerit -1 Newbie Poster · Answer 4 · 2010-08-16T15:55:30+00:00

Rather than using "die()" if your mysql_query fails, try printing the error message.
http://www.php.net/manual/en/function.mysql-error.php will show you how.

Sorry but that didnt help i already have error checking in my PHP file

Stefano Mtangoo 455 Senior Poster · Answer 5 · 2010-08-16T17:55:49+00:00

what are contents of lines 11 and 12?
Can you echo the values of $from and $to?

prerit -1 Newbie Poster · Answer 6 · 2010-08-18T12:56:51+00:00

what are contents of lines 11 and 12?
Can you echo the values of $from and $to?

Sorry that didnt help!!1 Can you tell general method take any example ofyours explaining the use of form variables and then using that data to query on two table simultaneusly.
Maybe it will help.

Stefano Mtangoo 455 Senior Poster · Answer 7 · 2010-08-18T18:04:40+00:00

Test this in your app
index.html

<!--
To change this template, choose Tools | Templates
and open the template in the editor.
-->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
    <head>
        <title></title>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    </head>
    <body>
        <form name="myform" action="test.php" method="POST">
              <p>Time In<input name="in_time" type="text" /></p>
              <p>Time Out<input name="out_time" type="text" /></p>
              <input name="Submit" type="submit" />
        </form>
</body>
</html>

test.php

<?php

$in = $_POST['in_time'];
$out = $_POST['out_time'];
echo "Out Time is $out<br />";
echo "You Got in at $in";

?>

How to Query on multiple tables using data from HTML form submit?

Dani AI

Recommended Answers Collapse Answers

All 10 Replies

Recommended Answers