Page 1 of 1

select form data

Posted: Fri Aug 22, 2003 12:46 pm
by pachox
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?

Re: select form data

Posted: Fri Aug 22, 2003 12:55 pm
by ericsodt
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

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?

Posted: Fri Aug 22, 2003 1:02 pm
by pachox
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

Posted: Fri Aug 22, 2003 1:09 pm
by ericsodt
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

Posted: Fri Aug 22, 2003 1:17 pm
by pachox
yes i understand what u mean.

can u give me a code hint, plz?

pacho

Posted: Fri Aug 22, 2003 1:31 pm
by ericsodt
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

Posted: Fri Aug 22, 2003 2:03 pm
by pachox
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);


?>

Posted: Fri Aug 22, 2003 2:19 pm
by ericsodt
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";
}

Posted: Fri Aug 22, 2003 2:24 pm
by ericsodt
You also have to watch for when the user only enters in one value cause the statement ends in 'or' so your where clause will end with or and that wil throw back an error at you

Posted: Fri Aug 22, 2003 2:37 pm
by pachox
ericsodt wrote:You also have to watch for when the user only enters in one value cause the statement ends in 'or' so your where clause will end with or and that wil throw back an error at you

opppsss.

lost in space now....

code to explain pls

Posted: Fri Aug 22, 2003 3:38 pm
by pachox
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";