Mutiple queries

johnathanamber · January 23, 2010

Hello,

I am trying to figure out what I am doing wrong.

I need to correlate two fields from two tables.

Table1: jos_bid_auctions

Field: cat

Table2: jos_bid_categories

Field: id

For instance:

jos_bid_auctions has the auction entry of: 125627178179

the 'cat' field has an ID of 9.

the ID of '9' of the 'jos_bid_categories' table is 'Aircraft Parts'

How would I create the query to get this done?

I have the following already done, this isn't working:

$query = "SELECT  id, title, shortdescription, description, picture, BIN_price, auction_type, start_date, end_date, hits, cat, auction_nr, nr_items_left, reserve_price, zipcode, weight, zip, city, state
FROM jos_bid_auctions
WHERE description OR shortdescription like '%$term%'

$cat = $query[cat];

if($cat != 0){
$findcat = "SELECT  id
FROM jos_bid_categories
WHERE  id = '%$cat%'";

Currently when I use the above code... it ALWAYS displays:

Category: S

Any assistance would be greatly appreciated.

NOTE: I know that the 1st section of code is correct since it does display the number is I display the output of $cat correctly.

So I know taht the issue is the code here:
if($cat != 0){
$findcat = "SELECT  id
FROM jos_bid_categories
WHERE  id = '%$cat%'";

Thank you and God bless,

Johnathan

kickstart · January 23, 2010

Hi

Appears to be a massive amount chopped from your code fragments.

I would say no need to do 2 queries, just do a single one to JOIN the 2 tables together. Something like this (no idea what the field is you want from the 2nd table though)

SELECT  a.id, a.title, a.shortdescription, a.description, a.picture, a.BIN_price, a.auction_type, a.start_date, a.end_date, a.hits, a.cat, a.auction_nr, a.nr_items_left, a.reserve_price, a.zipcode, a.weight, a.zip, a.city, a.state, b.somefield
FROM jos_bid_auctions a
LEFT OUTER JOIN jos_bid_categories b
ON a.cat = b.id
WHERE description like '%$term%' 
OR shortdescription like '%$term%'

All the best

Keith

johnathanamber · January 24, 2010

Keith,

You are right I did leave a lot of code out... mostly to not let things get confusing... here is all the code:

<?php
//==================================================================================================================================
//							Harvest Auction Search Form
//			Created by:
//				Johnathan Morlock
//				johnathan_morlock@tsgcomputers.net
//				http://www.tsgcomputers.net
//			For:
//				AviatorsAuction.com, HarvestAuction.com, HobbiesAuction.com and ToolsForAuction.com
//			Date:
//				10.28.2009
//			Revised:
//				01.22.2010
//==================================================================================================================================

//==================================================================================================================================
//								Search Form
//==================================================================================================================================
?>

<form action="index.php?option=com_content&view=article&id=54&Itemid=127" method="post" name="zipform">

<table>
<tr>
<td>Enter your search term:</td><td><input type="text" name="term" size="20" maxlength="20" /></td>
</tr>
<tr>
    <td>Enter your ZIP Code:</td><td><input type="text" name="zip" size="5" maxlength="5" /></td>
</tr>
<tr>
    <td>Select a distance in miles from this point:</td><td><input type="text" name="distance" size="4" maxlength="4" /></td>
    </tr>
<tr>
<td>Sort Results by:</td><td><select name="sortby">
								<option value="default">default</option>
								<option value="city">City</option>
								<option value="state">State</option>
								<option value="zip">ZIP</option>
							</select></td>
</tr>
</table>
    <input type="submit" name="findauctions" value="Find Auctions" />

</form>

<?php
if(isset($_POST['findauctions'])) {
//==================================================================================================================================
//							Variables from form
//==================================================================================================================================

$term = ($_POST['term']);
$zip = ($_POST['zip']);
$distance = ($_POST['distance']);
$sortby = ($_POST['sortby']);

if(!preg_match('/^[0-9]{5}$/', $zip)) {
	echo "<p><strong>Please enter a 5 digit ZIP Code.</strong> Please try again.</p>";
}elseif(!preg_match('/^[0-9]{1,4}$/', $distance)){
	echo "<p><strong>Please enter a distance less than 9999 miles.</strong> Please try again.</p>";
}else{

//==================================================================================================================================
//							Connect to the DB
//==================================================================================================================================

include("hadb.php");

//==================================================================================================================================
//					Is the ZIP in the zip_codes table?
//==================================================================================================================================

if(!$rs = mysql_query("SELECT * FROM zip_codes WHERE zip = '$zip'")) {
	echo "<p><strong>For some reason, there was an error attempting to retrieve your ZIP Code. Please try again.</strong></p>";
	}else{
		if(mysql_num_rows($rs) == 0){
			echo "<p><strong>No database match for provided ZIP Code. Please enter a new ZIP Code.</strong></p>";	
		}else{

//==================================================================================================================================
//							Variables from DB
//==================================================================================================================================

			$row = mysql_fetch_array($rs);
			$lat1 = $row['latitude'];
			$lon1 = $row['longitude'];
			$d = "$distance";
			//earth's radius in miles
			$r = 3959;

//==================================================================================================================================
//						Compute max and min LAT and LONG 
//==================================================================================================================================

			$latN = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(0))));
			$latS = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(180))));
			$lonE = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(90)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));
			$lonW = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(270)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));

