Page 1 of 1

I'm next to crash

Posted: Tue Mar 11, 2003 1:08 pm
by Igguana
I've been trying to find a way to do that:
I have a database and a form, the form has 5 fields (menus that are filled from the database itself) and you have to make a search using the combination of those 5 fields options or with some of them, you can leave them empty if you are not so interested in the particular option.
The database should return you the coincidences (It means that, if you do not choose any criteria, the database should bring you all its fields)
I have tried in different ways, but I get rubbish or warning messages.
<?
$base="bla";
$tabla="Datosinmueble";
$conexion=mysql_connect("bla","bla","bla");
mysql_select_db($base,$conexion);
$datoseleccion1=$_POST[envio1];
$datoseleccion2=$_POST[envio2];
$datoseleccion3=$_POST[envio3];
$datoseleccion4=$_POST[envio4];
$datoseleccion5=$_POST[envio5];
$pegar=mysql_query("SELECT * FROM $tabla WHERE (Modo='$datoseleccion1') or (Localidad='$datoseleccion2') or (Tipo='$datoseleccion3') or (Habitaciones='$datoseleccion4') or (Precio='$datoseleccion5')");
?>
<TABLE BORDER=1 CELLSPACING=1 CELLPADDING=1>
<TR>
<TD>&nbsp;Modo</TD>
<TD>&nbsp;Localidad&nbsp;</TD>
<TD>&nbsp;Tipo&nbsp;</TD>
<TD>&nbsp;Habitaciones&nbsp;</TD>
<TD>&nbsp;Precio&nbsp;</TD>
</TR>
<?
while($row = mysql_fetch_array($pegar)) {
printf("<tr><td>&nbsp;%s</td><td>&nbsp;%s&nbsp;</td><td>&nbsp;%s&nbsp;</td><td>&nbsp;%s&nbsp;</td><td>&nbsp;%s&nbsp;</td></tr>",
$row["Modo"],$row["Localidad"],$row["Tipo"],$row["Habitaciones"],$row["Precio"]);
}
mysql_close();
?>

The error message:
"Mysql_fetch_array():suplied argument is not a valid mysql resource"

Posted: Tue Mar 11, 2003 1:50 pm
by volka
maybe your query is malformed. mysql_error() tells you about the last error.
try

Code: Select all

<?php
$base="bla";
$tabla="Datosinmueble";
$conexion = mysql_connect('bla', 'bla', 'bla') or die(mysql_error());
mysql_select_db($base,$conexion) or die(mysql_error());

$fields = array('Modo','Localidad','Tipo','Habitaciones','Precio');
$clauses = array();
for($i=0; $i!=count($fields); $i++)
{
	if (isset($_POST['envio'.($i+1)]) && $_POST['envio'.($i+1)] != -1)
		$clauses[] = "$fields[$i]='{$_POST['envio'.($i+1)]}'";
}

$query = "SELECT * FROM $tabla";
if (count($clauses) > 0)
	$query .= ' WHERE '.join('AND ', $clauses);
$pegar=mysql_query($query, $conexion) or die($query.' :'.mysql_error());
?>
<TABLE BORDER="1" CELLSPACING="1" CELLPADDING="1">
	<TR>
		<TD>Modo</TD>
		<TD>Localidad </TD>
		<TD>Tipo</TD>
		<TD>Habitaciones</TD>
		<TD>Precio</TD>
	</TR>
<?php
while($row = mysql_fetch_array($pegar))
{
?>
	<TR>
		<TD><?php echo $row["Modo"]; ?></TD>
		<TD><?php echo $row["Localidad"]; ?></TD>
		<TD><?php echo $row["Tipo"]; ?></TD>
		<TD><?php echo $row["Habitaciones"]; ?></TD>
		<TD><?php echo $row["Precio"]; ?></TD>
	</TR>
<?php	
}
?>
where I assume -1 to be the value of the default-option, like

Code: Select all

&lt;select&gt;
	&lt;option value="-1"&gt;doesn't matter&lt;/option&gt;
	&lt;option value="1"&gt;&lt;0&lt;/option&gt;
	&lt;option value="2"&gt;=0&lt;/option&gt;
	&lt;option value="3"&gt;&gt;0&lt;/option&gt;
