CODEWITHSUNDEEP
phpmysql
BlackMouse
BlackMouse

Reputation: 4552

Mysql php complex query

I'm trying to make a query, to find out if a player is in a match, and if his status isn't quit (6). The query below isn't working though. What am I doing wrong?

Thanks

$query = mysql_query("SELECT * FROM matches WHERE 
        (player1Name = '$name' AND player1Status != 6) || 
        (player2Name = '$name' AND player2Status != 6) || 
        (player3Name = '$name' AND player3Status != 6) ||
        (player4Name = '$name' AND player4Status != 6) ||
        (player5Name = '$name' AND player5Status != 6) ||
        (player6Name = '$name' AND player6Status != 6)
        ");

Upvotes: 0

Views: 236

Answers (4)

liquorvicar
liquorvicar

Reputation: 6106

I would suggest to change the structure of your schema. If you ever find yourself creating columns like player1Name,player1Status, player2Name, player2Status, player3Name, there's almost certainly something wrong with your schema. You could try something like this

matches
-------
match_id (int)
...other match info columns

players
-------
player_id (int)
player_name (varchar)

matches_players
---------------
match_id
player_id
status

Your query could then easily become

SELECT *
FROM matches_players mp
    INNER JOIN players p ON mp.player_id=p.player_id
WHERE p.name='$name'
    AND mp.status != 6

Upvotes: 2

Nick
Nick

Reputation: 6025

Both OR and || are legit as it says here.You could try PDO format as recommended here:

The DB connection is like this (with your db details, of course):

$dsn = "mysql:host=127.0.0.1;dbname=reportslave1";
$username = "root";
$password = "";
try {
    $DBH = new PDO($dsn, $username, $password);
}
catch(PDOException $e) {
    echo $e->getMessage();
}

The prep work like this:

$STH = $DBH->prepare("SELECT * FROM matches WHERE 
      (player1Name = :name AND player1Status != 6) OR
      (player2Name = :name AND player2Status != 6) OR
      (player3Name = :name AND player3Status != 6) OR
      (player4Name = :name AND player4Status != 6) OR
      (player5Name = :name AND player5Status != 6) OR
      (player6Name = :name AND player6Status != 6) 
    ");

$STH->bindParam(":name",$name);

The call is then made as follows.

try {
  $STH->execute();
  $STH->setFetchMode(PDO::FETCH_ASSOC);
}
catch(PDOException $e){
  echo $e->getMessage();
}

Upvotes: 2

Code Lღver
Code Lღver

Reputation: 15603

Got it try below code:

$query = mysql_query("SELECT * FROM matches WHERE 
        (player1Name = '".$name."' AND player1Status != 6) OR 
        (player2Name = '".$name."' AND player2Status != 6) OR 
        (player3Name = '".$name."' AND player3Status != 6) OR
        (player4Name = '".$name."' AND player4Status != 6) OR
        (player5Name = '".$name."' AND player5Status != 6) OR
        (player6Name = '".$name."' AND player6Status != 6)
        ");

This will work.

Upvotes: 0

ThiefMaster
ThiefMaster

Reputation: 318478

In SQL, || is the concatenation operator (like . in PHP). Use OR instead.

Upvotes: 3

Related Questions