List row values from different tables

Linked by user id, separated by commas

[s9901470]

Hi

I have rows of values stored in different mysql tables, linked by a primary key 'userid'.

I would like a snippet of code that will print a complete row of values for each person, separated by the table name and commas. This will help me combine the tables into excel later.

user1,TABLE1,1,0,1,1,TABLE2,1,1,1,0,0
user2,TABLE1,0,0,0,1,TABLE2,0,0,1,0,1

Any suggestions?

Thanks

[khaki monster]

would you please at least give us the fields of those two table? so that we know what are those fields for..

cheerz!

[coopster]

Or better yet, give the code a shot first, see what you come up with and if you have troubles we can go from there ;)

And welcome to WebmasterWorld there, khaki monster.

[s9901470]

Sorry yes.

In table1, q1 ... q100
In table2, q101 ... q200

Here is what I've got so far. And I want to:

1. Combine them to it prints one result for each username, linking the two tables together
2. Avoid typing out q1 ... q100 manually

require 'db_connect.php';
require_once('DbConnector.php');
$connector = new DbConnector();
mysql_select_db('table1');
$result = $connector->query("SELECT * FROM table1 WHERE username = 'username' ");
while ($row = $connector->fetchArray($result)){
echo ' '.$row['username'].','.$row['q1'].','.$row['q2'].','.$row['q3'].','.$row['q4'].','.$row['q100'].', ';
}

mysql_select_db('table2');
$result = $connector->query("SELECT * FROM table2 WHERE username = 'username' ");
while ($row = $connector->fetchArray($result)){
echo ' '.$row['username'].','.$row['q101'].','.$row['q102'].','.$row['q103'].','.$row['q104'].','.$row['q200'].', ';
}

[coopster]

OK, then let's start with the JOIN statement in your query. Will there always be a matching userid in both tables? This old thread on SQL Join problems [webmasterworld.com] has some explanations that might help you decide how you want to JOIN the tables.

[s9901470]

OK so far I have this, but it doesn't seem to work. Any suggestions?

"SELECT
table1.*,
table2.*,
FROM table1,table2
LEFT JOIN table1 ON (table1.username = table2.username)
ORDER BY table1.username"
");
while ($row = $connector->fetchArray($result)){
echo ' '.$row['username'].','.$row['q1'].','.$row['q100'].' ';
}

[s9901470]

OK I've got it started now - thanks for the JOIN tip, I've included in the example below just one from each table (q1,q101,q201) but there will eventually be 300 variables to print (q1-q100 in table1, q101-q200 in table2, q201-q300 in table3)!

How can I do that without typing them all out individually in the sql statement?

echo 'username,Q1,Q101,Q201<BR>';
$query = "select table1.username AS username, table1.q1 AS q1, table2.q101 AS q101, table3.q201 AS q201
FROM table1
LEFT JOIN table1
ON table1.username=table2.username
LEFT JOIN table3
ON table2.username=table3.username
";
$result = mysql_query($query) or die(mysql_error());
// Print out the contents of each row separated by commas
while($row = mysql_fetch_array($result)){
echo ' '.$row['username'].','.$row['q1'].','.$row['q101'].','.$row['q201'].',<BR>';
}

[Habtom]

What is the result you are getting? Is it a syntax error. The query should come to this:

SELECT table1.*, table2.* FROM table1
LEFT OUTER JOIN table2 ON (table1.username = table2.username) ORDER BY table1.username

Habtom

[coopster]

Habtom is correct here, do you see why? You have your first LEFT JOIN on the same table! Not what you wanted. Use the update provided and go from there now ...

$query = "SELECT 
 table1.*, 
 table2.*, 
 table3.* 
FROM table1 
LEFT JOIN table2 ON table1.username=table2.username 
LEFT JOIN table3 ON table1.username=table3.username 
"; 
$result = mysql_query($query) or die(mysql_error()); 
// Print out the contents of each row separated by commas 
while($row = mysql_fetch_array($result)) { 
 echo ' '.$row['username'].','.$row['q1'].','.$row['q101'].','.$row['q201'].',<BR>'; 
}

What is happening here is you are telling the database engine to return all the rows from every table -- the syntax 'table1.*' is handling that. Oh-oh, we have the

username

column in each table though! According to the mysql_fetch_array [php.net] function the last value is going to replace any previous value in the result set! Well, it doesn't matter to us, we are joining

username

so it will always be the same anyway! So, who cares ;) Now we can picture the data coming back. It probably looks something like ...

Name1, q1, q2, q3, ... Name1, q101, q102, q103, Name1, q201, q202, q203 ... q299 
Name2, q1, q2, q3, ... Name2, q101, q102, q103, Name2, q201, q202, q203 ... q299 
Name3, q1, q2, q3, ... Name3, q101, q102, q103, Name3, q201, q202, q203 ... q299 
Name4, q1, q2, q3, ... Name4, q101, q102, q103, Name4, q201, q202, q203 ... q299

Now is the easy part. Instead of printing all those variables one-by-one you just create a loop. You know how many columns there are, and you know your naming convention. So now apply that understanding to some logic and you can loop and build your result set without hard-coding all those column names.

