[RESOLVED] Selecting Multiple Rows with same ID

chandan_tiwari

hi everyone,

I want to know whether i can select multiple rows from a MYSQL database table with one ID common in all of them.

Not getting it............the full story is like this---

My site has a member system. I want that when my user logs in he/she can create any no of new profiles (profile form includes--name, dob,country etc.), now my script is storing that new profile along with the members existing userID in a new database table.
Now i want to retrieve all the profiles he/she has created in a select box where he/she can select the profile to do some operations.
How can i implement it?

please help me !!

Thanking you!

djjjozsi

The answer is right here:

http://sfx.site40.net/profiles_to_user

hello, jjozsi.

chandan_tiwari

Thank you very much for your reply.

yes, that's what i was looking for.

but i don't know much about foreign keys. Can you please let me know how can i create the tables and use it.

Thanking You !

djjjozsi

do you know what is a primary key in a table ? You read the definition about it: foreign key

If you use a primary key in a related table that field its called foreign key.

chandan_tiwari

Thank you
i got the concept of foreign key.

but there is one problem how can i retrieve all the profile values of particular user in the php variable.

What will be the select statement .
The one given on the web page is for all the users, i want only for the particular user.

djjjozsi

add a
WHERE user_id='$value'
to this query.

chandan_tiwari

Thanks !

i tried doing that but i am getting error ..
my code is:

$query = SELECT profiles.dob, profiles.mob FROM profiles RIGHT JOIN (profile_connector RIGHT JOIN user ON profile_connector.user_id = user.user_id) ON profiles.profile_id = profile_connector.profile_id WHERE  user_id= '$_SESSION['userId']';
$result = mysql_query($query, $GLOBALS['DB']);
if (!$result) {
    echo 'Could not run query: '. mysql_error();
}
$row = mysql_fetch_row($result);

echo $row[0];
echo $row[1];

Error is :

Parse error: syntax error, unexpected T_STRING in /home/rishaorg/php_code/profiles.php on line 3

djjjozsi

you forget the quote-mark when you created the string.
Are you sure that sql works? If not, then check it in phpmyadmin.
If you have more than one result the technique how you get these results is different.
Here is an example how to check if it has no result, one result, and has more then one result.

<?php

$query = sprintf( "SELECT profiles.dob, profiles.mob FROM profiles 
   	   	   	   	 RIGHT JOIN (
							 profile_connector 
							   	 RIGHT JOIN user 
									ON profile_connector.user_id = user.user_id
							) 
				 			ON profiles.profile_id = profile_connector.profile_id 
				 WHERE  user_id= '%d'", $_SESSION['userId'] );
$result = mysql_query( $query, $GLOBALS['DB'] );
if ( !$result )
{
		echo 'Could not run query: ' . $query . " , error:" . mysql_error();
} 

$nums = mysql_num_rows( $result );

if ( $nums < 1 )
{ 
		// if the results is 0
		echo "No result";
} elseif ( $nums == 1 )
{ 
		// if we have only one result
		$row = mysql_fetch_assoc( $result );
		echo $row["dob"];
		echo $row["mob"];
} 
else
{ 
		// if we have more then one results
		while ( $row = mysql_fetch_assoc( $result ) )
		{
				echo $row["dob"];
				echo $row["mob"];
				echo "<br />";
		} 
} 

?>

Lets have a look at these commands in the manual:

mysql_error
mysql_fetch_assoc
mysql_num_rows
sprintf

chandan_tiwari

I am getting the following error :

Could not run query: SELECT profiles.dob, profiles.mob FROM profiles RIGHT JOIN (profile_connector RIGHT JOIN user ON profile_connector.user_id = user.user_id) ON profiles.profile_id = profile_connector.profile_id WHERE user_id= '1' , error:Column 'user_id' in where clause is ambiguous
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/rishaorg/php_code/profiles.php on line 13
No result

chandan_tiwari

Do i need to use

WHERE  profile_connector.user_id= '%d'

because i have used the same user_id in 'user' and 'profile_connector' table
though both are same.

chandan_tiwari

yeah it works by adding profile_connector.user_id

Thank you very much for your support 'dear djjjozsi'.

Thanks a lot

thank you very much once again!

djjjozsi

yo know that they are the same, but the mysql interpreter douesn't
So always determine which table . which field you're thinking of.

your're welcome 🙂

don't forget to set this thread as resolved if its already solved.

jjozsi