Syntax problem using "WHERE"

[agrophobic]

G'Day, the following code works as intended but on the first load of the page a syntax error occurs:
(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 'where Suburb =' at line 1)

<form action="" method="post" name="Form">
       <p>Business Type:
       <select name="table" id="table">
      <option value="Plumber">Plumber</option>
      <option value="Electrician">Electrician</option>
      <option value="Painter">Painter</option>
      </select>
      </p>
      <p>Suburb:
      <select name="suburb" id="suburb">
      <option value="Bucketty">Bucketty</option>
      <option value="Kulnura">Kulnura</option>
      <option value="Gosford">Gosford</option>
      </select>
      
       
      <input type="submit" name="submit" value="Submit" />
      </p>
      </form>
      

<?php
$table = $_POST['table'];
$suburb = $_POST['suburb'];
$sql = mysql_query("SELECT * FROM {$table} WHERE Suburb='$suburb'");
      
       while ($row = mysql_fetch_array($sql))
      {
            echo '<br/> Name: '.$row['Name'];
      echo '<br/> Suburb: '.$row['Suburb'];
      echo '<br/> Listing: '.$row['Listing'];
      echo '<br/><br/>';
      }
      

    ?>

Any suggestions would be welcome.

Agro

[Thirusha]

I dont know php, but have u have u tried writing out the sql statement. To me it seems as though the statement is looking for "$suburb", i think u have forgotten to put the curly brackets around $suburb, i say this cause i see to get teh value of the table u used the curly brackets, shouldnt the same be done to get the value for the variable $suburb?

[slyme]

Is $suburb text or numeric? If it is text it needs the single quotes around it.
If it is numeric then you don't need the quotes.
Either way, as Thirusha says, you do need the curly brackets (plus the double quote on the end).

[agrophobic]

If I put the curly brackets in it breaks the code fully. At least the way it is I get a syntax error on initial load but then the script works as it is, with brackets it doesn't work at all:
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in ....\index.php on line 122

while ($row = mysql_fetch_array($sql))

P.S. suburb is text

[slyme]

Try this:

("SELECT * FROM '" . $table . "' WHERE Suburb = '" . $suburb . "'")

[agrophobic]

Try this:

("SELECT * FROM '" . $table . "' WHERE Suburb = '" . $suburb . "'")

Get the usual result ->
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean.....

:(

[nav33n]

1. Do you have mysql_connect ?
2. Are you selecting the database ?
3. Does that table exist ?
4. Are you sure the query is correct ? Ie., echo the query and test it in phpmyadmin/mysql console.

If your answer is *yes to all*, please post your complete code, so that others can assist.

[slyme]

You can use this script to test your db/php stuff - fill in the blanks at the top and run it, if it fails you should glean some more meaningful info:

<?php

$db_host = ''; 
$db_name = '';
$db_user = '';
$db_password = '';


$db_table = '';
$db_query = '';
$db_field = '';
$db_display = '';


$con = mysql_connect($db_host, $db_user, $db_password);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db($db_name, $con);
$result = mysql_query("SELECT * FROM $db_table WHERE $db_field = '$db_query'");

while($row = mysql_fetch_array($result))
  {
  echo $row[$db_display];
  }


mysql_close($con);
?>

[agrophobic]

nav33n:
YES... db connection etc all fine - the script actually works, it select the table from the database based on the dropdown menu - then shows results based on the second drop down menu. All this works fine, the only problem is that on first loading the page shows a syntax error... Once a search is performed the error dissappears.

All relevant code is posted at the start of this thread, if you need anything else, let me know.

Thanks,

Agro

[kokoro90]

Have you tried escaping the single quotes like this?

$sql = mysql_query("SELECT * FROM {$table} WHERE Suburb=\'$suburb\'");

The problem with the single quotes is that PHP interprets anything in single quotes as literal.

Hope this helps

[slyme]

Ok - surround the code with an if statement to check whether the form has been posted or not.

I'm guessing that the code is being executed without any input when you first load the page.

[nav33n]

Are you using Linux server ? If yes, then make sure to check the case (lowercase/uppercase) of table and column names. Linux is case-sensitive. :)
Did you try printing out the query and executing it in mysql ?

