Help with te results from a query

[rob7676]

I am new to php and mysql so I don't understand too much. I maybe asking the question wrong.

TODO:
A user logs into the website - tries to perform a search for reports made by there company - then the results are displayed. In the results I need it to only show the results of their company accourding to there login info. I am using multiple tables (call_in, drivers, users, and company) The call_in table is where the report is stored. Below is the code I have.

<?php require_once('Connections/search.php'); ?>
<?php
error_reporting(E_ALL);
		
		//Connect to DB
		
		function doconnect()
		{
		  $dbhost = 'localhost';
		  $dbuser = 'username';
  		  $dbpass = 'password'; // 
          
		
		  $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to MySQL');
		
		  $dbname = 'db_name';
		  mysql_select_db($dbname, $conn) or die ('Error finding database');
		
		  return $conn;
		}
		
		echo "<form method=\"get\" action=\"search_reports.php\">";
		echo "<p style=\"text-align:center;\">Type in your three digit Company ID and click \"Search\" to view your reports.</p>";
		echo "<p style=\"text-align:center;\"><input type=\"text\" name=\"filename\"  size=\"30\" /></p>";
		echo "<p style=\"text-align:center;\"><input type=\"submit\" name=\"search\" value=\"Search\" /></p>";
		echo "</form>";
		
		$conn=doconnect();
		

		
		if (isset($_GET['search']))
		{
		  if (isset($_GET['filename']))
		  {
		  
			$query = "SELECT * FROM call_in WHERE company_id LIKE '%" . $_GET['filename'] . "%' OR date LIKE '%" . $_GET['filename'] . "%'";
			
			$result = mysql_query($query) or die(mysql_error());
			$numrows = mysql_num_rows($result);
			
			if (strlen($_GET['filename'])>0)
			{
			  echo 'Your search has returned ' . $numrows . ' records.';
			  echo "<table style=\"width:850px;\"><tr><th style=\"text-align:left;\">Time</th><th style=\"text-align:left;\">Date</th><th style=\"text-align:left;\">Company</th><th style=\"text-align:left;\">Vehicle ID</th><th style=\"text-align:left;\">Stop Number</th><th style=\"text-align:left;\">Driver</th><th style=\"text-align:left;\">Cargo 1.1</th><th style=\"text-align:left;\">Cargo 1.5</th><th style=\"text-align:left;\">Cargo 1.6</th><th style=\"text-align:left;\">Cargo Other</th></tr>\n";
			  			  
			  while ($row = mysql_fetch_array($result))
			  {
				echo '<tr><td>' . $row['time'] . '</td><td>' . $row['date'] . '</td><td>' . $row['company_id'] . '</td><td>' . $row['vehicle_id'] . '</td><td>' . $row['stop_no'] . '</td><td>' . $row['driver_id'] . '</td><td>' . $row['cargo_11'] . '</td><td>' . $row['cargo_15'] . '</td><td>' . $row['cargo_16'] . '</td><td>' . $row['cargo_other'] . '</td></tr>';
			  }
			  
			  echo '</table>';	  
			}
			
			else
			{
			  echo '<p style="font-size:16px;font-weight:bold;color:#ff0000;">Sorry, no records were found.</p>';
			}
		  }
		
		  else
		  {
			echo "<p style=\"font-size:16px;font-weight:bold;color:#ff0000;\">Please type in a name.</p>";
		  }
		}
		
		
		

mysql_free_result($company_id);
?>

Please help for I am running out of time.

[php_daemon]

Something along the lines:

$query = "
SELECT call_in.* 
  FROM call_in 
  JOIN users
    ON users.company_id=call_in.company_id
WHERE date LIKE '%$filename%'";

And of course:

$filename = mysql_real_escape_string($_GET['filename']); 

mysql_real_escape_string
http://en.wikipedia.org/wiki/SQL_injection

[rob7676]

That didn't do anything. Any other thoughts?

[php_daemon]

Did you adapt the code? "Something along the lines" means that because there was not enough information and/or I was too lazy, I did not write down the exact code. If you did, show the latest code.

[rob7676]

