SitePoint Sponsor |
|
User Tag List
Results 1 to 9 of 9
-
Jul 28, 2008, 12:43 #1
- Join Date
- Jul 2008
- Posts
- 18
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Help with te results from a query
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.
Code:<?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); ?>
-
Jul 28, 2008, 15:03 #2
Something along the lines:
Code php:$query = " SELECT call_in.* FROM call_in JOIN users ON users.company_id=call_in.company_id WHERE date LIKE '%$filename%'";
And of course:
PHP Code:$filename = mysql_real_escape_string($_GET['filename']);
http://en.wikipedia.org/wiki/SQL_injectionSaul
-
Jul 31, 2008, 12:21 #3
- Join Date
- Jul 2008
- Posts
- 18
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
That didn't do anything. Any other thoughts?
-
Jul 31, 2008, 12:37 #4
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.
Saul
-
Jul 31, 2008, 13:29 #5
- Join Date
- Jul 2008
- Posts
- 18
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 Code:<?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);
?>
-
Jul 31, 2008, 13:44 #6
Not following. Sample data, please, full rows from the tables and what the query gives, and what it should give.
Saul
-
Jul 31, 2008, 13:58 #7
- Join Date
- Jul 2008
- Posts
- 18
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 31, 2008, 14:02 #8
- Join Date
- Jul 2008
- Posts
- 18
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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)
-
Jul 31, 2008, 15:07 #9
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).
Saul
Bookmarks