My results table isn't showing up, any ideas why?

PHP_Idiot · December 5, 2009

Hi Freaks

The below code shows a listbox, once the user selects a value this is meant to go into the second query and then display the relavant results in the table.

The queries work in phpadmin, so I think they are ok, but the table doesn't show at all, not even just the headers!

Any help would be great

Cheers

PHP_Idiot

                  <?php
// Make a MySQL Connection
mysql_connect("localhost", "XXXX", "XXXX") or die(mysql_error());
mysql_select_db("XXXX") or die(mysql_error());
$query="SELECT DISTINCT (
Results.Date
), Venue.VenueName, Venue.VenueID
FROM Results, Venue
WHERE Results.VenueID = Venue.VenueID
ORDER BY `Results`.`Date` DESC ";

  
$result = mysql_query ($query); 
echo '<form action="" method="post">';
echo "<select name='Date','VenueName'>";
// printing the list box select command
while($nt=mysql_fetch_array($result))
        {//Array or records stored in $nt
                echo "<option ";

                 if($_POST['VenueID'] == $nt['VenueID']) echo "selected=\"selected\"";

               echo " value=\"$nt[VenueID]\">$nt[Date], $nt[VenueName], $nt[VenueID] </option>";
                /* Option values are added by looping through the array */
        }
echo "</select>";// Closing of list box



?>
                  <input type="submit" value="Go" />
                  <a href="print/<?php echo $_POST['VenueID'] ['Date'] ?>.php" target="_blank"></a></p>
          
<?php 
if (isset($_POST['VenueID']['Date']) && !empty($_POST['VenueID']['Date'])) {
//mySQL queries
mysql_connect("localhost", "XXXX", "XXXX") or die(mysql_error());
mysql_select_db("XXXXX") or die(mysql_error());
$query = "SELECT po.`Position` , p.`FirstName` , p.`LastName` , po.`Points` 
FROM `Player` p
INNER JOIN `Results` r ON p.`MembershipNo` = r.`MembershipNo` 
INNER JOIN `Venue` v ON v.`VenueID` = r.`VenueID` 
INNER JOIN `Position` po ON po.`Position` = r.`Position` 
WHERE r.`Date` = '".$_POST['Date']."'
AND v.`VenueID` = '".$_POST['VenueID']."'
ORDER BY po.`Position`"; 
$result=mysql_query($query)
	or die ("couldn't execute query");


echo <<<html

<table border="1" width="480" cellpadding="1" cellspacing="1">
<tr><td align="center"><strong>Position</strong></td>
		<td align="center"><strong>First Name</strong></td>
		<td align="center"><strong>Last Name</strong></td>
		<td align="center"><strong>Points</strong></td>
		</tr>
html;

//Now start the loop.

echo  "<h3> League Games </h3>"; 
$pos=0;


while($r = mysql_fetch_array($result)){
//and echo each new row


echo <<<html

<tr><td align="center">{$r['Position']}</td>
	<td align="center">{$r['FirstName']}</td>
	<td align="center">{$r['LastName']}</td>
	<td align="center">{$r['Points']}</td>

</tr>
html;
$pos++;	}


//And close the table.
echo "</table>";
}
?>

mrMarcus · December 5, 2009

what's going on in this line:

echo "<select name='Date','VenueName'>";

what is name='Date','VenueName'>??

PHP_Idiot · December 5, 2009

Hmm looks like thats the name of the dropdown box.

If I delete the line no box shows and the contents are just printed in one big blurb!

Although having changed it something totally meaningless, the box is still there and the page acts the same.

This leads me to think that the box should be named something and presumably that name called upon later in the script...that's all I got so far! So I'll play and see if I can work out where it's used later!

PHP_Idiot · December 5, 2009

Ok I think I tidied up the listbox name and renamed it correctly, but it didn't make any difference to the page!

Heres the updated code:

                  <?php
