MySQL Query SELECT parameters? Re: Where

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
hbnmgr
Forum Newbie
Posts: 13
Joined: Mon Mar 07, 2005 8:22 pm

MySQL Query SELECT parameters? Re: Where

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you forgot to use quotes around $searchstate in your query string.

..and you're not using $company_name :?
hbnmgr
Forum Newbie
Posts: 13
Joined: Mon Mar 07, 2005 8:22 pm

I still goofed somewhere ....

Post 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.
hbnmgr
Forum Newbie
Posts: 13
Joined: Mon Mar 07, 2005 8:22 pm

This is what I did wrong ...

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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..
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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 ); 

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

  }
Post Reply