MySQL Forums
Forum List  »  PHP

Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship
Posted by: Kostas Telias
Date: January 18, 2011 04:23AM

Rick thank you.

I consideer seriously the possibility of leaving the application like it is (fetching all the species and not only the one that i want to show), after all the info you have given to me, because the searchs can be by 19 different fields (and all its combinations), 11 of which are classification. Your purpose (second in last post) would be too complex, at least for me, with 19 different fields and perhaps hundreds of combinations.

But i have a doubt more: which solution would be more efficient:

1.- Original (fetching all the species and not only the one that i want to show).

2.- A new table with two fields: Id and order. Then, use the INSERT statement once the order is changed or a search is made, inserting ordered the recordset of records that answer to the search (for example, the 50 records with family='Gramineae'). And for fetching only the specie i want to show, retrieve the Id of the wanted order:


if ((isset($_GET['ob']) && $_GET['ob']!="") || (isset($_GET['sf']) && isset($_GET['sw']) && $_GET['sf']!="" && $_GET['sw']!="")){
if (isset($_GET['ob']) && $_GET['ob']!=""){//'orderby' handling
	switch ($_GET['ob']) {
		case "0":
			$sqr="SELECT Id FROM tbltaxonomia wHERE ... GROUP BY t.nombrecomun, ...";
			break;		
		...
	}
}else if (isset($_GET['sf']) && isset($_GET['sw']) && $_GET['sf']!="" && $_GET['sw']!=""){

	switch ($_GET['sf']) {
		case "0":
			$sqr="SELECT Id FROM tbltaxonomia WHERE (... AND t.nombrecomun LIKE '$_GET['sw'])') GROUP BY ...;
			break;			
		...
	}
}
$resultprov=$plantasic->query($sqr);
$num=$resultprov->num_rows;

for ($i=0;$i<$num;$i++){	
	$resultprov->data_seek($i);
	$row=$resultprov->fetch_row();
	$plantasic->query("INSERT into tblIdsFiltered(Idfltrd, order) VALUES ($row[0],$i));
}	
$limit=$resultprov->num_rows;
$resultprov->close();
}



Once after the array is built:


if (isset($_GET['p']) && $_GET['p']>0 && $_GET['p']<=$limit){
	$_SESSION['p']=$_GET['p'];
}
else {
	$_SESSION['p']=1;
}

$position=$_SESSION['p'];
$result=$plantasic->query("SELECT * FROM tbltaxonomia JOIN tblIdsFiltered ON tbltaxonomia.Id=tblIdsFiltered.Id 
WHERE (tblIdsFiltered.order=$position)");



I would desire you say me which do you think that would be more efficient, or less ineficient, 1 or 2.

Options: ReplyQuote


Subject
Written By
Posted
Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship
January 18, 2011 04:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.