Well, here is a quote from php.net
mysql_fetch_array
Return Values:
Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows.
As you can see, you are using the empty result resource in the while loop. Try this.

<form action="" method="post" name="Form">
   <p>Business Type:
   <select name="table" id="table">
  <option value="Plumber">Plumber</option>
  <option value="Electrician">Electrician</option>
  <option value="Painter">Painter</option>
  </select>
  </p>
  <p>Suburb:
  <select name="suburb" id="suburb">
  <option value="Bucketty">Bucketty</option>
  <option value="Kulnura">Kulnura</option>
  <option value="Gosford">Gosford</option>
  </select>
  
   
  <input type="submit" name="submit" value="Submit" />
  </p>
 </form>
      

<?php
$table = $_POST['table'];
$suburb = $_POST['suburb'];
$sql = "SELECT * FROM $table WHERE Suburb='$suburb'";
$result = mysql_query($sql);
if(mysql_num_rows($result) > 0) {
	while ($row = mysql_fetch_array($result)) {
	  echo '<br/> Name: '.$row['Name'];
	  echo '<br/> Suburb: '.$row['Suburb'];
	  echo '<br/> Listing: '.$row['Listing'];
	  echo '<br/><br/>';
	}
} else {
	echo "No records found :)";
}      
?>

[agrophobic]

As you can see, you are using the empty result resource in the while loop. Try this.

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\JackNEW\index.php on line 144
No records found :)

Line 144 is:

if(mysql_num_rows($result) > 0) {

I think the empty result resource is the culprit but I can't seem to get rid of it :(

[nav33n]

Can you post your code ? (and print the variable $sql and post it here ?)
I guess the variable $table is empty or something.

[slyme]

The variables $table and $suburb will be empty when the page is first loaded. They will only populated once the form has been submitted - whatever other problems there may be, I think this should be avoided by using an if statement to check that the form has been submitted before executing any other code.

[nav33n]

The variables $table and $suburb will be empty when the page is first loaded. They will only populated once the form has been submitted - whatever other problems there may be, I think this should be avoided by using an if statement to check that the form has been submitted before executing any other code.

:) Thanks for waking me up. That is absolutely right.

[agrophobic]

The variables $table and $suburb will be empty when the page is first loaded. They will only populated once the form has been submitted - whatever other problems there may be, I think this should be avoided by using an if statement to check that the form has been submitted before executing any other code.

Working on this now, thanks for all the input guys, much appreciated.

Agro

[agrophobic]

Well, all issues fixed with a couple of lines to catch unselected fields and draw it to the user's attention:

<?php
$table = $_POST['table'];
$suburb = $_POST['suburb'];

if ($table=="select") {
        die ( "Select a business type to search for" );
      }
if ($suburb=="select") {
        die ( "Select a suburb to search for" );
      }

if (!$suburb==0)
{
  $result = mysql_query("SELECT * FROM $table WHERE Suburb='$suburb'");
 
       while ($row = mysql_fetch_array($result))
      {
      echo '<br/> Name: '.$row['Name'];
      echo '<br/> Suburb: '.$row['Suburb'];
      echo '<br/> <a href="' . $row['Listing'] . '" target="_blank"> More Information </a>';
	  echo '<hr>';
      }
	 }
 else
  {
echo "No records found";
}

 ?>

[Manuz]

Hey agrophobic ,

When the page loads for the first time there won't be any value for $_POST['table'],since u r not yet submit the form.
so put ur php codes inside a condition like below
<?php
if(isset($_POST['submit']) && $_POST['submit'] == "submit")
{
$table = $_POST['table'];
$subur ..................... etc
}

[Graphix]

$query = "SELECT * FROM $table WHERE Suburb='$suburb'";
$result = mysql_query($query);

But you will need to check wether the actual name of the column is Suburb and not suburb.