queries in php form with 2 different tables
Posted: Thu Oct 16, 2008 11:52 am
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:
results.php:
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
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>
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>
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