Use data from one sql query to achieve results from another

Question

I want to pretty much display the number of rounds for a NBA season. I have a database in mysql where the rounds are all stored. There are 2 different table which cannot be joined. One of the tables contains the CURRENT ROUND, which is pretty much what round is happening right now and the other contains all the rounds. When a user goes to select which rounds results they wanna see, they'll click on a select list and taken to that round.

What i want, i want to be able to make it so, the user can only view the present or past round since viewing future results isnt possible. Since they are both on different tables, i thought by getting the current round from a query

$curr = mysql_query("SELECT current
                    FROM seasons 
                    WHERE year='2014'");
$result = mysql_query($curr);

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

$sql = mysql_query("SELECT allrounds
                FROM fixtures
                WHERE allrounds<= '$row['current']'");}

and kinda use it as a variable for the other query, this doesnt seem to be working. This is my select list.

Select a round:
<?php
echo "<select name = 'rounds' id = 'rounds'>\n";
echo "<option value=>Select...</option>\n";
while ($data = mysql_fetch_array($sql, MYSQL_ASSOC))
{echo "<option value='{$data['allrounds']}'>{$data['allrounds']}</option>\n";}
echo "</select>\n";
?>

Is there any way to fix this?

score 2 · Accepted Answer · answered May 29 '14 at 17:45

2

Try this using subquery.

SELECT allrounds
                FROM fixtures
                WHERE allrounds in (SELECT current
                    FROM seasons 
                    WHERE year='2014')

Per your post

SELECT allrounds
                FROM fixtures
                WHERE allrounds <= (SELECT current
                    FROM seasons 
                    WHERE year='2014' 
                    ORDER BY current LIMIT 1)

See the LIMIT 1 at end; it's to make sure that only one value gets returned since you are trying to do a comparison using <=

answered May 29 '14 at 17:45

Rahul

76,197
13
71
125

i understand how that works but my select doesnt seem to get populated – roro May 29 '14 at 17:51
@roro, not sure I understood your comment. please clarify. again, don't ask something related to PHP. sole reason I have answered cause this was related to SQL. – Rahul May 29 '14 at 17:53
it isnt populating my select to the round numbers, dont theres anything wrong with my php though – roro May 29 '14 at 17:56
@roro, I don't see anything wrong with the SQL. You might want to check your PHP code though; which to my knowledge is altogether a separate question and as such should be posted separately. – Rahul May 29 '14 at 17:58
is there any way to make current round stay at top so its selected by default. Say if current round is, round: 4, the list should like like 4 , 1 , 2 , 3?? @Rahul – roro May 29 '14 at 18:17
yeah that didnt work as i tried that, thats why i asked @Rahul – roro May 29 '14 at 18:24
@roro, ahh!! crap; I didn't mean that. you need to order by the outer query. In second query of my answer, add a order by at last like `order by allrounds desc` – Rahul May 29 '14 at 18:36

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

I don't know PHP but You seem to be wrongfully mixing PHP and MySQL code. You might be interested in Subqueries. Check this question: MySQL Nested Select Query?

Hope I helped. :)

edited May 23 '17 at 11:57

Community

1
1

answered May 29 '14 at 17:44

helluin

50
5

Please do not post "link-only" answers. Add some code and explain it such that future visitors to SO will understand why your solution is correct and how to use that solution. – Jay Blanchard May 29 '14 at 17:47

Use data from one sql query to achieve results from another

2 Answers2