Selecting from 2 tables

[Ne.OnZ]

Hello, just wondering how I would do this:

 

I have 2 tables named: users and news. I want to select the username from users, get a value from one of the columns there then check the table users to see if the username matches the one in the news table. If it does echo the value I selected from the users table.

 

Did that make sense?

 

Thank You! 

[radar]

Use a join...  Since i dont know the setup of your table, here is the way i do mine..

<?php 

$data = "SELECT u.u_id , u.d_id, u.f_name, u.l_name, u.d_name, u.email, u.password, u.date_added, u.status, d.department FROM users u LEFT JOIN departments d ON d.d_id = u.d_id ORDER BY u.u_id";
$data = mysql_query($data);
$cnt = mysql_num_rows($data);
$data = sql_md_array($data, $cnt) // returns a multi dimensional array

function sql_md_array($query, $cnt) {
for ( $row = 0; $row < $cnt && $array = mysql_fetch_assoc($query); $row++ ) {
foreach ($array as $key => $value) {
  $mda[$row][$key] = $value;
  }
}
	return $mda;
}

?>

 

hope it makes sense for you..

[Ne.OnZ]

Makes no sense lol...

[radar]

No problem brother i'll explain it a bit...

<?php 

$data = "SELECT u.u_id , u.d_id, u.f_name, u.l_name, u.d_name, u.email, u.password, u.date_added, u.status, d.department FROM users u LEFT JOIN departments d ON d.d_id = u.d_id ORDER BY u.u_id";

?>

 

First we setup our select statement for selecting from 2 tables (or even more)...

 

In this example, I am using a LEFT JOIN but there is also an INNER JOIN, and some others that I never use...

 

I have two tables setup, one is users the other is departments.

 

so u.u_id, u.d_id, u.f_name, u.l_name, u.d_name, u.email, u.password, u.date_added, u.status all selects that data from my users table.

 

d.department is drawn from my department table...

 

each instance after the periods is a column name in one of my tables.

 

FROM users u

 

this is where you put your first table, notice the u after the name of my table.

 

LEFT JOIN departments d

 

is where we are setting up the left join to make this whole thing work...  again notice the d after the name of my table.

 

ON d.d_id = u.d_id ORDER BY u.u_id";

 

This here is quite simple..  the d.d_id = u.d_id is a column name in each table..  when selecting from two tables you want one column in each to have the same value so you can do a join such as my department ID (d_id)

 

Now we have our query setup so we need to process it..

 

<?php
$data = mysql_query($data); // run the query to get a result ID
$cnt = mysql_num_rows($data); // check how many rows we have
$data = sql_md_array($data, $cnt) // returns a multi dimensional array

// this function i custom wrote and will provide a multi dimensional array such as after this code block.
function sql_md_array($query, $cnt) {
for ( $row = 0; $row < $cnt && $array = mysql_fetch_assoc($query); $row++ ) {
foreach ($array as $key => $value) {
  $mda[$row][$key] = $value;
  }
}
	return $mda;
}

?>

 

Array

(

    [0] => Array

        (

            [hd_id] => 5

            [cat_name] => Abuse / Security

            [count] => 9

        )

 

    [1] => Array

        (

            [hd_id] => 6

            [cat_name] => Account Changes / Addons

            [count] => 6

        )

 

    [2] => Array

        (

            [hd_id] => 3

            [cat_name] => Billing

            [count] => 5

        )

 

    [3] => Array

        (

            [hd_id] => 11

            [cat_name] => CPanel

            [count] => 4

        )

)

 

I wrote the function above to put my arrays into a suitable arrangement for use with Smarty, the template engine I use..  but could very well be used with a for loop to output your HTML if you are running HTML within your logic.

 

I hope this explains it a bit better for you?