WHERE Clause command

To talk on Icrontic, just register!

It only takes 30 seconds.

Have an account? Sign in:

Reply to Discussion

Options

Enverex

~-+-~

5,852 Posts

9 Jun 2003, 11:56am

PHP Code:


			
$k = $_REQUEST['k'];

// 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['Total'];

// 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";

};

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

__________________ RetroTracks - Classic Computer and Game Music

Reply with Quote

Enverex

~-+-~

5,852 Posts

9 Jun 2003, 1:10pm

PHP Code:


			
if ($_REQUEST['letter'] == "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['letter'];};



if( !isset($_GET['k'])) {

$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['Total'];

// Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)

$SQL="SELECT * FROM `$Letter` ORDER BY Name";

}else{

$k = $_REQUEST['k'];

// 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['Total'];

// 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';

};

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

Reply with Quote

Enverex

~-+-~

5,852 Posts

9 Jun 2003, 1:14pm

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


			
if ($_REQUEST['letter'] == "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['letter'];};



if( !isset($_GET['k'])) {

$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['Total'];

// Create a new SELECT Query with the ORDER BY clause and without the COUNT(*)

$SQL="SELECT * FROM `$Letter` ORDER BY Name";

}else{

$k = $_REQUEST['k'];

// 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['Total'];

// 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";

};

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

Reply with Quote

Enverex

~-+-~

5,852 Posts

9 Jun 2003, 10:02pm

Right, the code is now -

PHP Code:


			
else{

$k = $_GET['k'];

// 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['Total'];

// 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';

};

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

Reply with Quote

Enverex

~-+-~

5,852 Posts

9 Jun 2003, 10:36pm

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


			
<? 

If( !isset($_REQUEST['letter'])){ include("gallery.php");}else{ 

include("ss/amigamestitle.html"); 

include("ss/amigamesalpha.html"); 

echo "<div class=normbod>"; 

echo ""; 

// Variables 

$DB_Host="localhost"; 

$DB_Name="amigagames"; 

$Per_Page=10; 

// Connection 

$Connection=mysql_connect($DB_Host); 

if ($_REQUEST['letter'] == "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['letter'];}; 



if( !isset($_GET['k'])) { 

$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['Total']; 

// Create a new SELECT Query with the ORDER BY clause and without the COUNT(*) 

$SQL="SELECT * FROM `$Letter` ORDER BY Name"; 

}else{ 

$k = $_REQUEST['k']; 

// 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['Total']; 

// 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'])) 

   { 

   $res=0; 

   $SQL.=" LIMIT $res, $Per_Page"; 

   }else 

   { 

   $res=$_GET['res']; 

   $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['Name']; 

    $Description=$SQL_Array['Description']; 

    $Creator=$SQL_Array['Creator']; 

    $GFX=$SQL_Array['GFX']; 

    $File=$SQL_Array['File']; 

    $HDLoad=$SQL_Array['HDLoad']; 

    $GameSS= "/dl?amigss="; 

    $HDLLocation= '/dl?amihd='; 

    $MTheme="files/gamethemes/"; 

    $themefile="".$MTheme."".$File.".zip"; 



if("None" == $SQL_Array['HDLoad']){$hdrem = "No HDLoad";} 

elseif("Self" == $SQL_Array['HDLoad']){$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'> 

      <span color='#CEBF53'><A HREF=\"dir?game=".$File."\">Download</A> 

      $hdrem | $Theme</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>"; 

}; 

?>

Reply with Quote

Enverex

~-+-~

5,852 Posts

10 Jun 2003, 6:40pm

PHP Code:


			
<? 

// Append a LIMIT clause to the SQL statement 

if (empty($_GET['res'])) 

   { 

   $res=0; 

   $SQL.=" LIMIT $res, $Per_Page"; 

   }else 

   { 

   $res=$_GET['res']; 

   $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>"; 

         } 

      } 

   }

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

NS

Reply with Quote

		Icrontic Forums > Tech: Software > General Software
WHERE Clause command

Jump to

This Thread	Search this Thread
Print Email	Search this Thread: Advanced Search

Username
Password	Forgot?
Remember me