SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    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);
    ?>
    Please help for I am running out of time.

  2. #2
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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']); 
    mysql_real_escape_string
    http://en.wikipedia.org/wiki/SQL_injection
    Saul

  3. #3
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That didn't do anything. Any other thoughts?

  4. #4
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SitePoint Member
    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);
    ?>

  6. #6
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Not following. Sample data, please, full rows from the tables and what the query gives, and what it should give.
    Saul

  7. #7
    SitePoint Member
    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.

  8. #8
    SitePoint Member
    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)

  9. #9
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •