Chamara Denipitiya Chamara Denipitiya - 7 months ago 11
PHP Question

How to get information from 2 tables at once in PHP and MySQL?

<?php

include('includes/config.php');
$topi = $_GET['id']; //id of url

mysql_select_db("ban", $con);

$query = "SELECT * FROM `basic` WHERE id = '$topi' LIMIT 0, 30";

$result = mysql_query($query) or die(mysql_error());

$row = mysql_fetch_array($result) or die(mysql_error());

$aa = $row['item'];
$cc = $row['moreinfo'];
$dd = $row['contactinfo'];
$ff = $row['id'];


In this script, I get information from the table
basic
, but I want to retrieve data from another table named
users
. How can I retrieve data from two tables at once?

users
table consists of following columns:


  • email

  • username

  • ID


Answer

You need to JOIN the two tables on a common value, called a foreign key. Once you've posted the structure of the users table as requested in the comments, I can provide a more complete example.

EDIT: See example. This calls explicit column names instead of SELECT *.

$query = "SELECT 
    basic.id,
    basic.item,
    basic.moreinfo,
    basic.contactinfo,
    users.email,
    users.username
  FROM basic JOIN users ON basic.id = users.id
  WHERE id   = '$topi'
  LIMIT 0 , 30";