我是否需要在SQL中进行左连接，自然连接或简单连接? [英] Do I need a left, natural or simple join in SQL?
问题描述
我是PHP编码的新手，只是试图修复主要开发人员遗留下来的网站上的某些功能.
该网站 [Vloggi] 是一个市场.因此，我需要在任务页面中显示职位发布者的姓名.我有工作的表只有ID，没有名称.
所以我需要加入，但我已经尝试过，它破坏了整个站点.
SQL有17个表，我需要显示表3中包含的用户名(usr_name)，表7中包含的组织(usrg_orgname)和表14中的职位发布用户(vlop_usr_id)详细信息.
主键是users.usr_id，它链接到users_gor.usrg_usr_id和vlog-ops.vlog_usr_id.
表3:users
usr_id，usr_email，usr_password，usr_fbuser，usr_fbtoken， usr_name ，usr_loc_name，usr_loc_lat1，usr_loc_lon1，usr_loc_lat2，usr_loc_lon2，usr_status，usr_gor，usr_v
表7:users_gor
usrg_usr_id，usrg_creditops，usrg_creditvlog，usrg_creditvlogette，usrg_destination， usrg_orgname ，usrg_orgtype，usrg_location，usrg_website，usrg_jobtitle，usrg_phone，usrg_address1，状态
表14:vlog-ops
vlop_id，vlop_title，vlop_description，vlop_tags，vlop_deadline，vlop_quantity，vlop_quantityposted，vlop_vser_id， vlop_usr_id ，vlop_loc_name，vlop_loc_lat1，vlop_loc_lon1，
因此在main.php中，我编写了以下Sql查找 在main.php中，我有以下SQL查找:
$sql = "SELECT * FROM users_gor WHERE usrg_usr_id = ".$db->quote($user_info['usr_id'])." LIMIT 1";
$rows = $db->select($sql);
$users_gor = $rows[0];
$sql = "SELECT * FROM users_vgr WHERE usrv_usr_id = ".$db->quote($user_info['usr_id'])." LIMIT 1";
$rows = $db->select($sql);
$users_vgr = $rows[0];
$sql = "SELECT * FROM users WHERE usr_id = ".$db->quote($user_info['usr_id'])." LIMIT 1";
$rows = $db->select($sql);
$users = $rows[0];
$sql = "SELECT * FROM vlog-ops WHERE vlop_usr_id ".$db->quote($user_info['usr_id'])." LIMIT 1";
$rows = $db->select($sql);
$users = $rows[0];
$sql = "SELECT usr_name AS vlop_usr_name FROM users WHERE usr_id = ".$db->quote($user_info['usr_id'])." LIMIT 1";
$rows = $db->select($sql);
$users = $rows[0];
然后在页面模板本身中，我写了
<?php echo $vlop['vlop_vser_id'] ?>
<?php echo $vlop['vlop_usr_name'] ?>
第一个有效，第二个无效.我最终想要的是在表中显示用户名和组织名称.
每当我尝试进行JOIN或NATURAL JOIN或LEFT JOIN时，它都会中断，整个站点将变为空白.
向新手提供的任何帮助，都将受到一百万个感谢.
使用JOIN时，您需要指定如何加入他们. 在下面的查询中，我假设您正在从问题中搜索粗体字的字段.
$query='SELECT u.usr_name, g.usrg_orgname, v.vlop_usr_id FROM users u
JOIN vlog-ops v on u.usr_id = v.vlop_usr_id
JOIN users_gor g on u.usr_id = g.usrg_usr_id';
我相信我正确地知道了这些字段的名称，但如果不是，只需将其替换为正确的字段即可.
一旦获取了数据，您就可以遍历结果:
$result = mysqli_query($connection, $query);
while($row = mysqli_fetch_assoc($result)) {
echo 'User name = ' . $row['u.usr_name'];
echo 'Org name = ' . $row['g.usrg_orgname'];
echo 'Job posting user id = ' . $row['v.vlop_usr_id'];
}
I am new to PHP coding and just trying to fix some functionality on my site that was left over from the lead developer.
The site, [Vloggi], is a marketplace. So I need to show the name of the job poster in the assignments page . The table I have the jobs in only has the ID, not the name.
So I need a join, but I've tried and it breaks the entire site.
The SQL has 17 tables, I need to display the User Name (usr_name) contained in table 3, the organisation contained in table 7 (usrg_orgname) with the job posting user (vlop_usr_id) details in table 14.
The primary key is users.usr_id, which is linked to users_gor.usrg_usr_id and vlog-ops.vlog_usr_id.
Table 3: users
usr_id, usr_email, usr_password, usr_fbuser, usr_fbtoken, usr_name, usr_loc_name, usr_loc_lat1, usr_loc_lon1, usr_loc_lat2, usr_loc_lon2, usr_status, usr_gor, usr_vgr, usr_token, usr_regtoken,
table 7: users_gor
usrg_usr_id, usrg_creditops, usrg_creditvlog, usrg_creditvlogette, usrg_destination, usrg_orgname, usrg_orgtype, usrg_location, usrg_website, usrg_jobtitle, usrg_phone, usrg_address1, usrg_address2, usrg_state, usrg_postcode, usrg_country
Table 14: vlog-ops
vlop_id, vlop_title, vlop_description, vlop_tags, vlop_deadline, vlop_quantity, vlop_quantityposted, vlop_vser_id, vlop_usr_id,vlop_loc_name, vlop_loc_lat1, vlop_loc_lon1, vlop_loc_lat2, vlop_loc_lon2, vlop_campaign, vlop_rules, vlop_tips, vlop_status
So in main.php i have written the following Sql lookup in main.php, I have the following SQL lookups:
$sql = "SELECT * FROM users_gor WHERE usrg_usr_id = ".$db->quote($user_info['usr_id'])." LIMIT 1";
$rows = $db->select($sql);
$users_gor = $rows[0];
$sql = "SELECT * FROM users_vgr WHERE usrv_usr_id = ".$db->quote($user_info['usr_id'])." LIMIT 1";
$rows = $db->select($sql);
$users_vgr = $rows[0];
$sql = "SELECT * FROM users WHERE usr_id = ".$db->quote($user_info['usr_id'])." LIMIT 1";
$rows = $db->select($sql);
$users = $rows[0];
$sql = "SELECT * FROM vlog-ops WHERE vlop_usr_id ".$db->quote($user_info['usr_id'])." LIMIT 1";
$rows = $db->select($sql);
$users = $rows[0];
$sql = "SELECT usr_name AS vlop_usr_name FROM users WHERE usr_id = ".$db->quote($user_info['usr_id'])." LIMIT 1";
$rows = $db->select($sql);
$users = $rows[0];
And then in the page template itself, I have written
<?php echo $vlop['vlop_vser_id'] ?>
<?php echo $vlop['vlop_usr_name'] ?>
The first one works, the second doesn’t. What I want eventually is to display the user name and the organisation name in a table.
Whenever I try a JOIN or a NATURAL JOIN or a LEFT JOIN it breaks and the entire site goes blank.
Any help for a newbie would be appreciated with a million thanks.
When you use JOIN you need to specify how you're joining them. In the query below I'm assuming you're looking for the fields in bold from your question.
$query='SELECT u.usr_name, g.usrg_orgname, v.vlop_usr_id FROM users u
JOIN vlog-ops v on u.usr_id = v.vlop_usr_id
JOIN users_gor g on u.usr_id = g.usrg_usr_id';
I believe I got the name of the fields right but if not just replace them with the correct ones.
Once you have the data fetched, you just loop through the results:
$result = mysqli_query($connection, $query);
while($row = mysqli_fetch_assoc($result)) {
echo 'User name = ' . $row['u.usr_name'];
echo 'Org name = ' . $row['g.usrg_orgname'];
echo 'Job posting user id = ' . $row['v.vlop_usr_id'];
}
这篇关于我是否需要在SQL中进行左连接，自然连接或简单连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