//==================================================================================================================================
//					Display Originating Information with LAT and LONG
//==================================================================================================================================

/*				echo "Your originating ZIP code:<table><tr><td valign='top'>
				<table cellspacing='0' cellpadding='4'>
				<tr><th>ZIP</th><td><a href='index.php?option=com_bids&task=showSearchResults&zipcode=$row[zip]&description=$term' target='_blank'>$row[zip]</a></td></tr>
				<tr><th>Search Term</th><td>$term</td></tr>
				<tr><th>City</th><td>$row[city]</td></tr>
				<tr><th>State</th><td>$row[state]</td></tr>
				</table>";
			echo "</td></tr></table><br />"; */

//==================================================================================================================================
//							Sort by...
//
//			Table 1:	jos_bid_auctions
//			Fields:		userid, title, shortdescription, picture, link_extern, initial_price, currency, BIN_price,
//					auction_type, automatic, payment, shipment_info, shipment_price, start_date, end_date, closed_date,
//					params, published, close_offer, close_by_admin, hits, modified, newmessages, winner_id, cat,
//					auction_nr, nr_items, nr_items_left, featured, reserve_price, min_increase, extended_counter,
//					payment_info, zipcode, weight
//
//			Table 2:	zip_codes
//			Fields		zip, latitude, longitude, city, state, country, type
//
//			Example:
//					SELECT field1, field2, field3, etc (all fields from all tables)
//					FROM table1, tables2, etc (from all tables being used)
//					WHERE table1.field1 = tables2.field2
//
//					AND (latitude != $lat1
//					AND longitude != $lon1)
//
//==================================================================================================================================
			if ($sortby == "default"){
			$query = "SELECT  id, title, shortdescription, description, picture, BIN_price, auction_type, start_date, end_date, hits, cat, auction_nr, nr_items_left, reserve_price, zipcode, weight, zip, city, state
					FROM jos_bid_auctions JOIN zip_codes ON zipcode = zip
					WHERE description OR shortdescription like '%$term%'
					AND(latitude <= $latN
						AND latitude >= $latS
						AND longitude <= $lonE
						AND longitude >= $lonW)
					ORDER BY zipcode";
			}

			if(!$rs = mysql_query($query)) {
				echo "<p><strong>There was an error selecting nearby ZIP Codes from the database. Please try again.</strong></p>";

//==================================================================================================================================
//						Display The Information from Auctions
//==================================================================================================================================

			}elseif(mysql_num_rows($rs) == 0){
				echo "<p><strong>No auctions have been located within the distance specified.</strong> Please try wider distance.</p>";								
			}else{
				echo "<table width='100%'>";
				while($query = mysql_fetch_array($rs)){
					$id = $query[id];
					$title = $query[title];
					$shortdescription = $query[shortdescription];
					$description = $query[description];
					$picture = "images/auctions/" . $query[picture];
					$BIN_price = $query[bIN_price];
					$start_date = $query[start_date];
					$end_date = $query[end_date];
					$hits = $query[hits];
					$auction_nr = $query[auction_nr];
					$nr_items_left = $query[nr_items_left];
					$reserve_price = $query[reserve_price];
					$zipcode = $query[zipcode];
					$weight = $query[weight];
					$city = $query[city];
					$state = $query[state];
					$cat = $query[cat];

//===============================================Category Search===============================================
					$findcat = "SELECT  id
					FROM jos_bid_categories
					WHERE  id = '%$cat%'";

					$category = $findcat[id];
					$auctiontype = $query[auctiontype];

// Current Price, BIN_price

				echo "<tr>
					<td width='900px'>
					<fieldset style='border: 1px solid gray'>
					<legend><b>Title: <a href='index.php?option=com_bids&task=viewbids&id=$id&Itemid=0' target='_blank'  valign='top'>$title - #$auction_nr</b></a></legend>
					<img src='$picture' width='150px' align='left'><b>Auction Type:</b> $auctiontype<br />
					<b>Category: </b>$cat<br /></br />
						<table width='100%'>
						<tr>
						<td><b>QTY:</b></td><td>$nr_items_left</td><td><b>Start Date:</b></td><td>$start_date</td>
						</tr>
						<tr>
						<td><b>Weight:</b></td><td>$weight lbs</td><td><b>End Date:</b></td><td>$end_date</td>
						</tr>
						<tr>
						<td><b>Location:</b></td><td>$city, $state $zipcode</td>
						</tr>
						</table>
						<b>Short Description:</b> $shortdescription
					</fieldset>
					</td>
					</tr>";
				}
				echo "</table>";
			}
		}
	}
}
}
?>

