[SOLVED] Returning MySQL results - probably simple answer.

dotbin · July 23, 2009

Hi

What I'm trying to do is the following:

I have a databse which contains Music Venues and Promoters in those venues.

If a user types "the" into the search field, a number of music venues which have "the" in the title are displayed (these are music venues such as "The Golden Lion", "The Kings Arms", etc).

I have done this using a simple

$queryVenue2 = "SELECT * FROM gigs WHERE venue LIKE '%$location2%'"; ($location is the search term)

I then use a while statement which loops and displays each music venue result.

The problem lies here:

Under each music venue heading I need to display a list of Promoters which operate in that music venue. But the user hasn't searched for the venue name, they've searched for "The" and the results are returning all venues with 'The' in the title.

So how do I display the promoters within the venue - I understand it will require 2 while loops, 1 main one which will present the music venues, and 1 sub loop which will display all the promoters in that venue.

Hope this makes sense? anyone got any ideas? :shrug:

Here's my code for it at the moment:

  $queryVenue2 = "SELECT * FROM gigs WHERE venue LIKE '%$location2%'";
  
  $queryVenueResult2 = mysql_query($queryVenue2);


	  while ($row = mysql_fetch_array($queryVenueResult2)) {
		  $numResults++;
              
		  
              echo "\n\n\n\n<!-- Result Number " . $numResults . ": " . $row['event'] . " in " . $row['location'] . " -->\n\n\n\n";
              echo "<div id=\"CollapsiblePanel" . $numResults . "\" class=\"CollapsiblePanel\">";
              echo "<div class=\"CollapsiblePanelTab\">";			  
		  echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";
              echo "<tr class=\"SpryCollapsiblePanelTableCellHeader\">";
              echo "<td>" . $row["venue"] . " - " . $row['location'] . "</td>";
              echo "<td><div align=\"right\">+</div></td>";
              echo "</tr>";
              echo "</table>";
              echo "</div>";
              echo "<div class=\"CollapsiblePanelContent\">";
              echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";
              
              if (!$row["map"]) {
                  echo "<tr>";
                  echo "<td class=\"SpryCollapsiblePanelTableCell\">Sadly there is no map link for this venue.</td>";
                  echo "</tr>";
              }
              
              else {
                  echo "<tr>";
                  echo "<td class=\"SpryCollapsiblePanelTableCell\">";
                  echo "<a href=\"http://maps.google.co.uk/maps?q=" . $row["map"] . "\" target=\"_blank\">Click here to see map</td>";
                  echo "</tr>";
              }
              

              ///////////////// Here is the loop which will display promoters specific to that venue /////


              while ()) {        
		  
                  echo "<tr>";
                  echo "<td class=\"SpryCollapsiblePanelTableCellHeader\">" . $row2['event'] . "</td>";
                  echo "</tr>";
                  echo "<tr>";
                  echo "<td class=\"SpryCollapsiblePanelTableCell\"><a href=\"" . $row2['link'] . "\">" . $row2["link"] . "</td>";
                  echo "</tr>"; 
              }
              
              
              echo "</table>";
              echo "</div>";
              echo "</div>";
              echo "\n<script type=\"text/javascript\">\n";
              echo "<!--\n";
              echo "var CollapsiblePanel" . $numResults . " = new Spry.Widget.CollapsiblePanel(\"CollapsiblePanel" . $numResults . "\")\n";
              echo " //-->\n";
              echo "</script>";
		  }
              
              mysql_query($addResultsToDatabase);
          }

EDITED BY akitchin: code tags ftw. please use them in future posts.

kickstart · July 23, 2009

Hi

No need for 2 seperate statements.

Need a bit more detail on how your tables are laid out.

Does the gigs table also contain a coloumn for the promotor? Do you have a table just listing venues?

All the best

Keith

dotbin · July 23, 2009

Hi,

an example row from the table would be

ID - LOCATION (CITY) - VENUE - VENUE POSTCODE - PROMOTER NAME - ABOUT PROMOTER - PROMOTER WEBSITE ADDRESS

Hope this helps.

Many thanks!

Chris

kickstart · July 23, 2009

Hi

Still need a bit more info.

What is on the gigs table?

What is on the venues table?

BAsically what you need to do is do a JOIN with a subselect that returns all the promoters per venue.

All the best

Keith

dotbin · July 23, 2009

Hi Keith,

There is 1 table called Gigs and it includes all the information about promoters and venues, because in reality the table is for promoters but there needs to be an options to see what promoters operate in a particular venue.

