CODEWITHSUNDEEP
phpmysql
Sebastian
Sebastian

Reputation: 3628

PHP fetching data from MySQL database

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?

Upvotes: 3

Views: 14076

Answers (5)

Shashi Thakur
Shashi Thakur

Reputation: 11

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];

}

?>

Upvotes: 1

Johan
Johan

Reputation: 76537

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'];
}

Upvotes: 0

Billy Moon
Billy Moon

Reputation: 58521

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());

Upvotes: 3

user562854
user562854

Reputation: 

$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());

Upvotes: 2

Martin Vrkljan
Martin Vrkljan

Reputation: 879

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

Upvotes: 1

Related Questions