Complete PHP noob needs help

So I got this PHP & mySQL book for dummies and am just blindly typing in some sample scripts. I had a handy SQL database just lying around, so I decide to do something with it, to approximate an Excel table.

No matter what I do, however, I end up with a problem.

Error in query: $query. Unknown column '$source' in 'where clause'

What I want to do is provide a lot of drop-down lists, and let the user select whatever they want and run the query. There is no text input box here, so I don't think there's a need to sanitize input.

My code is as follows:

This content has been hidden by the poster

I think the naughty code in question is:

$query = 'SELECT `Reporter` , `Partner` , `Value` FROM `database` WHERE `Reporter` LIKE $source AND `Partner` LIKE $destination AND `Year` = $year AND `Flow` = $flow ORDER BY `Reporter`,`Partner`';

BUT I already did define the variables earlier:

$source = $S_POST['source'];
$destination = $S_POST['destination'];
$year = $S_POST['year'];
$flow = $S_POST['flow'];

So... what am I doing wrong

How ya doing, buddy?

I think that the first thing you should do is print out $query and see what it's really trying to do. Since you're not a programmer, you need to learn how to approach a problem like this logically and devise a set of tests to help narrow down the source of the problem.

Here the error message isn't very clear, but usually the target of the WHERE clause is in single quotes (e.g., WHERE `Reporter` LIKE '$source'.) This also means you need to be using double quotes (") for your string instead of single quotes (') so that PhP isn't confused about where the string ends. ($query = "Select ... etc.)

How ya doing, buddy?

Enclosing the variables in apostrophes work! Thanks Secret.

Now I'm encountering another problem. I've now split up the code into two parts. The first part contains just a set of forms with a submit button. The second part contains the PHP code. It seems PHP is not picking up on the options posted to it, and so all the variables $source $destination $year and $flow are NULL.

I have tested the mysql query by manually defining each variable, and it works. So the error now is that the option values are not being picked up.

My forms code are:

<form method="post" action="Result.php" target="bottomFrame">
  <table width="100%" border="1">
    <tr>
      <td>Source Country</td>
      <td>Destination</td>
      <td>Trade Flow</td>
      <td>Year</td>
    </tr>
    <tr>
      <td><select name="source" id="source">
        <option value='%'>List all countries</option>      
...
    <input type="submit" name="submit" id="submit" value="Display Data"></center>
  </p>
</form>

While the PHP parse code is the same as above.

Implementation here

Thoughts?

How ya doing, buddy?

It's $_POST, not $S_POST.

Also, always scrub your variables without fail, otherwise you're a ripe target for SQL injection. Whether there's a text box or any form of editable input does not matter. People can manually craft POST parameters, place form elements using local javascript, etc.


Another note about the difference between double quotes and single quotes: if you use single quotes around a string, PHP will NOT insert variable values. In other words, if you have

$variable = 'test';
$string = 'This is a $variable';
echo $string;

PHP outputs

This is a $variable

Put double quotes instead:

$string = "This is a $variable";

And you get the correct output ("This is a test").

I am a dolphin, do you want me on your body?

Well I'll be. I'm blind. That did the trick ^____^ Thanks Blah!

I didn't know people can automagically insert post elements where none is editable. But since I'm now getting a working result, I can attempt scrubbing at my leisure. (Though, since the database user has SELECT privileges only, there's probably not too much damage they can do...)

I was speaking idiomatically.

Originally Posted by Zergrinch

(Though, since the database user has SELECT privileges only, there's probably not too much damage they can do...)

Other than querying your tables for any sensitive data or terminating your query and starting their own, no, they probably couldn't do much damage.

Always always always scrub your data while developing. It's not a leisurely activity to be undertaken while you're cleaning your code and filling your comment blocks.

What kind of toxic man-thing is happening now?