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 14, 2011 08:54AM

Rick thank you very much.

And about showing, for example, the record number 40 (according to that order) of the 50 records with family='Gramineae', is it possible?

I am thinking about a 4th possibility: 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)");


You say your last formulation won't be very efficient, because of the several ORs. Can you say me the inneficience order of the four alternatives considered in this thread?: Your last formulation, the array formulation with 2.000-3.000 species, the table formulation for 2.000-3.000 species, and the original formulation (fetching all the species and not only the one that i want to show)?



Edited 2 time(s). Last edit at 01/14/2011 12:09PM by Kostas Telias.

Options: ReplyQuote


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


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.