mysql question...

Portal Home > Knowledgebase > Articles Database > mysql question...

Posted by XeonG, 01-03-2013, 09:50 PM
So I have mysql db with 2 tables 1 table "player" id | player name and the other table "playerdata" player_id | data | etc How can I make a single sql query that takes a specified playername and looks it up in the first table, and then uses that players 'id' in the 2nd table "playerdata" Pretty new to mysql and php(somewhat anyway) I can do it by making 2 queries, but I was wondering if it isn't just possible to make mysql do the extra working in finding that players id and then using it.
Posted by ubservers, 01-03-2013, 09:59 PM
You have to do a cross query. Usually something like: "GET data FROM playerdata as A, player as B WHERE player_id.A = id.B"
Posted by XeonG, 01-03-2013, 10:08 PM
Hmm where would that go... here is how my php code looks with 2 queries the first to search for the playername, and get the 'id' of that player. And the second query where I get the 'player_id' from the first... currently just put in a number to test with. $sqlplayername = "SELECT * FROM `players` WHERE player = '$username'"; $sqlInitialIP = "SELECT `data_id`, `date`, `player_id`, `action`, `data` FROM `logger` WHERE player_id='5' AND action='5' ORDER BY `logger`.`date` ASC LIMIT 0 , 1";
Posted by foobic, 01-03-2013, 10:53 PM
Consider using a prepared statement too, instead of just joining strings to make your query. You may think it's too hard now but if you persevere, you'll be glad you did...
Posted by XeonG, 01-04-2013, 12:57 AM
ah that helps thanks, got it working. yeh I heard prepared statements and PDO ... and arf it just scares me away
Posted by valley, 01-04-2013, 12:58 AM
The following query will do the trick:
Posted by MattF, 01-04-2013, 02:41 AM
It scared WHMCS developers too... So they just went on the string banging route, can't say its caused them any problems
Posted by dlucian, 02-01-2013, 09:01 AM
You can also use LEFT JOIN for that. SELECT * FROM player LEFT JOIN player_data ON player.id = player_data.player_id You get all the information from those two tables, joined together. Only be careful on the fact that queryies that include several tables can be really slow in some cases, such as when the tables are large or they contain clauses that aren't matching a key, such as the primary key.
Posted by creativeartist, 02-01-2013, 01:19 PM
You can use different tyoes of JOINS with tables here... If you are sure that the second table has a row with every row id in first table go for normal JOIN. If the records are not mandatory, use LEFT JOIN.