...

View Full Version : strange mysql problem



LJackson
06-30-2011, 12:08 AM
I am having great difficulty understanding how the mysql works simply because i have two almost identical querys


SELECT * FROM tbl_cds WHERE cdBinding = 'audio cd' AND cdReleaseDate <= NOW()


SELECT * FROM tbl_cds WHERE cdBinding = 'cd' AND cdReleaseDate <= NOW()

the problem is the first one returns a mysql_query(): Unable to save result set in ... on line 198 MySQL client ran out of memory BUT the first one works as expected... its not the number of records because the first one used to hold twice as many records as the second one (say 60000 in total) and it was still worked fine but the second one i cant remember ever working.

its all very strange, ive spent ages going over my code and have even removed all other queries but i still get this error :(

kinda pulling my hair out at this now

for record purposes
the first query returns 55148 rows at present
the second query returns 36936 rows at present

does the space in the cdBinding make any difference? thats all i can think of

any help is greatly appreciated

LJackson
06-30-2011, 12:57 AM
my code is


<?php
header('Content-type: text/html; charset=utf-8');
$cat = mysql_real_escape_string($_GET['cat']);
$bin = mysql_real_escape_string($_GET['bin']);
$age = mysql_real_escape_string($_GET['age']);
$search = mysql_real_escape_string($_GET['res']);
include_once('dbinfo.php');
include_once('paginator.class.php');
$pagepath = "/cd_new.php";
$menuitems = array(1,2);
$parentid = 2;
$pageid = "cd";
$startQuery = "SELECT * FROM tbl_cds";
$def = " AS cds ";
$field = "cdReleaseDate";
$endingQuery = "cdReleaseDate <= NOW()";
$direction = "DESC";
$gap = " WHERE ";
$includeStart = 0;
if($bin)
{
$bread = $bread.$bin;
$query = $query.$gap."cdBinding = '$bin'";
$gap = " AND ";
}
if($search)
{
$bread = $bread.$search;
switch($search)
{
case "bestsellers";
$query = $query."";
$searchName = "";
#$gap = " AND ";
break;
case "new releases";
$query = $query."";
$searchName = "cdReleaseDate <= NOW()";
$endingQuery = "cdReleaseDate <= NOW()";
#$gap = " AND ";
break;
case "pre-orders";
$direction = "ASC";
$query = $query."";
$endingQuery = "cdReleaseDate > NOW()";
$searchName = "cdReleaseDate > NOW()";
#$gap = " AND ";
break;
case "box sets";
$startQuery = $startQuery.$def." LEFT JOIN ( SELECT cdID, formatID FROM tbl_product_format WHERE formatID = '4') AS format ON cds.filmID = format.filmID ";
$query = $query.$gap."formatID = '4'";
$searchName = " formatID = '4'";
$def = "";
$gap = " AND ";
break;
case "compilations";
$startQuery = $startQuery.$def." LEFT JOIN ( SELECT cdID, formatID FROM tbl_product_format WHERE formatID = '4') AS format ON cds.cdID = format.filmID ";
$query = $query.$gap."formatID = '4'";
$searchName = " formatID = '4'";
$def = "";
$gap = " AND ";
break;

}
}
if($cat)
{
$bread = $bread.$cat;
$startQuery = $startQuery.$def." LEFT JOIN( SELECT prodID, deptID FROM tbl_product_departments WHERE deptID = '$cat') AS dept ON cds.cdID = dept.prodID ";
$query = $query.$gap."deptID = '$cat'";
$gap = " AND ";
}
$finalQuery = $startQuery.$query.$gap.$endingQuery;
$finalQuery = preg_replace('/\s\s+/', ' ', $finalQuery);

function ShortenText($text)
{
// Change to the number of characters you want to display
$chars = 70;
$len = strlen($text);
$text = $text." ";
$text = substr($text,0,$chars);
$text = substr($text,0,strrpos($text,' '));
if($len > $chars)
{
$text = $text."...";
}
return $text;
}
function reset_link_builder($remove,$pagepath)
{
$resetLink = '';
$varToRemove = $remove;
if(count($_GET) > 1)
{
foreach($_GET as $variable => $value)
{
if($variable != $varToRemove)
{
$resetLink .= $variable.'='.$value.'&';
}
}
$resetLink = "?".$resetLink;
}
else
{
$resetLink = $pagepath;
}
$resetLink = rtrim($resetLink,'&');
return $resetLink;
}

include("functions.php");?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta name="google-site-verification" content="BUs-Ts-JsTOoTdN_VHIA47zzZivSsl1vBx4l9ngo0Bc" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Kernow Connect - Bestselling DVDs, DVD Pre-Orders, Latest Releases, DVD Box Sets, Official DVD Charts, Bargain DVDs </title>
<!--[if IE 6]>
<style>
body {behavior: url("csshover3.htc");}
#menu li .drop {background:url("img/drop.gif") no-repeat right 8px;
</style>
<![endif]-->
<link rel="stylesheet" type="text/css" href="contact_data/jqtransformplugin/jqtransform.css" />
<link rel="stylesheet" type="text/css" href="contact_data/formValidator/validationEngine.jquery.css" />
<link rel="stylesheet" type="text/css" href="css/new_css.css" />
<script type="text/javascript" src="contact_data/jqtransformplugin/jquery.jqtransform.js"></script>
<script type="text/javascript" src="contact_data/formValidator/jquery.validationEngine.js"></script>
<script type="text/javascript" src="contact_data/script.js"></script>
</head>

<body>
<?php
if(isset($_POST['submit']))
{
switch($field)
{
case "Release Date";
$field = "cdReleaseDate";
$direction = "DESC";
break;
case "Name A-Z";
$field = "cdName";
$direction = "ASC";
break;
case "Name Z-A";
$field = "cdName";
$direction = "DESC";
break;
case "Price High to Low";
$field = "cdReleaseDate";
$direction = "DESC";
break;
case "Price Low to High";
$field = "cdReleaseDate";
$direction = "DESC";
break;
case "Saving";
$field = "cdReleaseDate";
$direction = "DESC";
break;
}
}?>
<div id="wrap">
<?php #include("menu_drop.php")?>

<div class="taller_new">
<?php
include("tree_menutest2.php");
?>
<div class="right">
<div class='pageheader'>CDs<span class='psContainer'>
<form action='' method='post' name='sort_form' target='_self'>
<label>Sort By: </label>
<select name='sort'>
<option>Release Date</option>
<option>Name A-Z</option>
<option>Name Z-A</option>
<option>Price High to Low</option>
<option>Price Low to High</option>
<option>Saving</option>
</select>
<input name='submit' type='submit' value='Submit' />
</form></span>
</div><?php

echo "<pre>".$finalQuery."</pre>";
$objQuery = mysql_query($finalQuery)or die(mysql_error());
#$rows=mysql_fetch_array($objQuery);
$numrows = mysql_num_rows($objQuery);
#echo "<h1>$numrows</h1>";
// number of rows to show per page
$rowsperpage = 20;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage']))
{
// cast var as int
$currentpage = (int) $_GET['currentpage'];
}
else
{
// default page num
$currentpage = 1;
}

// if current page is greater than total pages...
if ($currentpage > $totalpages)
{
// set current page to last page
$currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
// set current page to first page
$currentpage = 1;
}

// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage;

$finalQuery = $finalQuery." ORDER BY $field $direction LIMIT $offset, $rowsperpage";
$result = mysql_query($finalQuery);

// while there are rows to be fetched... and publish data.... 25 rows.
while ($list = mysql_fetch_array($result))
{
$id = $list['cdID'];
$rrp = $list['cdRRP'];
$prices = mysql_query("SELECT * FROM tbl_prices WHERE prodID = '$id' && prodPrice <> '0.00' ORDER BY prodPrice ASC LIMIT 0,1")or die(mysql_error());
$queryCount ++;
$row = mysql_fetch_array($prices);
$cheapest = $row['prodPrice'];
if($rrp <> "0.00" && $rrp <> "")
{
$rrp = "".$list['cdRRP'];
if($cheapest)
{
$rrp = $list['cdRRP'];
$cheapest = "".$cheapest;
$saving = number_format($rrp-$row['prodPrice'],2);
$percentage = ( $saving / $rrp) * 100;
$percentage = number_format($percentage,0);
$percentage = $percentage."%";
$saving = "".$saving;
$rrp = "".$list['cdRRP'];
}
else
{
$saving = "???";
$percentage = "???";
$cheapest = "???";
}
}
else
{
if($cheapest)
{
$cheapest = "".$cheapest;
}
else
{
$cheapest = "???";
}
$rrp = "???";
$saving = "???";
$percentage = "???";
}

$title = strtolower($list['cdName']);
$title = preg_replace("/\s*\(.*\)|\s*\[.*\]|/U", "", $title);
$title = htmlspecialchars_decode($title);
$title = trim($title," -");
$title2 = str_replace("/"," ",$title);
$title3 = preg_replace("/[^\sA-Za-z0-9]/"," ", $title2);
$title3 = preg_replace('/\s\s+/', ' ', $title3);
$title3 = str_replace(" ","-",$title3);?>

<div class="prod_container">
<?php
if($list['cdIMG'] == '')
{?>
<a href='<?php echo "/cd/".$id."/".$title3?>'>
<img class="prodIMG" src="/images/dvdNOIMG.png" alt="<?php echo ucwords($title)?>" title="<?php echo ucwords($title)?>" border="0" width="120" />
</a><?php
}
else
{?>
<a href='<?php echo "/cd/".$id."/".$title3?>'>
<img class="prodIMG" src="<?php echo $list['cdIMG']?>" alt="<?php echo ucwords($title)?>" title="<?php echo ucwords($title)?>" border="0" width="120" />
</a><?php
}?>
<h2 class="prodTitle"><a href='<?php echo "/cd/".$id."/".$title3?>'><?php echo ShortenText(ucwords($title))?></a></h2>
<div class="prodBinding">Release Date: <?php echo date("d F Y",strtotime($list['cdReleaseDate']))?></div>
<div class="prodDetails">
<?php
#switch($list['cdBinding'])
#{
# case "CD";
# $catLink = "/dvd_new.php?bin=dvd";
# break;
# case "Blu-Ray";
# $catLink = "/dvd_new.php?bin=blu-ray";
# break;
# case "3D";
# $catLink = "/dvd_new.php?bin=3d";
# break;
#}
echo "Format: <a href='$catLink'>".$list['cdBinding']."</a>"?>
<br /><br /><span style="font-size:11px; color:#999999;">RRP: <span style="text-decoration:line-through"><?php echo $rrp?></span> Saving: <?php echo $saving." ($percentage)"?></span><br />
<span style="font-size:11px;">From:</span>
<span style="font-size:18px; font-weight:bold; color:#FF6600;"><?php echo $cheapest?></span>
</div>
</div><?php
}
echo $queryCount;

/****** build the pagination links ******/
// range of num links to show
$range = 4;
$pglink = '';
$divider = "?";
if($bin)
{
$pglink = $pglink.$divider."bin=$bin";
$divider = "&amp;";
}
if($search)
{
$pglink = $pglink.$divider."res=$search";
$divider = "&amp;";
}
if($age)
{
$pglink = $pglink.$divider."age=$age";
$divider = "&amp;";
}
if($cat)
{
$pglink = $pglink.$divider."cat=$cat";
$divider = "&amp;";
}

echo "<div class='pagination_container'>";
echo "<ul id=\"pagination-digg\">";
// if not on page 1, don't show back links
if ($currentpage > 1)
{
// get previous page num
echo " <li class=\"next\"><a href=$pglink&amp;currentpage=1'> First page</a><li> ";
$prevpage = $currentpage - 1;
// show < link to go back to 1 page
echo " <li class=\"next\"><a href=$pglink&amp;currentpage=" . $prevpage ."> Previous</a><li> ";
} // end if
else
{
// get previous page num
echo " <li class=\"nextdis\"> First page<li> ";
$prevpage = $currentpage - 1;
// show < link to go back to 1 page
echo " <li class=\"nextdis\"> Previous<li> ";
}

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++)
{
// if it's a valid page number...
if (($x > 0) && ($x <= $totalpages))
{
// if we're on current page...
if ($x == $currentpage)
{
// 'highlight' it but don't make a link
echo " <li class=\"active\">". $x . "</li> ";
// if not current page...
}
else
{
// make it a link
echo " <li><a href=$pglink&amp;currentpage=". $x . ">$x</a><li> ";
} // end else
} // end if
} // end for

