Using mysql query output as variable in php

Question

I am trying to use the output of an SQL query (in this case the output will be something like 26 "http://www.google.com"

I want to use the id and address in a process in php but cant seem to do anything with the output of the query other than echo it.

ultimately if i could end up with $id and $pos as variables that i could use that would be great.

<?php
$con = mysql_connect("localhost","root","root");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("movieCol", $con);
$query = "SELECT * FROM `movie_collection` ORDER BY `id` DESC LIMIT 1";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
echo $row['id']. " ". $row['poster'];
mysql_close($con);
?>

[Please, don't use mysql_* functions in new code.](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-function-in-php) They are no longer maintained and the [deprecation process](http://news.php.net/php.internals/53799) has begun on it. See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://uk3.php.net/pdo) or [MySQLi](http://uk1.php.net/mysqli) - this [article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. — ajtrichards, Dec 03 '12 at 09:56
Please, don't use the `mysql_*` extension anymore: it's being deprecated. Look into `PDO` or `mysqli_*` — Elias Van Ootegem, Dec 03 '12 at 09:56
@socialrel8, This [Link](http://uk.php.net/manual/en/function.mysql-connect.php#function.mysql-connect-soft-deprecation-notice) will directly point to red box. — saji89, Dec 03 '12 at 10:25

MrCode · Answer 1 · 2012-12-03T10:23:05.117

0

If you want them as separate variables then:

$query = "SELECT id, poster FROM `movie_collection` ORDER BY `id` DESC LIMIT 1";

...

list($id, $pos) = mysql_fetch_row($result);

echo $id. " ". $pos;

Although functionally this won't be any more or less beneficial than using the array elements that you already had.

You could also use extract() which populates individual variables with the array elements:

$row = mysql_fetch_array($result) or die(mysql_error());
extract($row);
echo $id. " ". $pos . " " . $director; // access all variables

Extracting is not recommended though, there really is no benefit over using the array elements.

edited Dec 03 '12 at 10:23

answered Dec 03 '12 at 09:58

MrCode

63,975
10
90
112

movie_collection id title year rated released genre director writer actors plot poster runtime rating votes imdb tstamp – user1872176 Dec 03 '12 at 10:13
sorry that didnt format well but its one table with a the various details about a movie all fields are default null except id which is auto increment – user1872176 Dec 03 '12 at 10:14

score 0 · Accepted Answer · answered Dec 03 '12 at 10:09

0

Just assign the values to variables as: $id=$row['id']; and $pos=$row['poster'];

Now you can use the variables $id and $pos wherever you want.

I hope this is what you wanted.

answered Dec 03 '12 at 10:09

Chetana Kestikar

570
6
23

i have tried that and it didnt seem to work. From what i could find the variables from mysql are different to those from php and something needs to be done to turn a mysql variable into a php one – user1872176 Dec 03 '12 at 10:22
are the values from database echoed as they are? I mean to say are the values properly retrieved? – Chetana Kestikar Dec 03 '12 at 10:34
well now i can echo them separately ($id and &pos) but I cant use them outside of that capacity i would like to copy('$pos', '$id.jpg'); but i dont think that is formatted correctly – user1872176 Dec 03 '12 at 10:41
i am not sure i followed 'cant use them outside of that capacity...'. Can u please elaborate? – Chetana Kestikar Dec 03 '12 at 10:48
when i try to use the new variables in anything other than a echo they dont work. Im currently trying to use the php copy() object and can do it with the actual values but not the variables – user1872176 Dec 03 '12 at 10:52
Try this. Take a new variable say, $target. So, $target=$id.'.jpg'; Now use this in copy($id,$target); – Chetana Kestikar Dec 03 '12 at 10:59
anyone tell you that you are genius lately? spot on! – user1872176 Dec 03 '12 at 11:00

FrancescoMM · Answer 3 · 2012-12-03T10:30:22.610

Are you looking to something like this?

<?php
$con = mysql_connect("localhost","root","root");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("movieCol", $con);
$query = "SELECT * FROM `movie_collection` ORDER BY `id` DESC LIMIT 1";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());

$whatYouWant = $row['id']. " ". $row['poster'];

mysql_close($con);
?><html>
<head><title><?=$whatYouWant?></title></head>
<body><h1><?=$whatYouWant?></h1></body></html>

All the warning about mysql_* being deprecated remain valid, of course. They become risky if your SQL contains anything that comes from outside the PHP script, like this:

NEVER DO THIS:

$value=$_REQUEST['item_id'];
$query = "SELECT * FROM `movie_collection` WHERE `id`=$value DESC LIMIT 1";

Consider PDO or mysqli, etc.. as others have suggested (or at least remember to filter $value correctly every time).

thats perfect, i think im going nuts i swear that didnt work earlier. I will also be be tweaking to accommodate mysqli thanks! — user1872176, Dec 03 '12 at 10:33

Using mysql query output as variable in php

3 Answers3