Select from two tables in mysql

User avatar
JanMolendijk
Posts: 80
Location: Holland Rotterdam

Select from two tables in mysql

I looking for a example how to select data from two data-bases

i have a table group with: group_id, club_id & member_id
i have a table user with: user_id user_firstname, user_lastname

I wanna have the member_id from the group & user_id from user selected in html php mysqli code
This follow example is wrong + have errors

Code: Select all

<?php
   $connection_mysql = mysqli_connect("","u790012824_ubudu","","u790012824_egehy");
   
   if (mysqli_connect_errno($connection_mysql)){
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
   }
   
   $sql = "SELECT mem_id FROM group;SELECT user_id FROM user";
   
   if (mysqli_multi_query($connection_mysql,$sql)){
      do{
         if ($result=mysqli_store_result($connection_mysql)){
            while ($row=mysqli_fetch_row($result)){
               printf("%s '. $row['user_firstname']. '  \n",$row[0])";
            }
            mysqli_free_result($connection_mysql);
         }
      }while (mysqli_next_result($connection_mysql));
   }
   mysqli_close($connection_mysql);
?>

Admin
Hello
Try these sql queries:

Code: Select all

SELECT member_id, user_id, user_firstname, user_lastname FROM group, user WHERE member_id = user_id

or:

Code: Select all

SELECT t1.user_id, t1.user_firstname, t1.user_lastname, t2.member_id FROM user AS t1 LEFT JOIN group AS t2 ON t2.member_id = t1.user_id

If it not works as you wish, see the tutorials from these pages:
http://coursesweb.net/php-mysql/mysql-inner-left-join-right-join_t
http://coursesweb.net/php-mysql/select-two-mysql-tables_t

JanMolendijk
i getting 0 results as result please could you give full example

Code: Select all

<?php
// connect to the "tests" database
$conn = new mysqli('', 'u790012824_ubudu', '', 'u790012824_egehy');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// SELECT sql query
$sql = "SELECT t1.user_id, t1.user_firstname, t1.user_lastname, t2.member_id FROM user AS t1 LEFT JOIN group AS t2 ON t2.member_id = t1.user_id";

// perform the query and store the result
$result = $conn->query($sql);

// if the $result contains at least one row
if ($result->num_rows > 0) {
  // output data of each row from $result
  while($row = $result->fetch_assoc()) {
    echo '<br /> '. $row['user_firstname']. '  '. $row['user_lastname']. ' ' ;
  }
}
else {
  echo '0 results';
}

$conn->close();
?>

Admin
Test the sql queries in phpmyadmin, and try to make changes to see what results return.
I can try to help you if you post the structure (some rows) of those tables, and you tell what results you want to get.

JanMolendijk
i turnt into my script what still is not working the member_id to mem_id
group

Code: Select all

Type   Collatie   Attributen   Leeg   
1   group_id   int(11)         Nee   Geen      
2   club_id   int(11)         Nee   Geen      
3   mem_id   int(11)         Nee   Geen   

user

Code: Select all

Type   Collatie   Attributen   Leeg   Standaardwaarde   Extra   Actie
1   user_id   int(11)         Nee   0      
2   user_email   varchar(255)   utf8_general_ci      Nee   Geen      
3   user_password   varchar(255)   utf8_general_ci      Nee   Geen      
4   user_firstname   text   utf8_general_ci      Nee   Geen      
5   user_lastname   text   utf8_general_ci      Nee   Geen

I wanna have result from the group mem_id orderd by club_id into a table with the user user_firstname user_lastname
i just need one good example :)

Admin
Try this code:

Code: Select all

$sql ="SELECT t1.mem_id, t2.user_id, t2.user_firstname, t2.user_lastname FROM `group` AS t1 LEFT JOIN user AS t2 ON t1.mem_id = t2.user_id ORDER BY t1.club_id";
$result = $conn->query($sql) or trigger_error('SQL Error: '. $conn->error);

// if the $result contains at least one row
if($result && $result->num_rows >0){
  // output data of each row from $result
  while($row = $result->fetch_assoc()) {
    echo '<br/>'. $row['user_firstname']. '  '. $row['user_lastname'];
  }
}
else {
  echo '0 results';
}

- The "group" is a reserved word for mysql syntax, it is indicated to Not use it in table or column names; better change it with other name, or you have to use it allways in mysql query between `...`.

JanMolendijk
I tried this morning a view thinks & had result just before your posting
Great support Admin thanks i`m view day`s buissy to find out how tables are working

I used this code for my succes its only without the ORDER BY t1.club_id

Code: Select all

<?php
// connect to the "tests" database
$conn = new mysqli('', 'u790012824_ubudu', '', 'u790012824_egehy');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// SELECT sql query
$sql = "SELECT A.`user_firstname` , A.`user_lastname` , B.`mem_id`
FROM `user`AS A
INNER JOIN `group` AS B
ON B.`mem_id` = A.`user_id` ";

// perform the query and store the result
$result = $conn->query($sql);

// if the $result contains at least one row
if ($result->num_rows > 0) {
  // output data of each row from $result
  while($row = $result->fetch_assoc()) {
    echo '<br /> '. $row['user_firstname']. '   ' ;
  }
}
else {
  echo '0 results';
}

$conn->close();
?>

Adv. Posts: 01

Similar Topics