[RESOLVED] MySQL skips result, errno 0

[Ark3typ3]

I built a while loop that iterates through a typeID that is then altered by a switch based on the resource id returned by a mysql query. This works, however, it will hit a point where it will suddenly skip over a result and then all the following results will be the original value without being modified.

while($row = mysql_fetch_array($materialsResult)){

$typeNameResult = mysql_query("SELECT typeName FROM invTypes WHERE typeID='$row[materialTypeID]'");
$temp = mysql_fetch_row($typeNameResult);
$typeNameResult = $temp[0];

$materialIcon = mysql_query("SELECT icon FROM eveGraphics WHERE graphicID IN 
				(SELECT graphicID FROM invTypes WHERE typeID='$row[materialTypeID]')");
$tempMaterialIcon = mysql_fetch_row($materialIcon);
$materialIcon = $tempMaterialIcon[0];

$parentQtyQuery = mysql_query("SELECT quantity FROM invTypeMaterials WHERE materialTypeID='$row[materialTypeID]' AND typeID IN 
				(SELECT parentTypeID FROM invMetaTypes WHERE typeID='".$row['typeID']."')") or die(mysql_error());
$parentQtyArr = mysql_fetch_array($parentQtyQuery);// or die("MySQL error ".mysql_errno().": ".mysql_error()."\n<br>When executing: \n$parentQtyQuery\n<br>");
$wasteQuery = mysql_query("SELECT wasteFactor FROM invBlueprintTypes WHERE productTypeID='".$row['typeID']."'");
$wasteFactor = mysql_fetch_array($wasteQuery);

if($parentQtyArr['quantity'] <= 0){
	$techmod[]=0;
} else {
	$techmod[]=$parentQtyArr['quantity'];
}

//Material Prices from evePrices object
switch($row[materialTypeID]){
	//Mineral Prices
	case 34: $price = $mineralArr[0]; $mod=$techmod[0]; break; //Tritanium
	case 35: $price = $mineralArr[1]; $mod=$techmod[1]; break; //Pyerite
	case 36: $price = $mineralArr[2]; $mod=$techmod[2]; break; //Mexallon
	case 37: $price = $mineralArr[3]; $mod=$techmod[3]; break; //Isogen
	case 38: $price = $mineralArr[4]; $mod=$techmod[4]; break; //Nocxium
	case 39: $price = $mineralArr[5]; $mod=$techmod[5]; break; //Zydrine
	case 40: $price = $mineralArr[6]; $mod=$techmod[6]; break; //Megacyte
	//No price data available
	default: $price = 0; $mod=0; break;
}

if($row['quantity']-$mod <= 0){
	$quantity = 0;
} else {
	$quantity = $row['quantity']-$mod;
}
//create array of total
$total[] = ($quantity*(1+($wasteFactor['wasteFactor']/100)))*$qty*$price;

echo "<tr>
	<td><img src='http://leafexcavations.com/eveimg/icons/16_16/icon".$materialIcon.".png' /></td>
	<td>" .$typeNameResult. "</td>
	<td>" . number_format(round($quantity*(1+($wasteFactor['wasteFactor']/100)),0)*$qty,0) . "</td>
	<td>" . number_format(round($quantity*(1+($wasteFactor['wasteFactor']/100)),0)*$qty*$price,2) . "</td>
</tr>";

}

It varies depending on the number of results which leads me to believe that something isn't matching up with the iterations and eventually they over lap and the iteration fails. I don't know, I've been staring at this for a little while now and I need a pair of fresh eyes on it.

The line in question is $parentQtyArr, I commented out the die stuff because I was using the page in the meantime. It returns Error number 0 on resource ID 37 for some, and 43 for others (there is not even a 43 resource...)

[Weedpacket]

I haven't seen the query that produces the $materialsResult array nor the $mineralArr and $techmod arrays, but I'm almost certain that all of those queries could be combined with it so that you only have the one array to iterate through that contains exactly the details for each row of the display.

[Ark3typ3]

do you think that's causing the issue?

[Ark3typ3]

Alright, solved. If anyone stumbles across this I just took the parent queries and iterated them separately, injected them along with the prices into a multidimensional array and used that array to list prices in the while loop above.

Thanks Weedpacket 🙂

[Weedpacket]

Ark3typ3 wrote:

do you think that's causing the issue?

I reckon that's likely behind it, yes.

Good to hear you got it working; however, I still think all of the information used in the table could be retrieved in a single query. That would be much more efficient by taking advantage of the database's indexes and the DBMS's ability to optimise the query, and it would also ensure that data that should be related is related. Something like (there are alternatives):

Select
	t.typeName,
	g.icon,
	tm.quantity,
	bt.wasteFactor

From
	invTypes          t,
	eveGraphics       g,
	invTypeMaterials  tm,
	invBlueprintTypes bt,
	invMetaTypes      mt

Where
             t.typeID = $row[materialTypeID]
and         mt.typeID = $row[typeID]
and       g.graphicID = t.graphicID
and tm.materialTypeID = t.typeID
and         tm.typeID = mt.parentTypeID
and  bt.productTypeID = mt.typeID

Although in actual fact this would be combined with the query that's producing the $materialsResult recordset.

The switch statement ("from the evePrices object") and total price calculations could potentially also be built directly into the query, reducing this loop to a couple of "fetch_assoc(); echo;" lines.

[Ark3typ3]

Thanks, I'm all for cleaner code and I'll give it a shot. I figured you meant something like that I just didn't know how to implement it so that will be very helpful.