// Make a MySQL Connection
mysql_connect("localhost", "XXXX", "XXXX") or die(mysql_error());
mysql_select_db("XXXXX") or die(mysql_error());
$query="SELECT DISTINCT (
Results.Date
), Venue.VenueName, Venue.VenueID
FROM Results, Venue
WHERE Results.VenueID = Venue.VenueID
ORDER BY `Results`.`Date` DESC ";

  
$result = mysql_query ($query); 
echo '<form action="" method="post">';
echo "<select name='lisbox'>";
// printing the list box select command
while($nt=mysql_fetch_array($result))
        {//Array or records stored in $nt
                echo "<option ";

                 if($_POST['listbox'] == $nt['VenueID']) echo "selected=\"selected\"";

               echo " value=\"$nt[VenueID]\">$nt[Date], $nt[VenueName], $nt[VenueID] </option>";
                /* Option values are added by looping through the array */
        }
echo "</select>";// Closing of list box



?>
                  <input type="submit" value="Go" />
          
<?php 
if (isset($_POST['listbox']) && !empty($_POST['listbox'])) {//mySQL queries

$query = "SELECT po.`Position` , p.`FirstName` , p.`LastName` , po.`Points` 
FROM `Player` p
INNER JOIN `Results` r ON p.`MembershipNo` = r.`MembershipNo` 
INNER JOIN `Venue` v ON v.`VenueID` = r.`VenueID` 
INNER JOIN `Position` po ON po.`Position` = r.`Position` 
WHERE r.`Date` = '$nt[Date]'
AND v.`VenueID` = '$nt[VenueID]'
ORDER BY po.`Position`"; 
$result=mysql_query($query)
	or die ("couldn't execute query");


echo <<<html

<table border="1" width="480" cellpadding="1" cellspacing="1">
<tr><td align="center"><strong>Position</strong></td>
		<td align="center"><strong>First Name</strong></td>
		<td align="center"><strong>Last Name</strong></td>
		<td align="center"><strong>Points</strong></td>
		</tr>
html;

//Now start the loop.

echo  "<h3> League Games </h3>"; 
$pos=0;


while($r = mysql_fetch_array($result)){
//and echo each new row


echo <<<html

<tr><td align="center">{$r['Position']}</td>
	<td align="center">{$r['FirstName']}</td>
	<td align="center">{$r['LastName']}</td>
	<td align="center">{$r['Points']}</td>

</tr>
html;
$pos++;	}


//And close the table.
echo "</table>";
}
?>

PHP_Idiot · December 5, 2009

Ok I've narrowed it down, after spotting a typo.

I need to get the Date and the VenueID from the listbox selection into the query this isn't working though:

