Forum

Posted: **11 Jan 2017, 15:31**

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

Posted: **11 Jan 2017, 15:56**

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

Posted: **11 Jan 2017, 17:01**

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

Posted: **11 Jan 2017, 17:28**

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.

Posted: **11 Jan 2017, 17:58**

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

Posted: **12 Jan 2017, 08:06**

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

Posted: **12 Jan 2017, 11:40**

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

Forum

Select from two tables in mysql

Select from two tables in mysql

Select from two tables in mysql

Select from two tables in mysql

Select from two tables in mysql

Select from two tables in mysql

Select from two tables in mysql

Select from two tables in mysql