Query AND OR

[siabanie]

Hi all,

 

Can anyone help me out.. I tried to run this query but it does not seem correct in particular the OR AND part. As the search dept (AND) did not give me any results when I run the query.

 

Anyone has idea what is the correct way to do it?

 

$query = ("SELECT `firstname`, `surname`, `deptname` FROM profile JOIN dept ON profile.dept_id = dept.id  WHERE surname LIKE '{$_POST['searchname']}' OR firstname LIKE '{$_POST['searchname']}' AND dept.id LIKE '{$_POST['dept']}'") or die (mysql_error());

 

 

I tried to put brackets e.g

$query = ("SELECT `firstname`, `surname`, `deptname` FROM profile JOIN dept ON profile.dept_id = dept.id  WHERE (surname LIKE '{$_POST['searchname']}' OR firstname LIKE '{$_POST['searchname']}') AND dept.id LIKE '{$_POST['dept']}'") or die (mysql_error());

....but it still did not work.

 

Thanks.

[PFMaBiSmAd]

Are you actually executing the query using mysql_query() and why do you have an or die() on the end of your $query = "..." string assignment statement?

[monkeytooth]

Worse, why are you using a raw $_POST var in the query.. thats just dangerous

[siabanie]

PFMaBiSmAd what are you suggesting? even if I removed the or die() I still not able to do the dept search.

 

monkeytooth, I am very new on PHP so any suggestion would be much appreciated.

 

Thanks.

[monkeytooth]

http://www.tizag.com/mysqlTutorial/ very nice site for beginners.. I learned on it some years ago.

 

$query =  mysql_query("SELECT firstname, surname, deptname FROM profile JOIN dept ON profile.dept_id = dept.id  WHERE surname LIKE '".$_POST['searchname']."' OR firstname LIKE '".$_POST['searchname']."' AND dept.id LIKE '".$_POST['dept']."'") or die(mysql_error());

 

try something to that effect as your query string, also..

[monkeytooth]

As for advice as to what I am implying about the raw $_POST/$_GET factor. The only thing I can say is brush up on SQL injection attacks. Im not really good at verbalizing things like that. But basics to it is. If there is any place on your site that allows user input from uploading a file to typing some text, you want to check, recheck, triple check, sanitize, check again, and make sure the user based input falls within any constraints you would want to come from that users input. Even on something like a "select" box, or radio button value. If you dont then expect travisty eventually. The interweb is not to be trusted, you will eventually have your site crawled by a malious bot, or person, and they will run a game on your site making it their bitch and your the one to suffer as of the result.

 

Improperly handled inputs can lead to someone injecting not only your Database but your server as well. Maybe I am an extremist in my thoughts about how to handle any data I am not plugging in myself that comes from my users. But Ill be damned if any of my sites have been hacked to date from anything I did

[siabanie]

Thanks monkeytooth,

 

I will have a look the website you suggested.

 

I have tried the query you suggested but when I search the dept and clicked search button - no results appear.  Any reason why?

 

Thanks for the advice about SQL injection attacks - I still not every good and aware about this and how to prevent them. But hopefully I will learn how to avoid this attacks.

 

Thanks again.

 

EDIT: This is what I did;

 

$query =  ("SELECT firstname, surname, deptname FROM profile JOIN dept ON profile.dept_id = dept.id  WHERE surname LIKE '".$_POST['searchname']."' OR firstname LIKE '".$_POST['searchname']."' AND dept.id LIKE '".$_POST['dept']."'") or die(mysql_error());
$result = mysql_query($query);

 

but it does not give me any results for dept search...

[monkeytooth]

The "LIKE" is most likely to be your problem.  Usually you want to add a wildcard to your statement on those. Example

 

WHERE surname LIKE '%".$_POST['searchname']."%'

 

what thats doing is telling the query to find anything like what you typed in but lets say your data in the database has a something outside of exactly what your typing a space, an extra letter, whatever.. putting the % on either side says search for "this" find something even if "this" looks like "Mr. this something"

[siabanie]

I placed my wildcard here:

 

$do_search = false;
if (!empty($_POST) ) {
	if ( $_POST['submit'] == 'Search' ) {
		$do_search = true;

		if ( $_POST['dept'] == 'All' ) {

			$_POST['dept'] = '%';
		}

		if (empty( $_POST['searchname']) ) {
			$_POST['searchname'] = '%';
		}
		else {
			$_POST['searchname'] = $_POST['searchname'];
		}
	}
}

 

