2

I have a MySQL Database Table containing products and prices. Though an html form I got the product name in a certain php file. For the operation in this file I want to do I also need the corresponding price.

To me, the following looks clear enough to do it:

$price = mysql_query("SELECT price FROM products WHERE product = '$product'");

However, its echo returns: 

Resource id #5

instead a value like like:

59.95

There seem to be other options like mysqli_fetch_assoc mysqli_fetch_array But I can't get them to output anything meaningful and I don't know which one to use.

Thanks in advance.

M.G.Poirot
  • 1,066
  • 2
  • 11
  • 22
  • yes, exactly... `query()` returns a result HANDLE, not the data you were querying. You need to FETCH a row of results, e.g. `$row = mysql_fetch_asssoc($result)`. – Marc B Mar 20 '14 at 00:29
  • Might I add that `mysql_*` is deprecated and `PDO mysql` or `mysqli` should be used instead – Mario Segura Mar 20 '14 at 00:32
  • 2
    [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) – Phil Mar 20 '14 at 00:32

2 Answers2

18

You will need to fetch data from your database

$price = mysql_query("SELECT price FROM products WHERE product = '$product'");
$result = mysql_fetch_array($price);

Now you can print it with

echo $result['price'];

As side note I would advise you to switch to either PDO or mysqli since mysql_* api are deprecated and soon will be no longer mantained

Fabio
  • 23,183
  • 12
  • 55
  • 64
  • 4
    Downvoting due to recommending deprecated libraries with potential security vulnerabilities. – Phil Mar 20 '14 at 00:31
  • 1
    I don't agree, that's just an answer to the op problem, and it's correct though, I didn't encourage to use deprecated mysql_* functions – Fabio Mar 20 '14 at 00:32
  • You're promoting unsafe code. In my opinion, that deserves to receive minimal visibility. It's answers like this along with bad / old tutorials floating around the net that we still have to deal with the *mysql* extension – Phil Mar 20 '14 at 00:35
  • I'm sorry but I didn't mean that, anyway i addd a side note – Fabio Mar 20 '14 at 00:36
  • Your edit only makes it worse now. It's like saying *"Here's how to do this with `eval()`. But for the love of God, don't use `eval`!"* – Phil Mar 20 '14 at 00:36
  • 2
    it DOES answer the question though, which is what this site is about. Helping the user: it answers the question, and also notes why the person should actually be doing something in a different way. Saying "this is a bad answer because it answers the question" is just daft IMO. – Tularis Mar 20 '14 at 00:38
  • FYI, the *mysql* extension **is** (and has been for some time) *unmaintained*. – Phil Mar 20 '14 at 00:38
  • 4
    Typical elitism, that is so frequent around this place. The answer was perfect. The question specifically referred to using mysql_query ; and not mysqli_query. The answer was fine. – Gary Jul 29 '18 at 02:23
3

If you read the manual at PHP.net (link), it will show you exactly what to do.

In short, you perform the query using mysql_query (as you did), which returns a Result-Resource. To actually get the results, you need to perform either mysql_fetch_array, mysql_fetch_assoc or mysql_fetch_object on the result resource. Like so:

$res = mysql_query("SELECT something FROM somewhere"); // perform the query on the server
$result = mysql_fetch_array($res); // retrieve the result from the server and put it into the variable $result
echo $result['something']; // will print out the result you retrieved

Please be aware though that you should not use the mysql extension anymore; it has been officially deprecated. Instead you should use either PDO or MySQLi. So a better way to perform the same process, but using for example the MySQLi extension would be:

$db = new mysqli($host, $username, $password, $database_name); // connect to the DB
$query = $db->prepare("SELECT price FROM items WHERE itemId=?"); // prepate a query
$query->bind_param('i', $productId); // binding parameters via a safer way than via direct insertion into the query. 'i' tells mysql that it should expect an integer.
$query->execute(); // actually perform the query
$result = $query->get_result(); // retrieve the result so it can be used inside PHP
$r = $result->fetch_array(MYSQLI_ASSOC); // bind the data from the first result row to $r
echo $r['price']; // will return the price

The reason this is better is because it uses Prepared Statements. This is a safer way because it makes SQL injection attacks impossible. Imagine someone being a malicious user and providing $itemId = "0; DROP TABLE items;". Using your original approach, this would cause your entire table to be deleted! Using the prepared queries in MySQLi, it will return an error stating that $itemId is not an integer and as such will not destroy your script.

Tularis
  • 1,506
  • 1
  • 8
  • 17
  • Because it is the answer to the actualy question, and I also mentioned alternatives which are better. – Tularis Mar 20 '14 at 00:33
  • I have updated my answer together with an explanation why the OP should use a different method as well as provided the actual preferred method. Now please remove the downvotes. – Tularis Mar 20 '14 at 00:47
  • 1
    Your *mysqli* example is a little off. You need to perform a *fetch* on the `mysqli_result` object returned from `get_result()` (you also have a typo there). Easier to use `bind_result()` when working with `mysqli_stmt` IMO – Phil Mar 20 '14 at 01:13
  • @Phil sorry, it was late; was indeed a typo (happens if you write things off the top of your head). Thank you for noticing. Personally I don't like using bind_result since it assumes that the order in which you provide the parameters is the order in which the columns were selected in the query. It's easier not to bother with that, and go for named columns via fetch_object or fetch_assoc. – Tularis Mar 20 '14 at 10:59