Shoutbox

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>

---

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.

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.

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

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.

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

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

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. 

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.

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