<?php 
if (isset($_POST['listbox']) && !empty($_POST['listbox'])) {//mySQL queries

$query = "SELECT po.`Position` , p.`FirstName` , p.`LastName` , po.`Points` 
FROM `Player` p
INNER JOIN `Results` r ON p.`MembershipNo` = r.`MembershipNo` 
INNER JOIN `Venue` v ON v.`VenueID` = r.`VenueID` 
INNER JOIN `Position` po ON po.`Position` = r.`Position` 
WHERE r.`Date` = '$nt[Date]'
AND v.`VenueID` = '$nt[VenueID]'
ORDER BY po.`Position`"; 
$result=mysql_query($query)
	or die ("couldn't execute query");

However, if i replace $nt[Date] and $nt[VenueID] with fixed values it prints correctly to the screen so it's these that are wrong, any ideas how I correct it to put the right variables in there?

mrMarcus · December 5, 2009

the typo being 'lisbox' instead of 'listbox'?

right now, $_POST['listbox'] contains the VenueID if an option is selected, correct? that's how i see it in the form.

you could try something so simple as to create a value containing both values (Date and VenueID), and then work with them in the query:

<?php
echo " value=\"$nt[VenueID].'_'.$nt[Date]\">$nt[Date], $nt[VenueName], $nt[VenueID] </option>";

//on to the query;

if (isset($_POST['listbox']) && !empty($_POST['listbox'])) {//mySQL queries
     $ex = explode ('_', $_POST['listbox']);

     $venueID = $ex[0];
     $date = $ex[1];

     //query here;
?>

just off the top of my head.

PHP_Idiot · December 5, 2009

Thanks for the help MrMarcus

But I'm getting this error now:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.2009-11-04' AND v.VenueID = '12.'' ORDER BY po.Position' at line 6

from this line:

echo " value=\"$nt[VenueID].'_'.$nt[Date]\">$nt[Date], $nt[VenueName], $nt[VenueID] </option>";

I've played about with it but can't fix it!

This is the code at the moment:

mysql_connect("localhost", "benn", "86gh44bn") or die(mysql_error());
mysql_select_db("gbpokerclub_benn") or die(mysql_error());
$query="SELECT DISTINCT (Results.Date), Venue.VenueName, Venue.VenueID FROM Results, Venue WHERE Results.VenueID = Venue.VenueID ORDER BY Results.Date DESC ";
   
$result = mysql_query ($query); 
echo '<form action="" method="post">';
echo "<select name='listbox'>";
// printing the list box select command
while($nt=mysql_fetch_array($result))
        {//Array or records stored in $nt
                echo "<option ";

                 if($_POST['listbox'] == $nt['VenueID']) echo "selected=\"selected\"";

echo " value=\"$nt[VenueID].'_'.$nt[Date]\">$nt[Date], $nt[VenueName], $nt[VenueID] </option>";
                /* Option values are added by looping through the array */
        }
echo "</select>";// Closing of list box



?>
                  <input type="submit" value="Go" />
          
<?php

//on to the query;

if (isset($_POST['listbox']) && !empty($_POST['listbox'])) {//mySQL queries
     $ex = explode ('_', $_POST['listbox']);

     $venueID = $ex[0];
     $date = $ex[1];

     //query here;



$query2 = "SELECT po.Position , p.FirstName , p.LastName , po.Points 
FROM Player p
INNER JOIN Results r ON p.MembershipNo = r.MembershipNo 
INNER JOIN Venue v ON v.VenueID = r.VenueID
INNER JOIN Position po ON po.Position = r.Position 
WHERE r.Date = '$date'
AND v.VenueID = '$venueID'
ORDER BY po.Position"; 
$result2=mysql_query($query2)
	or die(mysql_error());

mrMarcus · December 5, 2009

change:

echo " value=\"$nt[VenueID].'_'.$nt[Date]\">$nt[Date], $nt[VenueName], $nt[VenueID] </option>";

to:

echo " value=\"{$nt[VenueID]}_{$nt[Date]}\">{$nt[Date]}, {$nt[VenueName]}, {$nt[VenueID]} </option>";

PHP_Idiot · December 5, 2009

Woohoo MrMarcus Sir, you rock!

Thats got it working right at last, thanks a lot.

On last quick question, once the button is clicked and the table is populated, the selectionbox returns to it's default value, is there a way I can get it to stay on the last selected value?

Thanks a million

PHP_Idiot

mrMarcus · December 5, 2009

for that you could setup an array holding the values/keys of the form values in question. as i only see one <option> box, i will create a very quick example, and you can then implement it into your code accordingly:

<?php
echo '<select name="listbox">';
while ($nt = mysql_fetch_array ($result))
{
echo '<option value="'.$nt['VenueID'].'_'.$nt['Date'].'"'.(($_POST['listbox'] == $nt['VenueID'].'_'.$nt['Date']) ? ' selected' : '').' />'.$nt['Date'], $nt['VenueName'], $nt['VenueID'].'</option>';
}
echo '</select>';
?>

try that .. my attention is currently torn between the Dallas/Edmonton game and the compooter, so i hope it works.

PHP_Idiot · December 5, 2009

Absolutely fantastic

I made a tiny alteration just to give me a comma and space between the date and venuename, and removed the venueID as that isn't actually needed in the listbox, so now it's:

	echo '<option value="'.$nt['VenueID'].'_'.$nt['Date'].'"'.(($_POST['listbox'] == $nt['VenueID'].'_'.$nt['Date']) ? ' selected' : '').' />'.$nt['Date'].', '. $nt['VenueName'].'</option>';

And is perfect

Thanks so much for all the help, it's really really appreciated

Cheers

PHP_Idiot

mrMarcus · December 5, 2009

good stuff. glad i could help.

Sign In

My results table isn't showing up, any ideas why?

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

Join the conversation

Important Information