[SOLVED] Help with DB Query

84 Posts

nickfury Reply #1, 16 years, 8 months ago

I’m a MySQL noob, but I’m trying to improve and solve some problems on some projects. I could us some help, though, trying to return multiple results attached to a matching ID from a second table.

The site is for a local classic car show.

What I have are hundreds of Exhibitors in one table and hundreds of Vehicles in the other. Any Exhibitor can have an unlimited number of vehicles. They are matched on the ID of the Exhibitor.

The code I’m currently playing with is below. It is modified from something I found in the ModX Wiki

<?php
$output = '';//create variable for holding the output
        $sql = $modx->db->query( 'SELECT * FROM `concours_exhibitors` INNER JOIN `concours_vehicles` ON concours_exhibitors.oldID=concours_vehicles.exhibitorOldID ORDER BY ownerLastName');//define the DB query, in this case the table is named 'tblprojects', add WHERE AND etc. as needed
        $resultArray = $modx->db->makeArray( $sql );//put it into an array
        foreach($resultArray as $item)//go through each record in the array
        {//start looping through the data array
            $params['lastname']=$item['ownerLastName'];//set the placeholders, the $params sets the name of the placeholder, the $item is the column name from the DB table
            $params['firstname']=$item['ownerFirstName'];
            $params['address']=$item['ownerAddress'];
            $params['city']=$item['ownerCity'];
            $params['state']=$item['ownerState'];
            $params['zip']=$item['ownerZip'];
            $params['email']=$item['ownerEmail'];
            $params['car']=$item['fullVehicleName'];
            $output.=$modx->parseChunk('ProjectTpl', $params, '[+', '+]');//define the chunk name and process the content, here it's ProjectTpl which has the placeholders for the data
        }//finish looping through the data array
        return $output;//now return the output from the processed chunk
?>

You can see the current result here (there is currently a PHx error due to the length of the results. I will be moving this to it’s own template, bypassing the PHx shortly)
http://dev.hhiconcours.com/index.php?id=106

It works in that I am able to pull a full list of the Exhibitors and a correctly matched vehicle. I am trying to figure out how to list all of the matching vehicles under each Exhibitor. I believe it requires a subquery, but am unsure. Any help is greatly appreciated, as always.

2,310 Posts

bunk58 Reply #2, 16 years, 8 months ago

Assuming all the place holder data comes from the `concours_exhibitors` table, except fullVehicleName you could try this (untested) version :

<?php
$output = '';
        $sql = $modx->db->query('SELECT DISTINCT oldID, ownerLastName, ownerFirstName, ownerAddress, ownerCity, ownerState, ownerZip, ownerEmail FROM `concours_exhibitors` ORDER BY ownerLastName');
        $resultArray = $modx->db->makeArray( $sql );
        foreach($resultArray as $item)
        {
        $res = $modx->db->query( 'SELECT * FROM `concours_vehicles` WHERE OldID = '.$item['oldID'].' ');
        $resArray = $modx->db->makeArray( $res );
        foreach($resArray as $sub_item)
        {
			$params['car']=$sub_item['fullVehicleName'];
		}
            $params['lastname']=$item['ownerLastName'];
            $params['firstname']=$item['ownerFirstName'];
            $params['address']=$item['ownerAddress'];
            $params['city']=$item['ownerCity'];
            $params['state']=$item['ownerState'];
            $params['zip']=$item['ownerZip'];
            $params['email']=$item['ownerEmail'];
            $output.=$modx->parseChunk('ProjectTpl', $params, '[+', '+]');
        }
        return $output;
?>

EDIT : Not sure this will work with place holders and a sub-query, let me know wink

84 Posts

nickfury Reply #3, 16 years, 8 months ago

bunk58,

Thanks for the help. I’m still getting a single car per Exhibitor, but I think that may be because I haven’t set the chunks up properly to handle the new output.

Currently, I have one chunk for the Exhibitor that looks like this

<div class="exhibitor-list-result">
<h3>[+firstname+] [+lastname+] <span style="background-color: #000000; color: #FFFFFF; padding: 4px; margin-left: 12px;">[+id+]</span></h3>
<p> [+address+]<br />
[+city+], [+state+] [+zip+]<br />
<a href="mailto:[+email+]">[+email+]</a></p>
<ul>
<li>[+car+]</li>
</ul>
</div>

