[RESOLVED] Query based on form input, variables passing, query not working

dapuxter

I have a form that i'm using to build queries. Its basically a select with the options set to the column headers from my db I want to be searchable and a text box for entering the value to search for. For example, my db has two rows, and the "city" field of each of these rows is "gainesville". If I select "City" in the select and then type "gainesville" and hit submit, I want it to return those two rows. I added a simple html statement at the top of the table to be sure that my variables were passing, and they are. However, when entering a query that I know should have two rows as a result, I'm getting nothing. I've played around with backticks and single quotes around my variables in the select statement with no luck. Perhaps one of you could shed some light on this for me? Here's the code. Thanks!

<?php
if(get_magic_quotes_gpc()) {
            $selector = stripslashes($_POST['queryselector']);
            $value = stripslashes($_POST['queryvalue']);
} else {

        $selector = $_POST['queryselector'];
        $value = $_POST['queryvalue'];
}
?>
You are looking for outages where <?php echo $selector; ?> is equal to <?php echo $value; ?>.
<body>
<?php
$con = mysql_connect("server", "user", "pw");
if (!con)
   {
   die('Could not connect: '. mysql_error());
   }
mysql_select_db("outage", $con);
$result = mysql_query("SELECT * FROM `outage` where '.$selector.'='.$value.' order by id");
echo "<table>";
echo "<tr>";
echo "<th>ID</th>";
echo "<th>Date</th>";
echo "<th>Time</th>";
echo "<th>Status</th>";
echo "<th>Planned/Unplanned</th>";
echo "<th>MTC/Plant</th>";
echo "<th>City</th>";
echo "<th>Node</th>";
echo "<th>Services</th>";
echo "<th>Customer Count</th>";
echo "<th>CBS Customers Affected?</th>";
echo "<th>Impact</th>";
echo "<th>Description</th>";
echo "</tr>";
    //display the data
    while ($rows = mysql_fetch_array($result,MYSQL_ASSOC))
    {
      echo "<tr>";
      foreach ($rows as $data)
      {
        echo "<td align='center'>". $data . "</td>";
      }
    }

  echo "</table>";
?>

cgraz

Why aren't you using mysql_error() on your query? Are you sure you're not getting any errors? Your database and your table are both named "outage"?

Are you getting a blank page? Does your server have display_errors set to on in your php.ini file?

I would first add this at the top of your script

// set the error_reporting level
error_reporting( E_ALL ^ E_NOTICE );
ini_set('display_errors','On');

Any errors?

Next run your query with mysql_error(), but your concatenation (is that a word?) looks wrong. If you're using double quotes, you can't concatenate with ' . $variable . '

You need " . $variable . "

Try this:

$result = mysql_query("SELECT * FROM outage WHERE " . $selector . " = '" . $value . "' ORDER BY id") or die(mysql_error());

dapuxter

Would double quotes not give me a parse error for unexpected termination? Since its already in a double quoted string, I thought it had to be single quotes? That was the reasoning behind single quotes, anyway.

Looks like that was the problem. I replaced the single quotes with double and it all works now. Thanks!

cgraz

When you concatenate, you break out of the double (or single) quotes (depending on which you are using).

print 'Text: ' . $someVariable;
print 'Text: ' . $someVariable . ' followed by more text';
print "Double quotes work in the same way, even with my varabies " . $someVariable;

Notice how if I'm using single quotes, the ' . breaks me out of them then . ' puts me back in.

Alternatively, you could have written your query like so

$query = "SELECT * FROM outage WHERE $selector = '$value' ORDER BY id";

This works because you're in double quotes, and technicaly don't need to break out of them to print variables. Your variables in this case won't be be treated as strings, but as PHP variables. The single quotes then are literally interpreted as they are within double quotes (Note: You don't need single quotes around your column names in SQL, just around your values if they are strings).

Or using single quotes

$query = 'SELECT * FROM outage WHERE ' . $selector . ' = \'' . $value . '\' ORDER BY id';

But unlike standard quotes, I now have to break out of the single quotes to print the variables. I also need to surround the value by a single quote, so in order for it to be treated literally, i have to escape it with a \

Hope this clears up any confusion.