Here's an example row from the table:

261 | Birmingham | Checks Dig Jerks | Collection of friendly n fun folk who like DJing dancing cavorting & putting on shows for all their favourite bands & DJs. Although this is a Birmingham based Promotion, nights are held in London with a coach arranged. | The Queen of Hoxton | http://www.myspace.com/chicksdigjerks | EC2A3JX

Please let me know if you need anything more.

Cheers!

Chris

kickstart · July 23, 2009

Hi

Think this would basically give you what you want (maybe a typo in it):-

SELECT *
FROM Gigs a
LEFT OUTER JOIN (SELECT DISTINCT venue, promotor FROM Gigs) b
ON a.venue = b.venue
WHERE a.venue LIKE '%$location2%'
ORDER BY a.venue

Should be multiple lines per returned record on Gigs, one for each promotor of gigs at that venue

In php you could then just loop through the results and change the heading / whatever on a change of venue / gig.

All the best

Keith

dotbin · July 24, 2009

Thanks for this! will try it out and see if I can get it working!

Chris

dotbin · July 24, 2009

Hi,

I'm trying to return the results using

mysql_fetch_array()

and it's displaying "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Applications/MAMP/htdocs/GetaGig2/search.php"

Am I doing this wrong?

Thanks Chris

kickstart · July 24, 2009

Hi

Can you post your current code?

Worth putting an or die(mysql_error()); on the end of the mysql_query

All the best

Keith

dotbin · July 24, 2009

The Code I'm using is

while ($row = mysql_fetch_array($queryVenueResult2)) {

$numResults++;

echo "\n\n\n\n\n\n\n\n";

echo "<div id=\"CollapsiblePanel" . $numResults . "\" class=\"CollapsiblePanel\">";

echo "<div class=\"CollapsiblePanelTab\">";

echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";

echo "<tr class=\"SpryCollapsiblePanelTableCellHeader\">";

echo "<td>" . $row["venue"] . " - " . $row['location'] . "</td>";

echo "<td><div align=\"right\">+</div></td>";

echo "</tr>";

echo "</table>";

echo "</div>";

echo "<div class=\"CollapsiblePanelContent\">";

echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";

if (!$row["map"]) {

echo "<tr>";

echo "<td class=\"SpryCollapsiblePanelTableCell\">Sadly there is no map link for this venue.</td>";

echo "</tr>";

}

else {

echo "<tr>";

echo "<td class=\"SpryCollapsiblePanelTableCell\">";

echo "<a href=\"http://maps.google.co.uk/maps?q=" . $row["map"] . "\" target=\"_blank\">Click here to see map</td>";

echo "</tr>";

}

while ($row2 = mysql_fetch_array($queryVenueResult3)) {

echo "<tr>";

echo "<td class=\"SpryCollapsiblePanelTableCellHeader\">" . $row2['event'] . "</td>";

echo "</tr>";

echo "<tr>";

echo "<td class=\"SpryCollapsiblePanelTableCell\"><a href=\"" . $row2['link'] . "\">" . $row2["link"] . "</td>";

echo "</tr>";

}

echo "</table>";

echo "</div>";

echo "\n<script type=\"text/javascript\">\n";

echo "<!--\n";

echo "var CollapsiblePanel" . $numResults . " = new Spry.Widget.CollapsiblePanel(\"CollapsiblePanel" . $numResults . "\")\n";

echo " //-->\n";

echo "</script>";

}

mysql_query($addResultsToDatabase);

}

It's to display broad search results which are outside of a perfect match. The idea is that it displays the venue name and beneath the Venue name is the list of promoters which work in that area.

In some instances though there are many many venues listed which contain many many promoters.

Really grateful for your help on this Keith - as you can see I"m fairly new to PHP and the whole MySQL thing!!!

dotbin · July 24, 2009

Oops, just realised you might also want the variables and stuff for the code above:

$queryVenue = "SELECT * FROM gigs WHERE venue = '$location' ORDER BY venue";

$queryVenue2 = "SELECT * FROM gigs WHERE venue LIKE '%$location2%'";

$queryVenue3 = "SELECT * FROM Gigs a

LEFT OUTER JOIN (SELECT DISTINCT venue, promotor FROM Gigs) b

ON a.venue = b.venue

WHERE a.venue LIKE '%$location2%'

ORDER BY a.venue";

$result = mysql_query($queryLocation);

$result2 = mysql_query($queryNearbyLocation);