&lt;/select&gt;
and I chose AND as sentential connective ..maybe that's what you want...maybe not ;)

The little problem

Posted: Tue Mar 11, 2003 2:08 pm
by Igguana
The problem is that the form is dinamical, the ptions included in the menus come from the database so what I pass is not a number, is a string.
This is the form code.
<?
$base="BLA";
$tabla="DatosInmueble";
$conexion=mysql_connect("BLA","BLA","BLA");
mysql_select_db($base,$conexion);
$pegar1 = "SELECT Modo FROM $tabla GROUP BY Modo";
$pegar2 = "SELECT Localidad FROM $tabla GROUP BY Localidad";
$pegar3 = "SELECT Tipo FROM $tabla GROUP BY Tipo";
$pegar4 = "SELECT Habitaciones FROM $tabla GROUP BY Habitaciones";
$pegar5 = "SELECT Precio FROM $tabla GROUP BY Precio";
echo "<table width=50% border=0>
<tr>
<td bgcolor='#CCCC00'>
<form name='form1' method='post' action='motorbusqueda.php'>
<table width='50%' border='0'>";

?>

<tr>
<td><font face='Arial, Helvetica, sans-serif' size='1'

color='#FFFF00'>Modalidad</font></td>
<td><font face='Arial, Helvetica, sans-serif' size='1'

color='#FFFF00'>Localidad</font></td>
<td><font face='Arial, Helvetica, sans-serif' size='1'

color='#FFFF00'>Tipo</font></td>
<td>&nbsp;</td>
</tr>
<tr>
<td> <font face='Arial, Helvetica, sans-serif' size='1' color='#FFFF00'>
<select name='envio1' size='1'>
<option></option>
<?
$resultado1 = mysql_db_query($base,$pegar1)or die (mysql_error());
while ($registro1['Modo'] = mysql_fetch_row($resultado1)){
foreach($registro1['Modo'] as $clave1){
}
?>
<option><?

echo $clave1;
}

?></option>
</select>
</font></td>
<td> <font face='Arial, Helvetica, sans-serif' size='1' color='#FFFF00'>
<select name='envio2' size='1'>
<option></option>
<?
$resultado2 = mysql_db_query($base,$pegar2)or die (mysql_error());
while ($registro2['Localidad'] = mysql_fetch_row($resultado2)){
foreach($registro2['Localidad'] as $clave2){
}
?>
<option><?

echo $clave2;
}

?></option>
</select>
</font></td>
<td> <font face='Arial, Helvetica, sans-serif' size='1' color='#FFFF00'>
<select name='envio3' size='1'>
<option></option>
<?
$resultado3 = mysql_db_query($base,$pegar3)or die (mysql_error());
while ($registro3['Tipo'] = mysql_fetch_row($resultado3)){
foreach($registro3['Tipo'] as $clave3){
}
?>
<option><?

echo $clave3;
}
?></option>
</select>
</font></td>
<td>&nbsp; </td>
</tr>
<tr>
<td><font face='Arial, Helvetica, sans-serif' size='1'

color='#FFFF00'>Habitaciones</font></td>
<td><font face='Arial, Helvetica, sans-serif' size='1'

color='#FFFF00'>Precio</font></td>
<td><font face='Arial, Helvetica, sans-serif' size='1'

color='#FFFF00'></font></td>
<td>&nbsp;</td>
</tr>
<tr>
<td> <font face='Arial, Helvetica, sans-serif' size='1' color='#FFFF00'>
<select name='envio4' size='1'>
<option></option>
<?
$resultado4 = mysql_db_query($base,$pegar4)or die (mysql_error());
while ($registro4['Habitaciones'] = mysql_fetch_row($resultado4)){
foreach($registro4['Habitaciones'] as $clave4){
}
?>
<option><?

echo $clave4;
}

