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...

have u tried this statement in MySQL itself

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

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

I always thought we queried mysql by column name then the variable ?

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

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...

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.

sounds like its running missing the single quotes

@Jstfsklh211 - Where is it missing the single quotes...

Did u try what I said?

the error message indicated that it's tyring to use United Kingdom as a row instead of a text value

@Mohamed_26 yes I did and I already said it worked...

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

country = :country

stmt->bindParam(field, value, datatype)

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".

Member Avatar for diafol

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.

@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.

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}?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.