MySQL and PHP - using 2 tables in one query? |
Author: |
Message: |
Jimbo
Veteran Member
Posts: 1650 Reputation: 18
32 / /
Joined: Jul 2006
|
O.P. MySQL and PHP - using 2 tables in one query?
Okay, first of all, I am not even sure if this is possible, but what I am trying to do is:
I have two tables, Table A that stores just SteamID, with some other fields, and then, another Table B that stores Player name, SteamID and then some other fields.
What I currently have setup is some PHP to query Table A with an entered SteamID, to output all the other fields.
What I am looking to do, is allow someone to enter a name, this then finds the SteamID corresponding to that name in Table B, then searches Table A using this steamid.
I use the following PHP to query Table A at the moment:
PHP code:
<?php echo '<table class="sortable2" id="reports" name="reports">
<tr>
<th><font face="Arial, Helvetica, sans-serif" size="3">SteamID</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">VIP</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Upgrade 1</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Upgrade 2</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Upgrade 3</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Upgrade 4</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Upgrade Points</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Zombie Upgrade Points</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Bonus Upgrade Points</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Player Model</font></th>
</tr>';
?>
<?
mysql_connect("censored","censored","censored");
mysql_select_db("servers");
$search=$_POST["search"];
$result = mysql_query("SELECT * FROM upgrades WHERE steamid LIKE '%$search%'");
while($r=mysql_fetch_array($result))
{
$steam=$r["steamid"];
$dt=$r["vip"];
$pn=$r["playerupgrade1"];
$rs=$r["playerupgrade2"];
$rp=$r["playerupgrade3"];
$se=$r["playerupgrade4"];
$sns=$r["upgradepoints"];
$snr=$r["zombieupgradepoints"];
$snp=$r["bonusupgradepoints"];
$sn=$r["playermodel"];
echo "<tr><td>$steam</td><td>$dt</td><td>$pn</td><td>$rs</td><td>$rp</td><td>$se</td><td>$sns</td><td>$snr</td><td>$snp</td></tr>";
}
?>
</table>
With the following html form:
HTML code:
<form method="post" action="search.php">
<input type="text" value="Enter SteamID" name="search" size=25 maxlength=25>
<input type="Submit" name="Search" value="Search">
</form>
This post was edited on 01-17-2010 at 01:38 AM by Jimbo.
|
|
01-17-2010 01:32 AM |
|
|
MeEtc
Patchou's look-alike
In the Shadow Gallery once again
Posts: 2200 Reputation: 60
38 / /
Joined: Nov 2004
Status: Away
|
RE: MySQL and PHP - using 2 tables in one query?
To me, it sounds like you have 2 tables that are in a 1 to 1 relation to each other. One record on table A is only relevant to one record on table B. Why not just merge the 2 tables?
Also, do some research on SQL injection. Using just that code above, what do you think would happen if in the HTML field someone writes in:
code: ';DROP TABLE 'upgrades
As for just a straight answer for what you are asking,
SQL code:
SELECT `a.fields`, `b.fields`
FROM tableA a INNER JOIN tableB b ON `a.steamid` = `b.steamid`
WHERE `steamid` LIKE '%$search%'
One other quick thing, I wouldn't recommend using SELECT * unless you really do want everything. It also doesn't describe the statement a whole lot and just looking at it, you can't tell what info will actually be returned without looking at the DB structure.
This post was edited on 01-17-2010 at 03:08 AM by MeEtc.
I cannot hear you. There is a banana in my ear.
|
|
01-17-2010 03:05 AM |
|
|
Jimbo
Veteran Member
Posts: 1650 Reputation: 18
32 / /
Joined: Jul 2006
|
O.P. RE: MySQL and PHP - using 2 tables in one query?
Thanks for informing me of SQL injection. Researched and implemented a few methods now, which should prevent this.
About that query, I really do want to get everything from table A, but only steamid and playername from table B. I then want to be able to search via player name, and return all values from Table A, that relate to this player name's steamid. I was struggling with going about doing that using your query, however, its probably just me being stupid.
|
|
01-17-2010 01:51 PM |
|
|
stoshrocket
Senior Member
formerly methos
Posts: 748 Reputation: 31
34 / /
Joined: Aug 2005
|
RE: MySQL and PHP - using 2 tables in one query?
Is there any particular reason for using two separate tables? It just all sounds like trying to work around something that isn't needed. Why not just merge the steam id's, names and details into one table? It would make things a lot easier than having to access one table for an id then another for the details...
|
|
01-17-2010 04:41 PM |
|
|
Jimbo
Veteran Member
Posts: 1650 Reputation: 18
32 / /
Joined: Jul 2006
|
O.P. RE: MySQL and PHP - using 2 tables in one query?
Well, merging the two tables is kind of out of the question now, since it has been this way for months, and to redesign all the game plugins that retrieve and store info in these tables would be far too tedious.
|
|
01-17-2010 04:48 PM |
|
|
stoshrocket
Senior Member
formerly methos
Posts: 748 Reputation: 31
34 / /
Joined: Aug 2005
|
RE: MySQL and PHP - using 2 tables in one query?
quote: Originally posted by Jimbo
Well, merging the two tables is kind of out of the question now, since it has been this way for months, and to redesign all the game plugins that retrieve and store info in these tables would be far too tedious.
Ah. Then the only way I could see it would be to use 2 separate queries One to reteive the id, then query the next table for the results. Although, I'm not saying it isn't possible, but quite frankly I don't understand MeEtc's solution
|
|
01-17-2010 05:23 PM |
|
|
Jimbo
Veteran Member
Posts: 1650 Reputation: 18
32 / /
Joined: Jul 2006
|
O.P. RE: MySQL and PHP - using 2 tables in one query?
Well 2 queries wouldnt be a problem, in fact, it would probably be easier.
|
|
01-17-2010 05:33 PM |
|
|
Adeptus
Senior Member
Posts: 732 Reputation: 40
Joined: Oct 2005
|
RE: MySQL and PHP - using 2 tables in one query?
quote: Originally posted by Jimbo
I have two tables, Table A that stores just SteamID, with some other fields, and then, another Table B that stores Player name, SteamID and then some other fields.
What I currently have setup is some PHP to query Table A with an entered SteamID, to output all the other fields.
What I am looking to do, is allow someone to enter a name, this then finds the SteamID corresponding to that name in Table B, then searches Table A using this steamid.
quote: Originally posted by Jimbo
About that query, I really do want to get everything from table A, but only steamid and playername from table B. I then want to be able to search via player name, and return all values from Table A, that relate to this player name's steamid.
The query that MeEtc provided should basically do then, except you want to modify the WHERE clause a bit:
SELECT a.*, b.playername
FROM tableA a INNER JOIN tableB b ON a.steamid = b.steamid
WHERE b.playername = 'Johnny'
Although you said you wanted steamid from tableB, it would presumably be the same coming from tableA and you don't need it in your output twice. You can use = for an exact name match, or LIKE, as in MeEtc's example, for a partial name match.
Going by the questions I've seen you post over time (which, of course, is perfectly fine), I think you should spend some time reading first some general SQL tutorials, perhaps followed by MySQL documentation, and just play around with some tool that allows you to enter and execute queries and view results. You are in the same boat many other web developers are -- you don't know how to do much beyond "SELECT *", so that's what you end up doing and then process your results with various kludges. SQL is awesome. It can do all of that work for you, you just need to know how to ask it properly.
This post was edited on 01-17-2010 at 07:32 PM by Adeptus.
|
|
01-17-2010 07:24 PM |
|
|
Jimbo
Veteran Member
Posts: 1650 Reputation: 18
32 / /
Joined: Jul 2006
|
O.P. RE: MySQL and PHP - using 2 tables in one query?
Thanks Adeptus, I think I will go away and read some tutorials, so to not fill up this forum with all my MySQL questions xD
However, I am probably doing something really stupid, but this is outputting no results, and I can't figure out why.
PHP code:
<?php echo '<table class="sortable2" id="reports" name="reports">
<tr>
<th><font face="Arial, Helvetica, sans-serif" size="3">SteamID</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">VIP</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Upgrade 1</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Upgrade 2</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Upgrade 3</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Upgrade 4</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Upgrade Points</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Zombie Upgrade Points</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Bonus Upgrade Points</font></th>
<th><font face="Arial, Helvetica, sans-serif" size="3">Player Model</font></th>
</tr>';
?>
<?
mysql_connect("xxxxx","xxxxx","xxxx");
mysql_select_db("servers");
$search=$_POST["search"];
$search2 = mysql_real_escape_string($_POST['search']);
$result = mysql_query("SELECT a.*, b.playername
FROM upgrades a INNER JOIN zm_timetracker b ON a.steamid = b.steamid
WHERE b.playername LIKE '%$search2%'");
while($r=mysql_fetch_array($result))
{
$steam=$r["steamid"];
$dt=$r["vip"];
$pn=$r["playerupgrade1"];
$rs=$r["playerupgrade2"];
$rp=$r["playerupgrade3"];
$se=$r["playerupgrade4"];
$sns=$r["upgradepoints"];
$snr=$r["zombieupgradepoints"];
$snp=$r["bonusupgradepoints"];
$sn=$r["playermodel"];
echo "<tr><td>$steam</td><td>$dt</td><td>$pn</td><td>$rs</td><td>$rp</td><td>$se</td><td>$sns</td><td>$snr</td><td>$snp</td></tr>";
}
?>
</table>
I get no PHP errors, so I assume the syntax is all OK.
This post was edited on 01-17-2010 at 07:33 PM by Jimbo.
|
|
01-17-2010 07:33 PM |
|
|
Chrissy
Senior Member
Posts: 850 Reputation: 5
29 / /
Joined: Nov 2009
|
RE: MySQL and PHP - using 2 tables in one query?
I do know <? and ?> is not compatible on all servers. php configs.
This post was edited on 01-17-2010 at 08:03 PM by Chrissy.
|
|
01-17-2010 07:55 PM |
|
|
Pages: (3):
« First
[ 1 ]
2
3
»
Last »
|
|
|