[SOLVED] Empty query issue

[loganb]

I'm trying to select a faculty member from a drop down box on one page, then click submit and the corresponding faculty member's info will come up on the next page in text boxes to allow editing.  However when I select the faculty member and click submit, I get the following error:  Can't execute SELECT Faculty* FROM Faculty WHERE LastName=: Query was empty.  I'm guessing my problem is how I'm calling the selected item on the first page, but I can't get it figured out.  Below is the code for the first select box:

 

form action="facultyedit.php" method=POST>
    <select name="Faculty">
<option>Please select a faculty member from the list</option>

<?php
$query1 = "SELECT LastName FROM Faculty";
  if ($result = mysql_query($query1)) {
    if (mysql_num_rows($result)) {
      while ($row = mysql_fetch_assoc($result)) {
        echo "<option>".$row['LastName']."</option>";
      }
    } else {
      echo "No results found";
    }
  } else {
    echo "Query failed<br />$query1<br />".mysql_error();
  }
?>

</select><br><br>
     

 

And here is the code so far for the second page:

 

<!--php connection-->
<?PHP

// open a mysql connection 
$db=mysql_connect('db.imse.ksu.edu', 'loganb', 'clifton');
if (!$db)
  {
     echo 'Error: Could not connect to database.  Please try again later.';
     exit;
  }

//define the database used
mysql_select_db('ABET_Course_Reports');

// query for the record
$sql = "SELECT Faculty* FROM Faculty WHERE LastName=$query1";
$result = mysql_query($query1) or die ("Can't execute $sql: " . mysql_error());

// get the details into a single row
$row = mysql_fetch_array($result, MYSQL_ASSOC);

// echo the form
echo 'First Name:   <input type="text" name="FirstName" value="'. $row['FirstName'] .'"><br>';
echo 'Last Name:   <input type="text" name="LastName" value="'. $row['LastName'] .'"><br>';
echo 'Title:   <input type="text" name="Title" value="'. $row['Title'] .'"><br>';
echo 'Office Number:   <input type="text" name="OfficeNumber" value="'. $row['OfficeNumber'] .'"><br>';
echo 'Email Address:  <input type="text" name="EmailAddress" value="'. $row['EmailAddress'] .'"><br>';
echo 'Phone:   <input type="text" name="Phone" value="'. $row['Phone'] .'"><br>';
?>

 

If anyone could help with this problem it would be very much appreciated....Thanks

[marcus]

Do SELECT * FROM `Faculty`

[loganb]

Ok, I changed that and I'm still getting basically the same error message:

Can't execute SELECT * FROM 'Faculty' WHERE LastName=: Query was empty

[marcus]

You use tilde's not apostrophes when calling a table.

 

And you can't select one thing from another query, do:

 

$row2 = mysql_fetch_assoc($query1);
$sql = SELECT * FROM `Faculty` WHERE `LastName` ='$row2[LastName]'";

[loganb]

I'm still getting the same error: Can't execute SELECT * FROM `Faculty` WHERE `LastName` ='': Query was empty

 

the current code on the edit page is:

<?PHP

// open a mysql connection 
$db=mysql_connect('host', 'user', 'pass');
if (!$db)
  {
     echo 'Error: Could not connect to database.  Please try again later.';
     exit;
  }

//define the database used
mysql_select_db('ABET_Course_Reports');

// query for the record
$row2 = mysql_fetch_assoc($query1);
$sql = "SELECT * FROM `Faculty` WHERE `LastName` ='$row2[LastName]'";
$result = mysql_query($query1) or die ("Can't execute $sql: " . mysql_error());

//echo the form
echo 'First Name:   <input type="text" name="FirstName" value="'. $row['FirstName'] .'"><br>';
echo 'Last Name:   <input type="text" name="LastName" value="'. $row['LastName'] .'"><br>';
echo 'Title:   <input type="text" name="Title" value="'. $row['Title'] .'"><br>';
echo 'Office Number:   <input type="text" name="OfficeNumber" value="'. $row['OfficeNumber'] .'"><br>';
echo 'Email Address:  <input type="text" name="EmailAddress" value="'. $row['EmailAddress'] .'"><br>';
echo 'Phone:   <input type="text" name="Phone" value="'. $row['Phone'] .'"><br>';
?>

 

