Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts

    Angry strange mysql problem

    I am having great difficulty understanding how the mysql works simply because i have two almost identical querys

    PHP Code:
    SELECT FROM tbl_cds WHERE cdBinding 'audio cd' AND cdReleaseDate <= NOW() 
    PHP Code:
    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

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    my code is

    PHP Code:
    <?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>

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    run the queries outside of PHP, run them directly in mysql.
    repair your tables if needed.

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    done both of that mate and still no change

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    even stranger, this also works

    PHP Code:
    SELECT COUNT (*) AS count FROM tbl_cds WHERE cdBinding 'audio cd' AND cdReleaseDate <= NOW() 
    but this doesnt
    PHP Code:
    SELECT FROM tbl_cds WHERE cdBinding 'audio cd' AND cdReleaseDate <= NOW() 

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,677
    Thanks
    80
    Thanked 4,644 Times in 4,606 Posts
    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?
    Code:
    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?
    Code:
    SELECT cdBinding, cdReleaseDate FROM tbl_cds 
    WHERE cdBinding = 'audio cd' AND cdReleaseDate <= NOW()
    LIMIT 20l


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •