We help IT Professionals succeed at work.

PHP MySQL Query 2 seperate tables

mimoser
mimoser used Ask the Experts™
on
I have the query below, where the aboutme works great....put I wanted to add a new output called 'new', but use the same query. The problem is.. The 'new' information resides in a seperate table called 'user'

I know I can do:

SELECT new FROM user.. but I wanted to use the same $tmp + $i

Can this be done?
$tmp=mysql_query("SELECT * FROM profiles WHERE user_name ='".$_GET['gud']."'");
	$do_user_exist = mysql_num_rows($tmp);

	while($i=mysql_fetch_array($tmp)) {
	
	<b><?php echo C_PRO8;?></b>: <?php echo ($i['aboutme']);?><br>
	<b><?php echo C_PRO9;?></b>: <?php echo ($i['new']);?>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Is there a field that links the tables profiles and user?
Let's say it is user_id from profiles and id from user - you could then use this query.

$tmp=mysql_query("SELECT p.*, u.new FROM profiles p left join user u on u.id=p.user_id WHERE p.user_name ='".$_GET['gud']."'");

Author

Commented:
I think... lol

They both have a user_name field
Give alias to identify the fields with same name in two table
$tmp=mysql_query("SELECT profilealias.new as newsdetails,useralias.aboutme as abtme, FROM profiles profilealias left join user useralias on profilealias.user_id =useralias.id WHERE profilealias.user_name ='".$_GET['gud']."'");
	$do_user_exist = mysql_num_rows($tmp);

	while($i=mysql_fetch_array($tmp)) {
	
	<b><?php echo C_PRO8;?></b>: <?php echo ($i['abtme']);?><br>
	<b><?php echo C_PRO9;?></b>: <?php echo ($i['newdetails']);?>

Open in new window

Author

Commented:
@rinirinku

Is there still a way to do that while gaining everything in the profile table? I had other data such as below, that I didn't include in the example.
<b><?php echo C_PRO4;?></b>: <?php echo ($i['age']);?><br>
			<b><?php echo C_PRO5;?></b>: <?php if($i['gender']=='1'){echo C_PRO13;}elseif($i['gender']=='2'){echo C_PRO14;}?><br>
			<b><?php echo C_PRO6;?></b>: <?php echo ($i['location']);?><br>
			<b><?php echo C_PRO7;?></b>: <?php echo ($i['hobbies']);?><br>
			<b><?php echo C_PRO8;?></b>: <?php echo ($i['aboutme']);?><br>
			<b><?php echo Hookah Bux;?></b>: <?php echo ($i['bux']);?>

Open in new window

give special alaisname to the field which have same name in both the table.. and for rest of the field use *.profilealias

	
	

Open in new window

To be more clear i
if you want to select all the fields in the profile and come fields which have same field name in both the table
$tmp=mysql_query("SELECT *,profilealias.new as newsdetails,useralias.aboutme as abtme, FROM profiles profilealias left join user useralias on profilealias.user_id =useralias.id WHERE profilealias.user_name ='".$_GET['gud']."'");.... display the field name you assign for about us the rest of field same as the database name
$tmp=mysql_query("SELECT *,profilealias.new as newsdetails,useralias.aboutme as abtme, FROM profiles profilealias left join user useralias on profilealias.user_id =useralias.id WHERE profilealias.user_name ='".$_GET['gud']."'");
	$do_user_exist = mysql_num_rows($tmp);

	while($i=mysql_fetch_array($tmp)) {
	
	<b><?php echo C_PRO8;?></b>: <?php echo ($i['abtme']);?><br>
	<b><?php echo C_PRO9;?></b>: <?php echo ($i['newdetails']);?>
<b>
<?php echo C_PRO4;?></b>: <?php echo ($i['age']);?><br>
			<b><?php echo C_PRO5;?></b>: <?php if($i['gender']=='1'){echo C_PRO13;}elseif($i['gender']=='2'){echo C_PRO14;}?><br>
			<b><?php echo C_PRO6;?></b>: <?php echo ($i['location']);?><br>
			<b><?php echo C_PRO7;?></b>: <?php echo ($i['hobbies']);?><br>
			
			<b><?php echo Hookah Bux;?></b>: <?php echo ($i['bux']);?>

Open in new window

@mimoser:
hope you got my point
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
$tmp=mysql_query("SELECT p.*, u.new FROM profiles p left join user u on u.user_name =p.user_name  WHERE p.user_name ='".$_GET['gud']."'");
i doubt p.* is correct instead of * as i got syntax error while using query
$tmp=mysql_query("SELECT p.*, u.new FROM profiles p left join user u on u.user_name =p.user_name  WHERE p.user_name ='".$_GET['gud']."'");
INSTEAD OF
$tmp=mysql_query("SELECT *, u.new FROM profiles p left join user u on u.user_name =p.user_name  WHERE p.user_name ='".$_GET['gud']."'");
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
rinirinku,

I am sure any version of mysql will work with that query.
What they mean

p.*, u.new   ===   all columns from p, and the "new" column from u.  As requested
*, u.new   ===    all columns from all tables, which will include u.new, AS WELL AS u.new as a separate column.

So, "*, u.new" gives you what "p.*, u.new" gives, AS WELL AS all the columns in user table again.
@cyberkiwi:
that i know clearly
but i dont know why i am getting error when using ' p.*'
i also had the same issue few days back ie y i am keen to know why this happens
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
I would try typing it out rather than copying over the web.  I just had an issue with another copied query that complained about a space, which turned out to be char(160), the HTML entity &nbsp; that crept into the query.
i tried with my own query and not from the one posted. the issue is the same
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.