Displaying Selected Table Data from MYSQL using ? in URL

jason.lafferty · #1 (**permalink**) 01-18-04, 02:33 PM

Hi,

Complete Newbie to this and been tearing my hair out for a few days trying to do something.

I have a new mysql db and have created a table. I've managed to take a simpler version of what's now below and expand it to go to my database and pull out ALL records in the database and put them into a nice table displayed on screen.

A little background, the database will be used to store results of 52 contests, the code I have here is using a WHERE statement to pick out 1 contest and this works fine, however, what I want to be able to do is use something like a $_GET variable (I think) here and supply a blablabla.php?contest=xxxx to the URL bar.

I've tried lots of boards and lots of examples but everything I try ends up in a Parse error.

Can someone please look at this code and come up with a suggestion to allow me to take out the hard coded WHERE statement and supply the ?contest = xxxx to the browser. The user will select the appropriate contest from a ComboBox and this will supply the ?Contest=xxx to the browser.

I'm using phpmyadmin and used that to help me generate the SQL, hence the 4 lines in case anyone is wondering. My host has PHP v 4.3.2 if that helps.

Thanks very much in advance for any help.

Jason

***** CODE *****

<html>
<body>


<?php
$databaseName = "*****";
$dbconnection = mysql_connect("*****", "*****", "*****") or die ('Cannot Connect To Results Databasebecause: ' . mysql_error());

mysql_select_db($databaseName,$dbconnection) or die ('Cannot use Results Database' . mysql_error());

$sql = 'SELECT * ';
$sql .= 'FROM `2003results` ';
$sql .= 'WHERE Grade = "Nov" AND Contest = \'Gourock\' ';
$sql .= 'ORDER BY grade, 'final place' LIMIT 0, 50';

$result = mysql_query($sql,$dbconnection);
$tablecolour = '#FFCCFF';

printf("Contest: %s \n", mysql_result($result,0,"Contest"));
printf("Date: %s \n", mysql_result($result,0,"Date"));

printf ("");
printf("Grade: %s \n", mysql_result($result,0,"Grade"));
printf("Piping Judge 1: %s \n", mysql_result($result,0,"Piping 1"));
printf("Piping Judge 2: %s \n", mysql_result($result,0,"Piping 2"));
printf("Ensemble Judge: %s \n", mysql_result($result,0,"Ensemble"));
printf("Drumming Judge: %s \n", mysql_result($result,0,"Drumming"));

