Page 1 of 1

queries in php form with 2 different tables

Posted: Thu Oct 16, 2008 11:52 am
by Dieneces
Hello,

I'm new to PHP and SQL and i'm trying to do a form that inputs the user for a Name and it querys the database and gives the result with the info of the name, class and experience of a certain character.

I will not get into details because I’m still trying to pass my issue by myself… but I will give a general ideia of my problem:

I have a form where I insert the name (or part of the name) and when I hit search it already returns all the results in the database, but…. With a problem… because the several classes of characters are in a table and the rest in other… I already have a relation between the to:

In my player table I have a class text field and in my classes table I have classID and Name fields and I have a relation between the two created with phpmyadmin (when I insert a new record in the players table it shows a dropbox with the classes available in the classes table).

The problem is that what the query results is the class field of the players table, witch is a number associated with the ClassID from classes table.

I now that my query has to go the both tables and do some kind of JOIN…. Not sure yet…

I’m working at the same time so I now that this topic may be a little hard to understand… eheh.

I will post my code for the search.php and results.php just for you guys try to understand what I need to do:

Search.php:

Code: Select all

 
<html>
<head>
    <title>Search</title>
</head>
<body>
    <h1>Search for players</h1>
<table border="0" cellpadding="60">
<tr>
<td>
    <form action="results.php" name="pesquisar" method="post">
      Choose Search Type:<br />
      <select name="searchtype">
        <option value="Nome">Jogador</option>
        <option value="Classe">Classe</option>
    </select>
    <br />
    Enter Search Term:<br />
    <input name="searchterm" type="text">
    <br />
    <input type="submit" name="pesquisa" value="Search">
    </form>
</td>
</tr>
</table>
</body>
</html>
 
results.php:

Code: Select all

 
<html>
<head>
    <title> Search Results</title>
</head>
 
<body>
    <h1>Players Search Results</h1>
<?php
// create short variable names
$searchtype=$_POST['searchtype'];
$searchterm=$_POST['searchterm'];
$searchterm= trim($searchterm);
if (!$searchtype || !$searchterm)
{
echo 'You have not entered search details. Please go back and try again.';
exit;
}
if (!get_magic_quotes_gpc())
{
$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);
}
@ $db = new mysqli('localhost', 'root', 'risefall', 'gladiadores');
if (mysqli_connect_errno())
{
echo 'Error: Could not connect to database. Please try again later.';
exit;
}
$query = "select player.Nome,player.Classe,player.EXP from player where ".$searchtype." like '%".$searchterm."%'";
$result = $db->query($query);
$num_results = $result->num_rows;
echo '<p>Gladiadores Encontrados: '.$num_results.'</p>';
for ($i=0; $i <$num_results; $i++)
{
$row = $result->fetch_assoc();
echo '<p><strong>'.($i+1).'. Nome: ';
echo htmlspecialchars(stripslashes($row['Nome']));
echo '</strong><br />Classe: ';
echo stripslashes($row['Classe']);
echo '<br />Experiência: ';
echo stripslashes($row['EXP']);
echo '</p>';
}
//$result->free();
//$db->close();
?>
</body>
</html>
 
I just started today looking more serious at php and mysql so when I get home I will try to find some info (in my work I can't access 80% of the internet... lol). But if someone could just point me in the right direction even better.

Thanks in advance ;)

P.s. - I'm thinking that I may have to use more than one query... ohh my... I have to read alot :P

Re: queries in php form with 2 different tables

Posted: Wed Oct 22, 2008 9:16 am
by aceconcepts
Take a loo at http://www.w3schools.com/Sql/sql_join.asp

In order for relational database to work properly you must ensure the referential integrity between the tables that have relationships.

Re: queries in php form with 2 different tables

Posted: Wed Oct 22, 2008 2:07 pm
by Dieneces
aceconcepts wrote:Take a loo at http://www.w3schools.com/Sql/sql_join.asp

In order for relational database to work properly you must ensure the referential integrity between the tables that have relationships.
thanks, I already made it doing exactly that and changing some other things ;) I'm now stuck with a problem with combo box's but I think I will manage ;)

thanks for the reply.