select form data
Moderator: General Moderators
select form data
i have a form like this:
<div id="form2"><form method="post" action="consulenti.php">
name: <input type="text" name="cognome">
email: <input type="text" name="specialita">
address: <input type="text" name="regione"> <br>
<br><br>
<input type="submit" name="Submit" value="Submit">
<input type="reset" value="Reset">
</form></div>
what i want is a user can do a search by name, email or address dependly by the data he inserts in the form.
i tried something like this, but no result:
$SQL="SELECT IDMedico, cognome, nome, email, telefono, regione, specialita, descrizione, picfile FROM medici WHERE cognome LIKE '%".$_POST['cognome']."%'
OR regione LIKE '$_POST[regione]%'
OR regione LIKE '$_POST[regione]%'
ORDER BY cognome
";
what's wrong?
<div id="form2"><form method="post" action="consulenti.php">
name: <input type="text" name="cognome">
email: <input type="text" name="specialita">
address: <input type="text" name="regione"> <br>
<br><br>
<input type="submit" name="Submit" value="Submit">
<input type="reset" value="Reset">
</form></div>
what i want is a user can do a search by name, email or address dependly by the data he inserts in the form.
i tried something like this, but no result:
$SQL="SELECT IDMedico, cognome, nome, email, telefono, regione, specialita, descrizione, picfile FROM medici WHERE cognome LIKE '%".$_POST['cognome']."%'
OR regione LIKE '$_POST[regione]%'
OR regione LIKE '$_POST[regione]%'
ORDER BY cognome
";
what's wrong?
Re: select form data
before you query, use some logic to 'weed out' null values so the information in the query is valid meaning you know your selecting by some element name and not just null
Make the query dynamic
Make the query dynamic
pachox wrote:i have a form like this:
<div id="form2"><form method="post" action="consulenti.php">
name: <input type="text" name="cognome">
email: <input type="text" name="specialita">
address: <input type="text" name="regione"> <br>
<br><br>
<input type="submit" name="Submit" value="Submit">
<input type="reset" value="Reset">
</form></div>
what i want is a user can do a search by name, email or address dependly by the data he inserts in the form.
i tried something like this, but no result:
$SQL="SELECT IDMedico, cognome, nome, email, telefono, regione, specialita, descrizione, picfile FROM medici WHERE cognome LIKE '%".$_POST['cognome']."%'
OR regione LIKE '$_POST[regione]%'
OR regione LIKE '$_POST[regione]%'
ORDER BY cognome
";
what's wrong?
Last edited by ericsodt on Fri Aug 22, 2003 1:03 pm, edited 1 time in total.
does not matter aobut whats in the db, but what the user puts into the web page. if they leave two blank and enter in only one, then your where clause qualifies by two null value and one valid value.
pachox wrote:there are not null values in db.
the problem is that the query works only with the name field.
if i insert email or address the result is the whole db, if i insert a name the ouput is correct and the script make a search by the name value
hint:
$qualifiers= '';
if(isset($_POST[regione])){
$qualifiers = "regione LIKE '%".$_POST['regione']."%' and";
}
if(isset($_POST[cognone])){
$qualifiers+=" cognome LIKE '%".$_POST['cognome']."%' and";
}
etc....
etc....
$SQL="SELECT IDMedico, cognome, nome, email, telefono, regione, specialita, descrizione, picfile FROM medici WHERE $qualifiers
ORDER BY cognome
";
just make sure you take into account the 'and' at the end so you dont end the where clause with 'and' and have no statements afterwards
$qualifiers= '';
if(isset($_POST[regione])){
$qualifiers = "regione LIKE '%".$_POST['regione']."%' and";
}
if(isset($_POST[cognone])){
$qualifiers+=" cognome LIKE '%".$_POST['cognome']."%' and";
}
etc....
etc....
$SQL="SELECT IDMedico, cognome, nome, email, telefono, regione, specialita, descrizione, picfile FROM medici WHERE $qualifiers
ORDER BY cognome
";
just make sure you take into account the 'and' at the end so you dont end the where clause with 'and' and have no statements afterwards
no more output at all.
here the code
<?php
$DB_Host="localhost";
$DB_User="root";
$DB_Pass="";
$Per_Page=3;
$db_name = "DBMedici";
$table_name = "medici";
$connect = @mysql_connect($DB_Host, $DB_User, $DB_Pass) or die("connect che????");
$select_db = @mysql_select_db($db_name, $connect) or die("cannot select db");
// Run The Query Without a Limit to get Total result
$SQL="SELECT COUNT(*) AS Total FROM medici where '$qualifiers'
";
$SQL_Result=mysql_db_query($db_name, $SQL);
$SQL_Result_Array=mysql_fetch_array($SQL_Result);
$Total=$SQL_Result_Array['Total'];
$qualifiers= '';
if(isset($_POST[cognome])){
$qualifiers = "cognome LIKE '%".$_POST['cognome']."%' or";
}
if(isset($_POST[regione])){
$qualifiers+=" regione LIKE '%".$_POST['regione']."%' or";
}
if(isset($_POST[specialita])){
$qualifiers+=" specialita LIKE '%".$_POST['specialita']."%' or";
}
// Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
$SQL="SELECT IDMedico, cognome, nome, email, telefono, regione, specialita, descrizione, picfile FROM medici WHERE '$qualifiers'
ORDER BY cognome
";
// Append a LIMIT clause to the SQL statement
if (empty($_GET['Result_Set']))
{
$Result_Set=0;
$SQL.=" LIMIT $Result_Set, $Per_Page";
}else
{
$Result_Set=$_GET['Result_Set'];
$SQL.=" LIMIT $Result_Set, $Per_Page";
}
// Run The Query With a Limit to get result
$SQL_Result=mysql_db_query($db_name, $SQL);
$SQL_Rows=mysql_num_rows($SQL_Result);
//inizia formattazione celle
echo "<tr class=`GridHeader`>
<th>Cognome</th>
<th>Nome</th>
<th>Regione</th>
<th>Specialità</th>
<th>Email</th>
<th>Telefono</th>
<th>Foto</th>
</tr>
";
// Display Results using a for loop
for ($a=0; $a < $SQL_Rows; $a++)
{
echo "<tr class=\"GridRow\"
onmouseover=\"this.className='GridMOverRow'\"
onmouseout=\"this.className='GridRow'\" id=\"ggg\">"
;
$SQL_Array=mysql_fetch_array($SQL_Result);
$cognome=$SQL_Array['cognome'];
$nome=$SQL_Array['nome'];
$regione=$SQL_Array['regione'];
$specialita=$SQL_Array['specialita'];
$email=$SQL_Array['email'];
$telefono=$SQL_Array['telefono'];
echo "<td>".$SQL_Array['cognome']."</a></td>";
echo "<td>".$SQL_Array['nome']."</a></td>";
echo "<td>".$SQL_Array['regione']."</a></td>";
echo "<td>".$SQL_Array['specialita']."</a></td>";
echo "<td>".$SQL_Array['email']."</a></td>";
echo "<td>".$SQL_Array['telefono']."</a></td>";
echo "<td><img src=\"images/medici/".$SQL_Array['picfile']."\"></td>";
echo "</td>";
echo "</tr>";
echo "<tr class=\"GridDesc\">";
echo "<td colspan=\"7\">" ."Descrizione Attività". "</td>";
echo "</tr>";
echo "<td colspan=\"7\">" .$SQL_Array['descrizione']."</td>";
echo "</td>";
echo "</tr>";
}
// Create Next / Prev Links and $Result_Set Value
echo "<table>";
if ($Total>0)
{
if ($Result_Set<$Total && $Result_Set>0)
{
$Res1=$Result_Set-$Per_Page;
echo "<tr><td align=\"right\"><A HREF=\"consulenti.php?Result_Set=$Res1&cognome=".$_POST['cognome']."\"><< Pagina precedente</A> ";
}
// Calculate and Display Page # Links
$Pages=$Total / $Per_Page;
if ($Pages>1)
{
for ($b=0,$c=1; $b < $Pages; $b++,$c++)
{
$Res1=$Per_Page * $b;
echo "<A HREF=\"consulenti.php?Result_Set=$Res1&cognome=".$_POST['cognome']."\">$c</A> \n";
}
}
if ($Result_Set>=0 && $Result_Set<$Total)
{
$Res1=$Result_Set+$Per_Page;
if ($Res1<$Total)
{
echo " <A HREF=\"consulenti.php?Result_Set=$Res1&cognome=".$_POST['cognome']."\">Pagina successiva >></A></td> ";
echo "</tr>";
echo "</table>";
}
}
}
// Close Database Connection
mysql_close($connect);
?>
here the code
<?php
$DB_Host="localhost";
$DB_User="root";
$DB_Pass="";
$Per_Page=3;
$db_name = "DBMedici";
$table_name = "medici";
$connect = @mysql_connect($DB_Host, $DB_User, $DB_Pass) or die("connect che????");
$select_db = @mysql_select_db($db_name, $connect) or die("cannot select db");
// Run The Query Without a Limit to get Total result
$SQL="SELECT COUNT(*) AS Total FROM medici where '$qualifiers'
";
$SQL_Result=mysql_db_query($db_name, $SQL);
$SQL_Result_Array=mysql_fetch_array($SQL_Result);
$Total=$SQL_Result_Array['Total'];
$qualifiers= '';
if(isset($_POST[cognome])){
$qualifiers = "cognome LIKE '%".$_POST['cognome']."%' or";
}
if(isset($_POST[regione])){
$qualifiers+=" regione LIKE '%".$_POST['regione']."%' or";
}
if(isset($_POST[specialita])){
$qualifiers+=" specialita LIKE '%".$_POST['specialita']."%' or";
}
// Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
$SQL="SELECT IDMedico, cognome, nome, email, telefono, regione, specialita, descrizione, picfile FROM medici WHERE '$qualifiers'
ORDER BY cognome
";
// Append a LIMIT clause to the SQL statement
if (empty($_GET['Result_Set']))
{
$Result_Set=0;
$SQL.=" LIMIT $Result_Set, $Per_Page";
}else
{
$Result_Set=$_GET['Result_Set'];
$SQL.=" LIMIT $Result_Set, $Per_Page";
}
// Run The Query With a Limit to get result
$SQL_Result=mysql_db_query($db_name, $SQL);
$SQL_Rows=mysql_num_rows($SQL_Result);
//inizia formattazione celle
echo "<tr class=`GridHeader`>
<th>Cognome</th>
<th>Nome</th>
<th>Regione</th>
<th>Specialità</th>
<th>Email</th>
<th>Telefono</th>
<th>Foto</th>
</tr>
";
// Display Results using a for loop
for ($a=0; $a < $SQL_Rows; $a++)
{
echo "<tr class=\"GridRow\"
onmouseover=\"this.className='GridMOverRow'\"
onmouseout=\"this.className='GridRow'\" id=\"ggg\">"
;
$SQL_Array=mysql_fetch_array($SQL_Result);
$cognome=$SQL_Array['cognome'];
$nome=$SQL_Array['nome'];
$regione=$SQL_Array['regione'];
$specialita=$SQL_Array['specialita'];
$email=$SQL_Array['email'];
$telefono=$SQL_Array['telefono'];
echo "<td>".$SQL_Array['cognome']."</a></td>";
echo "<td>".$SQL_Array['nome']."</a></td>";
echo "<td>".$SQL_Array['regione']."</a></td>";
echo "<td>".$SQL_Array['specialita']."</a></td>";
echo "<td>".$SQL_Array['email']."</a></td>";
echo "<td>".$SQL_Array['telefono']."</a></td>";
echo "<td><img src=\"images/medici/".$SQL_Array['picfile']."\"></td>";
echo "</td>";
echo "</tr>";
echo "<tr class=\"GridDesc\">";
echo "<td colspan=\"7\">" ."Descrizione Attività". "</td>";
echo "</tr>";
echo "<td colspan=\"7\">" .$SQL_Array['descrizione']."</td>";
echo "</td>";
echo "</tr>";
}
// Create Next / Prev Links and $Result_Set Value
echo "<table>";
if ($Total>0)
{
if ($Result_Set<$Total && $Result_Set>0)
{
$Res1=$Result_Set-$Per_Page;
echo "<tr><td align=\"right\"><A HREF=\"consulenti.php?Result_Set=$Res1&cognome=".$_POST['cognome']."\"><< Pagina precedente</A> ";
}
// Calculate and Display Page # Links
$Pages=$Total / $Per_Page;
if ($Pages>1)
{
for ($b=0,$c=1; $b < $Pages; $b++,$c++)
{
$Res1=$Per_Page * $b;
echo "<A HREF=\"consulenti.php?Result_Set=$Res1&cognome=".$_POST['cognome']."\">$c</A> \n";
}
}
if ($Result_Set>=0 && $Result_Set<$Total)
{
$Res1=$Result_Set+$Per_Page;
if ($Res1<$Total)
{
echo " <A HREF=\"consulenti.php?Result_Set=$Res1&cognome=".$_POST['cognome']."\">Pagina successiva >></A></td> ";
echo "</tr>";
echo "</table>";
}
}
}
// Close Database Connection
mysql_close($connect);
?>
You call the sql statement before $qualifiers gets set... the database has no idea what $qualifiers is.
set the qualifiers before you call the query...
[quote="pachox"]
// Run The Query Without a Limit to get Total result
$SQL="SELECT COUNT(*) AS Total FROM medici where '$qualifiers'
";
$SQL_Result=mysql_db_query($db_name, $SQL);
$SQL_Result_Array=mysql_fetch_array($SQL_Result);
$Total=$SQL_Result_Array['Total'];
$qualifiers= '';
if(isset($_POST[cognome])){
$qualifiers = "cognome LIKE '%".$_POST['cognome']."%' or";
}
if(isset($_POST[regione])){
$qualifiers+=" regione LIKE '%".$_POST['regione']."%' or";
}
if(isset($_POST[specialita])){
$qualifiers+=" specialita LIKE '%".$_POST['specialita']."%' or";
}
set the qualifiers before you call the query...
[quote="pachox"]
// Run The Query Without a Limit to get Total result
$SQL="SELECT COUNT(*) AS Total FROM medici where '$qualifiers'
";
$SQL_Result=mysql_db_query($db_name, $SQL);
$SQL_Result_Array=mysql_fetch_array($SQL_Result);
$Total=$SQL_Result_Array['Total'];
$qualifiers= '';
if(isset($_POST[cognome])){
$qualifiers = "cognome LIKE '%".$_POST['cognome']."%' or";
}
if(isset($_POST[regione])){
$qualifiers+=" regione LIKE '%".$_POST['regione']."%' or";
}
if(isset($_POST[specialita])){
$qualifiers+=" specialita LIKE '%".$_POST['specialita']."%' or";
}
solved like this:
// Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
$SQL="SELECT IDMedico, cognome, nome, email, telefono, regione, specialita, descrizione, picfile FROM medici WHERE cognome LIKE '%".$_POST['cognome']."%' and
specialita LIKE '%".$_POST['specialita']."%' and
regione LIKE '%".$_POST['regione']."%'
ORDER BY cognome";
// Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
$SQL="SELECT IDMedico, cognome, nome, email, telefono, regione, specialita, descrizione, picfile FROM medici WHERE cognome LIKE '%".$_POST['cognome']."%' and
specialita LIKE '%".$_POST['specialita']."%' and
regione LIKE '%".$_POST['regione']."%'
ORDER BY cognome";