PDO on MySQL query through String variables

Question

I'm using this code for my school project in which my program should be able to reply automatically to queries which was sent to it using an open-source program.

I have succeeded on creating a simple auto-reply with it with the help of someone who is knowledgeable with it. However I'm struggling on getting values through PHP-MySQL with string variables.

$reqproduct = $db->query("SELECT Crop FROM crops WHERE Crop = '$product'");
$reqprice = $db->query("SELECT Price FROM crops WHERE Crop = '$product'");

And yes of course, I've failed horribly.

Then I tried to learn PHP:PDO but I still get stuck on how to use its commands even with the help of its documentation.

Here is an excerpt of what I'm trying to do:

<?php
    try{
    //test database connection
    $db = new PDO('mysql:host=localhost;dbname=gammu;','root'); 
    echo "Connected<p>";} 
    catch (Exception $e){echo "Unable to connect: " . $e->getMessage() ."<p>";} 

    //get data from database through decoded message
        $product = 'Okra'; //sample decoded message

        $sth = $db->prepare('SELECT Crop FROM crops WHERE Crop = :product'); 
        $sth->execute(array(':product' => "{$product}"));
        $result = $sth->fetchAll();
        foreach ($result as $row) { $reqproduct = $row; break;}
        $sth = $db->prepare('SELECT Price FROM crops WHERE Crop = :product'); 
        $sth->execute(array(':product' => "{$product}"));
        $result = $sth->fetchAll();
        foreach ($result as $row) { $reqprice = $row; break;}

    //to be sent to the sender
        $message = "The price of $reqproduct is $reqprice/kilo." ;
        echo $message;
    $db = null;
?>

I would like to get the value of what's inside the $message, any help would be really appreciated.

Also, crops.sql contains 3 columns. Namely the 'ID', 'Crop', and 'Price'.

You should have in your pdo string a user and a password,I only see the user. — Mihai
– Mihai, Commented Jan 19, 2014 at 13:43
To help you understand the contents of $row, and $result, use var_dump($result); and var_dump($row); The first is a 2D array containing the second. So you need to retrieve your value Crop by its array index. $reqprice = $row['Crop']; — Michael Berkowski
– Michael Berkowski, Commented Jan 19, 2014 at 13:46
Otherwise, your code is pretty good. I'd recommend using indentation instead of inline {} blocks in the control structures like foreach, and don't use "{$product}" when you just need the variable $product. — Michael Berkowski
– Michael Berkowski, Commented Jan 19, 2014 at 13:47
@Mihai : It still connects though, I did not alter the default pass of my phpmyadmin :) — Dahh
– Dahh, Commented Jan 19, 2014 at 13:55
@Mihai The password argument is optional. MySQL supports password-less connections — Phil
– Phil, Commented Jan 20, 2014 at 3:06

ddoor · Accepted Answer · 2014-01-19 13:48:28Z

1

You are doing a few things wrong here.

Firstly SQL can grap a few columns at once so it should go into one query like this:

SELECT Crop, Price FROM crops WHERE Crop = 'product'

Then, when you do mysql fetch you can get the value out of the row like this:

$crop = $row['Crop'];
$price = $row['Price'];

Each column maps to an element in the array. To see what it looks like try:

var_dump($row); after you call fetchAll

See FETCH_ASSOC here: http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

answered Jan 19, 2014 at 13:48

ddoor

5,9719 gold badges36 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dahh Over a year ago

Yes, my bad. I just copy-pasted the reqprice part when posting the question. Also the "WHERE Crop = 'product'" part will only query the string itself instead of the variable, which was also my problem before moving on PHP: PDO as expressed on the question.

Dahh · Accepted Answer · 2014-01-22 06:34:49Z

0

Got another help again, here's the better code. :)

    $crop_query = $db->query("SELECT Crop, Price FROM crops WHERE Crop = '{$product}' ");
    $crop_query->execute(array(':product' => "{$product}"));
    $result = $crop_query->fetch(PDO::FETCH_OBJ); //just fetch one row

    $message = "The price of {$result->Crop} is {$result->Price}/kilo";

edited Jan 22, 2014 at 6:34

answered Jan 20, 2014 at 3:03

Dahh

156 bronze badges

1 Comment

Daedalus Over a year ago

Accept this answer to mark the question as solved; do this by clicking the outlined checkmark under the down arrow. I'm editing your title as [solved] is not supposed to be there.

Collectives™ on Stack Overflow

PDO on MySQL query through String variables

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related