Re: Showing recordsets from tables with one (main table) to many (secondary table) relationship
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.