and my HTML form is:

..

..

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

<div style="text-align:right;">

<input type="submit" name="submit" value="Search" />

<label for="surname">Surname / First Name :</label>

<input id="surname" name="searchname" type="text" size="20" maxlength="25" />

<label for="dept">Dept:</label>

<select id="dept" name="dept">

<option value="All">All</option>

...

...

 

 

EDIT: Question.

 

Do I need to match the textbox name with the PHP $_POST['whatever textbox name is here'] ? or does it needs to be matched in DB fields?

[siabanie]

Question.

 

Do I need to match the textbox name with the PHP $_POST['whatever textbox name is here'] ? or does it needs to be matched in DB fields?

 

The reason I asked is because if I changed this:

 

if (empty( $_POST['searchname']) ) {
$_POST['searchname'] = '%';
}
else {
$_POST['searchname'] = $_POST['searchname'];
}

 

into [name]

if (empty( $_POST['searchname']) ) {
$_POST['name'] = '%';
}
else {
$_POST['name'] = $_POST['name'];
}

 

It seems that.........its still running..? please explain..thanks.

[Muddy_Funster]

yeas, if you want it to actualy validate the input from the form you will need to use the field names from the form.  What you are doing is telling the program that

IF 'variable name' IS NOT assigned
THEN BEGIN
assign 'variable name' TO 'fixed value'
END IF
ELSE BEGIN
assign 'variable name' TO ITS SELF
END ELSE 

As such, it won't matter what you use here, it will assign what you tell it to the variable that you tell it.  This is regardless of if that variable has any relation to the data entered or not.

 

You really shouldn't be manualy assigning $_POST[] variables anyway.  you should be using local variables for everything that you are assiging through the code (i.e. everything to the left of a single = sign)

[siabanie]

Thanks guy,

 

I've sorted it out  - Just questions I have this log in page called index.php - but at the moment I can only add one admin.

 

How can I add if there are more than one admin? Using level '1' and '0'?

 

Here is my log in code:

 

index.php

<?php
    session_start();
    $error_msg = "";
    if (isset($_POST['submit'])) {
    
    $username = (isset($_POST['username'])) ? $_POST['username'] : '';
    $password = (isset($_POST['password'])) ? $_POST['password'] : '';
    
    if($username && $password) {
    
        $connect = mysql_connect("localhost", "root", "") or die ("Couldn't connect!");
        mysql_select_db("persons") or die ("Couldn't find the DB");

        $query = mysql_query ("SELECT * FROM `users` WHERE username = '$username'");
        
        $numrows = mysql_num_rows($query);
	//echo $numrows;
	//If $numrows != 0 there is a records in DB, then do the following
        if ($numrows != 0)
	{
        
            while ($row = mysql_fetch_array($query))
            {
          	$dbusername = $row['username'];
                $dbpassword = $row['password'];
            }
            
            //Check to see if they are match!
                if ($username == $dbusername && md5($password) == $dbpassword)
                {
                    $_SESSION['username'] = $username;
                    $_SESSION['is_admin'] = false;
                    
                    if ( $username == 'admin' ) 
				{
                        $_SESSION['is_admin'] = true;
                    }
                    
                    header("Location: firstpage.php");
                }
                else    
                    $error_msg = "Incorrect password!";
            //code of login
            
        }else
        $error_msg = "That user does not exist!";
    }
    else
    $error_msg = "Please enter a username and password!";
}
?>

<html>
<head><title>Login Page</title>
</head>

<body>
<br />
<?php
	require "header.php";
?><br />
<br />

<div align="center">
<table width="200" border="1">

<?php
        // If $error_msg not equal to emtpy then display error message
        if($error_msg!="") echo "<div id=\"error_message\"style=\"color:red; \">$error_msg</div><br />";
?>
        
<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<!--form action="login_a.php" method="post"-->
    Username: <input type="text" name="username" /><br /><br />
    Password: <input type="password" name="password"  /><br /><br />
    
    <input type="submit" name = "submit" value="Log in"  />
</form> <p> </p>

</table>
</div>
</body>
</html>

 

Any help would be highly appreciated.

Thanks.