Getting value's of dropdown from MySql not working

[brokenglass]

Hi guys,

 

Does anyone know why my code isn't working?:

 

 

$servername="127.0.0.1";
$username="root";
$password="";
$dbname="testdb";

$connection = new mysqli($servername,$username,$password);
 
 
$result = "SELECT 'Country' from 'w'";
echo '<select>';
foreach($result as $res) {
   echo '<option value="'.$res['Country'].'">' . $res['Country'] . '</option>';
}
echo '</select>';
}

 

There's not a credential issue, the table is called 'w' and the collumn I'm trying to get the values from is 'Country'

 

Any help is appreciated.

 

Cheers,

Broken

[Barand]

Having defined the query string you need to execute the query then get the results.

 

Also, your mysqli connection needs the dbname as the 4th argument.

$connection = new mysqli($servername,$username,$password,$dbname);
 
 
$sql = "SELECT Country from w ";
$r = $connection->query($sql);
$result = $r->fetch_all(MYSQLI_ASSOC);
echo '<select>';
foreach($result as $res) {
   echo '<option value="'.$res['Country'].'">' . $res['Country'] . '</option>';
}
echo '</select>';

edit: Nearly missed it - don't quote table and column names, and it does help to use meaningful names rather than just w

Edited November 6, 2014 by Barand

[brokenglass]

Having defined the query string you need to execute the query then get the results.

 

Also, your mysqli connection needs the dbname as the 4th argument.

$connection = new mysqli($servername,$username,$password,$dbname);
 
 
$sql = "SELECT Country from w ";
$r = $connection->query($sql);
$result = $r->fetch_all(MYSQLI_ASSOC);
echo '<select>';
foreach($result as $res) {
   echo '<option value="'.$res['Country'].'">' . $res['Country'] . '</option>';
}
echo '</select>';

edit: Nearly missed it - don't quote table and column names, and it does help to use meaningful names rather than just w

Thanks mate, but I get this now:

 

Fatal error: Call to a member function fetch_all() on a non-object in C:\xampp\htdocs\test\index.php on line 16

 

[Barand]

see what error you have

$r = $connection->query($sql) or die($connection->error);

[brokenglass]

 

see what error you have

$r = $connection->query($sql) or die($connection->error);

That also returns an error.

 

Here's my full code

 

 

<?php
$servername="127.0.0.1";
$username="root";
$password="";
$dbname="testdb";

$connection = new mysqli($servername,$username,$password,$dbname);
if($connection->connect_error){
 die("Connection failed  " . $connection->connect_error);
}else{
$sql = "SELECT 'Country' from 'w'";
$r = $connection->query($sql);
$result = $r->fetch_all(MYSQLI_ASSOC);
echo '<select>';
foreach($result as $res) {
   echo '<option value="'.$res['Country'].'">' . $res['Country'] . '</option>';
}
echo '</select>';
}
?>

[cyberRobot]

That also returns an error.

 

What error does it give?

[brokenglass]

What error does it give?

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 ''w'' at line 1

[cyberRobot]

Did you try removing the single quotes as Barand suggested? Try changing this

$sql = "SELECT 'Country' from 'w'";

To this

$sql = "SELECT Country from w";

[brokenglass]

 

Did you try removing the single quotes as Barand suggested? Try changing this

$sql = "SELECT 'Country' from 'w'";

To this

$sql = "SELECT Country from w";

Thanks man, that now works kind of. It displays the dropdown box with 3 entries (my collumn has 3 entries) but in each collumn it just says Countries rather than the 3 entries in the collumn (English, France, Argentina)

[cyberRobot]

It displays the dropdown box with 3 entries (my collumn has 3 entries) but in each collumn it just says Countries rather than the 3 entries in the collumn (English, France, Argentina)

 

Did you remove the single quotes around 'Country'? The query should be

 

$sql = "SELECT Country from w";

[brokenglass]

 

Did you remove the single quotes around 'Country'? The query should be

$sql = "SELECT Country from w";

You're a genius. Cheers mate.

[cyberRobot]

No problem; glad to help 

 

For future reference, if you need to surround a column name with quotes because it's a reserved word in MySQL or maybe the column name contains spaces, you use backticks:

$sql = "SELECT `limit` from w";