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!

php.net/mysql_fetch_assoc Ummm infact it should be mysqli

Hanky Panky
– Hanky Panky

2013-04-18 16:51:29 +00:00
Commented Apr 18, 2013 at 16:51 — Hanky Panky
– Hanky Panky, Commented Apr 18, 2013 at 16:51

Hanky Panky · Accepted Answer · 2013-04-18 16:53:10Z

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, 2013 at 16:53

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2023-11-17 20:13:27Z

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

https://www.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'];

Community · Accepted Answer · 2023-11-21 15:10:29Z

1

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: https://www.php.net/manual/en/mysqlinfo.api.choosing.php

edited Nov 21, 2023 at 15:10

CommunityBot

11 silver badge

answered Jul 5, 2014 at 15:48

Jason

15.4k23 gold badges87 silver badges119 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related