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 11, 2011 08:34AM

Rick thank you for your reply.

Yes, idinvoice should be not null: any article in tblarticles must belong to an invoice. Or rather, on a botanic database (it's the case, i used the invoice example searching a more common and less complex example), every vernacular name (in secondary table: tblnombresvernaculos, which would be tblarticles) must belong to a specie (in main table: tbltaxonomia, which would be tblinvoices). The foreign key in tblnomnresvernaculos is 'id', the same fieldname than primary key in the main table.

The same occurs for the other two (by now) secondary tables in the database: tblfotos and tblfoto. So i include their EXPLAIN SELECT... below:



> SHOW CREATE TABLE tbltaxonomia \G

mysql> show create table tbltaxonomia \G
*************************** 1. row ***************************
       Table: tbltaxonomia
Create Table: CREATE TABLE `tbltaxonomia` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `division` varchar(50) DEFAULT NULL,
  `subdivision` varchar(50) DEFAULT NULL,
  `clase` varchar(50) DEFAULT NULL,
  `subclase` varchar(50) DEFAULT NULL,
  `superorden` varchar(50) DEFAULT NULL,
  `orden` varchar(50) DEFAULT NULL,
  `familia` varchar(50) DEFAULT NULL,
  `subfamilia` varchar(50) DEFAULT NULL,
  `genero` varchar(50) DEFAULT NULL,
  `especie` varchar(61) DEFAULT NULL,
  `subVarFor` varchar(52) DEFAULT NULL,
  `nombreComun` varchar(52) DEFAULT NULL,
  `consistencia` varchar(20) DEFAULT NULL,
  `grupocultivo` varchar(50) DEFAULT NULL,
  `subgrupoCultivo` varchar(56) DEFAULT NULL,
  `aprovechamiento` varchar(50) DEFAULT NULL,
  `notas` longtext,
  `sinonimos` longtext,
  `fruto` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1787 DEFAULT CHARSET=utf8
1 row in set (0.55 sec)


> SHOW TABLE STATUS LIKE tbltaxonomia \G

mysql> show table status like 'tbltaxonomia' \G
*************************** 1. row ***************************
           Name: tbltaxonomia
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 769
 Avg_row_length: 2066
    Data_length: 1589248
Max_data_length: 0
   Index_length: 16384
      Data_free: 355467264
 Auto_increment: 1787
    Create_time: 2010-11-08 20:56:51
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.48 sec)




> EXPLAIN SELECT ...\G

mysql> explain select * from tblnombresvernaculos where id=34 order by orden \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tblnombresvernaculos
         type: ref
possible_keys: Id
          key: Id
      key_len: 5
          ref: const
         rows: 12
        Extra: Using where; Using filesort
1 row in set (0.58 sec)



mysql> explain select * from tblfotos where id=34 order by orden \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tblfotos
         type: ref
possible_keys: Id
          key: Id
      key_len: 4
          ref: const
         rows: 11
        Extra: Using where; Using filesort
1 row in set (0.60 sec)


mysql> explain select * from tblfoto where id=34 order by orden \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tblfoto
         type: ref
possible_keys: Id
          key: Id
      key_len: 5
          ref: const
         rows: 11
        Extra: Using where; Using filesort
1 row in set (0.52 sec)





> SHOW VARIABLES LIKE '%buffer%';

mysql> show variables like '%buffer%';

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size  | 1048576 |
| join_buffer_size        | 131072  |
| key_buffer_size         | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length       | 16384   |
| preload_buffer_size     | 32768   |
| read_buffer_size        | 65536   |
| read_rnd_buffer_size    | 262144  |
| sort_buffer_size        | 217088  |
| sql_buffer_result       | OFF     |
+-------------------------+---------+
12 rows in set (0.02 sec)



> No, this gets all the rows:
> "SELECT * FROM tblinvoices ORDER BY date"
> Maybe data_seek is pruning the list, but it is quite inefficient to get the whole list, then take one item. Perhaps you need a WHERE clause? Or a LIMIT clause?

I understand the problem. But the page (index.php) has a navigation bar and a form for searching groups of records. The working of navigation bar is this:

<img src="imags/auxil/prev5.jpg" alt="" onClick="decrement()">
<form name="formNavBar" style="margin-bottom:0px; vertical-align:0px;" action=""><div id="formularioNav"><input type="text" id="p" name='p' 
size='4' value='<?php echo $_SESSION['p'] ?>' class='estiloNavBarDark'></div></form>
<img alt="" src="imags/auxil/next5.jpg" onClick="increment()">



That is, a button (image) 'previous', a textfield for going to a specific record, and a button (image) 'next'. The buttons previous and next call to a javascript function (decrement and increment), which i include here:

function increment(){
	var variable = document.formNavBar.p.value;
	variable++;
	var pagina="index.php?p="+variable;
	window.location = pagina;
}
function decrement(){
	var variable = document.formNavBar.p.value;
	if (variable>1){
	variable--;
	}
	var pagina="index.php?p="+variable;
	window.location = pagina;
}


And the way of displaying the desired record is catching the 'p' variable at the beginning of the page index.php and passing it to the method data_seek():


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


So, i would need to have stored in somewhere the current searched recordset (perhaps an array??) and retieving the possition of the array with the values of 'p' variable.

Would this way (an array) be the best for that working purpose? I think this issue, according to your orientations, is the most important one for the efficience of the aplication.

By the way, the webpage is www.plantasic.es.

Options: ReplyQuote


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


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.