PHP fetching data from MySQL database

Question

So I'm trying to fetch data in a many-to-many relationship.

So far I have this, which finds the user:

$user = $_SESSION['user'];
$userID = mysql_query("SELECT * FROM users WHERE user='$user'") or die(mysql_error());

And I know that to echo this information I have to put it in an array like so:

while ($r = mysql_fetch_array($userID)) {
echo $r["0"];
}

This works fine, but when I try to find this variable in another table, I'm not sure what to use as the variable:

$projects = mysql_query("SELECT projects_ID FROM projects_users WHERE users_ID='???'") or die(mysql_error());

I've tried replacing ??? with $userID and $r, but to no avail. I know the code works because it's fine when I put a user ID in manually - where have I gone wrong?

Your sql query is vulnerable to SQL Injection. See this: segfaultlabs.com/files/pdf/php-session-security.pdf — user562854
– user562854, Commented Jun 4, 2011 at 11:33

Johan · Accepted Answer · 2011-06-04 12:54:36Z

3

Untested, but this should work:

$user = mysql_real_escape_string($_SESSION['user']);
$query = mysql_query("SELECT * FROM users WHERE user='$user'") or die(mysql_error());

$result = mysql_fetch_array($query);
$userID = $result[0];

$projects = mysql_query("SELECT projects_ID FROM projects_users 
  WHERE users_ID='$userID'") or die(mysql_error());

edited Jun 4, 2011 at 12:54

Johan

77.4k28 gold badges204 silver badges346 bronze badges

answered Jun 4, 2011 at 11:14

Billy Moon

58.9k27 gold badges148 silver badges244 bronze badges

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

Comments

user562854 · Accepted Answer · 2011-06-04 11:34:54Z

2

$user = $_SESSION['user'];
$query = mysql_query("SELECT * FROM users WHERE user='".mysql_real_escape_string($user)."' LIMIT 1") or die(mysql_error()); //--note the LIMIT

$result = mysql_fetch_array($query);
$userID = $result[0];

$projects = mysql_query("SELECT projects_ID FROM projects_users WHERE users_ID='$userID'") or die(mysql_error());

answered Jun 4, 2011 at 11:34

user562854

Comments

Martin Vrkljan · Accepted Answer · 2011-06-04 11:15:57Z

1

I your case, you'd need to place $r[0] there.

answered Jun 4, 2011 at 11:15

Martin Vrkljan

86910 silver badges20 bronze badges

Comments

jnhghy - Alexandru Jantea · Accepted Answer · 2015-01-21 11:19:34Z

1

I think this code is helpful for beginners when you want to get data in array form

we use mysqli instead of mysql to protecting your data from SQL injection.

Before use this code check the database connection first

<?php $tableName='abc';

$qry="select * from $tableName";

$results=mysqli_query($qry);

while($records=mysqli_fetch_array($results))

{
$firstrecord=$records[1];

$secondrecord=$records[2];

}

?>

edited Jan 21, 2015 at 11:19

jnhghy - Alexandru Jantea

2,78028 silver badges36 bronze badges

answered Aug 21, 2013 at 12:46

Shashi Thakur

113 bronze badges

Comments

Johan · Accepted Answer · 2011-06-04 13:01:20Z

0

You can get your projects with one query:

$user = mysql_real_escape_string($_SESSION['user']);

$query = mysql_query("SELECT pu.projects_ID FROM users u 
           INNER JOIN projects_users pu ON (pu.users_ID = u.users_id)    
           WHERE u.user='$user'") or die(mysql_error());

$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
  echo $row['projects_ID'];
}

answered Jun 4, 2011 at 13:01

Johan

77.4k28 gold badges204 silver badges346 bronze badges

1 Comment

showdev Over a year ago

"This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used." mysql_query.

Collectives™ on Stack Overflow

PHP fetching data from MySQL database

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related