Query using a OR ?

[thefollower]

I have a query when i need to use AND's along side some OR's on different fields ... but It's not working.. this is the query line i did :

 

$GetChancellor = mysql_query("SELECT * FROM governmentparties
                    WHERE Treasurer='$UserName' OR PartyLeader='$UserName' AND Prime='1' And CountryID='1'")
			or die(mysql_error());

 

When i did a get the row it would not find a row ... there was no error however.

 

I have this for $UserName = User2.

The current result is in the database, the "Treasurer" is User1,

PartyLeader = User2.

 

[pocobueno1388]

Try this

 

SELECT * FROM governmentparties
WHERE (Treasurer='$UserName' OR PartyLeader='$UserName') AND (Prime='1' And CountryID='1')

[thefollower]

Nah it doesnt find my $row when i checked.

This is what i got now:

$GetChancellor = mysql_query("SELECT * FROM governmentparties
WHERE (Treasurer='$UserName' OR PartyLeader='$UserName') AND (Prime='1' And CountryID='1')")
			or die(mysql_error());

 

Is that correct?

 

[toplay]

Yes, that looks alright. Display the actual query (with values populated). Take that and run the query outside of PHP to see if it works.

 

FYI: It's usually inefficient mixing OR's with AND's and it can slow queries down quiet a bit. I suggest this alternative:

 

SELECT

          *

  FROM

          governmentparties

 

WHERE  1

          AND (Treasurer = '$UserName')

          AND (Prime = '1')

          AND (CountryID = '1')

UNION

SELECT

          *

  FROM

          governmentparties

 

WHERE  1

          AND (PartyLeader = '$UserName')

          AND (Prime = '1')

          AND (CountryID = '1')

;

 

[thefollower]

Is that the correct syntax or you just written it like that for me to understand ? Ive not seen queries written like that before

[toplay]

Is that the correct syntax or you just written it like that for me to understand ? Ive not seen queries written like that before

 

Yes, that's the correct syntax.

 

Example:

<?php

// Assign $UserName something

$strQueryFmt = '
            SELECT 
                       * 
              FROM 
                       `governmentparties`
            
            WHERE      1
                       AND `Treasurer` = "%1$s"
                       AND `Prime` = "%2$s"
                       AND `CountryID` = "%3$s"
            UNION
            SELECT 
                       * 
              FROM 
                       `governmentparties`
            
            WHERE      1
                       AND `PartyLeader` = "%1$s"
                       AND `Prime` = "%2$s"
                       AND `CountryID` = "%3$s"
        ';

$strQuery = sprintf(  $strQueryFmt
                    , mysql_real_escape_string($UserName)
                    , 1      // Prime value
                    , 1      // CountryID value
                   );

$objResult = mysql_query($strQuery);

if ($objResult) {
    
    /**
     * If you know you're only getting back one row (or limit the query to 
     * return just one row), then you don't need the while loop.
     */
    while ($objRow = mysql_fetch_object($objResult)) {

        // Process data. Just an example of displaying two column values:
        echo 'Treasurer: ', $objRow->Treasurer, ' PartyLeader: ', $objRow->PartyLeader;
    }
  
    @mysql_free_result($objResult);
    
}

?>

[thefollower]

$strQuery = sprintf(  $strQueryFmt
                    , mysql_real_escape_string($UserName)
                    , 1      // Prime value
                    , 1      // CountryID value
                   );

$objResult = mysql_query($strQuery);

 

what is this part doing =/

[toplay]

It replaces %1$s, %2$s, %2$s with their corresponding values of $UserName, 1, 1. Then it executes the query.

 

See manual page:

http://us3.php.net/manual/en/function.sprintf.php

 

[thefollower]

why cant AND `Treasurer` = "%1$s"

 

just be AND `Treasurer` = "$Username"

like i usually do ?

[fenway]

why cant AND `Treasurer` = "%1$s"

 

just be AND `Treasurer` = "$Username"

like i usually do ?

Smells like a prepared statement, but not quite there yet.

[thefollower]

i am so lost lol.. i been learning php/mysql for over a year and never come across mysql like this before.. because i've always just put the $Username straight in .. or what ever the variable is that i need..

[toplay]

why cant AND `Treasurer` = "%1$s"

 

just be AND `Treasurer` = "$Username"

like i usually do ?

You can. FYI - Any variable value obtained from user (like from a web form), especially a string, should be escaped first. I like using the sprintf() function because you can do calculations or call functions before a value is assigned. If you're going to use $Username in double quotes, then I recommend that you escape it before using it in the query.

 

http://us3.php.net/manual/en/function.mysql-real-escape-string.php

http://us3.php.net/manual/en/function.addslashes.php

 

i am so lost lol.. i been learning php/mysql for over a year and never come across mysql like this before.. because i've always just put the $Username straight in .. or what ever the variable is that i need..

No need to feel lost. Just do it any way you want, like or used to.

 

The main thing I was trying to show is an example of using UNION instead of having to use OR's.

 

hth.