$queryVenueResult = mysql_query($queryVenue);

$queryVenueResult2 = mysql_query($queryVenue2);

$queryVenueResult3 = mysql_query($queryVenue3);

kickstart · July 24, 2009

Hi

Couple of things. Add the "or die" syntax to these for now so you get any errors:-

$result = mysql_query($queryLocation) or die(mysql_error());

$result2 = mysql_query($queryNearbyLocation) or die(mysql_error());

$queryVenueResult = mysql_query($queryVenue) or die(mysql_error());

$queryVenueResult2 = mysql_query($queryVenue2) or die(mysql_error());

$queryVenueResult3 = mysql_query($queryVenue3) or die(mysql_error());

Secondly, this bit:-

              while ($row2 = mysql_fetch_array($queryVenueResult3)) {
           
                  echo "<tr>";
                  echo "<td class=\"SpryCollapsiblePanelTableCellHeader\">" . $row2['event'] . "</td>";
                  echo "</tr>";
                  echo "<tr>";
                  echo "<td class=\"SpryCollapsiblePanelTableCell\"><a href=\"" . $row2['link'] . "\">" . $row2["link"] . "</td>";
                  echo "</tr>"; 
              }

will loop through the same result set repeatedly. I presume what you actually want is to generate a list of promotors for gigs at the venue that you are currently out in the outer loop.

Also the idea of the SQL I suggested was that you only needed to do it once, and loop round that. Hence only one piece of SQL to execute.

However what you appear to be doing is then looping around an extra set of events in the middle.

What I thought you wanted was something similar to this (excuse probably typos). This gets the list of gigs and for each gig loops round displaying all the promoters who have gigs at the same venue.

<?php

$queryVenue3 = "SELECT * FROM Gigs a
LEFT OUTER JOIN (SELECT DISTINCT venue, promotor FROM Gigs) b
ON a.venue = b.venue
WHERE a.venue LIKE '%$location2%'
ORDER BY a.venue";

$queryVenueResult3 = mysql_query($queryVenue3) or die(mysql_error());

$PrevEvent = "";

while ($row = mysql_fetch_array($queryVenueResult2)) 
{
if ($row['event'] != $PrevEvent)
{
	if ($PrevEvent <> "")
	{	
		echo "</table>";
		echo "</div>";
		echo "</div>";
		echo "\n<script type=\"text/javascript\">\n";   
		echo "<!--\n";
		echo "var CollapsiblePanel" . $numResults . " = new Spry.Widget.CollapsiblePanel(\"CollapsiblePanel" . $numResults . "\")\n";
		echo " //-->\n";
		echo "</script>";
	}
	$numResults++;
	$PrevEvent = $row['event'];
	echo "\n\n\n\n<!-- Result Number " . $numResults . ": " . $row['event'] . " in " . $row['location'] . " -->\n\n\n\n";
	echo "<div id=\"CollapsiblePanel" . $numResults . "\" class=\"CollapsiblePanel\">";
	echo "<div class=\"CollapsiblePanelTab\">";           
	echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";
	echo "<tr class=\"SpryCollapsiblePanelTableCellHeader\">";
	echo "<td>" . $row["venue"] . " - " . $row['location'] . "</td>";
	echo "<td><div align=\"right\">+</div></td>";
	echo "</tr>";
	echo "</table>";
	echo "</div>";
	echo "<div class=\"CollapsiblePanelContent\">";
	echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";

	if (!$row["map"]) 
	{
		echo "<tr>";
		echo "<td class=\"SpryCollapsiblePanelTableCell\">Sadly there is no map link for this venue.</td>";
		echo "</tr>";
	}
	else 
	{
		echo "<tr>";
		echo "<td class=\"SpryCollapsiblePanelTableCell\">";
		echo "<a href=\"http://maps.google.co.uk/maps?q=" . $row["map"] . "\" target=\"_blank\">Click here to see map</td>";
		echo "</tr>";
	}
}

echo "<tr>";
echo "<td class=\"SpryCollapsiblePanelTableCellHeader\">" . $row['promotor'] . "</td>";
echo "</tr>";

}
if ($PrevEvent <> "")
{	
echo "</table>";
echo "</div>";
echo "</div>";
echo "\n<script type=\"text/javascript\">\n";   
echo "<!--\n";
echo "var CollapsiblePanel" . $numResults . " = new Spry.Widget.CollapsiblePanel(\"CollapsiblePanel" . $numResults . "\")\n";
echo " //-->\n";
echo "</script>";
}

