drop down selection wont display

[kvnirvana]

function search() 
{ 

//base sql 
  $sql = "select * from behan WHERE 1"; 

//get the values from the form 


  if ((!empty($_POST['beha']))&&($_POST['beha'] != 'all')) 
  { 
    $sql .= " and beha like '". addslashes($_POST['beha'])."%' "; 
  } 
  
  if ((!empty($_POST['omraede']))&&($_POST['omraede'] != 'all')) 
  { 
    $sql .= " and omraede like '". addslashes($_POST['omraede'])."%' "; 
  } 
  
  if ((!empty($_POST['pr']))&&($_POST['pr'] != 'all')) 
  { 
    $sql .= " and pr = '". addslashes($_POST['pr'])."' "; 
  } 
  

  
print "<table border=1>";

 

 

If I add ‘order by total_value DESC’

to  the select it wont work, like this

$sql = "select * from behan WHERE 1 order by total_value DESC";  

I get this message

Can't connect because You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and ????? like '?????%'' at line 1 

 

How can I make it work?

 

 

[Hybride]

Your MySQL statement is wrong.

$sql = "select * from behan WHERE 1 order by total_value DESC"; 

What is "WHERE 1"? It has to be equal to something, such as id=1, name=1, etc., etc.

[kvnirvana]

Actually I don't know what "WHERE 1" is, I didn't write the code i've just adjusted it but even though I take away the "WHERE 1" it still doesn't work

[kvnirvana]

If I remove the 'Where 1' from the select I get the same error as if I add the 'order by total_value DESC'.

I think it has something to do with this

if ((!empty($_POST['beha']))&&($_POST['beha'] != 'all'))   {     $sql .= " and beha like '". addslashes($_POST['beha'])."%' ";   }     if ((!empty($_POST['omraede']))&&($_POST['omraede'] != 'all'))   {     $sql .= " and omraede like '". addslashes($_POST['omraede'])."%' ";   }     if ((!empty($_POST['pr']))&&($_POST['pr'] != 'all'))   {     $sql .= " and pr = '". addslashes($_POST['pr'])."' ";   } 

[kvnirvana]

This is the full code

<?php 

/*------------------------------------------------------------------------ 
            control codes 
------------------------------------------------------------------------*/ 


if (isset($_POST['submit'])) 
{ 

  search();       //call the search function 

}else{ 

  show_form();   //call the show form function 

}//end if 

/*------------------------------------------------------------------------ 
            show the search form 
------------------------------------------------------------------------*/ 

function show_form() 
{ 
  //call the dropdown function which creates an html string to build a select box for each element 
  $beha   = dropdown('beha','behan'); 
  $omraede  = dropdown('omraede','behan'); 
  $pr     = dropdown('pr','behan'); 


  
  
  
  echo "<form name='search' action=".$_SERVER['PHP_SELF']." method='post'> 
        <table width='50%' align='center' valign='center'> 
        
	<tr> 
          <td colspan='2' align='center'>Search Form</td> 
        </tr> 
        <tr> 
          <td align='right'>Beha:</td><td>$beha</td> 
        </tr> 
        <tr> 
          <td align='right'>Omraede:</td><td>$omraede</td> 
        </tr> 
        <tr> 
          <td align='right'>Pr:</td><td>$pr</td> 
        </tr> 
  
         
          <td colspan='2' align='center'> </td> 
        </tr> 
        <tr> 
          <td colspan='2' align='center'><input type='submit' name='submit' value='Go!'></td> 
        </tr> 

        </table> 
        </form>"; 

}//end function 


/*------------------------------------------------------------------------ 
            run the search and show the results 
------------------------------------------------------------------------*/ 

function search() 
{ 

//base sql 
  $sql = "select * from behan WHERE 1"; 

//get the values from the form 


  if ((!empty($_POST['beha']))&&($_POST['beha'] != 'all')) 
  { 
    $sql .= " and beha like '". addslashes($_POST['beha'])."%' "; 
  } 
  
  if ((!empty($_POST['omraede']))&&($_POST['omraede'] != 'all')) 
  { 
    $sql .= " and omraede like '". addslashes($_POST['omraede'])."%' "; 
  } 
  
  if ((!empty($_POST['pr']))&&($_POST['pr'] != 'all')) 
  { 
    $sql .= " and pr = '". addslashes($_POST['pr'])."' "; 
  } 
  

  
print "<table border=1>";

  //add more elements (or take away) as you desire...follow the same code structure as above 

    
  //run query 
$result = conn($sql);
  if (!$result){ die("No results due to database error.<br>".mysql_error());  }
  if (mysql_num_rows($result)==0)
  {
    echo "No Results found!";
  }else{
echo "<TABLE  width=100% height=300 border='0' cellpadding='5' cellspacing='10'>";
$first_run = "true";
while ($rows= mysql_fetch_array($result))
{
   echo "<TR>";   
if ($first_run == "true"){
    echo "<TH colspan='5' scope='colgroup'><u>Resultat for: <scope='row'>". $rows['beha'] .", ". $rows['omraede'] .", ". $rows['pr'] ."</u><br></br></Th>";
$first_run = "false";
}
    echo "</TR>";
   echo     "<TR>";
    echo "<TH scope='col' abbr='beha'><u>Beha</u></TH>";
    echo "<TH scope='col' abbr='rating'><u>Rating</u></TH>";
    echo "<TH scope='col'><u>Kommentar</u></TH>";
  
  echo "</TR>";
   echo "<TR>";
    echo "<TD width=20% height=100 scope='row'>". $rows['beha'] ."<p> ". $rows['na'] ."<p> ". $rows['ad'] ."<p> ". $rows['po'] .", ". $rows['by'] ."<p> ". $rows['tl'] ."</TD>";
    echo "<TD width=30% height=100>". rating_bar($rows['id'],'6','static')."<a href='jadak.php?id={$rows['id']}&na={$rows['na']}&pr={$rows['pr']}&beha={$rows['beha']}'>Bedøm </a> </TD>";
    echo "<TD width=30% height=100><a href='komment.php?na={$rows['na']}&id={$rows['id']}&beha={$rows['beha']}'>Læs kommentarer</a> </TD>";
echo "<TABLE  width=100% height=100 border='0' cellpadding='5' cellspacing='10'><hr>";
  echo "</TR>"; 
    }  
     echo "</table>";  }



  
  //end if 



}//end function 






/*------------------------------------------------------------------------ 
            create the drop downs 
------------------------------------------------------------------------*/ 

function dropdown($field, $table) 
{  
  //initialize variables 
  $oHTML  = ''; 
  $result = ''; 
  
  //check to see if the field is passed correctly 
  if (($field == "")||($table == "")) 
  { 
    die("No column or table specified to create drop down from!"); 
  } 

  $sql = "select distinct($field) from $table"; 
  
  //call the db function and run the query 
  $result = conn($sql); 

  //if no results are found to create a drop down return a textbox 
  if ((!$result) ||(mysql_num_rows($result)==0)) 
  { 
    $oHTML .= "<input type='text' name='$field' value='' size='15'>"; 
  }elseif (($result)&&(mysql_num_rows($result)>0)){ 
    
    //build the select box out of the results 
    $oHTML .= "<select name='$field'>\n<option value='all'>All</option>\n"; 
    while ($rows = mysql_fetch_array($result)) 
    { 
      $oHTML .= "<option value='".$rows[$field]."'>".$rows[$field]."</option>\n"; 
    } 
    $oHTML .= "</select>\n"; 
  } 
  
  //send the value back to the calling code 
  return $oHTML; 
}//end function 

/*------------------------------------------------------------------------ 
            database connection function 
------------------------------------------------------------------------*/ 

function conn($sql) 
{    
  $username  = "****"; 
     $pwd      = "***"; 
     $host        = "localhost"; 
     $dbname      = "*****"; 

    //echo "commnecing connection to local db<br>"; 
    
    if (!($conn=mysql_connect($host, $username, $pwd)))  { 
        printf("error connecting to DB by user = $username and pwd=$pwd"); 
        exit; 
    } 
    $db3=mysql_select_db($dbname,$conn) or die("Unable to connect to local database"); 
    
    $result = mysql_query($sql) or die ("Can't connect because ". mysql_error()); 
    
    return $result; 
    
}//end function      

?>

 

Any help appriciated

[kvnirvana]

Anyone????

[kvnirvana]

Nobody knows how to fix it?

[DavidAM]

Actually, the "WHERE 1" seems to work on my server, although I don't recommend it.  It looks like it was put there so the rest of the statements building up the SQL don't have to worry about whether the WHERE has already been added and can just always use AND.  I would make it "WHERE 1=1 " because it makes it more clear to me that it was done on purpose.

 

Where are you trying to add the ORDER BY?  I think it would work if you added it after the last if test. 

function search() 
{ 
  //base sql 
  $sql = "select * from behan WHERE 1"; 

  //get the values from the form 
  if ((!empty($_POST['beha']))&&($_POST['beha'] != 'all')) 
  { 
    $sql .= " and beha like '". addslashes($_POST['beha'])."%' "; 
  } 
  
  if ((!empty($_POST['omraede']))&&($_POST['omraede'] != 'all')) 
  { 
    $sql .= " and omraede like '". addslashes($_POST['omraede'])."%' "; 
  } 
  
  if ((!empty($_POST['pr']))&&($_POST['pr'] != 'all')) 
  { 
    $sql .= " and pr = '". addslashes($_POST['pr'])."' "; 
  } 

  // ADD ORDER BY 
  $sql .= ' order by total_value DESC ';

 

On the other hand, your error message says:

Can't connect because You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and ????? like '?????%'' at line 1

 

where did that first set of ?'s come from?  The code you presented is using column names there.  You need to echo $sql before you execute it, and possibly at various other places, to find out where this is coming from.

[kvnirvana]

Actually it's now working after I added what what told me, thank you so much, very appriciated :=)