// Print out the contents of each row separated by commas 
$out = ''; // initialize 
while($row = mysql_fetch_array($result)) { 
 $out .= $row['name'].','; // start with the name 
 // column questions run from 1 - 299, and all start with 'q' 
 for [php.net] ($i = 1; $i <= 299; $i++) { 
   $out .= $row['q'.$i] . ','; 
 } 
 $out = rtrim [php.net]($out, ',') . "\n"; // get rid of the last comma, add a newline 
} 
print $out;

[s9901470]

Thanks Coopster and Habtom - that works perfectly!

Next, I want to exclude any rows which have substantial missing values.

For example "exclude if more than three 9s in a row", where 9 is my missing/null value.

Can that be done?

[s9901470]

I've also come across a second issue, that when I join another table called h with variable names starting with a different letter (h), I can't use that code.

How can I modify that last bit to get the contents from these tables?

tableh (h1-h40)
table1 (q1-q100
table2 (q101-q200)
table2 (q201-q300)

// Print out the contents of each row separated by commas
$out = ''; // initialize
while($row = mysql_fetch_array($result)) {
$out .= $row['username']. ',';
// start with the name
// column questions run from 1 - 299, and all start with 'q'
for ($i = 1; $i <= 300; $i++) {
$out .= $row['i'.$i] . ',';
}
$out = rtrim($out, ',') . "<BR>"; // get rid of the last comma, add a newline
}

print $out;

I'm almost there! Any help most appreciated :0)

[coopster]

You need to understand how the for loop is building the data in the example provided. Have a closer look at that for loop so you can understand what is happening there. Hint: you can put another for loop in front of this one to build the 'h' questions data first.

[s9901470]

I see. This seems to work:

while($row = mysql_fetch_array($result)) {
$out .= $row['username'].','; // start with the name
// column questions run from 1 - 44, and all start with 'h'
for ($i = 1; $i <= 44; $i++){$out .= $row['h'.$i] . ',';}
// column questions run from 1 - 299, and all start with 'q'
for ($i = 1; $i <= 300; $i++){$out .= $row['q'.$i] . ',';}
$out = rtrim($out, ',') . "<BR>"; // get rid of the last comma, add a newline
}
print $out;

Although, to be totally sure it's printing the correct variables, is there a way to have the first line print all the variable names it has called? These could become column headings if I export into excel etc.

Thanks very much for all your help on this, it's looking great.

[coopster]

Hey, you got it! Nice job ;)

Yes, there are a number of ways to get the headings out. If you think about what is going on here though you might be able to figure this one out as well. Let me explain. Each $row is an array of data being fetched from the database table. The key word here is array. We know that arrays are made up of key=value pairs, like so:

Array 
( 
  [username] => FirstUser 
  [h1] => anwser to h1 
  [h2] => answer to h2 
  [h3] => answer to h3 
  . 
  . 
  . 
  [q300] => answer to q300 
)

So, knowing how the data looks in that array, what part do you want? The keys, right?

[s9901470]

I see! So I want field names instead of row names. I had a look at doing this at the database level and found that mysql_field_name worked perfectly!

Many thanks for your help with all of this :0)

Any suggestions on how to identify strings of missing values e.g. do not display if there are 3 or more 9s in a row?

[s9901470]

I'm also having problems modifying this so that it can detect variables starting with either 'h' or 'q'. Any suggestions? At the moment it lists the variables but only according to the numbers given in the loop, not according to what letter they start with.

for ($i = 0; $i <= 43; $i++){ echo '<th>'.mysql_field_name($result, $i).'</th>'; }
for ($i = 50; $i <= 50; $i++){ echo '<th>'.mysql_field_name($result, $i).'</th>'; }
for ($i = 58; $i <= 58; $i++){ echo '<th>'.mysql_field_name($result, $i).'</th>'; }
for ($i = 1; $i <= 300; $i++){ echo '<th>'.mysql_field_name($result, $i).'</th>'; }

[coopster]

I want field names instead of row names

Yes, you want the column names which are the 'keys' in each $row being read in.

mysql_fetch_array()

returns both the associative index as well as a numerical index for each and every column returned in the result set row. The example I showed you only shows the associative index. That is not truly what is being returned by mysql_fetch_array() [php.net] but it is indeed what is being returned by mysql_fetch_assoc() [php.net].

You could check the difference by changing your while loop. Try each of these to see what I mean. Run one to see the array structure, then comment it out and uncomment the other to see the other array structure.

// Try this one first: 
while($row = mysql_fetch_array($result)) { 
 // Try this one next: 
 // while($row = mysql_fetch_assoc($result)) { 
 print '<pre>'; print_r($row); exit('</pre>'); 
 // remove the above statement when you are done viewing things. 
 $out .= $row['username'].','; // start with the name 
 // column questions run from 1 - 44, and all start with 'h' 
 for ($i = 1; $i <= 44; $i++){$out .= $row['h'.$i] . ',';} 
 // column questions run from 1 - 299, and all start with 'q' 
 for ($i = 1; $i <= 300; $i++){$out .= $row['q'.$i] . ',';} 
   $out = rtrim($out, ',') . "<BR>"; // get rid of the last comma, add a newline 
 } 
}