WHERE Clause command

EnverexEnverex Worcester, UK Icrontian
edited June 2003 in Science & Tech
[PHP]$k = $_REQUEST;
// Run The Query Without a Limit to get Total result
$SQL="SELECT COUNT(*) AS Total FROM `$Letter` WHERE Name LIKE $k";
$SQL_Result=mysql_db_query($DB_Name, $SQL);
$SQL_Result_Array=mysql_fetch_array($SQL_Result) || die(mysql_error());
$Total=$SQL_Result_Array;
// Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
$SQL="SELECT * FROM `$Letter` WHERE Name LIKE $k ORDER BY Name";
};[/PHP]

Getting an
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\http\ac\amiga\gs.php on line 27
Unknown column 'cap' in 'where clause'

Why does it think $k is a column rather than a WHERE critera?

NS

Comments

  • EnverexEnverex Worcester, UK Icrontian
    edited June 2003
    [PHP]
    if ($_REQUEST == "all") {$Letter = '123, A, B, C, D, E, F, G, H, I, J, K, L, M, N, I, O, P, Q, R, S, T, U, V, W, X, Y, Z' ;}else{$Letter = $_REQUEST;};

    if( !isset($_GET)) {
    $SQL="SELECT COUNT(*) AS Total FROM `$Letter`";
    $SQL_Result=mysql_db_query($DB_Name, $SQL);
    $SQL_Result_Array=mysql_fetch_array($SQL_Result) || die(mysql_error());
    $Total=$SQL_Result_Array;
    // Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
    $SQL="SELECT * FROM `$Letter` ORDER BY Name";
    }else{
    $k = $_REQUEST;
    // Run The Query Without a Limit to get Total result
    $SQL='SELECT COUNT(*) AS Total FROM `$Letter` WHERE Name LIKE "' .$k. '"';
    $SQL_Result=mysql_db_query($DB_Name, $SQL);
    $SQL_Result_Array=mysql_fetch_array($SQL_Result) || die(mysql_error());
    $Total=$SQL_Result_Array;
    // Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
    $SQL='SELECT * FROM `$Letter` WHERE Name LIKE "' .$k. '" ORDER BY Name';
    };
    [/PHP]

    Now giving the error of -

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\http\ac\amiga\gs.php on line 27
    Table 'amigagames.$letter' doesn't exist

    Why isnt it using the defined $Letter rather than actually writing $letter?

    NS
  • EnverexEnverex Worcester, UK Icrontian
    edited June 2003
    Ok, had to change the ' back to " to get it to work.

    Now the code doesnt return errors, it just doesnt do anything when you search -

    [PHP]if ($_REQUEST == "all") {$Letter = '123, A, B, C, D, E, F, G, H, I, J, K, L, M, N, I, O, P, Q, R, S, T, U, V, W, X, Y, Z' ;}else{$Letter = $_REQUEST;};

    if( !isset($_GET)) {
    $SQL="SELECT COUNT(*) AS Total FROM `$Letter`";
    $SQL_Result=mysql_db_query($DB_Name, $SQL);
    $SQL_Result_Array=mysql_fetch_array($SQL_Result) || die(mysql_error());
    $Total=$SQL_Result_Array;
    // Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
    $SQL="SELECT * FROM `$Letter` ORDER BY Name";
    }else{
    $k = $_REQUEST;
    // Run The Query Without a Limit to get Total result
    $SQL="SELECT COUNT(*) AS Total FROM `$Letter` WHERE Name LIKE ' .$k. '";
    $SQL_Result=mysql_db_query($DB_Name, $SQL);
    $SQL_Result_Array=mysql_fetch_array($SQL_Result) || die(mysql_error());
    $Total=$SQL_Result_Array;
    // Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
    $SQL="SELECT * FROM `$Letter` WHERE Name LIKE ' .$k. ' ORDER BY Name";
    };
    [/PHP]

    http://atomnet.co.uk/amiga/?p=gs&letter=S&k=Speed - Should return 2 games, but it just gives a blank result. Any ideas?

    NS
  • EnverexEnverex Worcester, UK Icrontian
    edited June 2003
    Right, the code is now -

    [PHP]else{
    $k = $_GET;
    // Run The Query Without a Limit to get Total result
    $SQL='SELECT COUNT(*) AS Total FROM `' .$Letter. '` WHERE Name LIKE ' .$k. '';
    $SQL_Result=mysql_db_query($DB_Name, $SQL);
    $SQL_Result_Array=mysql_fetch_array($SQL_Result) || die(mysql_error());
    $Total=$SQL_Result_Array;
    // Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
    $SQL='SELECT * FROM `' .$Letter. '` WHERE Name LIKE ' .$k. ' ORDER BY Name';
    };[/PHP]

    But I am still getting -
    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\http\ac\amiga\gs.php on line 27
    Unknown column 'egg' in 'where clause'

    When using the URL - http://atomnet.co.uk/amiga/?p=gs&letter=C&k=egg
    Any ideas?

    NS
  • EnverexEnverex Worcester, UK Icrontian
    edited June 2003
    Here is the entire code, the problem now (as well as the WHERE clause) is that it doesnt generate the "Prev" and "Next" links at the bottom, though I cant see anything wrong.

    [PHP]<?
    If( !isset($_REQUEST)){ include("gallery.php");}else{
    include("ss/amigamestitle.html");
    include("ss/amigamesalpha.html");
    echo "<div class=normbod>";
    echo "<br>";
    // Variables
    $DB_Host="localhost";
    $DB_Name="amigagames";
    $Per_Page=10;
    // Connection
    $Connection=mysql_connect($DB_Host);
    if ($_REQUEST == "all") {$Letter = '123, A, B, C, D, E, F, G, H, I, J, K, L, M, N, I, O, P, Q, R, S, T, U, V, W, X, Y, Z' ;}else{$Letter = $_REQUEST;};

    if( !isset($_GET)) {
    $SQL="SELECT COUNT(*) AS Total FROM `$Letter`";
    $SQL_Result=mysql_db_query($DB_Name, $SQL);
    $SQL_Result_Array=mysql_fetch_array($SQL_Result) || die(mysql_error());
    $Total=$SQL_Result_Array;
    // Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
    $SQL="SELECT * FROM `$Letter` ORDER BY Name";
    }else{
    $k = $_REQUEST;
    // Run The Query Without a Limit to get Total result
    $SQL="SELECT COUNT(*) AS Total FROM `$Letter` WHERE Name LIKE ' .$k. '";
    $SQL_Result=mysql_db_query($DB_Name, $SQL);
    $SQL_Result_Array=mysql_fetch_array($SQL_Result) || die(mysql_error());
    $Total=$SQL_Result_Array;
    // Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)
    $SQL="SELECT * FROM `$Letter` WHERE Name LIKE ' .$k. ' ORDER BY Name";
    };

    // Append a LIMIT clause to the SQL statement
    if (empty($_GET))
    {
    $res=0;
    $SQL.=" LIMIT $res, $Per_Page";
    }else
    {
    $res=$_GET;
    $SQL.=" LIMIT $res, $Per_Page";
    }


    // Query With a Limit to get result
    $SQL_Result=mysql_db_query($DB_Name, $SQL);
    $SQL_Rows=mysql_num_rows($SQL_Result);
    // Display Results using a for loop
    for ($a=0; $a < $SQL_Rows; $a++)
    {
    $SQL_Array=mysql_fetch_array($SQL_Result);
    $Name=$SQL_Array;
    $Description=$SQL_Array;
    $Creator=$SQL_Array;
    $GFX=$SQL_Array;
    $File=$SQL_Array;
    $HDLoad=$SQL_Array;
    $GameSS= "/dl?amigss=";
    $HDLLocation= '/dl?amihd=';
    $MTheme="files/gamethemes/";
    $themefile="".$MTheme."".$File.".zip";

    if("None" == $SQL_Array){$hdrem = "No HDLoad";}
    elseif("Self" == $SQL_Array){$hdrem = "Self Install";}
    else{$hdrem = "<A HREF=\"/dl?amihd=".$HDLoad."\">HDLoad File</A>";};
    if (file_exists($themefile)){$Theme = "<a href=\"".$MTheme."".$File.".zip\">Theme Tune</a>";}
    else{$Theme = "No Theme";};

    echo "<table border='0' style='border-collapse: collapse'>
    <tr>
    <td width='188'>
    <img src=\"".$GameSS."".$File."\">
    </td>
    </td>
    <td width='421'>
    <div class=normbodt>$Name <font color='#CEBF53'>|</font> $Creator <font color='#CEBF53'>|</font> $GFX</div>
    <div class=normbod>$Description
    <img src='/ss/spacer.gif'>
    <table border='0' class=fsize style='border-collapse: collapse'>
    <tr>
    <td>
    <img src='/ss/spacer.gif' height='3'><br>
    <span color='#CEBF53'><A HREF=\"dir?game=".$File."\">Download</A><br>
    $hdrem | $Theme<br></span></div>
    </td>
    <td>
    </td>
    </tr>
    </table>
    </tr>
    <tr> <td height='7'>
    </td> </tr>
    </table>
    ";};

    if ($Total>0)
    {
    if ($res<$Total && $res>0)
    {
    $Res1=$res-$Per_Page;
    echo "<A HREF=\"gs?res=$Res1\"><;<; Previous Page</A> ";
    }
    // Calculate and Display Page # Links
    $Pages=$Total / $Per_Page;
    if ($Pages>1)
    {
    for ($b=0,$c=1; $b < $Pages; $b++,$c++)
    {
    $Res1=$Per_Page * $b;
    echo "<A HREF=\"gs?res=$Res1\">$c</A> \n";
    }
    }
    if ($res>=0 && $res<$Total)
    {
    $Res1=$res+$Per_Page;
    if ($Res1<$Total)
    {
    echo " <A HREF=\"gs?res=$Res1\">Next Page >></A>";
    }
    }
    }
    // Close Database Connection
    mysql_close($Connection);
    echo "</div>";
    };
    ?>
    [/PHP]

    NS
  • EnverexEnverex Worcester, UK Icrontian
    edited June 2003
    [PHP]<?
    // Append a LIMIT clause to the SQL statement
    if (empty($_GET))
    {
    $res=0;
    $SQL.=" LIMIT $res, $Per_Page";
    }else
    {
    $res=$_GET;
    $SQL.=" LIMIT $res, $Per_Page";
    }

    if ($Total>0)
    {
    if ($res<$Total && $res>0)
    {
    $Res1=$res-$Per_Page;
    echo "<A HREF=\"gs?res=$Res1\"><;<; Previous Page</A> ";
    }
    // Calculate and Display Page # Links
    $Pages=$Total / $Per_Page;
    if ($Pages>1)
    {
    for ($b=0,$c=1; $b < $Pages; $b++,$c++)
    {
    $Res1=$Per_Page * $b;
    echo "<A HREF=\"gs?res=$Res1\">$c</A> \n";
    }
    }
    if ($res>=0 && $res<$Total)
    {
    $Res1=$res+$Per_Page;
    if ($Res1<$Total)
    {
    echo " <A HREF=\"gs?res=$Res1\">Next Page >></A>";
    }
    }
    }
    [/php]

    Ok, thats narrowed it down, M33PINS, can you see anything wrong with that?

    NS
Sign In or Register to comment.