MySQL and PHP - using 2 tables in one query?

MySQL and PHP - using 2 tables in one query? - Printable Version

-Shoutbox (https://shoutbox.menthix.net)
+-- Forum: MsgHelp Archive (/forumdisplay.php?fid=58)
+--- Forum: Skype & Technology (/forumdisplay.php?fid=9)
+---- Forum: Tech Talk (/forumdisplay.php?fid=17)
+----- Thread: MySQL and PHP - using 2 tables in one query? (/showthread.php?tid=93557)

MySQL and PHP - using 2 tables in one query? by Jimbo on 01-17-2010 at 01:32 AM

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>SteamID</th>
<th>VIP</th>
<th>Upgrade 1</th>
<th>Upgrade 2</th>
<th>Upgrade 3</th>
<th>Upgrade 4</th>
<th>Upgrade Points</th>
<th>Zombie Upgrade Points</th>
<th>Bonus Upgrade Points</th>
<th>Player Model</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>

RE: MySQL and PHP - using 2 tables in one query? by MeEtc on 01-17-2010 at 03:05 AM

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.

RE: MySQL and PHP - using 2 tables in one query? by Jimbo on 01-17-2010 at 01:51 PM

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.

RE: MySQL and PHP - using 2 tables in one query? by stoshrocket on 01-17-2010 at 04:41 PM

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...

RE: MySQL and PHP - using 2 tables in one query? by Jimbo on 01-17-2010 at 04:48 PM

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.

RE: MySQL and PHP - using 2 tables in one query? by stoshrocket on 01-17-2010 at 05:23 PM

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

RE: MySQL and PHP - using 2 tables in one query? by Jimbo on 01-17-2010 at 05:33 PM

Well 2 queries wouldnt be a problem, in fact, it would probably be easier.

RE: MySQL and PHP - using 2 tables in one query? by Adeptus on 01-17-2010 at 07:24 PM

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.

RE: MySQL and PHP - using 2 tables in one query? by Jimbo on 01-17-2010 at 07:33 PM

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>SteamID</th>
<th>VIP</th>
<th>Upgrade 1</th>
<th>Upgrade 2</th>
<th>Upgrade 3</th>
<th>Upgrade 4</th>
<th>Upgrade Points</th>
<th>Zombie Upgrade Points</th>
<th>Bonus Upgrade Points</th>
<th>Player Model</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.

RE: MySQL and PHP - using 2 tables in one query? by Chrissy on 01-17-2010 at 07:55 PM

I do know <? and ?> is not compatible on all ~~servers.~~ php configs.

RE: MySQL and PHP - using 2 tables in one query? by Jimbo on 01-17-2010 at 07:57 PM

[slightly offtopic]
<? and ?> tags are short_open_tags, and I always use these, so they are enabled. Since I rent my own server box, and host everything from there, I can change anything I like in my PHP.ini.
[/slightly offtopic]

RE: MySQL and PHP - using 2 tables in one query? by Chrissy on 01-17-2010 at 08:04 PM

Ok - I see!

RE: MySQL and PHP - using 2 tables in one query? by Adeptus on 01-17-2010 at 08:13 PM

First of all, your questions are welcome. I just think you would benefit from knowing what kind of functionality is out there when querying a typical SQL engine.

Secondly, I recommend testing queries in an interactive tool before you put them in code, so that when things don't work you don't have to guess whether it's a problem with the query or the PHP code. With mysql, you can do this on the command line, using a web based tool or one of several Windows GUI tools.

If you are getting no results, a good test might be to drop your WHERE clause and see what happens without it. Sure, you will probably then get results you don't want, but if that works it's a good clue where to look for the issue.

RE: MySQL and PHP - using 2 tables in one query? by MeEtc on 01-17-2010 at 08:15 PM

Try running the query as-is in phpmyadmin, to see what it results in. You might be able to drop the "INNER" from the inner join part, to see if this has any impact.

RE: MySQL and PHP - using 2 tables in one query? by Matti on 01-17-2010 at 08:19 PM

I don't know which version of MySQL you're running and I'm not a MySQL expert myself, but perhaps adding "AS" between the table names and its alias may help? Or maybe it's the new line characters? Have you tried checking for errors from MySQL first?

PHP code:
<?php
$result = mysql_query("SELECT a.*, b.playername "
. "FROM upgrades AS a INNER JOIN zm_timetracker AS b ON a.steamid = b.steamid "
. "WHERE b.playername LIKE '%$search2%'") or die("MySQL error: " . mysql_error());
?>

If that doesn't resolve the issue, try running the query in something like phpMyAdmin and see if the query works as expected. At least that'll tell us whether it's the query or the PHP that's faulty.

RE: MySQL and PHP - using 2 tables in one query? by Jimbo on 01-17-2010 at 08:39 PM

When running that query using PHP, I now get all results from table A returned, no matter what I type into the search.

When running the following query in navicat:

SQL code:
SELECT a.*, b.name FROM upgrades AS a INNER JOIN zm_timetracker AS b ON a.steamid = b.steamid WHERE b.name LIKE '%cherry%'

I get 4 results returned, with all their corresponding fields in TableA, and all of which have "cherry" in their name in tableB.

EDIT: Forget that, I found what I did wrong. Thank you so much everyone for your help

RE: MySQL and PHP - using 2 tables in one query? by Adeptus on 01-17-2010 at 08:55 PM

quote:
Originally posted by Jimbo
When running the following query in navicat:

SQL code:
SELECT a.*, b.name FROM upgrades AS a INNER JOIN zm_timetracker AS b ON a.steamid = b.steamid WHERE b.name LIKE '%cherry%'

I get 4 results returned, with all their corresponding fields in TableA, and all of which have "cherry" in their name in tableB.

That is what I would expect -- is that not what you wanted?

RE: MySQL and PHP - using 2 tables in one query? by Jimbo on 01-17-2010 at 09:16 PM

Yeah, it is. Thanks. (Check my recent post edit )

RE: MySQL and PHP - using 2 tables in one query? by Adeptus on 01-17-2010 at 09:22 PM

Great. You should come on IRC (#banana) sometime.

RE: MySQL and PHP - using 2 tables in one query? by NanaFreak on 01-18-2010 at 01:49 AM

Jimbo, a site that one of my teachers got me (and my class) to use to learn and try SQL is http://sqlzoo.net/

though that is mainly to get you to learn and remember some stuff... i would try and learn the basics from another site first and then use that once you 'get the hang' of it

RE: MySQL and PHP - using 2 tables in one query? by Jimbo on 01-18-2010 at 01:44 PM

Thanks Nana, I'll be sure to take a look at that.