select form data

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pachox
Forum Newbie
Posts: 23
Joined: Sun Aug 17, 2003 7:14 am

select form data

Post 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?
ericsodt
Forum Commoner
Posts: 51
Joined: Fri Aug 22, 2003 12:12 pm
Location: VA

Re: select form data

Post 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?
Last edited by ericsodt on Fri Aug 22, 2003 1:03 pm, edited 1 time in total.
pachox
Forum Newbie
Posts: 23
Joined: Sun Aug 17, 2003 7:14 am

Post 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
ericsodt
Forum Commoner
Posts: 51
Joined: Fri Aug 22, 2003 12:12 pm
Location: VA

Post 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
pachox
Forum Newbie
Posts: 23
Joined: Sun Aug 17, 2003 7:14 am

Post by pachox »

yes i understand what u mean.

can u give me a code hint, plz?

pacho
ericsodt
Forum Commoner
Posts: 51
Joined: Fri Aug 22, 2003 12:12 pm
Location: VA

Post 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
pachox
Forum Newbie
Posts: 23
Joined: Sun Aug 17, 2003 7:14 am

Post 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);


?>
ericsodt
Forum Commoner
Posts: 51
Joined: Fri Aug 22, 2003 12:12 pm
Location: VA

Post 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";
}
ericsodt
Forum Commoner
Posts: 51
Joined: Fri Aug 22, 2003 12:12 pm
Location: VA

Post 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
pachox
Forum Newbie
Posts: 23
Joined: Sun Aug 17, 2003 7:14 am

Post 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
pachox
Forum Newbie
Posts: 23
Joined: Sun Aug 17, 2003 7:14 am

Post 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";
Post Reply