Maybe I put it in wrong. But here is what happens. I didnt notice before, but it seems to be only showing the results according to the company id, but it doubles the record (ex. the db has row 1-3 and it showing it 1-3 twice)

<?php require_once('Connections/search.php'); ?>
<?php
error_reporting(E_ALL);

//Connect to DB

function doconnect()
{
$dbhost = 'localhost';
$dbuser = 'user';
$dbpass = 'password'; 


$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to MySQL');

$dbname = 'trucker';
mysql_select_db($dbname, $conn) or die ('Error finding database');

return $conn;
}

echo "<form method=\"get\" action=\"search_reports.php\">";
echo "<p style=\"text-align:center;\">Type in your three digit Company ID and click \"Search\" to view your reports.</p>";
echo "<p style=\"text-align:center;\"><input type=\"text\" name=\"filename\" size=\"30\" /></p>";
echo "<p style=\"text-align:center;\"><input type=\"submit\" name=\"search\" value=\"Search\" /></p>";
echo "</form>";

$conn=doconnect();



if (isset($_GET['search']))
{
if (isset($_GET['filename']))
{
$filename = mysql_real_escape_string($_GET['filename']); 

$query = "
SELECT call_in.*
  FROM call_in
  JOIN users
    ON users.company_id=call_in.company_id
WHERE date LIKE '%$filename%'";

$result = mysql_query($query) or die(mysql_error());
$numrows = mysql_num_rows($result);

if (strlen($_GET['filename'])>0)
{
echo 'Your search has returned ' . $numrows . ' records.';
echo "<table style=\"width:850px;\"><tr><th style=\"text-align:left;\">Time</th><th style=\"text-align:left;\">Date</th><th style=\"text-align:left;\">Company</th><th style=\"text-align:left;\">Vehicle ID</th><th style=\"text-align:left;\">Stop Number</th><th style=\"text-align:left;\">Driver</th><th style=\"text-align:left;\">Cargo 1.1</th><th style=\"text-align:left;\">Cargo 1.5</th><th style=\"text-align:left;\">Cargo 1.6</th><th style=\"text-align:left;\">Cargo Other</th></tr>\n";

while ($row = mysql_fetch_array($result))
{
echo '<tr><td>' . $row['time'] . '</td><td>' . $row['date'] . '</td><td>' . $row['company_id'] . '</td><td>' . $row['vehicle_id'] . '</td><td>' . $row['stop_no'] . '</td><td>' . $row['driver_id'] . '</td><td>' . $row['cargo_11'] . '</td><td>' . $row['cargo_15'] . '</td><td>' . $row['cargo_16'] . '</td><td>' . $row['cargo_other'] . '</td></tr>';
}

echo '</table>';
}

else
{
echo '<p style="font-size:16px;font-weight:bold;color:#ff0000;">Sorry, no records were found.</p>';
}
}

else
{
echo "<p style=\"font-size:16px;font-weight:bold;color:#ff0000;\">Please type in a name.</p>";
}
}




mysql_free_result($company_id);
?>

[php_daemon]

Not following. Sample data, please, full rows from the tables and what the query gives, and what it should give.

[rob7676]

when the logged in user searches it returns the call_in table (time,date,company_id,vehicle_id,stop_no,driver_id,cargo_11,cargo_15,cargo_16,cargo_other)
- in the call in table i have sample data:
1102 7302008 159 1234567 111 33333333333 1 2 2 2
1327 7302008 159 1234567 111 33333333333 2 2 2 2
1608 7312008 159 7654321 111 33333333333 2 2 2 2

but when the search is performed with the code you suggested it returns the above rows but doubled.

sorry, not too sure what i'm doing so dont really know how to ask or trouble shoot. i appreciate you taking the time to look at all this.

[rob7676]

what should happen is
a user logs in - then they go to the search page where they perform a search for reports made by their company ( search by date ) - when the results return, they need to only return the reports of their company according to there company id (which is also on the users table)

[php_daemon]

Where does the username of the logged in user come from? You need to add the username criterion to the WHERE clause (to show only records related to the current user, obviously).