Output from Two Tables

MySQL database
#1

I have two tables. They are:

Table 1: test_static with two fields: sName and phone

It includes the follow records:

sName; phone
Bob; 111-1111
Jim; 222-2222
Sam; 333-3333

Table 2: test_records with two fields: rName and date

rName; date
Bob; 1/1/2009
Bob; 1/1/2010
Bob; 1/1/2011
Jim; 2/2/2010
Jim; 2/2/2011
Sam; 3/3/2008
Sam; 3/3/2009
Sam; 3/3/2010
Sam; 3/3/2011

I need output that combines the above tables and that looks like:

Row 1: Bob; 111-1111; 1/1/2009, 1/1/2010, 1/1/2011
Row 2: Jim; 222-2222; 2/2/2010, 2/2/2011
Row 3: Sam; 333-3333, 3/3/2008, 3/3/2009, 3/3/2010, 3/3/2011

I have tried many different SELECT statements with a variety of JOINs and coding approaches, but I cannot figure this out. I have used kludges for months to get around my poor programming abilities, and I am looking for a “real” solution. I don’t have a formal programming background (this may be obvious). Could anyone please offer a coding solution (or hints) that produces the above output?

Thank you!

#2

This should produce what you’re after:

[php]
$sql = "SELECT
ts.sName AS name
,ts.phone
,tr.date
FROM
test_static AS ts
INNER JOIN
test_records AS tr
ON
tr.rName=ts.sName
";
$query = mysql_query($sql);
$people = array();
while(($row = mysql_fetch_assoc($query)) !== false)
{
/*
* Add all our info into the $people array under the key of the person’s name
*/
$people[$row[‘name’]][‘phone’] = $row[‘phone’];
if(!isset($people[$row[‘name’]][‘dates’])) $people[$row[‘name’]][‘dates’] = array();
$people[$row[‘name’]][‘dates’][] = $row[‘date’];
}

