Mysql calls within loop

[ElmoTheClown]

Apparently, this is bad.  Wish someone would have told me.

 

Sadly, it is too simple.

 

I had

<?
$company=mysql_query("select this and that")
while($company=mysql_fetch_array($company)) {
   a bit of output;
   $properties=mysql_query("Select this and that from properties where company_id=$company['id']")
   while(properties=mysql_fetch_array()) {
        what I want to output
    }
}

I seem to be missing some understanding of what the heck to do now.

 

Please, please any suggestions.  I suck at object orientation, and arrays of arrays of arrays confuse the crap out of me.  Am I missing something simple.

 

Am I over-reacting?  I have 15 companies, and about 100 properties total.

 

[cunoodle2]

Please use the "(code)" tags on your code.  Also you should look at using an innerjoin and just do 1 query.  Honestly it will be much easier to help if I saw the actual queries.

 

I.E..

SELECT company.id, company.name, property.id, property.name, property.price FROM `company` INNER JOIN property on property.companyid = company.id

 

There are ways to shorten that using "AS" but lets just start with that for right now..

[ElmoTheClown]

Thank you.

 

$resMenuLines=mysql_query("SELECT linName, line_id 
					FROM line 
					ORDER BY linName") or die(mysql_error());
while($rsMenuLines=mysql_fetch_array($resMenuLines)) {
        $lineName=$rsMenuLines[linName];
$resMenuShips=mysql_query("SELECT shiName,shiMates,shiBlogURL 
						FROM ship 
						WHERE line_id=".$rsMenuLines['line_id']." 
						ORDER BY shiName") or die(mysql_error());
$shipDisplay=array();
while($rsMenuShip=mysql_fetch_array($resMenuShips)){
	$shipName=$rsMenuShip['shiName'];
	$shipHyper=F_crunch($rsMenuShip['shiName']);
	if($rsMenuShip['shiMates']) {
			$mates=$rsMenuShip['shiMates'];
			$blogs=$rsMenuShip['shiBlogURL'];
			$shipDisplay[$mates] = $blogs;
	}
	else $shipDisplay[$shipName] = $shipHyper;
}
array_unique($shipDisplay);
foreach ($shipDisplay as $thePage=>$theLink){
	echo "<a href='$H_absURL"."info/".F_crunch($rsMenuLines['linName'])."/$theLink/'>$thePage</a>";
}			
}

 

I was thinking:

SELECT line.linName, line.line_id, ship.shiName,ship.shiMates,ship.shiBlogURL 
FROM line, ship 
WHERE line.line_id=ship.ship_id 
ORDER BY linName

But I can't seem to comprehend how the heck to get it to return:

Company property property, property.

 

 

 

[cunoodle2]

You have this..

SELECT line.linName, line.line_id, ship.shiName,ship.shiMates,ship.shiBlogURL 
FROM line, ship 
WHERE line.line_id=ship.ship_id 
ORDER BY linName

 

Use INNER JOIN instead of "WHERE line.line_id=ship.ship_id" as your method will take much much longer/resources especially once you get into very large data sets.

 

Also for the Company property, property propery item add in a "ORDER BY company, property;" at the end.  So your query should look something like this..

 

SELECT line.linName as line_name, line.line_id, ship.shiName as ship_name ,ship.shiMates,ship.shiBlogURL 
FROM line
INNER JOIN ship on ship.ship_id=line.line_id
ORDER BY line_name, ship_name;

 

Then do a loop to go through all items.  Roughly..

<?php
$temp = "";
while($rsMenuShip=mysql_fetch_array($resMenuShips))
{
if ($temp == "")
$temp = $rsMenuShip['line_name'];
else if ($temp != $rsMenuShip['line_name'])
{
echo "<br />New line name{$rsMenuShip['line_name']} starts here. <br />\n";
echo "Here are the associated ships.. <br />\n";
$temp = $rsMenuShip['line_name'];
}
echo $rsMenuShip['ship_name']."<br />\n";
}
?>

[ElmoTheClown]

Thank you.

 

That's the first time I have used INNER JOIN.  I had the hardest time understanding it, and found WHERE to work just fine most times (until I found our I was not supposed to have a call within a loop).

 

"as" still doesn't work in my scripts.  Works fine in the mysql admin query box, but not in my scripts, but I make table names I understand. 

 

And curly brackets worked too... WOOOT I never could get them to work for me and always ended up ".$rs['this']." it.

 

So, thank you, and thank you.  You gave me a clearcut way of doing it.  Check out one of the things I have been trying to work with to do the same thing:

function search($array, $key, $value)
{
    $results = array();

    if (is_array($array))
    {
        if ($array[$key] == $value)
            $results[] = $array;

        foreach ($array as $subarray)
            $results = array_merge($results, search($subarray, $key, $value));
    }

    return $results;
}

$arr = array(0 => array(id=>1,name=>"cat 1"),
       1 => array(id=>2,name=>"cat 2"),
       2 => array(id=>3,name=>"cat 1"));

print_r(search($arr, 'name', 'cat 1'));

 

WTH?

 

Thanks!

[ElmoTheClown]

Thanks again for your code!

 

The rest scratched because I muffed it up. 

 

The first line name called was omitted.

edited out my screwed up code correction.