// if not on last page, show forward and last page links
if ($currentpage != $totalpages)
{
// get next page
$nextpage = $currentpage + 1;
// echo forward link for next page
echo " <li class=\"next\"><a href=$pglink&amp;currentpage=". $nextpage . ">Next </a></li> ";
echo " <li class=\"next\"><a href=$pglink&amp;currentpage=". $totalpages . ">Last page </a></li> ";
} // end if
else
{
$nextpage = $currentpage + 1;
// echo forward link for next page
echo " <li class=\"nextdis\">Next </li> ";
echo " <li class=\"nextdis\">Last page </li> ";
}
/****** end build pagination links ******/
echo "</ul>";
echo "</div>";
?>
</div>

</div>
<?php #footer()?>

</div>


</body>
</html>

guelphdad
06-30-2011, 04:56 AM
run the queries outside of PHP, run them directly in mysql.
repair your tables if needed.

LJackson
06-30-2011, 04:32 PM
done both of that mate and still no change :(

LJackson
06-30-2011, 06:17 PM
even stranger, this also works


SELECT COUNT (*) AS count FROM tbl_cds WHERE cdBinding = 'audio cd' AND cdReleaseDate <= NOW()

but this doesnt

SELECT * FROM tbl_cds WHERE cdBinding = 'audio cd' AND cdReleaseDate <= NOW()

Old Pedant
07-01-2011, 01:51 AM
So what *IS* the COUNT(*) you get from that first query that works??

And have you tried doing something like this, just to see if the problem is too many records?


SELECT * FROM tbl_cds
WHERE cdBinding = 'audio cd' AND cdReleaseDate <= NOW()
LIMIT 20l

Or, maybe even more importantly, have you tried only getting specific fields, by name?


SELECT cdBinding, cdReleaseDate FROM tbl_cds
WHERE cdBinding = 'audio cd' AND cdReleaseDate <= NOW()
LIMIT 20l



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum