drop down select help please

Gem · December 8, 2010

Hi everyone - this code is doing my head in!

You might want to take a look at the page as it will help me explain it better (www.bradleystokejudoclub.co.uk/inttest.php

Basically what I have is a database with the results from international competitions, and I am trying to build a kind of search for it.

I have 3 drop down boxes, one with player names, one with competiton and one with year.

The only one that works is the competiton. I can do competition + year, but not year by itself, and player doesnt work at all ....

this is the relevant code:

(before head)

<?php
   include("includes/dbconnect120-gem.php");
   include("includes/db_auth_bits.php");
   include("includes/db_stp.php");
if($_POST) {
if($name == 'select') {
	$sql1 = ""; 
} else {
	if(($award == 'select') && ($year == 'select')) {
		$sql1 = "r.pname_id = '$pname'";
   } else {
		$sql1 = "r.pname_id = '$pname' AND ";
	}
}

if($award == 'select') {
	$sql2 = ""; 
} else {
	if($year == 'select') {
		$sql2 = "r.comp_id = '$comp'";
   } else {
		$sql2 = "r.comp_id = '$comp' AND ";
	}
} 

if($year == 'select') {
	$sql3 = ""; 
} else {
	$sql3 = "r.year_id = '$year'";
}

if(($sql1 == "") && ($sql2 == "") && ($sql3 == "")) {
	$where = "";
}
else {
	$where = " WHERE ";
}
$sql = "select  p.pname, i.comp, m.place_name, yr.year_full
from intcomp_result r
left join playername p
on r.pname_id=p.name_id
left join intcomp i
on i.comp_id = r.comp_id
left join place m
on m.place_id = r.place_id
left join yearname yr
on r.year_id = yr.year_id 
$where $sql1 $sql2 $sql3
order by r.year_id desc, r.comp_id, r.place_id";

$search_result = mysql_query($sql);
}

   ?>

(body)

<form method="post" action="<?php echo $PHP_SELF;?>">
Name:<select name="name">
<option value="select" selected="selected" >-SHOW ALL-</option>
  <?php
$sql="select distinct r.pname_id, p.pname
from intcomp_result r
left join playername p
on r.pname_id=p.name_id
order by p.pname";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)) {
		$nameid=$row['pname_id'];
	$pname=$row['pname']; ?>
<option value="<?php echo $nameid; ?>"><?php echo $pname;?></option>

	<?php } ?>
   
</select><br />

   Competition:<select name="comp">
<option value="select" selected="selected" >-SHOW ALL-</option>
  <?php 
$sql="select * from intcomp order by comp_id asc";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)) {
		$compid=$row['comp_id'];
	$comp=$row['comp']; ?>
<option value="<?php echo $compid; ?>"><?php echo $comp;?></option>

	<?php } ?>
   
</select><br />


   Year:<select name="year">
<option value="select" selected="selected" >-SHOW ALL-</option>
  <?php 
$sql="select distinct r.year_id, y.year_full
from intcomp_result r
left join yearname y
on r.year_id=y.year_id
order by y.year_id";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)) {
		$yearid=$row['year_id'];
	$year=$row['year_full']; ?>
<option value="<?php echo $yearid; ?>"><?php echo $year;?></option>

	<?php } ?>
   
</select><br />
<input name="Submit" type="submit" class="button" tabindex="14" value="Submit" />
</form>
<?php
if(isset($search_result)) {
while($row = mysql_fetch_array($search_result)) {
	echo $row['pname'].' - '.$row['comp'].' - '.$row['year_full'].' - '.$row['place_name'].'<br />';
}
}
?>

Hope you can help! :-\

Thanks

Gem

Psycho · December 8, 2010

The only one that works is the competiton. I can do competition + year, but not year by itself, and player doesnt work at all ....

Can you be more specific please? How does it work or not work?

Gem · December 8, 2010

im not very good at explaining things ... did u take a look at the page?

ummm ...

when you select something in the dropdown boxes, itwill query the database and then display the results underneath.

when you select a competition, and leave the others SHOWALL, it displays all the results from that competition i.e. Gem - World Masters - 2010 - Gold

Pete - World Masters - 2010 - Gold

Nick - World Masters - 2008 - Silver

When you select a competition and year (i.e.world masters and 2010) - you see the 2 results

BUT

If you only select Pete, nothing shows. And if you select only 2010, nothing shows ....

Does that help?? xx

Psycho · December 8, 2010

I decided not to try and decypher your logic and instead rewote the entire script with a more logical flow. I suggest always trying to put the logic (that generates dynamic content) at the top of your page and just use PHP within the HTML to output the results of the logic.

I rewrote all of this on-the-fly without any testing, so there might be some syntax errors

<?php

    include("includes/dbconnect120-gem.php");
    include("includes/db_auth_bits.php");
    include("includes/db_stp.php");

    $search_results = '';
    if(isset($_POST))
    {
        //Generate the WHERE clause
        $WHERE_PARTS = array();
        if(isset($_POST['name']) && $_POST['name']!='')
        {
            $search_name = mysql_real_escape_string(trim($_POST['name']));
            $WHERE_PARTS[] = "r.pname_id = '$search_name'";
        }
        if(isset($_POST['comp']) && $_POST['comp']!='')
        {
            $search_comp = mysql_real_escape_string(trim($_POST['comp']));
            $WHERE_PARTS[] = "r.comp_id = '$search_comp'";
        }
        if(isset($_POST['year']) && $_POST['year']!='')
        {
            $search_year = mysql_real_escape_string(trim($_POST['year']));
            $WHERE_PARTS[] = "r.year_id = '$search_year'";
        }
        $WHERE_CLAUSE = (count($WHERE_PARTS)>0) ? 'WHERE ' . implode(' AND ', $WHERE_PARTS) : '';

        //Generate the search results
        $sql = "SELECT  p.pname, i.comp, m.place_name, yr.year_full
                FROM intcomp_result r
                LEFT JOIN playername p
                  ON r.pname_id=p.name_id
                LEFT JOIN intcomp i
                  ON i.comp_id = r.comp_id
                LEFT JOIN place m
                  ON m.place_id = r.place_id
                LEFT JOIN yearname yr
                  ON r.year_id = yr.year_id 
                {$WHERE_CLAUSE}
                ORDER BY r.year_id desc, r.comp_id, r.place_id";
        $search_result = mysql_query($sql);

        while($row = mysql_fetch_assoc($search_result))
        {
            $search_results .= "{$row['pname']} - {$row['comp']} - {$row['year_full']} - {$row['place_name']}<br />\n";
        }
    }

    //Create the options for select lists
    function createOptions($optionsQuery)
    {
        $result = mysql_query(optionsQuery);
        $options = '<option value="select" selected="" >-SHOW ALL-</option>';
        while($row=mysql_fetch_assoc($result))
        {
            $options .= "<option value=\"{$row['id']}">{$row['value']}</option>\n";
        }
    }

    $names_query = "SELECT distinct r.pname_id as id, p.pname as value
                    FROM intcomp_result r
                    LEFT JOIN playername p
                      ON r.pname_id=p.name_id
                    ORDER BY p.pname";
    $names_options = createOptions($names_query);

    $comps_query = "SELECT comp_id as id, comp as value FROM intcomp ORDER BY comp_id ASC";
    $comps_options = createOptions($comps_query);

    $years_query = "SELECT distinct r.year_id as id, y.year_full as value
                    FROM intcomp_result r
                    LEFT JOIN yearname y
                      ON r.year_id = y.year_id
                    ORDER BY y.year_id
    $years_options = createOptions($years_query);
?>
<html>
<head></head>

<body>
<form method="post" action="<?php echo $PHP_SELF;?>">

Name:
<select name="name">
  <?php echo $name_options; ?>
</select><br />

Competition:
<select name="comp">
  <?php echo $comps_options; ?>
</select><br />

Year:<select name="year">
  <?php echo $comps_options; ?>
</select><br />

<input name="Submit" type="submit" class="button" tabindex="14" value="Submit" />
</form>

<?php echo $search_results; ?>
</body>
</html>

Also, I think there might be some inneficiency in your queries but didn't take the time to really review those.

Gem · December 8, 2010

Wow - That looks pretty complicated! Anyway, I uploaded the code, fixed a couple of syntex errors but I get a HTTP500

You know where you wrote //generate where clause ... amI supposed to write something there??

Am such a noob, sorry!!

x

Psycho · December 8, 2010

Wow - That looks pretty complicated! Anyway, I uploaded the code, fixed a couple of syntex errors but I get a HTTP500

You know where you wrote //generate where clause ... amI supposed to write something there??

Am such a noob, sorry!!

x

Since I don't have your DB to test against I can't run the code to debug it. The thing is that code is NOT complicated. In fact it is more logical than what you had previously provided. It will also make it easier for you to debug.

THe comment "//Generate the search results" was just a comment to explain the following code - which you should always be doing in your code. Did you even look at the code that follows the comment? At least look at the code I provided to see if you can understand what it is doing.

Anyway, I do see a couple of errors without having to run the code. Replace the function to create the options with this

    //Create the options for select lists
    function createOptions($optionsQuery)
    {
        $result = mysql_query(optionsQuery);
        $options = '<option value="select" selected="" >-SHOW ALL-</option>';
        while($row=mysql_fetch_assoc($result))
        {
            $options .= "<option value=\"{$row['id']}\">{$row['value']}</option>\n";
        }
        return $options;
    }

Anti-Moronic · December 8, 2010

"The quality of the responses received is directly proportional to the quality of the question asked."

not always so

Sign In

drop down select help please

Recommended Posts

Gem

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Gem

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Gem

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Anti-Moronic

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information