1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Another little PHP script problem (but a bit more complicated)

Discussion in 'PHP' started by jacobbannier, Oct 25, 2008.

  1. #1
    Hi,
    As a test I am creating a little jobs database which allows users to input + search for jobs in my database. Currently it is all fine apart from the line:
    	$sql= mysql_query("SELECT * FROM jobs WHERE Company like '%$company_term%' or Hours like '$hours_term' or Job like '$type_term' or Salary like '$salary_term'") or die("MySQL Search Error: " . mysql_error());
    PHP:
    It doesn't work and only outputs every value in the database.

    Heres the full script:
    <html>
    
    <head>
    	<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
    	<meta name="author" content="ANDY1" />
    
    	<title>Untitled 1</title>
    </head>
    
    <body>
    
    <table>
    <form method=POST action='search.php?search1=yes'>
    <tr><td>Company Name:</td><td><input type=text name='company_term' ></input></td></tr>
    <tr><td>Job Type:</td><td><input type=text name='type_term' ></td></tr>
    <tr><td>Salary (Per Month)</td><td><input type=text name='salary_term'></td></tr>
    <tr><td>Hours (Per Week)</td><td><input type=text name='hours_term'></td></tr>
    <tr><td><input type=submit value="Look for Your New Jobs!"></td></tr>
    </form>
    
    </table>
    
    <?php
    
    $company_term=$_POST['company_term'];
    $type_term=$_POST['type_term'];
    $salary_term=$_POST['salary_term'];
    $hours_term=$_POST['hours_term'];
    $search1=$_GET['search1'];
    
    if ($search1 == yes) {
    	
    	mysql_connect ('localhost','root','') or die(mysql_error());
    	mysql_select_db ('jobs_database') or die (mysql_error('No Database Selected'));
    	
    	echo 'Connected To Database <br />We have found the following jobs for you: <br/> <br/>';
    	
    	$sql= mysql_query("SELECT * FROM jobs WHERE Company like '%$company_term%' or Hours like '$hours_term' or Job like '$type_term' or Salary like '$salary_term'") or die("MySQL Search Error: " . mysql_error());
    	
    	         while ($row = mysql_fetch_array($sql)){    
         echo 'Job ID: '.$row['ID'];   
       echo '<br/> Company Name: '.$row['Company'];  
         echo '<br/> Job Type: '.$row['Job'];  
         echo '<br/> Salary: '.$row['Salary'];
    	 echo '<br/> Hours: '.$row['Hours'];  
         echo '<br/><br/>'; 
    	 
    	 
    }
    	
    	
    }
    
    
    
    
    ?>
    
    
    </body>
    </html>
    PHP:
    Assume database is ok and the input system works fine. All comment appreciated!

    Thanks.
     
    jacobbannier, Oct 25, 2008 IP
  2. P2Host

    P2Host Guest

    Messages:
    217
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Hmm sorry i cant help but i do need a job so send me link when done lol
     
    P2Host, Oct 25, 2008 IP
  3. Kyosys

    Kyosys Peon

    Messages:
    226
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    0
    #3
    1) there are sql injections possible in the script

    use mysql_real_escape_string

    2)
    $sql= mysql_query("SELECT * FROM jobs WHERE Company like '%$company_term%' or Hours like '$hours_term' or Job like '$type_term' or Salary like '$salary_term'") or die("MySQL Search Error: " . mysql_error());

    that means it will find anything that contains the company term, or the hours term, or the type term or the salary term, if the company term is left empty, it will find everything. Maybe you meant to put ands in there instead of ors?
     
    Kyosys, Oct 25, 2008 IP
  4. jacobbannier

    jacobbannier Active Member

    Messages:
    1,155
    Likes Received:
    30
    Best Answers:
    0
    Trophy Points:
    90
    #4
    In response to 2):

    I want the user to be able to type into 4 boxes which say:
    So if they just enter the type of job they want, e.g Programmer, it will search the 'Job' field of my database and return any results where the row Job = Programmer. If they search for the Salary it will search the salary row of my database etc.

    I hope i explained that correctly :)

    In response to P2Host:

    Its just a tester to learn PHP, but there are already many job searching sites out there :)
     
    jacobbannier, Oct 25, 2008 IP
  5. EricBruggema

    EricBruggema Well-Known Member

    Messages:
    1,740
    Likes Received:
    28
    Best Answers:
    13
    Trophy Points:
    175
    #5
    mysql_fetch_assoc instead of array? hope that works :)
     
    EricBruggema, Oct 25, 2008 IP
  6. jacobbannier

    jacobbannier Active Member

    Messages:
    1,155
    Likes Received:
    30
    Best Answers:
    0
    Trophy Points:
    90
    #6
    No i'm afraid not :(

    Still outputs all data when I put a value into the Job Type, Salary or Hours box :(
     
    jacobbannier, Oct 25, 2008 IP
  7. Kyosys

    Kyosys Peon

    Messages:
    226
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Then what you need to do is have 4 different queries. Maybe have a little radio choosing what they want to search with
     
    Kyosys, Oct 25, 2008 IP
  8. ads2help

    ads2help Peon

    Messages:
    2,142
    Likes Received:
    67
    Best Answers:
    1
    Trophy Points:
    0
    #8
    They are the same.
    mysql_fetch_assoc return associative array.
    mysql_fetch_row return numeric array
    mysql_fetch_array return both associative and numeric array, thus slower
     
    ads2help, Oct 25, 2008 IP
  9. Sillysoft

    Sillysoft Active Member

    Messages:
    177
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    58
    #9
    Still to early in the morning but here is some code I came up with. Now you need to add in your stuff, this just concentrates on the query building part. the query assumes you have an id field in your Jobs table:

    
    $search1 = trim($_GET['search1']);
    
    //If form is submitted we run the search query
    if($search1 == 'yes')
    {
    
    //Clean your variables always! I just used trim here but strip variables
    //of any html etc etc to prevent cross site scripting
    $company_term = trim($_POST['company_term']);
    $type_term = trim($_POST['type_term']);
    $salary_term = trim($_POST['salary_term']);
    $hours_term = trim($_POST['hours_term']);
    
    //Make sure you connect to db before using mysql_real_escape_string
    $db_company = mysql_real_escape_string($company_term);
    $db_type = mysql_real_escape_string($type_term);
    $db_salary = mysql_real_escape_string($salary_term);
    $db_hours = mysql_real_escape_string($hours_term);
    
    //We build the search query based on the variables given from the search form
    $search_company = (!empty($company_term)) ? " OR company LIKE '%$db_company%'" : '';
    $search_type = (!empty($type_term)) ? " OR Job LIKE '%$db_type%'" : '';
    $search_salary = (!empty($salary_term)) ? " OR Salary LIKE '%$db_salary%'" : '';
    $search_hours = (!empty($hours_term)) ? " OR Hours LIKE '%$db_hours%'" : '';
    
    //If any of the search queries is used we then add it to the query
    if(!empty($search_company) || !empty($search_type) || !empty($search_salart) || !empty($search_hours))
    {
    	
    	$query_search = "AND ($search_company $search_type $search_salary $search_hours)";
    	
    }
    
    //Here we put the query together and do a little search replace to make the query
    //a correct sql query
    $sql= "SELECT * FROM jobs WHERE id > 0 $query_search";
    $sql = preg_replace('#\([\s]+OR#i','(',$sql);
    
    }
    
    PHP:
     
    Sillysoft, Oct 25, 2008 IP
    jacobbannier likes this.
  10. jacobbannier

    jacobbannier Active Member

    Messages:
    1,155
    Likes Received:
    30
    Best Answers:
    0
    Trophy Points:
    90
    #10
    Thanks for your great input (green rep), I am yet to try it as I have been busy. I did play around with it a little but nothing major yet.
    Just to let you know there is one table, with 4 rows, not 4 tables :)

    Thanks!
     
    jacobbannier, Oct 27, 2008 IP