Multiple queries in a while loop problem

[tijn]

Hi, I`m kinda new with php/mysql and I am trying to make a while loop with some queries inside, but it can only make  a few (slow) loops. It blocks when I`m asking for more records /loops.

 

I have no idea if the problem is in the loops or that I am missing something in the database…that`s why I`ll post them both.  (MySQl_error returned me “0 :0 :0 “, I guess that’s  ok) Hope somebody can see the problem…..

 

<div id=container>
<?php
include "connect.php"; 	


$b=1; 

//LOOP///////////////////////////////////// 

while ($b < 12){ 


echo"<DIV id=frame>"; 

//company///////////////////////////////// 



$query1 = "SELECT companyname 
    	From company 
   	where comp_id=".$b." LIMIT 0, 30 ";		

$result = mysql_query("SELECT companyname FROM company", $connectie);


$sql = mysql_query($query1) or die ( mysql_error( ) ); 


echo"<div id=\"contentleft\" class=\"floatleft\">";

while($record = mysql_fetch_object($sql)){ 

echo"<table><tr><td>".$record->companyname."</td></tr>";  
} 
echo"</table></div>"; 


// reference/////////////////////////////// 
  

$query2 = "SELECT reference\n"
    . "FROM reference\n"
    . "JOIN companyref ON reference.ref_id = companyref.ref_id\n"
   . "where companyref.comp_id=$b";			

$sql = mysql_query($query2) or die ( mysql_error( ) ); 

echo"<div id=\"contentmid\" class=\"floatleft\">"; 

echo"<table><tr><td>references</td></tr>"; 

while($record = mysql_fetch_object($sql)){ 

echo"<tr><td>".$record->reference."</td></tr>"; 
} 
echo"</table></div>"; 


// technology////////////////////////////// 
  

$query3 = "SELECT technology\n"
    . "FROM technology\n"
    . "JOIN companytech ON technology.tech_id = companytech.tech_id\n"
    . "where companytech.comp_id=$b LIMIT 0, 30 ";			

$sql = mysql_query($query3) or die ( mysql_error( ) ); 

echo"<div id=\"contentright\" class=\"floatleft\">"; 

echo"<table><tr><td>technology</td></tr>"; 

while($record = mysql_fetch_object($sql)){ 

echo"<tr><td>".$record->technology."</td></tr>"; 

} 

echo"</table></div>";


//LOOP//////////////////////////////////

$b++;

}
?>
</div>

 

 

 

-- Tabel structuur voor tabel `company`

--

 

DROP TABLE IF EXISTS `company`;

CREATE TABLE IF NOT EXISTS `company` (

  `comp_id` smallint(5) NOT NULL auto_increment,

  `companyname` varchar(50) default NULL,

  `publishYN` enum('Y','N') default NULL,

  `lastupdate` date default NULL,

  `contractortype` varchar(50) default NULL,

  `streetname` varchar(50) default NULL,

  `streetnumber` varchar(5) default NULL,

  `zipcode` varchar(10) default NULL,

  `city` varchar(50) default NULL,

  `province` varchar(50) default NULL,

  `country` varchar(50) default NULL,

  `numberofemployees` int(20) default NULL,

  `turnover` int(20) default NULL,

  `logo` varchar(255) default NULL,

  `image` varchar(255) default NULL,

  PRIMARY KEY  (`comp_id`),

  KEY `companyname` (`companyname`),

  KEY `companyname_2` (`companyname`)

) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 AUTO_INCREMENT=50 ;

 

-- --------------------------------------------------------

 

--

-- Tabel structuur voor tabel `companyref`

--

 

DROP TABLE IF EXISTS `companyref`;

CREATE TABLE IF NOT EXISTS `companyref` (

  `comp_id` smallint(5) NOT NULL,

  `ref_id` smallint(5) NOT NULL,

  KEY `comp_id` (`comp_id`,`ref_id`),

  KEY `ref_id` (`ref_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

-- --------------------------------------------------------

 

--

-- Tabel structuur voor tabel `companytech`

--

 

DROP TABLE IF EXISTS `companytech`;

CREATE TABLE IF NOT EXISTS `companytech` (

  `comp_id` smallint(5) NOT NULL,

  `tech_id` smallint(5) NOT NULL,

  KEY `comp_id` (`comp_id`,`tech_id`),

  KEY `tech_id` (`tech_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

-- --------------------------------------------------------

 

--

-- Tabel structuur voor tabel `contact`

--

 

DROP TABLE IF EXISTS `contact`;

CREATE TABLE IF NOT EXISTS `contact` (

  `cont_id` smallint(5) NOT NULL auto_increment,

  `comp_id` smallint(5) NOT NULL,

  `genderMF` enum('m','v') default NULL,

  `lastname` varchar(50) default NULL,

  `firstname` varchar(30) default NULL,

  `streetname` varchar(30) default NULL,

  `streetnumber` varchar(5) default NULL,

  `zipcode` varchar(10) default NULL,

  `city` varchar(50) default NULL,

  `province` varchar(50) default NULL,

  `country` varchar(50) default NULL,

  `telephone` varchar(20) default NULL,

  `fax` varchar(20) default NULL,

  `mobilephone` varchar(20) default NULL,

  `emailadres` varchar(50) default NULL,

  PRIMARY KEY  (`cont_id`),

  KEY `comp_id` (`comp_id`),

  KEY `comp_id_2` (`comp_id`)

) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 AUTO_INCREMENT=50 ;

 

-- --------------------------------------------------------

 

--

-- Tabel structuur voor tabel `reference`

--

 

DROP TABLE IF EXISTS `reference`;

CREATE TABLE IF NOT EXISTS `reference` (

  `ref_id` smallint(5) NOT NULL auto_increment,

  `reference` varchar(100) default NULL,

  PRIMARY KEY  (`ref_id`),

  KEY `reference` (`reference`)

) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;

 

-- --------------------------------------------------------

 

--

-- Tabel structuur voor tabel `technology`

--

 

DROP TABLE IF EXISTS `technology`;

CREATE TABLE IF NOT EXISTS `technology` (

  `tech_id` smallint(5) NOT NULL auto_increment,

  `technology` varchar(100) default NULL,

  PRIMARY KEY  (`tech_id`),

  KEY `technology` (`technology`)

) ENGINE=InnoDB AUTO_INCREMENT=146 DEFAULT CHARSET=latin1 AUTO_INCREMENT=146 ;

 

 

--

-- Beperkingen voor tabel `companyref`

--

ALTER TABLE `companyref`

  ADD CONSTRAINT `companyref_ibfk_1` FOREIGN KEY (`comp_id`) REFERENCES `company` (`comp_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT `companyref_ibfk_2` FOREIGN KEY (`ref_id`) REFERENCES `reference` (`ref_id`) ON DELETE CASCADE ON UPDATE CASCADE;

 

--

[trq]

Running multiple queries within loops is never a good idea. Its not at all efficient and its likely your script is timing out.

 

Take a look at UNIONS and JOINS (there is a tutorial on our main site) and you should be able to get the data you wont within one query.

[tijn]

I really needed a direction, so THANKS A LOT!

 

I `ll go for the tuturials……