?>

If you want to display all the other gigs at the same venue on each line then that is possible but will take some fiddling.

All the best

Keith

dotbin · July 24, 2009

Got it working and it looks good!!!

Do you think it is possible with my current set up to display the names of the Venues only once, and the Promoters underneath.

So for example it would be:

Venue 1

- Promoter 1

- Promoter 2

- Promoter 3

Instead of it's current display which is:

Venue 1

- Promoter 1

Venue 1

- Promoter 2

Venue 1

- Promoter 3

What do you think? I can probably hash it out but my brain is struggling to work out the process for it!!!

kickstart · July 24, 2009

Hi

Which code are you currently using?

What you want is what the changes I gave should have produced.

All the best

Keith

dotbin · July 27, 2009

Hi Keith -

I've put a demo version of the site here: http://www.getagig.info/new/ with the code your kindly supplied!

If you search the word "the" under "venue search", you'll see that if a venue has more than 1 promoter working, then more than the venue is displayed more than once. So essentially for each promoter the venue is displayed once.

Is there a way of grouping all the promoters in a venue into 1 listing?

kickstart · July 27, 2009

Hi

Right, seen the issue. I was changing the heading on the change of event, but on this panel you are looping through venues.

Change $PrevEvent to $PrevVenue and check (and set) against $row['venue'] instead of $row['event'].

Like this:-

<?php

$queryVenue3 = "SELECT * FROM Gigs a
LEFT OUTER JOIN (SELECT DISTINCT venue, promotor FROM Gigs) b
ON a.venue = b.venue
WHERE a.venue LIKE '%$location2%'
ORDER BY a.venue";

$queryVenueResult3 = mysql_query($queryVenue3) or die(mysql_error());

$PrevEvent = "";

while ($row = mysql_fetch_array($queryVenueResult2)) 
{
if ($row['venue'] != $PrevVenue)
{
	if ($PrevEvent <> "")
	{	
		echo "</table>";
		echo "</div>";
		echo "</div>";
		echo "\n<script type=\"text/javascript\">\n";   
		echo "<!--\n";
		echo "var CollapsiblePanel" . $numResults . " = new Spry.Widget.CollapsiblePanel(\"CollapsiblePanel" . $numResults . "\")\n";
		echo " //-->\n";
		echo "</script>";
	}
	$numResults++;
	$PrevVenue = $row['venue'];
	echo "\n\n\n\n<!-- Result Number " . $numResults . ": " . $row['event'] . " in " . $row['location'] . " -->\n\n\n\n";
	echo "<div id=\"CollapsiblePanel" . $numResults . "\" class=\"CollapsiblePanel\">";
	echo "<div class=\"CollapsiblePanelTab\">";           
	echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";
	echo "<tr class=\"SpryCollapsiblePanelTableCellHeader\">";
	echo "<td>" . $row["venue"] . " - " . $row['location'] . "</td>";
	echo "<td><div align=\"right\">+</div></td>";
	echo "</tr>";
	echo "</table>";
	echo "</div>";
	echo "<div class=\"CollapsiblePanelContent\">";
	echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";

	if (!$row["map"]) 
	{
		echo "<tr>";
		echo "<td class=\"SpryCollapsiblePanelTableCell\">Sadly there is no map link for this venue.</td>";
		echo "</tr>";
	}
	else 
	{
		echo "<tr>";
		echo "<td class=\"SpryCollapsiblePanelTableCell\">";
		echo "<a href=\"http://maps.google.co.uk/maps?q=" . $row["map"] . "\" target=\"_blank\">Click here to see map</td>";
		echo "</tr>";
	}
}

echo "<tr>";
echo "<td class=\"SpryCollapsiblePanelTableCellHeader\">" . $row['promotor'] . "</td>";
echo "</tr>";

}
if ($PrevVenue <> "")
{	
echo "</table>";
echo "</div>";
echo "</div>";
echo "\n<script type=\"text/javascript\">\n";   
echo "<!--\n";
echo "var CollapsiblePanel" . $numResults . " = new Spry.Widget.CollapsiblePanel(\"CollapsiblePanel" . $numResults . "\")\n";
echo " //-->\n";
echo "</script>";
}

?>

All the best

Keith

dotbin · July 29, 2009

KEITH YOU ARE A GENIUS!

thanks for your help - finally got all the code working - to be honest i need to try and understand it now!!

thanks again

Chris

Sign In

[SOLVED] Returning MySQL results - probably simple answer.

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information