foreach($people as $name => $person)
{
/*
* Collate and output the information
*/
echo $name.’; ‘.$person[‘phone’].’; ‘.implode(’, ',$person[‘dates’]);
}
[/php]

Any problems, let me know.

#3

Thank you very much for this, Smokey. I’m entering this now to see how it works. I’ll let you know!

Regards,
Scott

#4

It works perfectly. This is the output from my production system.

Bob; 111-1111; 1/1/2009, 1/1/2010, 1/1/2011
Jim; 222-2222; 2/2/2010, 2/2/2011
Sam; 333-3333; 3/3/2008, 3/3/2009, 3/3/2010, 3/3/2011

I do my programming in Dreamweaver CS4 and I am not familiar with some of the commands that you used. I’ll look up all of the terms to better understand how your solution works.

I have spent days (weeks?) trying to find a solution, but each time that I needed to do this, I had to kludge an ugly process to make it work. Now I can eliminate the old code and replace it with this. Thank you again, Smokey, for your help.

#5

If you’d rather not go looking up the methods I’ve used individually, let me know what parts you don’t know about (or if it’s the whole thing) and I’ll explain them for you.

#6

The code that you provided worked great. The only exception that I had to make was to change the INNER JOIN to a LEFT JOIN because I had to show records from the test_static table that had no corresponding test_records.

I have another twist that I can’t figure out after a couple of days of head-pounding. I need to JOIN another table, let’s call it test_boxes. It has the following values:

Table 3: test_boxes with two fields: phone and boxes

phone; boxes
111-1111; 2
111-1111; 3
111-1111; 5
222-2222; 1
222-2222; 2
222-2222; 3
555-5555, 10

Going back to the original example, the output that I need is to sum the boxes and include them:

Row 1: Bob; 111-1111; 1/1/2009, 1/1/2010, 1/1/2011; 10 (which is 2+3+5)
Row 2: Jim; 222-2222; 2/2/2010, 2/2/2011; 6 (which is 1+2+3)
Row 3: Sam; 333-3333, 3/3/2008, 3/3/2009, 3/3/2010, 3/3/2011; 0 (no values in test_boxes)

Are there capabilities in PHP/MySQL that will allow me to “LEFT JOIN to connect the test_static to the test_records table” and “LEFT JOIN the test_static to the test_boxes table and sums the boxes”?

The output that I am receiving typically looks like this:

Row 1: Bob; 111-1111; 1/1/2009; 2
Row 2: Jim; 222-2222; 2/2/2010; 1
Row 3: Sam; 333-3333, 3/3/2008; 0

Thank you very much in advance!

#7

Ok, if I’ve understood correctly, you want to change your SQL statement to the following (I have simply amended the original one I wrote including the change from inner to left join - if you have made further changes you will of course need to remake those, but its basically just a case of adding the last 4 lines and the last field select):

[php]SELECT
ts.sName AS name
,ts.phone
,tr.date
,SUM(tb.boxes) AS box_total
FROM
test_static AS ts
LEFT JOIN
test_records AS tr
ON
tr.rName=ts.sName
LEFT JOIN
test_boxes AS tb
ON
tb.phone=ts.phone
[/php]

In the while loop the total number from the boxes column should be $row[‘box_total’]. Any issues let me know.

#8

Thank you, Smokey.

The result when I added that code produced the following output.

Row 1: Bob; 111-1111; 1/1/2009; 42

Rather than:

Row 1: Bob; 111-1111; 1/1/2009, 1/1/2010, 1/1/2011; 10
Row 2: Jim; 222-2222; 2/2/2010, 2/2/2011; 6
Row 3: Sam; 333-3333, 3/3/2008, 3/3/2009, 3/3/2010, 3/3/2011; 0

Please note that I also added the following line to the code after the "while(($row = mysql_fetch_assoc($query)) !==false){ "

$people[$row[‘name’]][‘box_total’] = $row[‘box_total’];

I tried adding:

           GROUP BY ts.`phone`

on the last line and that gave me the following results which were a bit better but still not correct:

Bob; 111-1111; 1/1/2009; 30
Jim; 222-2222; 2/2/2010; 12
Sam; 333-3333; 3/3/2008;

Any ideas on what I should try next?

Thanks!

#9

This was getting too confusing in my head so I built my own mini version of your setup. The following code worked perfectly for me:

[php]
$sql = "SELECT
ts.sName AS name
,ts.phone
,tr.date
,(SELECT SUM(boxes) FROM test_boxes WHERE phone=ts.phone) AS box_total
FROM
test_static AS ts
LEFT JOIN
test_records AS tr
ON
tr.rName=ts.sName
";
$query = mysql_query($sql);
$people = array();
while(($row = mysql_fetch_assoc($query)) !== false)
{
/*
* Add all our info into the $people array under the key of the person’s name
*/
$people[$row[‘name’]][‘phone’] = $row[‘phone’];
if(!isset($people[$row[‘name’]][‘dates’])) $people[$row[‘name’]][‘dates’] = array();
$people[$row[‘name’]][‘dates’][] = $row[‘date’];
$people[$row[‘name’]][‘box_total’] = intval($row[‘box_total’]);
}

foreach($people as $name => $person)
{
/*
* Collate and output the information
*/
echo $name.’; ‘.$person[‘phone’].’; ‘.implode(’, ‘,$person[‘dates’]).’; ‘.$person[‘box_total’].’
’;
}
[/php]

The changes are the SQL statement, the addition of the box_total figure to the array and the output at the end.

#10

All I can say is “wow”. This does work perfectly. This would have taken me years to figure out.

Thank you very much for your help on this.

(OK. To be brutally honest, I would have never ever been able to figure it out…! )

#11

Haha, glad to have helped out. If you want explanations of any of it so that you can apply the knowledge elsewhere, don’t hesitate to ask and I’ll comment the bits of code you want explained (if needed/wanted).

#12

I will definitely apply this knowledge elsewhere. I thought that another separate SELECT would be necessary, but I didn’t know how to apply it. I was also not familiar with the INTVAL command.

Thanks again!

#13

When I try to change a setting in the database and want save this.
I get this message:

MySQL Error : MyISAM table sigparsed is in use most likely by a MERGE table. Try FLUSH TABLES
Error Number : 1105

My question is how I can Flush Tables and what is this?

Sponsor our Newsletter | Privacy Policy | Terms of Service