Select from two tables in mysql

User avatar
JanMolendijk
Posts: 112
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-i ... ght-join_t
http://coursesweb.net/php-mysql/select- ... l-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(); ?>

Similar Topics