Please help with PHP search

[rob7676]

I am new to this forum and hope someone can help. I am also new to using PHP and MySQL (seem to be picking up on MySQL better)
Bare with me, there are alot of things going on with what I need done and I'm not sure how to word it all. But here it goes.

I build login and restriction scripts using dreamweaver, but dreamweaver can't help me know. I need a couple scripts or 1 which ever is best. the script I need needs to search a database for reports made and display the results (simple enough even for a noob like me) but it also needs to limit the results according to the logged in user. Each user has a company id number and the results can only show the reports made by their company(this is where I'm lost). The user will search by date. Also, in the result is a column called driver_id, I need that to return the driver name from a different table(some kind of join but I can't get it to work).

If there is anyone that can do this or help me do this, please let me know.

[cameraman]

Welcome to WebmasterWorld, rob7676!

This forum has a good library [webmasterworld.com]; have a look at the threads on Developing MySQL search query, Commonly used MySQL Commands, and Basics of extracting data from MySQL.
Once you get something going, if it's not working the way you want, post the relevant code snippet and we can help you refine it.

[rob7676]

I have a search that works some what, just doesn't join or limit the results according to the users company id.

<?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 = 'db';
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 = '%" . $_SESSION['company_id'] . "%' OR date LIKE '%" . $_GET['filename'] . "%' ORDER BY `date` DESC";
$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);
?>

[cameraman]

On your form, you have them typing their company id into a field named filename. But here,
$query = "SELECT * FROM call_in WHERE company_id = '%" . $_SESSION['company_id'] . "%' OR date LIKE '%" . $_GET['filename'] . "%' ORDER BY `date` DESC";

you're using the company id to compare to the date. I believe you want something more like:
$query = "SELECT * FROM call_in WHERE company_id = '" . $_GET['filename'] . "' ORDER BY `date` DESC";

Also, you should never, ever trust data that comes from a user. If it is indeed three numeric digits for the company id, change your if line:
if(isset($_GET['filename']) && preg_match('#\d{3}#',$_GET['filename'])) {

The second part checks that the entry is exactly 3 numeric digits.

[rob7676]

thanks