MySQL Query with variables

[jtelo]

I'm building a website in Flash, wich uses PHP to send/get data from a database.

In Flash I have 3 comboboxes, that if selected send to the PHP script their index value. If one of them is not selected, it sends 0 to PHP.

My PHP script is this:

<?php
include("setup.php");
$cat = $GET['cat']; //combobox 1 value
$act = $GET['act']; //combobox 2 value
$pais = $_GET['pais']; //combobox 3 value
$cant = 0;
echo "&";

$result = mysql_query("SELECT local, duracao, distancia_total, data, preco FROM programas WHERE ID_categoria=$cat AND ID_actividade=$act AND ID_pais=$pais");
while($row=mysql_fetch_array($result)){
$cant++;
echo "local$cant=$row[local]&";
echo "duracao$cant=$row[duracao]&";
echo "distancia_total$cant=$row[distancia_total]&";
echo "data$cant=$row[data]&";
echo "preco$cant=$row[preco]&";
}
echo "total_prog=$cant";
?>

The problem is that if one of the comboboxes is not select, with de 0 (zero) value the query fails. Can someone help me?

[sapoxgn]

of course its not going to work.... you are using AND

Why dont you use or?

$result = mysql_query("SELECT local, duracao, distancia_total, data, preco FROM programas WHERE ID_categoria=$cat OR ID_actividade=$act OR ID_pais=$pais"); 

or just try making some ifs with different querys

[jtelo]

Ok, that should work if only one of the comboboxes are selected.

But if two of them are selected? Will the query return the selection for the two comboboxes?

[mrhappiness]

include("setup.php");
$criterias = array();
if (!empty($_GET['cat']))
	$criteris[] = 'id_categoria = '.$_GET['cat'];
if (!empty($_GET['act']))
	$criteris[] = 'id_categoria = '.$_GET['act'];
if (!empty($_GET['pais']))
	$criteris[] = 'id_categoria = '.$_GET['pais'];

if (count($criterias) > 0)
	$where = ' WHERE '.implode(' AND ', $criterias);
else	$where = '';
$sql = 'SELECT local, duracao, distancia_total, data, preco FROM programas'.$where;


$cant = 0;
echo "&";

$result = mysql_query($sql) or die(mysql_error().'<hr />'.$sql);
while($row=mysql_fetch_array($result)){
	$cant++;
	echo "local$cant=$row[local]&";
	echo "duracao$cant=$row[duracao]&";
	echo "distancia_total$cant=$row[distancia_total]&";
	echo "data$cant=$row[data]&";
	echo "preco$cant=$row[preco]&";
}
echo "total_prog=$cant";

how about that?

should use the combobox values that are not 0

[jtelo]

Thank you very much, mrhappiness. It worked!

But now I have another problem.
Since the second combobox is dynamic, when only this combobox is selected:
- Flash sends combobox 2 index number ($act) and label ($act_lbl) to php.
- I have to make a previous query to the table 'actividades' for column nome_pt=$act_lbl, and return all the results in column ID.
- Then i have to use all the results of ID with the query you built yesterday, like SELECT ID, local, duracao, distancia_total, data, preco FROM programas, WHERE ID_actividade=ID[]

I know that it's hard to understand, but can you help me?