Select from two tables in mysql

[JanMolendijk]

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);
?>

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:
https://coursesweb.net/php-mysql/mysql-i ... ght-join_t
https://coursesweb.net/php-mysql/select- ... l-tables_t

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();
?>

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.

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

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 `...`.

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();
?>