What happened to the Messenger Plus! forums on msghelp.net?
Shoutbox » MsgHelp Archive » Skype & Technology » Tech Talk » MySQL and PHP - using 2 tables in one query?

Pages: (3): « First [ 1 ] 2 3 » Last »
MySQL and PHP - using 2 tables in one query?
Author: Message:
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
31 / Male / Flag
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
Profile E-Mail PM Find Quote Report
MeEtc
Patchou's look-alike
*****

Avatar
In the Shadow Gallery once again

Posts: 2200
Reputation: 60
38 / Male / Flag
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.
[Image: signature/]     [Image: sharing.png]
I cannot hear you. There is a banana in my ear.
01-17-2010 03:05 AM
Profile PM Web Find Quote Report
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
31 / Male / Flag
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
Profile E-Mail PM Find Quote Report
stoshrocket
Senior Member
****

Avatar
formerly methos

Posts: 748
Reputation: 31
33 / Male / Flag
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...
formerly methos
01-17-2010 04:41 PM
Profile PM Web Find Quote Report
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
31 / Male / Flag
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
Profile E-Mail PM Find Quote Report
stoshrocket
Senior Member
****

Avatar
formerly methos

Posts: 748
Reputation: 31
33 / Male / Flag
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 :undecided: 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 :P
formerly methos
01-17-2010 05:23 PM
Profile PM Web Find Quote Report
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
31 / Male / Flag
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
Profile E-Mail PM Find Quote Report
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
Profile E-Mail PM Find Quote Report
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
31 / Male / Flag
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
Profile E-Mail PM Find Quote Report
Chrissy
Senior Member
****

Avatar

Posts: 850
Reputation: 5
29 / Male / Flag
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
Profile E-Mail PM Web Find Quote Report
Pages: (3): « First [ 1 ] 2 3 » Last »
« Next Oldest Return to Top Next Newest »


Threaded Mode | Linear Mode
View a Printable Version
Send this Thread to a Friend
Subscribe | Add to Favorites
Rate This Thread:

Forum Jump:

Forum Rules:
You cannot post new threads
You cannot post replies
You cannot post attachments
You can edit your posts
HTML is Off
myCode is On
Smilies are On
[img] Code is On