johnathanamber · January 24, 2010

Keith,

Judging by the code you've given me... I assume that this would probably work... I just want to be sure that it is accurate:

				$query = "SELECT  a.id, a.title, a.shortdescription, a.description, a.picture, a.BIN_price, a.auction_type, a.start_date, a.end_date, a.hits, a.cat, a.auction_nr, a.nr_items_left, a.reserve_price, a.zipcode, a.weight, a.zip, a.city, a.state, a.auctiontype b.id c.id
					FROM jos_bid_auctions a
					LEFT OUTER JOIN jos_bid_categories b
					LEFT OUTER JOIN jos_bids_fields_options c
					JOIN zip_codes ON a.zipcode = zip
					ON a.cat = b.id
					ON a.auctiontype = c.id
					WHERE description OR shortdescription like '%$term%'
					AND(latitude <= $latN
						AND latitude >= $latS
						AND longitude <= $lonE
						AND longitude >= $lonW)
					ORDER BY zipcode";

kickstart · January 24, 2010

Hi

Not quite.

Try this minor change:-

$query = "SELECT  a.id, a.title, a.shortdescription, a.description, a.picture, a.BIN_price, a.auction_type, a.start_date, a.end_date, a.hits, a.cat, a.auction_nr, a.nr_items_left, a.reserve_price, a.zipcode, a.weight, a.zip, a.city, a.state, a.auctiontype, b.id, c.id
	FROM jos_bid_auctions a
	LEFT OUTER JOIN jos_bid_categories b
	ON a.cat = b.id
	LEFT OUTER JOIN jos_bids_fields_options c
	ON a.auctiontype = c.id
	JOIN zip_codes 
	ON a.zipcode = zip
	WHERE (description like '%$term%'
	OR shortdescription like '%$term%')
	AND latitude <= $latN
	AND latitude >= $latS
	AND longitude <= $lonE
	AND longitude >= $lonW
	ORDER BY zipcode";

All the best

Keith

johnathanamber · January 24, 2010

@Keith,

Thank you again for the reply.

I get an error...

//==================================================================================================================================
//					Is the ZIP in the zip_codes table?
//==================================================================================================================================

