Jump to content


Photo

MySQL Join

php mysql join

  • Please log in to reply
2 replies to this topic

#1 spertuit

spertuit

    Advanced Member

  • Members
  • PipPipPip
  • 59 posts
  • LocationLouisiana

Posted 11 July 2013 - 10:42 AM

Can anyone give me a hand turning this into a join statement?

$query1 = sprintf("SELECT * FROM companies WHERE status = 1");
$result1 = mysql_query($query1);
while ($row1 = mysql_fetch_assoc($result1)) {
	$C1=$row1["companyName"];
	$C3=$row1["id"];
	$C4=$row1["businessType"];
		
$query="SELECT * FROM companyAdmins WHERE companyID = '$C3'";
$result=mysql_query($query);
$num=mysql_numrows($result);
$email=mysql_result($result,0,"email");

echo '<p>' . $C1 . ' @ ' . $email . '</p>';
 
}

Im trying to select companyName,id, and business type from the companies table where the status is one

and I'm also trying to select the email from the companyAdmins table where the companyID is equal to the id from the companies table.

 

I want to basically get

 

companyName.companies, id.companies, businessType.companies, and email.companyAdmins

 

I have this file written and now I'm trying to go back and refactor the code and make everything cleaner.


Database, no problem. Just Google "Truncate."

#2 AbraCadaver

AbraCadaver

    Cracka Memba

  • Gurus
  • 1,888 posts
  • LocationThe Republic of Texas

Posted 11 July 2013 - 11:13 AM

Not tested:

SELECT companies.companyName, companies.id, companies.businessType, companyAdmins.email
FROM companies, companyAdmins
WHERE companies.id = companyAdmins.companyID AND companies.staus = 1

mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.

#3 thara

thara

    Advanced Member

  • Members
  • PipPipPip
  • 423 posts
  • LocationPanda

Posted 11 July 2013 - 11:23 AM

Perhaps, You can use 'join'

 

Try this,

SELECT c.companyName, c.id, c.businessType, ca.email
FROM companies c
INNER JOIN companyAdmins ca ON
    c.id = ca.companyID
WHERE c.staus = 1

IT consultation and Integration : Web Design and Web Development.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com