if ($myrow = mysql_fetch_array($result))
{ echo "<table border=1>\n";
echo "<tr><td BGCOLOR=$tablecolour>Band</td><td align='center' BGCOLOR=$tablecolour>Piping 1</td><td align='center' BGCOLOR=$tablecolour>Piping 2</td><td align='center' BGCOLOR=$tablecolour>Piping Place</td><td align='center' BGCOLOR=$tablecolour>Drumming</td><td align='center' BGCOLOR=$tablecolour>Ensemble</td><td align='center' BGCOLOR=$tablecolour>Final Place</td><td align='center' BGCOLOR=$tablecolour>Position</td></tr>\n";

do { printf("<tr><td>%s</td>
<td align='center'>%s</td>
<td align='center'>%s</td>
<td align='center'>%s</td>
<td align='center'>%s</td>
<td align='center'>%s</td>
<td align='center'>%s</td>
<td align='center' BGCOLOR=$tablecolour>%s</td>
</tr>\n", $myrow["Band Name"], $myrow["Piping 1 Place"], $myrow["Piping 2 Place"], $myrow["Piping Place"], $myrow["Drumming Place"], $myrow["Ensemble Place"], $myrow["Final Place"], $myrow["Position"]); } while ($myrow = mysql_fetch_array($result)); echo "</table>\n"; }

else { echo "Sorry, no records were found!"; }

?>

</body>
</html>

***** END CODE *****

NeverMind · #2 (**permalink**) 01-18-04, 06:59 PM

ok ..
your code is really difficult to read so I will just explain what to do ..
all you need is to have a link like this :

PHP Code:


			
echo'<a href="page.php?contest=' .$var. '&grade=' .$grade. '">Click</a>';

and yes, you write that links just like that and pass the value of $var and $grade as you like from where ever you like ..
and then this page.php should look like this :

PHP Code:


			
$contest=$_GET['contest'];

$grade=$_GET['grade'];



$check=mysql_query("SELECT * FROM `2003results` WHERE Grade = '$grade' AND Contest = '$contest' ORDER BY grade, final place LIMIT 0, 50");

this is the concept to achive what you want ...

jason.lafferty · #3 (**permalink**) 01-19-04, 11:13 AM

Hey Nevermind,

Just wanted to say thanks very much. got it working now, albeit, another little tweak required to what you posted, but thanks got me going in the right direction.

Cheers

Jason

jason.lafferty · #4 (**permalink**) 01-20-04, 06:55 AM

Hi Nevermind (hope you're reading this)

As I said I got your suggestion working with a slight tweak.

I'm now trying to progress to another step where now I want to pull out all the contests a band has competed in in a season.

I've got it grabbing by ?Band=xxx as per your suggestion but found that I'm always one row short in what it pulls back. I've added a counter $num_rows and that counts correct but my table is always missing the first entry that would appear.

I read on another post you answered something about calling twice and not printing first row, is that what I've done wrong ?

PHP Code:


			
<?php 

$databaseName = "rspba_allresults";

$dbconnection = mysql_connect("*****", "*****", "*****") or die ('Cannot Connect To Results Databasebecause: ' . mysql_error());



mysql_select_db($databaseName,$dbconnection) or die ('Cannot use Results Database' . mysql_error());





$Band=$_GET['Band']; 



// Pull out results for all contests per band

$sql = "SELECT * FROM 2003results WHERE Band = '$Band'";

$sql .= 'ORDER BY DATE, Grade DESC LIMIT 0, 30'; 



$result = mysql_query($sql,$dbconnection);

$num_rows = mysql_num_rows($result);

$tablecolour = '#FFCCFF'; 



echo'<a href="http://www.rspba.org/members/Results/2003Results/2003results.html" TARGET="_parent">Return To Contest Picker<br><br></a>';



printf("<b>Band:</b> %s<br>\n", mysql_result($result,0,"Band"));

printf("<b>No of Contests Entries Completed in all Grades:</b> %s<br>\n", $num_rows);





if ($myrow = mysql_fetch_array($result)) 



{ 

echo "<table border=1>\n"; 

echo "<tr><td BGCOLOR=$tablecolour><b><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">Contest</td></b></font>

<td align='center' BGCOLOR=$tablecolour><b><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">Date</td></b></font>

<td align='center' BGCOLOR=$tablecolour><b><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">Grade</td></b></font>

<td align='center' BGCOLOR=$tablecolour><b><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">Piping 1</td></b></font>

<td align='center' BGCOLOR=$tablecolour><b><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">Piping 2</td></b></font>

<td align='center' BGCOLOR=$tablecolour><b><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">Piping Place</td></b></font>

<td align='center' BGCOLOR=$tablecolour><b><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">Drumming</td></b></font>

<td align='center' BGCOLOR=$tablecolour><b><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">Ensemble</td></b></font>

<td align='center' BGCOLOR=$tablecolour><b><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">Final Place</td></b></font>

<td align='center' BGCOLOR=$tablecolour><b><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">Position</td></b></font></tr>\n"; 



do { printf("<tr><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">%s</font></td>

<td align='center'><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">%s</font></td>

<td align='center'><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">%s</font></td>

<td align='center'><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">%s</font></td>

<td align='center'><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">%s</font></td>

<td align='center'><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">%s</font></td>

<td align='center'><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">%s</font></td>

<td align='center'><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">%s</font></td>

<td align='center'><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">%s</font></td>

<td align='center' BGCOLOR=$tablecolour><b><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">%s</font></b></td>

</tr>\n", $myrow["Contest"],$myrow["Date"], $myrow["Grade"], $myrow["Piping 1 Place"], $myrow["Piping 2 Place"], $myrow["Piping Place"], $myrow["Drumming Place"], $myrow["Ensemble Place"], $myrow["Final Place"], $myrow["Position"]); 



} 



while ($myrow = mysql_fetch_array($result)); echo "</table>\n"; } 



else { echo "Sorry, no records were found!"; } 





// End Code Table Should Display



?>

Thanks for any advice.

Jason

jason.lafferty · #5 (**permalink**) 01-21-04, 11:28 AM

Figured it out (must be getting there slowly)

printf("Band: %s \n", mysql_result($result,0,"Band"));

grabbing the 1st result

just changed to :-

printf("Band: %s \n", $Band));

Working fine now.