-2

I'm trying to populate an html table from my SQL query based on a user's selections. I'm able to populate the first column however, my variable column "select_datapoint" is blank. Is there any reason this won't work?

<form>
<select name="select_datapoint">
<option value="test1">test1</option>
<option value="test2">test2</option>
<option value="test3">test3</option>
<option value="test4">test4</option>
<option value="test5">test5</option>    
</select>
<select name="minput">
<option value="ME">ME</option>
<option value="CT">CT</option>
<option value="AZ">AZ</option>
<option value="DE">DE</option>
<option value="MT">MT</option>    
</select>
<input type="submit" name="Submit">
</form>
<br></br>
<?php
print date("g:i a.", time());
?>
<br></br>
<!--use PHP to connect to sql database-->
<?php
$servername = "sql206.phpnet.us";
$username = "pn_14163829";
$password = "714405c";
$dbname = "pn_14163829_mexico";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$zurich = $_GET["minput"];
$montreal = $_GET["select_datapoint"];
$sql = "SELECT test1,'$montreal' AS testX FROM test WHERE test1='$zurich'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
echo "<table class=\"v\">";
echo "<th>test1</th><th>$montreal</th>";    
while($row = $result->fetch_assoc()) {
    echo "<tr>"."<td>".$row["test1"]."</td>"."<td>".$row['testX']."</td>"."</tr>";
    }
} else {
    echo "0 results";
echo "<br></br></table>";    
}
$conn->close();
?>
stranger in the alps
  • 19
  • 2

2 Answers2

2
$sql = "SELECT test1,'$montreal' AS testX FROM test WHERE test1='$zurich'";

Mark's answer is correct, but not for the reason he gives.

He says that the above will treat '$montreal' as a string literal. Normally that is correct, but because it's embedded in a double quoted string, it will actually be interpolated correctly.

What's actually happening is that you're generating a string along the lines of:

SELECT test1,'fieldname' AS testX FROM test WHERE test1='$zurich'

The 'fieldname' is incorrect - it's a column name. Single quotes in an SQL query are for values, so MySQL isn't interpreting this as you expect. If you want to use quotes for a column (or table) name, you need to use backticks:

$sql = "SELECT test1,`$montreal` AS testX FROM test WHERE test1='$zurich'";

Will work. Though as you noticed, you can get the same effect by omitting any quotes.

andrewsi
  • 10,807
  • 132
  • 35
  • 51
  • Actually, he edited the question after I answered. Originally he was using $row['$montreal'] as part of the table dump to the HTML side. – Mark Dec 21 '14 at 04:01
  • 1
    Ah, I see - that's terribly confusing. I wonder if that means that there's an error in the code that's there now, or if the code there is working? I wish people wouldn't edit their code after they get answers.... – andrewsi Dec 21 '14 at 04:03
0

This is invalid PHP Code: 

'$montreal'

Single quotes will not process variables inside of them. Change it to double quotes:

$row["$montreal"]

Or, better yet, remove the quotes all together:

$row[$montreal]

Read more here: What is the difference between single-quoted and double-quoted strings in PHP?

Community
  • 1
  • 1
Mark
  • 861
  • 9
  • 17