if(!$rs = mysql_query("SELECT * FROM zip_codes WHERE zip = '$zip'")) {
	echo "<p><strong>For some reason, there was an error attempting to retrieve your ZIP Code. Please try again.</strong></p>";
	}else{
		if(mysql_num_rows($rs) == 0){
			echo "<p><strong>No database match for provided ZIP Code. Please enter a new ZIP Code.</strong></p>";	
		}else{

//==================================================================================================================================
//							Variables from DB
//==================================================================================================================================

			$row = mysql_fetch_array($rs);
			$lat1 = $row['latitude'];
			$lon1 = $row['longitude'];
			$d = "$distance";
			//earth's radius in miles
			$r = 3959;

//==================================================================================================================================
//						Compute max and min LAT and LONG 
//==================================================================================================================================

			$latN = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(0))));
			$latS = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(180))));
			$lonE = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(90)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));
			$lonW = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(270)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));

			if ($sortby == "default"){
				$query = "SELECT  a.id,
								a.title,
								a.shortdescription,
								a.description,
								a.picture,
								a.BIN_price,
								a.auction_type,
								a.start_date,
								a.end_date,
								a.hits,
								a.cat,
								a.auction_nr,
								a.nr_items_left,
								a.reserve_price,
								a.zipcode,
								a.weight,
								a.zip,
								a.city,
								a.state,
								a.auctiontype,
								b.id,
								c.id
      							FROM jos_bid_auctions a
      							LEFT OUTER JOIN jos_bid_categories b
      								ON a.cat = b.id
      							LEFT OUTER JOIN jos_bids_fields_options c
      								ON a.auctiontype = c.id
      							JOIN zip_codes
      								ON a.zipcode = zip
      							WHERE (description like '%$term%'
      								OR shortdescription like '%$term%')
      								AND latitude <= $latN
      								AND latitude >= $latS
      								AND longitude <= $lonE
      								AND longitude >= $lonW
      							ORDER BY a.zipcode";
      			}

			if(!$rs = mysql_query($query)) {
				echo "<p><strong>There was an error selecting nearby ZIP Codes from the database. Please try again.</strong></p>";

The error being evoked is the last line of the code above:

"There was an error selecting nearby ZIP Codes from the database. Please try again."

Thank you and God bless,

Johnathan

kickstart · January 24, 2010

Hi

Echo out the error (ie, mysql_error()).

All the best

Keith

johnathanamber · January 24, 2010

@Keith,

Again, thanks for the help...

hehe... the error code results in another error...

echo mysql_errno($query) . ": " . mysql_error($query) . "\n";

Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource

N/M I got it...used: echo mysql_error(); instead:

"Unknown column 'a.city' in 'field list' "

Another EDIT:

This worked out better I think: echo mysql_errno() . ": " . mysql_error() . "\n";

"1054: Unknown column 'a.city' in 'field list' "

Thank you and God bless,

Johnathan

johnathanamber · January 24, 2010

OK, I think I need to be more clear... I don't think I portrayed what I am trying to accomplish... BTW I've corrected the error messages that were displayed... I added new fields.

I need a number 'a.cat' to reference 'b.id' and to display the 'b.catname' in reference to 'b.id'.

Something like this... although I know I do not have it correct:

See the lines below:

ON a.cat = b.id = b.catname

ON a.auctiontype = c.id = c.option_name

$query = "SELECT  a.id,
								a.title,
								a.shortdescription,
								a.description,
								a.picture,
								a.BIN_price,
								a.auction_type,
								a.start_date,
								a.end_date,
								a.hits,
								a.cat,
								a.auction_nr,
								a.nr_items_left,
								a.reserve_price,
								a.zipcode,
								a.weight,
								a.auctiontype,
								a.city,
								a.state,
								b.id,
								b.catname,
								c.id,
								c.option_name
      							FROM jos_bid_auctions a
      							LEFT OUTER JOIN jos_bid_categories b
      								ON a.cat = b.id = b.catname
      							LEFT OUTER JOIN jos_bids_fields_options c
      								ON a.auctiontype = c.id = c.option_name
      							JOIN zip_codes
      								ON a.zipcode = zip
      							WHERE (description like '%$term%'
      								OR shortdescription like '%$term%')
      								AND latitude <= $latN
      								AND latitude >= $latS
      								AND longitude <= $lonE
      								AND longitude >= $lonW
      							ORDER BY a.zipcode";

I hope this makes more sense.

Thank you and God bless,

Johnathan

kickstart · January 24, 2010

Hi

Think the previous SQL would give you what you want.

Think you are missing how the JOIN syntax works.

If you have:-

table1 JOIN table2 ON table1.id = table2.otherid

That is linking table 1 and table 2 on rows where the contents of table1.id is the same as table2.otherid.

You can't just add another equals and a column onto the end of the ON clause.

In your code:-

ON a.cat = b.id = b.catname

ON a.auctiontype = c.id = c.option_name

I am not sure what you want with b.catname or c.option_name as you do not appear to want to join based on those columns, merely pull back those values and you have them in the SELECT clause anyway.

All the best

Keith

Sign In

Mutiple queries

Recommended Posts

johnathanamber

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

johnathanamber

Link to comment

Share on other sites

johnathanamber

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

johnathanamber

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

johnathanamber

Link to comment

Share on other sites

johnathanamber

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information