CODEWITHSUNDEEP
phpmysqlsqljoinquery-optimization
user1022585
user1022585

Reputation: 13651

SQL table join?

I have these two SQL queries at the top of every page on my site:

$sql_result = mysql_query("SELECT * FROM accounts WHERE id='$user_ID'", $db); 
$rs = mysql_fetch_array($sql_result);

$level  = $rs['level'];
$name   = $rs['name'];  

$sql_result2 = mysql_query("SELECT * FROM members WHERE name='$name'", $db);
$rs2 = mysql_fetch_array($sql_result2); 

if (mysql_num_rows($sql_result2) == 0) {
  header("Location: dead.php");
}

// get data

First off it checks the accounts table to see if the player has an account. If he does, it checks if he has a player from members. If there isn't a record found, he's been killed.

Is this ok as it is? Or could i speed up things by making it one query?

I'm not sure how to go about it..

Upvotes: 0

Views: 135

Answers (2)

VictorSwords
VictorSwords

Reputation: 49

If you're having trouble with join you can use "SELECT . FROM WHERE . = .". Your code would probably look something like "SELECT accounts., members. FROM accounts, members WHERE accounts.memberID = members.memberID". Of course, I'm assuming your members table has an id column.

Upvotes: 0

Jordan Running
Jordan Running

Reputation: 106067

Yes, this should be one query. A JOIN is pretty straightforward here:

SELECT members.*, accounts.level, accounts.name FROM members
  JOIN accounts ON members.name = accounts.name
 WHERE accounts.id = '$user_ID';

Make sure accounts.name is indexed, however. An alternative would be to add an account_id column to members and join on that instead since presumably accounts.id is already indexed.

Upvotes: 2

Related Questions