Page 1 of 1

MySQL Query SELECT parameters? Re: Where

Posted: Wed Jan 25, 2006 1:13 pm
by hbnmgr
Having to use PHP v4, I have a MySQL Database: b16124 with TABLE: wd_user,
and I want to Pull Fields userid, company_name, city, state. But only where state is equal to a specific state passed/posted from previous page as $searchstate.


I receive the following ERROR;

Invalid query: Unknown column 'FL' in 'where clause

From the following PHP code;

Code: Select all

$query = sprintf("SELECT userid, company_name, city, state, phone FROM wd_users WHERE state=$searchstate",
  mysql_real_escape_string($company_name));  

mysql_select_db('b16124', $db)
	or die("Could Not Open Database: ".mysql_error());

 $result = mysql_query($query);

  if (!$result) {
   $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}

  $num_rows = mysql_num_rows( $result );
  
  print "<H3>Number of listings found: $num_rows</h3>";

while ( $row = mysql_fetch_array( $result ) )
  {
	 for ($i=0; $i < $mysql_num_rows; $i++)
     print "<p><strong>Business Name: <a href=members/index.php?id=";
     print $row['userid'].">";
     print $row['company_name'];
     print "</a></strong><br />City: ";
     print $row['city'];
     print "<br />Phone: ";
     print $row['phone'];
     print "</p>";
  }
mysql_free_result($result);
mysql_close( $db );
Any suggestions as to what to put for WHERE ?

Thanks!
SRR/arr

Posted: Wed Jan 25, 2006 1:22 pm
by feyd
you forgot to use quotes around $searchstate in your query string.

..and you're not using $company_name :?

I still goofed somewhere ....

Posted: Wed Jan 25, 2006 4:57 pm
by hbnmgr
OK .. Thanks , but I get this output;

Number of listings found: 1
2>Testo Company Inc.
City: That City
Phone: 800-CALLME2

As you can see, the LINK was left out. The number 2 is the userid and should be included in the LINK along with the company_name.

What did I do wrong?

Thanks
SRR.

This is what I did wrong ...

Posted: Wed Jan 25, 2006 5:46 pm
by hbnmgr
I tried including the iteration of mysql_fetch_array within the For Loop, leaving out the While and Presto. It worked.

CODE SNIPPET:

Code: Select all

for ($i=0; $i < $num_results; $i++){
	 $row = mysql_fetch_array($result);
And yes - I changed $num_rows to $num_results for clarification.

Thanks!
SRR.

Posted: Wed Jan 25, 2006 6:55 pm
by John Cartwright

Code: Select all

while ( $row = mysql_fetch_array( $result ) )
  {
     print "<p><strong>Business Name: <a href=members/index.php?id=";
     print $row['userid'].">";
     print $row['company_name'];
     print "</a></strong><br />City: ";
     print $row['city'];
     print "<br />Phone: ";
     print $row['phone'];
     print "</p>";
  }
makes a lot more sense to me..

Posted: Wed Jan 25, 2006 7:10 pm
by raghavan20
As Jcart suggested, this is the place of error, you are looping twice...follow any one of these methods

Code: Select all

while ( $row = mysql_fetch_array( $result ) ) 
  { 
    for ($i=0; $i < $mysql_num_rows; $i++)

Code: Select all

if (!$result) { 
   $message  = 'Invalid query: ' . mysql_error() . "\n"; 
    die($message); 
} 

  $num_rows = mysql_num_rows( $result );
More over, you are checking for valid result but if you are not going to set else here...you are going to recieve error any way when you are checking mysql_num_rows($result)...use else here like this...

Code: Select all

if (!is_resource($result)) { 
   $message  = 'Invalid query: ' . mysql_error() . "\n"; 
    die($message); 
}else {

  $num_rows = mysql_num_rows( $result ); 

....
......
......

  }