CODEWITHSUNDEEP
phpmysqlsqlinner-join
gb332
gb332

Reputation: 53

SQL query not working in PHP

I have a table in a mySQL database used for recording Fantasy Football (soccer) data. The table records how many points were earned each week as well as which players were selected as the captain and vice captain. The captain and vice captain columns link to a 'players' table via a unique ID.

e.g. 512 = David Beckham, 112 = Cristiano Ronaldo

When I run the following query in my SQL client it executes perfectly, bringing back the player names associated with the unique ID:

    select 
        g.id, 
        g.season, 
        g.year, 
        g.points_gross, 
        g.points_net, 
        g.trans_available,      
        g.trans_made, 
        g.wildcard, 
        g.month, 
        cap.name, 
        vice.name
    from 
        gameweeks g
    inner join 
        players cap
    on 
        cap.id = g.captain
    inner join 
        players vice
    on 
        vice.id = g.v_captain

    where 
        g.id in ('1','2','3','4')

However, when I execute the same query via a simple PHP page that I made, it returns the table but leaves the player names blank.

I am even echoing the SQL query to the PHP page in order to troubleshoot and it matches the SQL query run in the client perfectly.

Upvotes: 1

Views: 98

Answers (2)

Bipul Khan
Bipul Khan

Reputation: 707

  select g.id, g.season, g.year, g.points_gross, g.points_net, g.trans_available,      
    g.trans_made, g.wildcard, g.month, cap.name as captain_name, vice.name as vice_cap_name
    from gameweeks g
    inner join players cap
    on cap.id = g.captain
    inner join players vice
    on vice.id = g.v_captain
    where g.id in ('1','2','3','4')

you ware return to same value as name .

please using as then name like cap_name and vice_cap_name. hope it will work . Thanks

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You need to use alias for the selected column

cap.name as player_name

And in PHP end you need to use the index player_name to get the data.

The reason is you have 2 name selections

cap.name, vice.name

And to get the name from players you need to use alias as shown

Upvotes: 2

Related Questions