Mysql php complex query

Question

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)
        ");

just try to echo $query and check what it the result? I have reached you problem. just tell me immediately. — Code Lღver
– Code Lღver, Commented Mar 17, 2012 at 9:12
What does "Not working" mean exactly? Are you getting errors, or are you not getting any results? — Pekka
– Pekka, Commented Mar 17, 2012 at 9:12

ThiefMaster · Accepted Answer · 2012-03-17 09:13:51Z

3

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

answered Mar 17, 2012 at 9:13

ThiefMaster

320k85 gold badges608 silver badges648 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vyktor Over a year ago

It seems that's not entirely true for mySQL ||,OR is apparently logical operator.

ThiefMaster Over a year ago

It can be configured: dev.mysql.com/doc/refman/5.1/en/…

Nick · Accepted Answer · 2012-03-17 09:31:00Z

2

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();
}

answered Mar 17, 2012 at 9:31

Nick

6,04512 gold badges56 silver badges81 bronze badges

1 Comment

Nick Over a year ago

A pleasure, sir :) (Or ma'am - can't be sure!)

liquorvicar · Accepted Answer · 2012-03-17 09:40:20Z

2

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

answered Mar 17, 2012 at 9:40

liquorvicar

6,1462 gold badges19 silver badges21 bronze badges

10 Comments

Nick Over a year ago

I concur with the liquorvicar - there has to be a better way to organise the DB :)

BlackMouse Over a year ago

sounds much better... I have about 60 rows in the "active_matches" structure... 8 rows for each players... I don't quite get how the above code works.. Is there any good documentation or tutorial where I can learn about how to create a good schema? THanks

liquorvicar Over a year ago

@user1251004 Well, database design is a huge topic: en.wikipedia.org/wiki/Database_design I'm not sure I can recommend anything as I've learnt by experience, by osmosis.

Nick Over a year ago

If it was me, I would follow the liquor vicar's example and assign a unique match id and player id to the match and player record. I wouldn't have a matches_players table, though. When a player joins a game, I'd update fields in the match and player records. So... Match has: id, p1, p2, p3, p4, p5, p6. Player has: id, status, m_num, p_num (match number and player number). If player #179 joins match 32 as the 3rd player, you find player id 179 and set status to 1, m_num to 32 and p_num to 3. You also find match 32, and set p3 to 179. When the player quits, you unset everything/set status to 6.

liquorvicar Over a year ago

@Nick en.wikipedia.org/wiki/Database_normalisation would be a good starting point for further reading. This isn't really "my" wisdom, it's fairly basic database design theory...

|

Code Lღver · Accepted Answer · 2012-03-17 09:15:40Z

0

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.

answered Mar 17, 2012 at 9:15

Code Lღver

15.6k16 gold badges61 silver badges75 bronze badges

4 Comments

Milap Over a year ago

I dont think so , it will make any difference.

jmishra Over a year ago

That doesn't make any change...magic quotes should only be used in case of array variables like '".$row['variable']."' or '".$_SESSION['variable']."'

Code Lღver Over a year ago

it makes sense guys. I think you don't know that $var will be print as same as it is within a single quote and that's why the php cann't recognise that is a variable or a string.

nikc.org Over a year ago

@GauravVashishtha That is only true if the string is enclosed in single quotes. The OP's query is enclosed in double quotes (inside which variables are evaluated), it merely contains single quotes. Example: codepad.org/8QsTfUPF

Collectives™ on Stack Overflow

Mysql php complex query

4 Answers 4

2 Comments

1 Comment

10 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

10 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related