Selecting only if there is a MySQL entry

[bululu]

Hi PHP Experts, I need some help.

I have two tables as follows

 

names table

id      name       man_no
1       Alex       12340
2       Anne       12341
3       Ben        12342
4       Jude       12343
5       Carlos     12344
6       Goofy      12345

scores table

id      score       man_no
1        12         12340
2        5          12341
3        0          12342

1st query

$query = "SELECT * FROM names'";
$result = mysqli_query($dbc, $query);
($row = mysqli_fetch_array($result));
$name = $row["name"];
$mat_no = $row["man_no"];

2nd query

$query = "SELECT * FROM scores WHERE man_no='$man_no'";
$result = mysqli_query($dbc, $query);
($row = mysqli_fetch_array($result));
$man_no = $row["man_no"];

Then

var_dump($score);

gives me

string '12' (length=2)
string '5' (length=1)
string '0' (length=1)
null
null
null

Now, here is my question:

 

The Names table will always have people who do not have scores as the scores are added when available. The first query gets the man numbers and using the man numbers, queries for each man number's associated score, returning nill where a score is unavailable. What I want is to get a result set that only incldes scores available in mysql without the null values, a way to avoid null values in the result set. I am still learning joints so I do ask that your help deals with individual queries as I am not yet at a I can use joins. Is it possible to restrict mysql results to only when an entry is available in MySQL.

 

In the above example, is there a clause I can add to the query to only return results where a value was found?

 

Or may be killing the null values after the query? I could not find a way to kill them!

 

My aim is to have:

string '12' (length=2)
string '5' (length=1)
string '0' (length=1)

[Ch0cu3r]

This is exactly what joins are for. Running a query on a result of another query is not very efficient.

 

The following query does what you are trying to do

SELECT n.id, n.name, n.man_no,                     # select these fields from names table
       s.score                                     # select these fields from score table
FROM names n                                       # alias names table to the letter n
LEFT JOIN scores s                                 # join scores table and alias to letter s
     ON n.man_no = s.man_no                        # get the scores where the man_no matches
WHERE s.score >= 0                                 # filter results where score is greater than zero


# Result
+----+------+--------+-------+
| id | name | man_no | score |
+----+------+--------+-------+
| 1  | Alex | 12340  | 12    |
+----+------+--------+-------+
| 2  | Anne | 12341  | 5     |
+----+------+--------+-------+
| 3  | Ben  | 12342  | 0     |
+----+------+--------+-------+

But to answer your question all you need to do is check to see if the second query returned result

$query = "SELECT * FROM names'";
$result = mysqli_query($dbc, $query);
while($row = mysqli_fetch_array($result))
{
    $name = $row["name"];
    $man_no = $row["man_no"];

    $query2 = "SELECT score FROM scores WHERE man_no='$man_no'";
    $result2 = mysqli_query($dbc, $query2);
    while($row2 = mysqli_fetch_array($result))
    {
        if(mysqli_num_rows($result2) > 0)
        {
            $score = $row2['score'];
            echo "$name - $man_no - $score<br />";
        }
    }
}

Edited January 5, 2014 by Ch0cu3r

[Ch0cu3r]

 

But to answer your question all you need to do is check to see if the second query returned result

Swap the while loop and if statement round for the second query I added them in the wrong order.

Edited January 5, 2014 by Ch0cu3r

[bululu]

 

@Ch0cu3r Thanks very much for your kind contribution. I am going to be trying the join, and if I fail will look at the two (2) query version as I am not yet comfortable with joins.

 

[kicken]

Use INNER JOIN not LEFT JOIN when you want to exclude rows with missing scores.

 

SELECT n.name, s.score 
FROM names n 
INNER JOIN scores s ON n.man_no=s.man_no
WHERE
   s.score > 0