0

Hi Everyone, I have the following script to search for and display the CountryId (code) of a given country name.

                $stmt = $conn->query('SELECT CountryId FROM countries WHERE Country = ".$clcountry." ');
                while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                    echo $row['CountryId']; //etc...
                }

I have tried the above with '.$clcountry.' and with $clcountry - The above is taken from the pdo php manual.

I have also tried the following

                                $sql = "SELECT CountryId FROM countries WHERE Country = '.$clcountry.'";
                                $stmt = $conn->prepare($sql);
                                $stmt->execute();
                                $total = $stmt->rowCount();

                                    while ($row = $stmt->fetchObject()) {
                                        $countryid = $row['CountryId'];
                                        echo " $countryid
                                        ";
                                    }

                        } catch(PDOException $e) {
                            echo 'ERROR: ' . $e->getMessage();
                        }
*/

As well as trying

        $sql= "SELECT CountryId FROM countries WHERE Country = '.$clcountry.' "; 
        $stmt = $conn->query($sql); 
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        echo $row['CountryId'];

When I simply echo the variable

echo "$clcountry";

I get the country name echo'd out on the page, but for some reason I am unable to echo the CountryId variable.
I thought it may have been a concatenate issue, but I have tried the different types of variations and still no luck.

Can someone tell me where or why this is not displaying the CountryId correctly please...

5
Contributors
16
Replies
55
Views
3 Years
Discussion Span
Last Post by everton.retweets
Featured Replies
  • Yes, I have already done that and I get a list of all Country Id's as expected... The WHERE statement works like this... Search the database column named CountryId Where The Country Column is United Kingdom... Read More

0

have u tried this statement in MySQL itself

"SELECT CountryId FROM countries WHERE Country = '.$clcountry.' ";

0

I am not sure how the where statement works but I know that if the query statement does not work in MYSQL its not going to work on ur website.

May I suggest you remove the ' WHERE Country = '.$clcountry.' and try it in MYSQL and let me know if it works.

Thanks

1

Yes, I have already done that and I get a list of all Country Id's as expected...

The WHERE statement works like this...
Search the database column named CountryId Where The Country Column is United Kingdom...

0

So, have u tried that statement in php and it should work hopefully.

Oh wow, thank you for teaching me that. Oh btw I dont want to sound like I am forcing you, if I have helped you can u give me a thumbs up, Thanks. I am just trying to gain some reputation.

0

also if you are using pdo you should be binding your variable not just inserting them

country = :country

stmt->bindParam(field, value, datatype)

0

Can someone tell me where or why this is not displaying the CountryId correctly please...

Aside from what you are doing in MySQL directly... in your code are you sure that the variable "$clcountry" actually has a value prior to executing the query?

Yes but I get the error message - Unknown column 'United Kingdon' in 'where clause'

If you ran the query manually in MySQL, this means you probably forgot to use the single quotes around "United Kingdom".

0

Your SQL should be...

"SELECT CountryId FROM countries WHERE Country = '$clcountry'"

There's no need to concatenate the string. If you can guarantee that the $clcountry variable is clean use it by all means, but as mentioned by others, PDO has binding parameters:

$stmt = $conn->prepare("SELECT CountryId FROM countries WHERE Country = ?");
$stmt->execute(array($clcountry));

There are many variations on a theme for binding paramters.

0

@JorgeM & @diafol thanks gents for your replies & thanks for pointing out where I was going wrong. The example that @diafol replied with certainly help the most, thanks again.

0

I have another quick question please gents if you dont mind, When I am displaying my data on page I use.

$cpstatement = nl2br($cpstatement);

On one of my query results, I have {$row->duties} -
How can I add the nl2br to {$row->duties}?

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.