[per1os]

<?php
$sql = "SELECT * FROM `Faculty` WHERE `LastName` = '" . $row2['LastName'] . "'";
?>

 

Is the "proper" way for that to be done, it is generally not a good practice to not have the ' in an array index reference due to constants etc.

 

You are all sorts of backwards. Where are you trying to get the lastname from? POST data??

 

<?php
// open a mysql connection 
$db=mysql_connect('db.imse.ksu.edu', 'loganb', 'clifton');

if (!$db)
  {
     echo 'Error: Could not connect to database.  Please try again later.';
     exit;
  }

//define the database used
mysql_select_db('ABET_Course_Reports');

/*This whole section DOES NOT MAKE SENSE
// query for the record
$row2 = mysql_fetch_assoc($query1);
$sql = "SELECT * FROM `Faculty` WHERE `LastName` ='$row2[LastName]'";
$result = mysql_query($query1) or die ("Can't execute $sql: " . mysql_error());
*/

// remember to clean the get data if needed
$sql = "SELECT * FROM `Faculty` WHERE `LastName` = '" . $_GET['LastName'] . "'";
$query = mysql_query($sql);
$row = mysql_fetch_assoc($query);

//echo the form
echo 'First Name:   <input type="text" name="FirstName" value="'. $row['FirstName'] .'"><br>';
echo 'Last Name:   <input type="text" name="LastName" value="'. $row['LastName'] .'"><br>';
echo 'Title:   <input type="text" name="Title" value="'. $row['Title'] .'"><br>';
echo 'Office Number:   <input type="text" name="OfficeNumber" value="'. $row['OfficeNumber'] .'"><br>';
echo 'Email Address:  <input type="text" name="EmailAddress" value="'. $row['EmailAddress'] .'"><br>';
echo 'Phone:   <input type="text" name="Phone" value="'. $row['Phone'] .'"><br>';
?>

 

What you need to decide is how are you getting the lastname to pull, from a form, get data how? Because as it is there is no definition for the lastname which is why the query is empty because lastname is essentially equaled to nothing. IE: ""

 

 

[boo_lolly]

your first problem is with your dropdown menu....

 

<?php

$sql = "SELECT LastName FROM Faculty";
$query = mysql_query($sql);

echo "<form method=\"post\" action=\"facultyedit.php\">\n";
echo "<select name=\"Faculty\">\n";
      while ($row = mysql_fetch_array($query)) {
        echo "<option value=\"{$row['LastName']}\">".$row['LastName']."</option>";
      }
echo "</select>\n";
echo "</form>\n";
?>

that's the way it should look... before you weren't sending any data to the second page because you were missing a value="" in your option tag.

 

your second page should look like this:

<!--php connection-->
<?php
$db=mysql_connect('db.imse.ksu.edu', 'loganb', 'clifton');
if (!$db)
{
     echo 'Error: Could not connect to database.  Please try again later.';
     exit;
}

//define the database used
mysql_select_db('ABET_Course_Reports');

// query for the record
$sql = "SELECT Faculty* FROM Faculty WHERE LastName='". $_POST['Faculty'] ."'";
$result = mysql_query($sql) or die ("Can't execute $sql: " . mysql_error());

$row = mysql_fetch_array($result);

// echo the form
echo 'First Name:   <input type="text" name="FirstName" value="'. $row['FirstName'] .'"><br>';
echo 'Last Name:   <input type="text" name="LastName" value="'. $row['LastName'] .'"><br>';
echo 'Title:   <input type="text" name="Title" value="'. $row['Title'] .'"><br>';
echo 'Office Number:   <input type="text" name="OfficeNumber" value="'. $row['OfficeNumber'] .'"><br>';
echo 'Email Address:  <input type="text" name="EmailAddress" value="'. $row['EmailAddress'] .'"><br>';
echo 'Phone:   <input type="text" name="Phone" value="'. $row['Phone'] .'"><br>';
?>

 

that should work.

