Geek Answers Handbook

Choosing * from two tables using php and mySQL

I have two tables, one of them is projects, and the other is users.

PROJECTS table
ID     |     USER_ID    |   NAME
-------------------------------------
80     |        1       |   ABC Co.
82     |        2       |   XYZ Inc.

USERS table
ID     |     FIRSTNAME  |   LASTNAME
-------------------------------------
1      |     Joe        |   Namath
2      |     Jimmy      |   Fallon

I want to write a query, for example:

SELECT * FROM PROJECTS, USERS WHERE PROJECTS.USER_ID=USERS.ID AND FIRSTNAME = "Joe"

I can successfully execute the request in php, but when I try to access the results, I do not get what I want. I understand why, but I cannot figure out how to fix it. For instance:

$row = mysqli_fetch_query($awesomeDatabaseLink);
echo $row['ID];  //returns '1' and I really wanted '80'

I understand. Two tables have fields with the same name, but this is not the same data. MySQL returns its best guess about what I so ambiguously requested. However, I also tried:

echo $row['PROJECTS.ID']; //returns an empty string.

, "*" . "" ( , ). "" , , .

? SH

+4
4

- ( ).

, * . , * .

, JOIN. . :

SELECT PROJECTS.*
     , USERS.*
     , PROJECTS.ID AS MY_PROJECTS_ID
  FROM PROJECTS
  JOIN USERS
    ON USERS.ID=PROJECTS.USER_ID
   AND USERS.FIRSTNAME = "Joe"

MY_PROJECTS_ID , .

, , MY_PROJECTS_ID.

( ) , , .

+3

. .

:

SELECT 
PROJECTS.ID AS project_id, 
USER_ID, 
NAME, 
USERS.ID AS user_id, 
FIRSTNAME, 
LASTNAME 
FROM PROJECTS, USERS 
WHERE PROJECTS.USER_ID=USERS.ID AND FIRSTNAME = "Joe"

:

echo $row['project_id']; //returns Project id

, .

+2

, ( , table.column). , .

SELECT u.ID AS UserId,
       u.FIRSTNAME AS FirstName,
       u.LASTNAME AS LastName,
       p.ID AS ProjectId,
       p.NAME AS ProjectName
FROM   USERS AS u
JOIN   PROJECTS AS p ON p.USER_ID = u.ID
WHERE  u.FIRSTNAME = "Joe"

, , , , :

SELECT *,
       u.ID as USERID,
       p.ID as PROJECTID
FROM   USERS AS u
JOIN   PROJECTS AS p ON p.USER_ID = u.ID
WHERE  u.FIRSTNAME = "Joe"
+1

, .

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Using single SQL</title>
<style>
table,td,th
{
 padding:10px;
 border-collapse:collapse;
 font-family:Georgia, "Times New Roman", Times, serif;
 border:solid #ddd 2px;
}
</style>
</head>
<body>
<table align="center" border="1" width="100%">
<tr>
<th>product id</th>
<th>product name</th>
<th>category name</th>
</tr>
<?php
mysql_connect("localhost","root");
mysql_select_db("dbtuts");
$res=mysql_query("SELECT c.* , p.* FROM tbl_categories c,tbl_products p WHERE c.cat_id=p.cat_id");
while($row=mysql_fetch_array($res))
{
 ?>
    <tr>
    <td><p><?php echo $row['product_id']; ?></p></td>
    <td><p><?php echo $row['product_name']; ?></p></td>
    <td><p><?php echo $row['cat_name']; ?></p></td>
    </tr>
    <?php
}
?>
</table>
</body>
</html>

[link] (http://www.codingcage.com/2014/12/fetch-data-from-multiple-tables-in-php.html)!

0

More articles:


All Articles