The first Exhibitor with multiple Vehicle entries is "William Aldrich, 843", the 4th one on the list. He has 5 vehicles. Would it makes sense to alter the Snippet code to include the html output for the Car sub-items?

2,310 Posts

bunk58 Reply #4, 16 years, 8 months ago

Without the chunk, just hard coding the html :

<?php
$output = '';
        $sql = $modx->db->query('SELECT DISTINCT oldID, ownerLastName, ownerFirstName, ownerAddress, ownerCity, ownerState, ownerZip, ownerEmail FROM `concours_exhibitors` ORDER BY ownerLastName');
        $resultArray = $modx->db->makeArray( $sql );
        foreach($resultArray as $item)
        {
        $output .= '<div class="exhibitor-list-result">
<h3>'.$item['ownerFirstName'].' '.$item['ownerFirstName'].' <span style="background-color: #000000; color: #FFFFFF; padding: 4px; margin-left: 12px;">'.$item['oldID'].'</span></h3>
<p> '.$item['ownerAddress'].'<br />
'.$item['ownerCity'].', '.$item['ownerState'].' '.$item['ownerZip'].'<br />
<a href="mailto:'.$item['ownerEmail'].'">'.$item['ownerEmail'].'</a></p>
<ul>';
        $res = $modx->db->query( 'SELECT * FROM `concours_vehicles` WHERE OldID = '.$item['oldID'].' ');
        $resArray = $modx->db->makeArray( $res );
        foreach($resArray as $sub_item)
        {
		$output .= '	<li>'.[$sub_item['fullVehicleName'].'</li>';
		}
        $output.= '</ul>
		</div>';
        }
        return $output;
?>

209 Posts

Alik Reply #5, 16 years, 8 months ago

Hi nickfury and bunk58,
a query in the first post

SELECT *
FROM `concours_exhibitors`
INNER JOIN `concours_vehicles`
ON concours_exhibitors.oldID=concours_vehicles.exhibitorOldID
ORDER BY ownerLastName

returns all exhibitors and all cars they have, but the result set is being read in a wrong way. If exhibitor Joe XYZ has BMW and Porsche, then he is mentioned in result set twice as:
Joe XYZ, ..., BMW
Joe XYZ, ..., Porsche
You are looping result set in a careless way thus overwriting first row (BMW) with the second (Porsche). You need to rebuild at least one thing in your foreach loop -- $params[’car’] should be an array:
$params[’car’][] = $item[’fullVehicleName’];
And now you will obtain the thing that you are just requesting.

84 Posts

nickfury Reply #6, 16 years, 8 months ago

Thanks again, bunk! I’m playing with the PHP right now. I get no results as is and when I remove the $sub_item sequence I get the Exhibitor info. Moving things around to see what’s making the sub fail to output.

Alik - thanks for the help as well. I used your suggestion and I am getting a result of ’Array’ where the car parameter is output (I was getting the output of a single vehicle). Again, I suppose some correction in the way my output is formatted will fix this problem.

2,310 Posts

bunk58 Reply #7, 16 years, 8 months ago

Spotted an error in the WHERE OldID = bit, try with this :

<?php
$output = '';
        $sql = $modx->db->query('SELECT * FROM `concours_exhibitors` ORDER BY ownerLastName');
        $resultArray = $modx->db->makeArray( $sql );
        foreach($resultArray as $item)
        {
        $output .= '<div class="exhibitor-list-result">
<h3>'.$item['ownerFirstName'].' '.$item['ownerFirstName'].' <span style="background-color: #000000; color: #FFFFFF; padding: 4px; margin-left: 12px;">'.$item['oldID'].'</span></h3>
<p> '.$item['ownerAddress'].'<br />
'.$item['ownerCity'].', '.$item['ownerState'].' '.$item['ownerZip'].'<br />
<a href="mailto:'.$item['ownerEmail'].'">'.$item['ownerEmail'].'</a></p>
<ul>';
        $res = $modx->db->query( 'SELECT * FROM `concours_vehicles` WHERE exhibitorOldID = '.$item['oldID'].' ');
        $resArray = $modx->db->makeArray( $res );
        foreach($resArray as $sub_item)
        {
		$output .= '<li>'.[$sub_item['fullVehicleName'].'</li>';
		}
        $output.= '</ul>
		</div>';
        }
        return $output;
?>

209 Posts

Alik Reply #8, 16 years, 8 months ago

First you need to read the result set entirely without rendering -- dataset will be normalized (duplicates/triplicates/... will be removed):

$dataset = array();
foreach($resultArray as $item) {
$exhibitor = $item[’ownerFirstName’] . ’ ’ . $item[’ownerLastName’];
$dataset[$exhibitor][’address’] = $item[’ownerAddress’];
$dataset[$exhibitor][’city’] = $item[’ownerCity’];
$dataset[$exhibitor][’state’] = $item[’ownerState’];
$dataset[$exhibitor][’zip’] = $item[’ownerZip’];
$dataset[$exhibitor][’email’] = $item[’ownerEmail’];
$dataset[$exhibitor][’car’][] = $item[’fullVehicleName’];
}

Now you can render the output basing on your normalized dataset:

$output = ’’;
foreach ($dataset as $exhibitor => $details) {
$output .= ’<div>’ . $exhibitor . ’</div>’ .
’<div>’ . $details[’address’] . ’</div>’ .
’<div>’ . $details[’city’] . ’</div>’ .
’<div>’ . $details[’state’] . ’</div>’ .
’<div>’ . $details[’zip’] . ’</div>’ .
’<div>’ . $details[’email’] . ’</div>’;
// now add all cars
foreach ($details[’car’] as $car) {
$output .= ’<div>’ . $car . ’</div>’;
}
}

add classes to divs and send $output to browser, done.

84 Posts

nickfury Reply #9, 16 years, 8 months ago

alik,

I included what you posted, leaving the divs as they are (I’ll worry about display after I get the data to write). So my snippet looks like this:

<?php
$sql = $modx->db->query( 'SELECT * FROM `concours_exhibitors` INNER JOIN `concours_vehicles` ON concours_exhibitors.id=concours_vehicles.exhibitorOldID ORDER BY ownerLastName');
$dataset = array();
foreach($resultArray as $item) {
$exhibitor = $item['ownerFirstName'] . ' ' . $item['ownerLastName'];
$dataset[$exhibitor]['address'] = $item['ownerAddress'];
$dataset[$exhibitor]['city'] = $item['ownerCity'];
$dataset[$exhibitor]['state'] = $item['ownerState'];
$dataset[$exhibitor]['zip'] = $item['ownerZip'];
$dataset[$exhibitor]['email'] = $item['ownerEmail'];
$dataset[$exhibitor]['car'][] = $item['fullVehicleName'];
}
foreach ($dataset as $exhibitor => $details) {
$output .= '<div>' . $exhibitor . '</div>' .
'<div>' . $details['address'] . '</div>' .
'<div>' . $details['city'] . '</div>' .
'<div>' . $details['state'] . '</div>' .
'<div>' . $details['zip'] . '</div>' .
'<div>' . $details['email'] . '</div>';
// now add all cars
foreach ($details['car'] as $car) {
$output .= '<div>' . $car . '</div>';
}
}
?>

This resulted in a ModX Parse error

invalid argument supplied for foreach()

I changed the initial foreach($resultArray as $item) to foreach($dataset as $item) which rendered a page with no results. Any idea as to what the correct foreach argument should be?

[EDIT] I revised the code to include the original $resultArray

$resultArray = $modx->db->makeArray( $sql );
	$dataset = array();
	foreach($resultArray as $item){

Corrected the parsing error, but still no results.

209 Posts

Alik Reply #10, 16 years, 8 months ago

You forgot to fetch result after querying:

$sql = $modx->db->query( ’SELECT * FROM `concours_exhibitors` INNER JOIN `concours_vehicles` ON concours_exhibitors.id=concours_vehicles.exhibitorOldID ORDER BY ownerLastName’);
// why did you assume that this command is no longer required????
$resultArray = $modx->db->makeArray( $sql );
$dataset = array();
foreach($resultArray as $item) {
......

----

and one more thing -- you also need this at the end of your snippet:
return $output;

----

you need return statement outside foreach.