[loganb]

Ok, so I've made the changes to both pages and I must have missed a tag somewhere because the first page with the select box now is blank, however the second page where the fields are supposed to appear has a new error message:

Can't execute SELECT Faculty* FROM Faculty WHERE LastName ='': 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 'FROM Faculty WHERE LastName =''' at line 1

 

I'm thinking it is caused by the fact that you can't select an item on the first page, therfore the query string is empty.  However I have no idea what I may have changed that would cause the first page to not show.  I've attached all the  php code for the first page, the rest of the page wasn't changed and is just the tables and navigation structure.

 

<?php
// open a mysql connection 
$db=mysql_connect('host', 'user', 'pass');
if (!$db)
  {
     echo 'Error: Could not connect to database.  Please try again later.';
     exit;
  }

//define the database used
mysql_select_db('ABET_Course_Reports');
?>

<?php
$sql = "SELECT LastName FROM Faculty";
$query = mysql_query($sql);
echo "<form method=\"post\" action=\"facultyedit.php\">\n";
echo "<select name=\"Faculty\">\n";
while ($row = mysql_fetch_array($query))
{
echo "<option value=\"{$row['LastName']}\">".$row['LastName']."</option>";
}

echo "</select>\n;
echo "</form>\n;

?>

 

[boo_lolly]

your page is blank???

 

i have no explaination for that... but you should double-check by viewing the source of the page. also you're missing an end quote here:

echo "</select>\n;
echo "</form>n;

 

should be like this:

echo "</select>\n";
echo "</form>n";

 

and if it wasn't, you should at least be getting errors on your first page.

[loganb]

Ok, the page is no longer blank, it was the missing of the quotes that was causing it.  Now the error message on the second page is this: Can't execute SELECT Faculty* FROM Faculty WHERE LastName ='Harnett': 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 'FROM Faculty WHERE LastName ='Harnett'' at line 1 

Where Harnett was the faculty member selected

[boo_lolly]

well it looks like everything's working fine except your query... and i don't think it's the query, i think it may be the contents of your sql table. what values do you have in your table?

[loganb]

The mysql table contains around 12 entries, each with a FirstName, LastName, Phone, Title, EmailAddress, and OfficeNumber field.  Data exists in every field.  The LastName is the primary key, and the only optional fields are OfficeNumber and Phone.

[boo_lolly]

post your code for the second page.

[loganb]

Here's the second page...

 

<!--php connection-->
<?PHP

// open a mysql connection 
$db=mysql_connect('host', 'user', 'pass');
if (!$db)
  {
     echo 'Error: Could not connect to database.  Please try again later.';
     exit;
  }

//define the database used
mysql_select_db('ABET_Course_Reports');

// query for the record

$sql = "SELECT Faculty* FROM Faculty WHERE LastName ='". $_POST['Faculty'] ."'";
$result = mysql_query($sql) or die ("Can't execute $sql: " . mysql_error());

$row = mysql_fetch_array($result);


// echo the form
echo 'First Name:   <input type="text" name="FirstName" value="'. $row['FirstName'] .'"><br>';
echo 'Last Name:   <input type="text" name="LastName" value="'. $row['LastName'] .'"><br>';
echo 'Title:   <input type="text" name="Title" value="'. $row['Title'] .'"><br>';
echo 'Office Number:   <input type="text" name="OfficeNumber" value="'. $row['OfficeNumber'] .'"><br>';
echo 'Email Address:  <input type="text" name="EmailAddress" value="'. $row['EmailAddress'] .'"><br>';
echo 'Phone:   <input type="text" name="Phone" value="'. $row['Phone'] .'"><br>';
?>

[boo_lolly]

change this:

$sql = "SELECT Faculty* FROM Faculty WHERE LastName ='". $_POST['Faculty'] ."'";

 

to this:

$sql = "SELECT * FROM Faculty WHERE LastName ='". $_POST['Faculty'] ."'";

 

and that should work.

[loganb]

YES, that worked, thank you boo_lolly and all others who responded

[boo_lolly]

glad we could help you out. and thanks for using the 'Topic Solved' mod... people need to use that thing more often.