How to get MySQL select query values into a PHP variable?

Question

I know I need to use the MySQL fetch to prevent getting a resource ID in my variable but I wondered if you could help me out how to do that. I see from several tutorials they use a loop but I just want to select the one string into a variable. Here is the code I have:

$img = mysql_query('SELECT pname FROM photos WHERE pphotoid=21');
echo $img;

I basically want $img to contain the string in the database not Resource id #3 it is currently showing. Also is what I wrote prone to an SQL injection?

Learning MySQL so any help would be great!

http://www.php.net/mysql_fetch_assoc Ummm infact it should be `mysqli` — Hanky Panky, Apr 18 '13 at 16:51

score 4 · Answer 1 · answered Apr 18 '13 at 16:53

4

$img=mysql_fetch_assoc(mysql_query('SELECT pname FROM photos WHERE pphotoid=21'));
      echo $img["pname"];

Better would be

$img=mysqli_fetch_assoc(mysqli_query($link,'SELECT pname FROM photos WHERE pphotoid=21'));
echo $img["pname"];

answered Apr 18 '13 at 16:53

Hanky Panky

46,730
8
72
95

score 1 · Accepted Answer · edited May 23 '17 at 12:00

$handle = mysql_query('SELECT pname FROM photos WHERE pphotoid=21');
$row = mysql_fetch_row($handle);
echo $img[0];

http://pl1.php.net/mysql_fetch_row

this code will work for you.

To prevent SQL injection you should use escape functions like mysql_escape_string() you should also check what is your input and valid it

here you will learn more

How can I prevent SQL injection in PHP?

also it's better to use PDO because mysql_* are deprecated as of PHP 5.5.0, and will be removed in the future.

here is tutorial to learn connecting to db with PDO

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

your code in PDO(assuming you are connected to db) will look like: $id = intval(21); //this code here is senseless however if you get 21 for example via $_GET it will cast it to integer and prevent injection

$stmt = $db->prepare("SELECT pname FROM photos WHERE pphotoid=?");
$stmt->execute(array($id));
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['pname'];

score 1 · Answer 3 · answered Jul 05 '14 at 15:48

I like the following:

connection.php:

//All the $*_db variables are pulled from a file kept outside of the document root directory but referenced here to connect to the database
include('/path/to/file/not/in/docroot/connection_info.php');

$DBi = mysqli_connect($hostname_db, $username_db, $password_db, $database_db);
if($mysqli->connect_error) {
    //Do something for errors here...
};

file.php

include('connection.php');

$q_myQuery = "SELECT `pname` FROM `photos` WHERE `pphotoid` = 21";
$rsmyQuery = mysqli_query($DBi, $q_myQuery) or die(mysqli_error($DBi));
$row_rsmyQuery = mysqli_fetch_assoc($rsmyQuery);

$img = $row_rsmyQuery['pname'];

That's using mysqli* functions, not mysql* which have been deprecated. More on that here: http://us3.php.net/manual/en/mysqlinfo.api.choosing.php

How to get MySQL select query values into a PHP variable?

3 Answers3