multiple queries into one???

planetphp

Hi,

I have a links page where links are stored in a mysql db. The links are broken up into categories. At the moment I have a seperate query statement and while loop to list all the links from each category. This is messy and increases db queries...

Is there a way that I can make mutiple queries in one statment and then place the results relevent to its category...?

Please advise!

This is the current code... as you can see its just a repeat of each piece of code with a where clause relevent to the category! I suspect this is going to be the only way I can do it...

<?php
// uk sites
$link_id = db_connect("planetdnb");
$query = "select * from links where category='UK SITES' order by link asc";
$result = mysql_query($query);
while($myrow = mysql_fetch_row($result))
{
$site = $myrow[1];
$desc = $myrow[2];
$url = $myrow[3];

echo "<img src=\"http://www.planetdnb.com/images/page_gfx/link.gif\"><a href=\"$url\" target=\"_blank\">$site</a> - $desc ";
}
?>
 
<center><img src="http://www.planetdnb.com/images/page_gfx/section_titles/europe_sites.gif"> </center>

<?php
// european sites
$query = "select * from links where category='EUROPEAN SITES' order by link asc";
$result = mysql_query($query);
while($myrow = mysql_fetch_row($result))
{
$site = $myrow[1];
$desc = $myrow[2];
$url = $myrow[3];

<?php
// world sites
$query = "select * from links where category='WORLD SITES' order by link asc";
$result = mysql_query($query);
while($myrow = mysql_fetch_row($result))
{
$site = $myrow[1];
$desc = $myrow[2];
$url = $myrow[3];

<?php
// pirate sites
$query = "select * from links where category='PIRATE/INTERNET RADIO' order by link asc";
$result = mysql_query($query);
while($myrow = mysql_fetch_row($result))
{
$site = $myrow[1];
$desc = $myrow[2];
$url = $myrow[3];

<?php
// events
$query = "select * from links where category='EVENT SITES' order by link asc";
$result = mysql_query($query);
while($myrow = mysql_fetch_row($result))
{
$site = $myrow[1];
$desc = $myrow[2];
$url = $myrow[3];

<?php
// artists
$query = "select * from links where category='ARTIST SITES' order by link asc";
$result = mysql_query($query);
while($myrow = mysql_fetch_row($result))
{
$site = $myrow[1];
$desc = $myrow[2];
$url = $myrow[3];

<?php
// labels
$query = "select * from links where category='LABEL SITES' order by link asc";
$result = mysql_query($query);
while($myrow = mysql_fetch_row($result))
{
$site = $myrow[1];
$desc = $myrow[2];
$url = $myrow[3];

<?php
// shops
$query = "select * from links where category='ONLINE SHOPS' order by link asc";
$result = mysql_query($query);
while($myrow = mysql_fetch_row($result))
{
$site = $myrow[1];
$desc = $myrow[2];
$url = $myrow[3];

<?php
// various
$query = "select * from links where category='VARIOUS SITES' order by link asc";
$result = mysql_query($query);
while($myrow = mysql_fetch_row($result))
{
$site = $myrow[1];
$desc = $myrow[2];
$url = $myrow[3];

benbox

hiya,

This is unrelated to your Q but tis a tip for u or everyone else who duznt know bout it 😉

bcos $myrow[1]; can get confusing (which ever field number it is in the db table) and if you alter the table setup it will mess everything up...

if instead of: while($myrow = mysql_fetch_row($result))
you do: while($myrow = mysql_fetch_array($result))

you can name the field like this: $site = $myrow["fieldname"];

it just makes it a lot easier and you dont exactly have to learn anything to do it 😃

this may be useful to you :p

no1uknowdfw

how 'bout this...

$link_id = db_connect("planetdnb");
$query1 = "select from links where category='UK SITES' order by link asc";
$query2 = "select from links where category='EUROPEAN SITES' order by link asc";
$query3 = "whatever query and so on...";
$result1 = mysql_query($query1);
$result2 = mysql_query($query2);
$result3 = mysql_query($query3);
while(($myrow1 = mysql_fetch_row($result1)) && ($myrow2 = mysql_fetch_row($result2)) &&($myrow3 = mysql_fetch_row($result3)))
{
$site1 = $myrow1[1];
$desc1 = $myrow1[2];
$url1 = $myrow1[3];
$site2 = $myrow2[1];
$desc2 = $myrow2[2];
$url2 = $myrow2[3];
$site3 = $myrow3[1];
$desc3 = $myrow3[2];
$url3 = $myrow3[3];

}

To bring url1, url2 and url3 together as URL would require some common primary field if you want to sort or something...

Does this help?

Lars_Berg

This type of solution is untenable. What if you add a new category?

$query = "select category,site,desc,url from links order by category,link asc"; 
$result = mysql_query($query); 
$category = "";
while($myrow = mysql_fetch_row($result)) 
{ 
if  ( $category != $myrow["category"] ) {
    $category = $myrow["category"];
    echo $category . "<br>";
}
$site = $myrow[1]; 
$desc = $myrow[2]; 
$url = $myrow[3]; 

echo "<p style='margin-left: 10; margin-top: 0; margin-bottom: 0'><font face=\"arial\" size=\"2\" color='#ffffff'><img src=\"http://www.planetdnb.com/images/page_gfx/link.gif\"><a href=\"$url\" target=\"_blank\">$site</a> - $desc<br></font>"; 
} 
?>

One problem is that you will not get the same order as in your script. This can be solved by storing categories in a separate table (which should be done anyway) and have a sort column in that table. The links table should only contain a foreign key reference to the category table.

Weedpacket

It's probably easier if you get the data from the database, get it into
shape and then display it.

$query="select category,etc. from links order by link";
$results=mysql_query($query);
while($myrow = mysql_fetch_array($results))
{ $links[$myrow['category']][]=$myrow;
  $categories[] = $myrow['category'];
}

After that little lot, $links['UK SITES'] will contain a list of arrays, each containing the details for a UK site, so you can go

foreach($links['UK SITES'] as $link)
{ echo "whatever $link['link'] $link['url'] whatever...";
}

And $categories will be a list of the categories (in order), should you
want to loop over them:

<?php
foreach($categories as $category)
{ foreach($links[$category] as $link)
  { ....etc.etc.
  }
}
?>