FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Chained Selects





welshsteve
Does anybody have a good chained selects script? Basically, I'm trying to design a form for a snooker website I run whereby teams can submit their results online. I have a working version that allows them to free type names, but it would be easier if when they select the home team and away team, the 7 boxes for each player (teams of 7), are automatically pre-filled with a list of players from the database behind the scenes.

The database has a table for each team.

Team1
Team2
Team3

etc.

When a team is selected from the form, say Team1, the value of that option will be Team1, and the following sql would be run.

SELECT Player FROM Team1

In the code it would be

SELECT Player FROM $hometeam

or something liek that.

This sql would then populate each of the 7 boxes for the 7 players in that team. The same would then occur for the away team.

I've search and search for this to try and get it to work using php and mysql, but haven't had much luck so far.

Can anybody help?
rvec
where did you get stuck?
When you have to fill the select boxes you can loop through the mysql results like this:

php.net wrote:
Code:
<?php
mysql_connect("localhost", "mysql_user", "mysql_password") or
    die("Could not connect: " . mysql_error());
mysql_select_db("mydb");

$result = mysql_query("SELECT id, name FROM mytable");

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    printf("ID: %s  Name: %s", $row["id"], $row["name"]);
}

mysql_free_result($result);
?>

When you get the selection back from the user you can use it in a query (don't forget to escape like this):
php.net wrote:
Code:
<?php
// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    OR die(mysql_error());

// Query
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password));
?>
welshsteve
Thanks, I will give this a try.

The examples I found all pulled data from one data table. My needs are that data gets pulled from different tables, depending on what option is selected.
Fire Boar
You shouldn't be storing each team's data in a separate table. Try this.

Table team:
teamid (primary key, int)
...

Table player:
playerid (primary key, int)
teamid (foreign key for team.teamid)
...

Then use a query like...

SELECT p.foo, p.bar FROM player p INNER JOIN team t ON t.teamid=p.teamid WHERE stuff

Here 'foo' and 'bar' are columns in the player table, and 'stuff' is any additional conditions you want. There are a couple of clauses you may not be familiar with: INNER JOIN links two tables together, and ON imposes conditions for linking. You can also use WHERE for these conditions, but this isn't optimal.

Your query is somewhat simpler however.

SELECT name FROM player WHERE teamid=$hometeam

Here $hometeam is the ID of the team. If instead you want to search by name, say instead of $hometeam being 2, it might be "Team Awesome", you'd use like this:

SELECT p.name FROM player p INNER JOIN team t ON t.teamid=p.teamid WHERE t.name=$hometeam

This is the simplest and most common example of a one-to-many relationship in a relational database (one team, many players).
albuferque
You can also use subqueries which are similar to SELECT chaining. While SELECT chaining combines SELECTs on the same level in a query, however, subqueries allow SELECTs to be embedded inside other queries. They can perform several functions, they can take the place of a constant or return a list of values for use in a comparison. For example:

SELECT name FROM employee WHERE employee_id IN ( SELECT employee_id FROM salesorder WHERE order_date = '11/20/2009' )
welshsteve
I am not going to change the table structure of my database.

I have 28 teams in our league, and each of them has a table which contains a list of all the players registered for them, with the win/loss stats for each player held in these tables. I've put a lot of work into other areas of the site to get this to work.

All I want is a two select boxes, one for the home team and one for the away team. When the home team is selected, 7 select boxes below it get pre-filled with the list of names contained in that selected team's table from the database. And then the same for the away team.
Fire Boar
welshsteve wrote:
I am not going to change the table structure of my database.

I have 28 teams in our league, and each of them has a table which contains a list of all the players registered for them, with the win/loss stats for each player held in these tables. I've put a lot of work into other areas of the site to get this to work.

All I want is a two select boxes, one for the home team and one for the away team. When the home team is selected, 7 select boxes below it get pre-filled with the list of names contained in that selected team's table from the database. And then the same for the away team.


Important bit highlighted. What you're doing is something like asking whether it would be better to get a nail into the wall using an old shoe or a glass bottle. While technically you COULD do it either way and get results, possibly even successful results, it would be so much easier if you had just gone downstairs in the first place and fetched the hammer. When you're using lots of tables for essentially the same thing the alarm bells should be ringing that there's something fundamentally wrong with the way your database is designed.

One immediate example of what is wrong: what if you need to add another team later on? Your way would require altering the database schema which should never happen in the course of a production site's normal running. Mine requires simply adding an entry to the team table (or, if you're only using one table: player, it requires you to do... absolutely nothing).

Another example: I notice that in your code you can never be sure what table you are selecting data from. This is a REALLY BAD THING! It opens up a ridiculous number of avenues for exploitation, using your SQL code against you.

A third example is like your problem here. What if you need to select data from multiple teams? This requires multiple queries (or possibly some convoluted advanced SQL that I'm not even going to attempt to work out). This is really bad for performance, as SQL queries are often the most expensive thing a PHP script does, so you should really keep these to a minimum if at all possible.

Read up on relational databases and the basics of the JOIN and WHERE clauses, and rework your code using only two tables (or one, if you don't need to store additional information about each team). It will save you so many headaches later on.
AftershockVibe
I'm afraid that Fire Boar is correct in his assessment of your design. Your database is constructed in such a way that it makes a fundamental design mistake which databases were invented to avoid. As he elaborated in his hammer simile, you're using the tool in a way it wasn't designed to perform. And not just in a small way, normalisation is Rule 0 of database design.

Rvec's approach will work, however coding for your site is unnecessarily complex.
Related topics
black - a poem (by me)
managing a bilingual site
Bluetooth
Simulating the back button
(official) Firefox & Thunderbird 1.5 Beta2 Now Available
Firefox help
how do i put my site name on the frihost index page
Selects with ease
ExplorerXP
An Example to mySQL class
Suggestion aimed @ Bondings
Javascript and Dynamic Select Boxes
Assign unique rank in html form using javascript possibly?
Help needed with chained selects
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.