PHP - How do I get only 1 value from database?

Question

I have a table product that has columns of id, name, price, desc. But I only need to display price of a searched product.

For example, when user enters 0001, then the query should execute and return a value to $price of $5.00.

$sqlprice = $DB->Prepare("SELECT price FROM product WHERE id = '0001'");
$sqlprice -> Execute();
$result = $sqlprice->get_result();
$price = $result->fetch_assoc();

However, the $price variable does not has any value in it. What I did wrong in my code? I've searched across many threads but it don't seems to have an answer for my problem.

do you have an entry with id = `0001` (keeping in mind that `'0001'` is not the same as `'1'`) ? — apokryfos, May 30 '18 at 09:16
`->fetch_assoc` won't return the field, it returns an array .. have you `var_dump`'d the `$price` var? — treyBake, May 30 '18 at 09:16
@ThisGuyHasTwoThumbs I do not need an array, just a single value, no i didn't — hatched, May 30 '18 at 09:17
Then if all goes well `$price` should be an associative array like `[ 'price' => 5 ]` or something like that. That's how that works. If there's nothing in there then that means that that row just does not exist. There's no "fetch single value" method in PDO or MySQLi you have to roll your own — apokryfos, May 30 '18 at 09:19
@hatched you should var_dump it - you will see a value under the key of price .... it won't ever just return the value without a custom method implemented. It will either return an array so `$data['price']` or an object, so `$data->price` — treyBake, May 30 '18 at 09:28

score 0 · Answer 1 · answered May 30 '18 at 09:16

0

assuming that $sqlprice is an instance of mysqli i recommend using $sqlprice->fetchAll(PDO::FETCH_ASSOC)

answered May 30 '18 at 09:16

Max

180
4
13

score 0 · Answer 2 · answered May 30 '18 at 09:19

0

Access your price with $price[0] or $price['price']. If $price should be empty, then your SQL query was not successful. You should handle this case accordingly.

I advise you to get familiar with the var_dump() and print_r() methods for debugging purposes :)

answered May 30 '18 at 09:19

Adrian Baginski

336
1
8

it doesn't work , `print_r()` doesn't return print anything, my whole page crashes – hatched May 31 '18 at 02:32

score 0 · Answer 3 · 2018-05-30T09:53:25.963

0

$result->fetch_assoc() returns an associative array, which you can acces like $array["name"].

If the query is correct, you should get the price by accessing $price["price"]. But if $price does not has any value in it (=== null), then there is something wrong with the execution of your query / there is just no value in the DB.

You could also try the following (assuming your using PDO) and see if that works:

$sql = 'SELECT price FROM product WHERE id = "0001"';
foreach ($conn->query($sql) as $row) {
    echo $row['price'];
}

For mysqli:

$sql = 'SELECT price FROM product WHERE id = "0001"';
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
    echo $row['price'];
}

edited May 30 '18 at 09:53

answered May 30 '18 at 09:19

made my entire page blank – hatched May 31 '18 at 02:36
check your server logs or turn error logging on. "made my entire page blank" doesn't give me ANY information to help you out here. Are there errors being thrown? Is there just nothing in your database? Did it show something before? How did you implement it (i hope you didn't just copy paste)? Show us something – May 31 '18 at 06:28
its an existing page for years , just need to add a new query and my query doesn't return value I need, however by using your method, my entire page just went blank, but I had solved my problem already, thanks for your help – hatched May 31 '18 at 06:42
Glad to hear that it's solved! However, you're saying that "your entire page went blank", don't you have error logging turned on? Cause it shouldn't just show a blank page. If it does, there should be an error log written somewhere. What does that say? – May 31 '18 at 06:59

score 0 · Accepted Answer · answered May 31 '18 at 03:00

0

I don't really know why ...but this works for me after many tries.

$sqlprice= "SELECT price FROM product WHERE id='0001'";
        $rs = $DB -> Execute($sqlprice);
        while ($row = $rs -> FetchRow()) {
          $price= $row["price"];
        }

answered May 31 '18 at 03:00

hatched

795
2
9
34

score -1 · Answer 5 · edited May 30 '18 at 13:13

-1

You can check step by step where is the problem

if ($sqlprice = $DB->Prepare("SELECT price FROM product WHERE id = '0001'")) {
    if($sqlprice->execute()) {
        $result = $sqlprice->get_result();
        $price = $result->fetch_assoc();
    } else {
        die($sqlprice->error)
    }
} else {
    die($DB->error);
}

edited May 30 '18 at 13:13

treyBake

6,440
6
26
57

answered May 30 '18 at 09:17

Mithu CN

605
5
11

*the $price variable does not has any value in it*. Seems like this will just throw an undefined index error. – Loek May 30 '18 at 09:18

score -1 · Answer 6 · answered Jul 02 '18 at 18:03

I assume that 0001 is ID of the product in your product table. If this is so then you can try this query:

$this->db->select('price');
$this->db->where('id','id_entered_by_user');
$query = $this->db->get('product');

Your result will be stored in $query and you can check it by printing print_r($query->result());

Let me know that this query worked for you or not.

Ser · Answer 7 · 2022-08-12T08:25:35.017

-1

$conn = @new mysqli($host, $db_user, $db_password, $db_name);
$sql = "SELECT price FROM product WHERE id = '0001'";
$result = mysqli_query($conn, $sql);
$price = mysqli_fetch_assoc($result)["price"];

edited Aug 12 '22 at 08:25

answered Aug 12 '22 at 08:22

Ser

37
6

PHP - How do I get only 1 value from database?

7 Answers7