?></option>
</select>
</font></td>
<td> <font face='Arial, Helvetica, sans-serif' size='1' color='#FFFF00'>
<select name='envio5' size='1'>
<option></option>
<?
$resultado5 = mysql_db_query($base,$pegar5)or die (mysql_error());
while ($registro5['Precio'] = mysql_fetch_row($resultado5)){
foreach($registro5['Precio'] as $clave5){
}
?>
<option><?

echo $clave5;
}
?></option>
</select>
</font></td>
<td><font face='Arial, Helvetica, sans-serif' size='1' color='#FFFF00'>
<input type='submit' name='Consultar' value='Consultar'>
</font> </td>
<td>&nbsp;</td>
</tr>
<tr>
<td><font face='Arial, Helvetica, sans-serif' size='1'

color='#FFFF00'></font></td>
<td><font face='Arial, Helvetica, sans-serif' size='1'

color='#FFFF00'></font></td>
<td><font face='Arial, Helvetica, sans-serif' size='1'

color='#FFFF00'></font></td>
<td>&nbsp;</td>
</tr>

<?

echo "</table>
</form>
</td>
</tr>

</table>";


mysql_close();

?>

Posted: Tue Mar 11, 2003 2:15 pm
by volka
all you need to do is to prepend an option

Code: Select all

<option value="-1">doesn't matter</code>
or similar for each <select> ;)

It works like a fuc%%%% swiss clock!!!

Posted: Tue Mar 11, 2003 4:25 pm
by Igguana
Thank you a lot, it works fine and smooth!!! :lol:

Posted: Tue Mar 11, 2003 4:35 pm
by Igguana
Can you make some comments on what is exactly the code doing here?

$fields = array('Modo','Localidad','Tipo','Habitaciones','Precio');
$clauses = array();
for($i=0; $i!=count($fields); $i++)
{
if (isset($_POST['envio'.($i+1)]) && $_POST['envio'.($i+1)] != -1)
$clauses[] = "$fields[$i]='{$_POST['envio'.($i+1)]}'";
}
For me should be wonderfull to understand it, but I'm new on board!

Posted: Tue Mar 11, 2003 5:13 pm
by volka
right, I really should put more comments in more complex code

Code: Select all

/**
	if $_POST['envio1'] is set, then the WHERE clause should contain Modo=$_POST['envio1']
	if $_POST['envio2'] is set, then the WHERE clause should contain Localidad=$_POST['envio2']
*/
$fields = array('Modo','Localidad','Tipo','Habitaciones','Precio');
/**
	if $_POST['envio1'] is set, then the WHERE clause should contain $fields[0]=$_POST['envio1']
	if $_POST['envio2'] is set, then the WHERE clause should contain $fields[1]=$_POST['envio2']
	    -->
	if $_POST['envio'.$i] is set, then the WHERE clause should contain $fields[$i-1]=$_POST['envio'.$i]
	    or even better
	if $_POST['envio'.($i+1)] is set, then the WHERE clause should contain $fields[$i]=$_POST['envio'.($i+1)]
*/
$clauses = array(); // typedef of clause-array
for($i=0; $i!=count($fields); $i++)
{
	// testing wether $_POST['envio'.($i+1)] is set
	// using -1 as "invalid" value (--> doesn't matter), can be anything you like (and can be identified as "invalid")
	if (isset($_POST['envio'.($i+1)]) && $_POST['envio'.($i+1)] != -1)
	{
		// if so add a new "clause"-element $fields[$i]=$_POST['envio'.($i+1)] to the array
		$clauses[] = "$fields[$i]='{$_POST['envio'.($i+1)]}'";
	}
}
$query = "SELECT * FROM $tabla"; // base-query
if (count($clauses) > 0)	// if the $clauses-array contains at least one clause
   $query .= ' WHERE '.join('AND ', $clauses); // add the clauses
   
$pegar=mysql_query($query, $conexion) or die($query.' :'.mysql_error());
as an addition: the values of $_POST['envio'.($i+1)] should be checked, otherwise users may enter malicious data

Code: Select all

// if so add a new "clause"-element $fields[$i]=$_POST['envio'.($i+1)] to the array
$clauses[] = "$fields[$i]='". mysql_escape_string($_POST['envio'.($i+1)])."'";

Posted: Tue Mar 11, 2003 5:23 pm
by Igguana
Thanks one more time.