DaniWeb Forum Index > Web Development > PHP

PHP Variable in MySQL Statement

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jan 2007

Posts: 9

Reputation: twelvetwelve is an unknown quantity at this point

Solved Threads: 0

twelvetwelve twelvetwelve is offline

Offline

Newbie Poster

PHP Variable in MySQL Statement

May 30th, 2008

When I use the following PHP code I get this error

Error performing query: Unknown column 'nh12134' in 'where clause'

Yet when I use 'nh12345', which is the Value that the variable $username contains (i've checked that it does by printing it out elsewhere) instead of the $username in the query it returns the correct value.

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$tutorname = @mysql_query("SELECT Personal_tutor FROM Student WHERE student_ref=$username;"); 
if (!$tutorname) {
exit ('<p> Error performaing query: ' . mysql_error() . '</p>');
}
$tutorname = @mysql_query("SELECT Personal_tutor FROM Student WHERE student_ref=$username;"); 
if (!$tutorname) {
exit ('<p> Error performaing query: ' . mysql_error() . '</p>');
}

Any help is much appreciated

Thanks
NH

•

Join Date: Sep 2006

Posts: 13

Reputation: pzuurveen is an unknown quantity at this point

Solved Threads: 1

pzuurveen

Offline

Newbie Poster

Re: PHP Variable in MySQL Statement

May 30th, 2008

someting like this:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$query = sprintf("SELECT Personal_tutor FROM Student WHERE student_ref='%s' ",
    mysql_real_escape_string( $username));
 
$tutorname = @mysql_query($query); 
if (!$tutorname) {
exit ('<p> Error performaing query: ' . mysql_error() . '</p>');
}
$query = sprintf("SELECT Personal_tutor FROM Student WHERE student_ref='%s' ",
    mysql_real_escape_string( $username));

$tutorname = @mysql_query($query); 
if (!$tutorname) {
exit ('<p> Error performaing query: ' . mysql_error() . '</p>');
}

•

Join Date: Dec 2007

Posts: 252

Reputation: ProfessorPC is an unknown quantity at this point

Solved Threads: 27

ProfessorPC ProfessorPC is offline

Offline

Posting Whiz in Training

Re: PHP Variable in MySQL Statement

May 30th, 2008

try this

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$tutorname = mysql_query("SELECT Personal_tutor FROM Student WHERE student_ref = '".$username."'");
$tutorname = mysql_query("SELECT Personal_tutor FROM Student WHERE student_ref = '".$username."'");

Last edited by ProfessorPC; May 30th, 2008 at 8:50 am.

•

Join Date: Jul 2007

Posts: 2

Reputation: Peterpan71 is an unknown quantity at this point

Solved Threads: 0

Peterpan71 Peterpan71 is offline

Offline

Newbie Poster

Re: PHP Variable in MySQL Statement

May 30th, 2008

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
 
$Search=$_POST['Search'];
 
$query_Retrieve = "Select Model,Prime_kVA,Prime_kW,Engine, Alternator,Cylinders,Bore, Stroke,Governer,Type, Retail WHERE (`$Search` like '%{$_POST[Engine]}%') OR (`$Search` like '%{$_POST[Type]}%')";
 
$Search=$_POST['Search'];

$query_Retrieve = "Select Model,Prime_kVA,Prime_kW,Engine, Alternator,Cylinders,Bore, Stroke,Governer,Type, Retail WHERE (`$Search` like '%{$_POST[Engine]}%') OR (`$Search` like '%{$_POST[Type]}%')";

The above code produces: Wrong results

I have checked that the values are passed correctly.

The idea is that on the refering page the client selects the field to use when doing the query. I have never used variable to indicate the table in the query. Can someone please help me with this.

Last edited by Peterpan71; May 30th, 2008 at 4:03 pm.

•

Join Date: Dec 2007

Posts: 252

Reputation: ProfessorPC is an unknown quantity at this point

Solved Threads: 27

ProfessorPC ProfessorPC is offline

Offline

Posting Whiz in Training

Re: PHP Variable in MySQL Statement

May 30th, 2008

so you want to display everything from this table depending on the users search criteria. you are allowing the user to specify what field to search through. so you will need to grab 2 var's from other page.

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$col = $_POST['column'];
$search = $_POST['search'];
$query = mysql_query("Select Model,Prime_kVA,Prime_kW,Engine, Alternator,Cylinders,Bore, Stroke,Governer,Type, Retail WHERE ".$col." like '%".$search."%'") or die(mysql_error());
while($row = mysql_fetch_assoc($query)){
echo $row['Model']."<br>";
echo $row['Prime_kVA']."<br>";
//continue to display remainder of fields
}
$col = $_POST['column'];
$search = $_POST['search'];
$query = mysql_query("Select Model,Prime_kVA,Prime_kW,Engine, Alternator,Cylinders,Bore, Stroke,Governer,Type, Retail WHERE ".$col." like '%".$search."%'") or die(mysql_error());
while($row = mysql_fetch_assoc($query)){
echo $row['Model']."<br>";
echo $row['Prime_kVA']."<br>";
//continue to display remainder of fields
}

this is assuming you have the column in the other page for the user to select. and the textfield for the user to place the search text.

Last edited by ProfessorPC; May 30th, 2008 at 4:04 pm.

•

Join Date: Dec 2007

Posts: 252

Reputation: ProfessorPC is an unknown quantity at this point

Solved Threads: 27

ProfessorPC ProfessorPC is offline

Offline

Posting Whiz in Training

Re: PHP Variable in MySQL Statement

May 30th, 2008

sorry i changed the code lets try that again.

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$Search=$_POST['Search'];
 
$query_Retrieve = "Select Model,Prime_kVA,Prime_kW,Engine, Alternator,Cylinders,Bore, Stroke,Governer,Type, Retail WHERE (`".$Search."` like '%".$_POST[Engine]."%') OR (`".$Search."` like '%".$_POST[Type]."%')";
$Search=$_POST['Search'];

$query_Retrieve = "Select Model,Prime_kVA,Prime_kW,Engine, Alternator,Cylinders,Bore, Stroke,Governer,Type, Retail WHERE (`".$Search."` like '%".$_POST[Engine]."%') OR (`".$Search."` like '%".$_POST[Type]."%')";

•

Join Date: Jul 2007

Posts: 2

Reputation: Peterpan71 is an unknown quantity at this point

Solved Threads: 0

Peterpan71 Peterpan71 is offline

Offline

Newbie Poster

Re: PHP Variable in MySQL Statement

May 30th, 2008

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
 $query_Retrieve = "SELECT Model, Prime_kVA, Prime_kW, Engine, Alternator, Cylinders, Bore, Stroke, Governer, Type,Retail 
 
 
  FROM Import WHERE 
  ((`".$Search."` = '%".$_POST[Engine]."%') AND (`Prime_kVA` = '%".$_POST[Prime_kVA]."%'))
                  OR 
  (`".$Search."` = '%".$_POST[Prime_kVA]."%')
 
  ";
  //OR (`Engine`like '%{$_POST[Engine]}%') OR (`Alternator`like '%{$_POST[Alternator]}%') OR 
  mysql_select_db($database_generators, $generators);
  $Table = mysql_query($query_Retrieve, $generators) or die(mysql_error());
 $query_Retrieve = "SELECT Model, Prime_kVA, Prime_kW, Engine, Alternator, Cylinders, Bore, Stroke, Governer, Type,Retail 
   
  
  FROM Import WHERE 
  ((`".$Search."` = '%".$_POST[Engine]."%') AND (`Prime_kVA` = '%".$_POST[Prime_kVA]."%'))
                  OR 
  (`".$Search."` = '%".$_POST[Prime_kVA]."%')
                   
  ";
  //OR (`Engine`like '%{$_POST[Engine]}%') OR (`Alternator`like '%{$_POST[Alternator]}%') OR 
  mysql_select_db($database_generators, $generators);
  $Table = mysql_query($query_Retrieve, $generators) or die(mysql_error());

When I use "like" instead of the "=" I get results, but if I make the Prime_kVA (10) the the results that comes back is 10, 100 etc. when using the "=" I get no results.