Assign MySQL database value to PHP variable

Question

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.

Solution 1

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

Solution 2

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.

Comments

[Marc B]

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

[Mario Segura]

Might I add that mysql_* is deprecated and PDO mysql or mysqli should be used instead

[Phil]

Why shouldn't I use mysql_* functions in PHP?

[Phil]

Downvoting due to recommending deprecated libraries with potential security vulnerabilities.

[Fabio]

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

[Tularis]

Because it is the answer to the actualy question, and I also mentioned alternatives which are better.

[Phil]

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

[Fabio]

I'm sorry but I didn't mean that, anyway i addd a side note

[Phil]

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

[Tularis]

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.

[Phil]

FYI, the mysql extension is (and has been for some time) unmaintained.

[Tularis]

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.

[Phil]

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

[Tularis]

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

[Gary]

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.